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

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

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

Show HN: Figma alternative where AI works with vector primitives, not code

Is Email Confidential in Transit Yet?

Does Point Cloud Boost Spatial Reasoning of Large Language Models?

Chip Startup Aims to Shatter AI's Dreaded Memory Wall

Mild dehydration affects mood in healthy young women

Scaling Test-Time Compute for Agentic Coding

Show HN: Rocky – Rust SQL engine with branches, replay, column lineage

Workday's Last Workday

Big AI cluster little power the 8x Nvidia GB10 cluster

The Monetization of Spam

The Making of Digital Identity: Mobile Revolution and the Surveillance Machine

The Pragmatic Prompter

Show HN: Keychase – A zero-config, offline Python secret scanner

GPT-5.5 prompt for Codex tries to make it not talk about goblins

I Ditched the iCloud Keychain

Scammers use Gmail dot alias trick to spoof Robinhood in phishing scam

OpenAI's revenue, growth estimates fall short as company races toward IPO

Show HN: Integrations gateway for agents with 2FA for destructive ops (OSS)

U.S. is 'being humiliated by Iran,' says Germany's Merz

Free retirement tax calculators – SS tax torpedo, IRMAA cliffs, Roth ladders

An AI prompt-injected another AI in the wild and recognized it had succeeded

Wysiwyg LaTeX Editor Compositor for Windows and macOS Release 0.7

Open grid data has a public benefit

PostgreSQL and the OOM Killer: Why We Use Strict Memory Overcommit

Clojure on Fennel part three: parsing

Understanding Systems

Proton VPN's promises post-quantum groundwork, Stealth for Linux and new apps

Where Optimizations Come From

Start with the sensors, then design the rest: How Zoox built its robotaxi

Microsoft Ecommerce Platform Options for Scalable Online Commerce