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

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

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

How the Mind Changes

Google employees call for military limits on AI amid Iran strikes

Google AI previews helped me in Iran's internet shutdown of 2025

Motorola GrapheneOS devices will be bootloader unlockable/relockable

Developer Certificate of Origin and AI is a no-go

Wait what, you can't use a *code* editor when you're under 18 now?

Show HN: PreflightAPI – US airports, weather, NOTAMs and more via one API

Show HN: Formualizer – Arrow-backed spreadsheet engine, 320 functions,PyO3+WASM

Left-Handers Are More Competitive Than Right-Handers

The Markless Document Markup Standard

People Really Are More Likely to Commit Crimes After a Cancer Diagnosis

Show HN: Restless – a CLI that discovers and maps APIs automatically

Four months of Ruby Central moving Ruby backward

Trump Worries Iran's Leaders May Be Just 'As Bad' After War

Anatomy of a Web3 Supply Chain Attack

Windows 98 Disk Defrag Simulator

3M Canadian Adults Taking GLP-1 Drugs, Reshaping Eating and Spending Habits

Claude's Cycles [pdf]

Haptics: Tactile Feedback for the Mobile Web

Offshore Wind Turbine Will House a Data Center Underwater

Intel appoints noted technologist Dr. Craig Barratt as Board achair

OpenAI is developing alternative to Microsoft's GitHub

Scaling up enhanced rock weathering for equitable climate change mitigation

Sam Altman says OpenAI is renegotiating Pentagon 'opportunistic and sloppy' deal

Show HN: I built a LLM human rights evaluator for HN (content vs. site behavior)

The Supreme Court doesn't care if you want to copyright your AI-generated art

Google Chrome switches to two-week release cycle

ChatGPT Health 'under-triaged' half of medical emergencies in a new study

Universal-3 Pro Streaming

Show HN: Dracula-AI – A lightweight, async SQLite-backed Gemini wrapper