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?

A fast and secure tar parser for the Rust ecosystem

Clone any website you want

Discord to allow messages to appear in search engines

My LLM confirmed a booking that was never saved

Show HN: I built a Lovable Alternative that runs in the browser

Resilience4cata – retries and circuit breakers written at compile time

Who needs AI psychosis? I gave myself VR psychosis

Owning the Harder Problems

Show HN: Advaita Vedanta – Offline Books for Android

At 14 Justine Tunney published software. Now she makes AI models run from a file

I Believe P=NP

How the Nobel Became the Nobel

Which musical styles do smart people prefer?

AI co-authored commits: 3 open-source projects in 2023, 686 of 874 in 2026

Chinese scientists create powerful catalyst to turn air on Mars into rocket fuel

IP Camera Streaming: How to Set Up RTSP to WebRTC Streaming

Police Are Using AI to Pull over Drivers for 'Suspicious' Behavior

Heatbit's $899 Water Heater Element Mines Bitcoin While It Heats Your Tank

Running the Factorio PC Exe on an Xbox Series S

Aristotle: Formal Verification for C and Verilog

Show HN: Instance – A clean-room reimplementation of GameMaker 8.1 in Rust

A 28-Year-Old DOGE Operative Gutted USAID. HR Had No Record of Him Working There

Show HN: Duotone icon library with 2.5k+ icons and 250 flags, RTL aware

Oracle unveils its first in-house Image/Video Models

Show HN: Python Implementation of Publication Open the Universes Theorem

Large-scale single crystals printed layer by layer

CreatedThreeWeeksAgo – new Wikipedia articles after the initial discussions

Slow Cloudflare Connections

Come Back to Firefox. Meta Never Left

DeepMind unveils Genie 4 world model promising more realistic physics and humans