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?

Kinetic typography: the what, why, and how

Symposia AI

Solving CartPole in 8 Weights

Magical Realism: "Northern Exposure" 25 Years Later (2015)

Show HN: Wyndup – share a live countdown with your podcast guest

Elastic Cloud on Kubernetes, simplified: zone awareness, restarts, and mTLS

Jane Street's approach to AI adoption throughout their SDLC [video]

Brovan: Binary user-mode emulator for x86_64

WikiProject Editor Retention

A Compression Tool for LLM Reads. Est. 60-95% Fewer Tokens

North America's largest commuter rail system faces a potential shutdown

Random.website

Humanoid robots won't surprise us when they arrive

Thorchain halts trading after $10M cross-chain exploitRUNE token drops 12%

Engineer creates starwars inspired air bike

How to bypass Anti-Bots in 2026

Python by Example Using Cloudflare Dynamic Workers

Lost in Translation: Text Message Spoofing via Email [pdf]

New quantum algorithm solves "impossible" materials problem in seconds

What Is Rails-Way?

SpaceX targets June 11 IPO pricing, picks Nasdaq for historic market debut

Auto Rebaser v2 – browser-side GitHub PR housekeeping, now multi-account

Legacy Update for Windows 8.1 for x64-based Systems

NextEra and Dominion in talks over tie-up to create $400B US utility giant

Is China using fentanyl as a weapon against the United States?

China's unfinished buildings have created stranded assets with substantial costs

Gaps in AI Development Closing 2.1B in Pain

Volkswagen Detects CI Server Tests and Ensures They Pass (2015)

'No Way to Prevent This,' Says Only Package Manager Where This Regularly Happens

Picollo: Modern HDR histogram and PMU counters for .NET