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?

Zencoder: Zenflow

Show HN: I Replaced Vector DBs with Optimal Transport (Open Source Project))

Brooks on the System 360 and adoption of the 8 bit byte [video]

AgentCraft: RTS for AI Agents

Show HN: Travel itinerary manager (passion project)

Archaeologists find the oldest-known shell beads (2021)

Spirit of ThinkPad

Reverse Engineering the ESP32-C3 Wi-Fi Drivers for Static Worst-Case Analysis

FragCut – AI that turns gaming streams into viral TikTok/Shorts clips in minutes

Chat, Save, and Blog

Aesthetics Bento microsites with built-in analytics

What the future holds for AI – from the people shaping it

Disenshittify or die! How hackers can seize the means of computation (2024)

'He's an Idiot': Musk and Ryanair's O'Leary Trade Insults in Starlink Wi-Fi Row

How to Use Your Claude Code Pro Subscription in Docker

Verbalized Sampling: How to Mitigate Mode Collapse and Unlock LLM Diversity

Trump 25% tariff on European allies until Denmark sells Greenland to US

SFTP Still Delivers the Goods

Show HN: Figma-use – CLI to control Figma for AI agents

Tunnl.gg: Expose Localhost to the Internet

What were books like in ancient Greece and Rome?

AIVO Standard Operational AI Reliance Observation Protocol

Is the World Random?

Show HN: 30min video analysis for $0.003 via frame-tiling and Vision API

300X fast clustering with rust-louvain for nodes

Quantum Name Service (QNS)- Path to Web5

Show HN: vr.dev – simple 3D/VR/XR portfolio and links (Meta hit hard this week)

Shackleton and the Endurance Expedition: Photos from the 1915 Disastrous Journey

Show HN: Task Orchestrator – Production Safety for Claude Code Agents

Model is intended for use particularly for language learning