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?

'Humanizer' tool can erase signs of AI-written text

I Built the Ωmega Agent

NPM 12 makes install scripts, Git, and remote-url dependencies opt-in by default

Cancer cases worldwide are expected to soar in the coming decades

Waymo Calls Police on Teenage Riders Who Were Drinking

Top Legal Adviser to Joint Chiefs Is Stepping Down a Year Before Completing Term

Practical Lessons from Reinforcement Learning Post Training Experiments [pdf]

China Cuts 12,200 University Programs, Replaces Many with AI Degrees

Non-tech background, choosing between using CLaudeCode and CodeX

Texans deserve more then a Ken Paxton snake game

Show HN: Figment – An AI that I made my friends talk to for 2 weeks

Burner Email, Temp email Box lasting 24 hours

Llama 2 LLM on DOS (2025)

Rutazo – Argentina's freight marketplace, built solo in 4 months

Citicorp Center Engineering Crisis

What's slowing down the AI buildout

Researchers uncover possible cause of muscle pain from cholesterol medication

Show HN: Free and OSS way to extend client-facing functionality of Linear

AI could keep poor countries poor

Morph Chess

Looksmaxxing influencer who fled police found dead

Character AI Alternative for Roleplay

The library of lost journals 603 pages novel fiction book

DDB: Source-Level Interactive Debugging for Distributed Applications

Ask HN: How to recover from thin content or de-indexing penalty on Google

Meta Patents AI Device That Tracks Your Emotions, Watches You Take Your Meds

Counterfeit Air-Bags Are Killing U.S. Drivers–and the Government Can't Stop It

Ergo: Long Form Philosophy Lectures

Neuromancer (TV Series) – IMDB

Two teens learn the hard way not to do toy gun drive-bys from a Waymo