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

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

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

Some first thoughts about live immersive basketball

Where's the $100k iPhone?

MIT Non-AI License

Show HN: Understand the Picture of the Day

Haraltd – A cross-platform Bluetooth daemon with a JSON-based RPC

The Stick in the Stream

MAKERphone 2: first modular DIY phone, no soldering

Sodium-ion battery cells near lithium-ion cost parity, set to get cheaper

OpenAI to Buy Pinterest? Strategic Analysis

Vajra BM25 is a fast BM25 implementation in Python

Show HN: A website to save moments that remind you of someone

Google and chatbot startup Character move to settle teen suicide lawsuits

Agent skills: what can go wrong?

You probably don't need Oh My Zsh

Fix Your Robots.txt or Your Site Disappears from Google

Show HN: VoiceBrainDump – voice-first idea capture, single HTML file, offline

Show HN: Focus timer that turns hours into assets

Kazakhstan Launches First Institute of Transport Sciences and Technologies

AI Flatters with Fidelity

Lidify: Self-hosted, on-demand audio streaming platform like Spotify

Show HN: Rank up your local business on Google Maps

The world has too much oil – Will companies want Venezuela's?

Elon Musk's Grok Has Friends in High Places: US Patent Office chief AI officer

Checks and Balances Are Dead

M2.1: Multilingual and Multi-Task Coding with Strong Generalization

Character.ai and Google agree to settle lawsuits over teen suicides

Demystifying Evals for AI Agents

Best Practices for Coding with Agents

Microsoft revealed as company behind controversial data center proposal in MI

A man powers his home for 8 years using 1,000 recycled laptop batteries