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

1•LawZiL•1y 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•1y 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•1y 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•1y 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•1y ago

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

Democrats and Republicans agree: AI is scary

Mathematics: Its Content, Methods and Meaning

Ford execs say they made a mistake when they replaced human engineers with AI

We measured whether AI obeys architecture rules. Even Opus ignored them 60%

Show HN: OpenClaw Launch – deploy a managed OpenClaw AI agent in 30s

CraftsmanSHIP. Not CraftsmanSHIT

LFM2 VL WebGPU

Qwen3.5 WebGPU

Show HN: An LLM that designs M5Stack hardware builds

Explore the Leading Global Data Center Database

AirPlay for Google (Android) TV Chromecast

Emergent Geometry from the IKKT Matrix Model: Convergence to S^4

What Is an AI Intelligence Layer for Business Data?

Voxel Collision in SuperSplat

Apple asks Trump admin to approve Chinese RAM after product price increases

Blue print to let machines think like humans

Sony's State of Play Showed That Every Publisher Is Terrified of GTA 6

Ping pong: A game that requires a C compiler to play

IBM MCGA Gate Array Reverse Engineering

Testing 67 Models: Combining LLMs Rarely Beats the Best Single Model

Dan Petersen: Is this an worse time for a math career?

Apple Loses Another Top Executive to OpenAI

Is Germany looking again at coal-powered electricity?

'Fingerprints' of black hole's event horizon detected for first time

5120x2160 100Hz Android Desktop Mode on Razor Fold

AI glasses help students cheat in exams – test-obsessed Asia is ground zero

Certainty Volatility Theory: Stable Uncertainty vs. Fluctuating Certainty

Everything* – An interactive voyage through all orders of magnitude

Lore: Next-generation open source version control

Gambling disorder cases skyrockets in states where sports betting is legal