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?

The Security of Ephemeral Pages

StepStone: LLM-Based GPU Kernel Driver Fuzzing via User-Space Libraries [pdf]

GitHub Says 3,800 Repositories Breached

2026 Layoffs Tracker (WSJ)

Tor v3 .onion addresses are the same Ed25519 pubkey as Solana wallets

The Book of Mozilla

The Internet can't stop watching Figure AI's humanoid robots handling packages

The Hardware Lottery

Show HN: We wrote forensic intelligence reports on 20 open-source codebases

The surprising story behind the first British person in space

Newborns' cry melody is shaped by their native language

My Hermes and Obsidian Setup and Use Cases

Show HN: Lance – image/video generation and understanding in one model

You never learned to delegate. AI just made it obvious

What do you think of my new website?

I built 2k badges for Replay and I'm not sure I've slept since December

LightSolver Partners with Boeing

Show HN: Zero Protocol – A personal context file for AI

U.S. PV manufacturing capex could reach $7B in 2027 in breakout year

Show HN: Chatroom with curl command (requires IPv6)

Career coach based on psychological tests (desktop,BYOK)

SBCL: The Assembly Code Breadboard (2014)

From Skills Back to Tools: Why Our Dashboard Assistant Moved Off the Claude Code

StrangerLoops

The brain's code seems to be in constant flux. Neuroscientists are baffled

AI-generated abandonware is hollowing out open source

Chat client for Meshtastic LoRa mesh networks in Emacs

Localgcp: LocalStack for GCP, emulating 14 Google Cloud services locally

How much should we worry about secretly loyal AIs?

Digitally stuck on an island with 30 people