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

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

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

New fibre optic data transmission speed record of 450Tbps

Private Files on a Static, Open-Source Website

Gas Town from Clown Show to v1.0

Microbial upcycling of plastic waste to levodopa

Show HN: Lumina – a statically typed web-native language for JavaScript and WASM

Disclosing bluehammer exploit, vulnerability is still unpatched

I Tried Vibing an RSS Reader and My Dreams Did Not Come True

What Being Ripped Off Taught Me

Token-Aware LLM Load Balancer route by inflight tokens,not connections

A real-world case of property-based verification

Panther Lake is the real deal

Show HN: I built a 2-min quiz that shows you how bad you are at estimating

Baidu Silent About Failure of 100 Robotaxis in Wuhan

When the Push Button Was New, People Were Freaked (2021)

Nanonets OCR-3: A multimodal OCR model with bounding boxes & confidence scores

Muon and MuonClip Optimizers

Rational Matrix Iterations for Polar Decomposition

» the Mystery of Rennes-Le-Château, Part 3: A Secret History

NameWheel – Free wheel spinner with no ads, no signup, no tracking

Structural color can now be printed with an inkjet printer

Show HN: Ec – terminal native 3-way Git mergetool

Uber: Why Our Sense of Convenience Comes Always at the Expense of Others

Real-time poker engine with emotion-driven AI bots

New Chrome Extension lets you see what LLMs you can run on your hardware

Show HN: I turned ARC-AGI-3 into a daily browser game

Software Engineering Is Becoming Civil Engineering

NASA Elements of Engineering Excellence

OpenSim – Open-Source Flight Simulation Engine

Was Trump oblivious to the realities of Netanyahu's promised 'easy' war on Iran?

Show HN: I made a crossword app for language learners