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

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

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

Texas Instruments to Acquire Silicon Labs

Thaw.zip: Private Subreddit Used by ICE

Why "Just Fine-Tune YOLO" Often Fails

Show HN: Shaders Public Beta – Shader Magic for Modern Frontends

Show HN: OpenClaw Guide – multilingual docs and skills leaderboard

Show HN: Self-improvement platform

OpenClaw – Hosting

Former GitHub CEO raises record $60M dev tool seed round at $300M valuation

Show HN: GrillMyPitch – An AI investor-readiness simulator for founders

I ditched Gmail for Thunderbird on my Android

How old were you when you decided to start giving up? (2010)

An Asteroid Might Slam into the Moon in 2032–and Create a Fiery Flash

Using an Engineering Notebook

Ask HN: When autosave restores an invalid client-side state

Church of Molt

Mailly

The impact of subpolar salinity on AMOC remains masked when forcing is weak

South Korean crypto firm accidentally sends $44B in Bitcoin to users

Show HN: TextFix – instant text fixing (spelling/grammar) with LLMs on macOS

Tiny Titan or Overpromised Miniature? The Framework Desktop Reviewed

MiRAGE: Open-source framework for multimodal RAG evaluation

Making self hosted web user interface for BME280 sensor with ESPUI

Sruja: Architecture-as-Code for the AI Era

Chrome 146: WebMCP Early Preview

The Software Sovereignty Scale

RemixableFont.ttf

LoanFi – "Buy Now, Pay Later" for Crypto Using Aave/Compound

Claude Opus 4.6: This AI just passed the 'vending machine test'

Show HN: Airstrip – SaaS runway forecasting that models MRR lag

Rule #1 for coding with AI agents