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

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

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

Sometimes, the quiet achievements are the most important ones

Ask HN: Change my mind) should AI coding conversations be append-only?

Oracle stock on pace for worst quarter since 2001, AI concerns

The Secret Service Agent Haunted by His Failure to Save JFK

Cursed circuits #4: PLL frequency multiplier

Show HN: A small embeddable and hackable Lisp-2 interpreter in C

Elevator Wiki: wiki about elevators and their related topics

Star Guage

Resolve merge conflicts with Claude Code

Tixl: Open-source realtime motion graphics

Show HN: An authority gate for AI-generated customer communication

MongoBleed

Gh-yule-log: GitHubCLI extension turns your terminal into an animated Yule log

BitVault – A time-delayed multisig Wallet Solution delivering utmost security

The Louvre museum's collection database, updated daily

Show HN: StegCore – a decision boundary for AI systems (truth ≠ permission)

Ask HN: How to gain a solid understanding of IMUs?

The AI Reality Check: Deconstructing the 2025 Stack Overflow Developer Survey

Vanslist – No middlemen. No fees. Just people

Do you believe in society's lies?

Why Everyone Loves Japan

The 2m Peak

The Misfit Tree

Leaker Who Apple Is Suing Says 'Screw It,' Here's the Foldable iPhone Early

I Killed Color on My Phone. The Result Shocked Me

Konami Code

Laid Off After 25 Years in Tech [video]

Affiliarena Kickstart Challenge – Mehr Verdienen ALS Online Marketer

Chess960v2 – the first championship has officially started

Metastable Failures and Interactions Between Systems