Ask HN: How can I load test PostgreSQL but avoid changing actual data?

1•LawZiL•12mo ago

I'm running a load test on my Node.js application and want to simulate realistic write-heavy scenarios to stress test the system — especially the PostgreSQL database.

There's a part of the code that performs UPDATE queries, but during load testing, I don't want these updates to actually change the state of the database. However, I still want the database to experience the full impact of the update — query planning, locking, I/O, WAL writes, etc. — so I can measure how it affects performance under peak load.

Is there a best-practice approach to achieve this?

So far, the most viable option I’ve found is to wrap the UPDATE queries in a transaction and then roll it back — that way, the queries still execute, but the state doesn’t change. Is this the right way to go? Does rollback introduce any performance overhead that would skew my results? Are there better or more realistic alternatives that simulate the full write path without persisting changes?

Thanks in advance!

Comments

sargstuff•12mo ago

pgbench[2] / pgreplay-go[0] / python locust[1]

very old school approach:

Misuse sql VIEW[3][4] to generate a temporary parallel table setup. Testing database separate from production database would be 'better', IMHO.

-----

[0] pgreplay-go : https://github.com/gocardless/pgreplay-go

[1] python locust : https://miguel-codes.medium.com/unconventional-load-testing-...

[2] pgbench load testing :

   a) https://medium.com/@c.ucanefe/pgbench-load-test-166bdfb5c75a

   b) https://neon.tech/blog/autoscaling-in-action-postgres-load-testing-with-pgbench

   c) https://hackernoon.com/how-to-create-and-load-test-data-in-postgresql-i41e353m

----------------------------

[3] : a) https://hashrocket.com/blog/posts/materialized-view-strategi...

      b) https://tech.jonathangardner.net/wiki/PostgreSQL/Materialized_Views

      c) https://stackoverflow.com/questions/77603705/what-is-the-use-of-materialized-view-in-postgresql

[4] : https://en.wikipedia.org/wiki/View_(SQL)

LawZiL•12mo ago

but again, how can i perform the query, but not actually change the state, i really need the state to keep untouched, but be able to perform the query itself.

MatthiasPortzel•12mo ago

> Does rollback introduce any performance overhead that would skew my results?

I would expect it to be the other way around—since the transactions are rolled back and not committed, they would have significantly less performance impact. But I’m working from an academic model of the database.

apothegm•12mo ago

Load test against a clone instead of a DB whose contents you care about?

A spatial canvas of every public US Government UAP record

OpenCS2 – 5k hours recording of Counter Strike for world model training

Stupidly Simple SVG Sparklines

Show HN: Promptcellar – capture every Claude Code prompt as JSONL in your repo

What if your AI could buy you a car?

The US Is Winning the AI Race

Styled-Components v7 Alpha

California mayor resigns, admitting to being an agent for China

Open-source AI modding tool for Rimworld (and soon Factorio)

California Mayor Resigns, Admitting to Being an Agent for China

The wildlife sanctuary you can visit from anywhere [video]

Show HN: Hashiverse, an open-source decentralized social network in Rust

Wikipedia's bot traffic to overtake human readers by late 2026 (original data)

Tracking historical progress against slavery and forced labor

Coders in 2030 be like: (Rick Rubin, Vibe Producer)

Conway's Law Teaches a Grim Lesson About AI in Product Development

AI agents make small companies bigger

Show HN: RedactScreen – Share your screen, not secrets

Show HN: Rotunda - A Browser Built for Agents

Publishers: Google's eBook Ad "Ban" Blocked Legitimate Sellers, Not Pirates

The syrup of crime bears sticky fruit

What Inference-Platform Benchmark Posts Leave Out

Fine-Tuning TranslateGemma-4B for Better Welsh Translations on an H200 GPU

Show HN: A "Git history" for Twitter/X profiles

WordStar

Reliance on Middle East Oil and Gas Supplies by Country

Linguistic workaround allows Marco Rubio to travel to China

Social market liberalism has no political home in India

Fragnesia: Linux kernel local privilege escalation via ESP-in-TCP

Finnish marketplace for 'Environmentally responsible actions'