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?

Coding harnesses are used, an introspection

From-scratch reimplementation of Mythos Glasswing pipeline

A Simple Image Brightness and Contrast Adjustment Technique

Azure's MFA Warning Links Free-Tier Users to a Page They Can't Use

Nous Research edits GitHub issue to remove plagiarism claims about Hermes Agent

Show HN: I trained YOLOv8 to detect dynamics markings on sheet music

Phantom Data Centers Didn't Break the Power Grid–They Proved It Was Broken

ASMTP – SMTP for Agents

Click, Agree, and Your Kids Are Bound: Court Compels Minor Children to Arbitrate

Show HN: Vecdb – local-first hybrid vector database in Rust (HNSW and BM25)

Why Outsource Your Auth System and How to Sell the Decision to Your Company

Show HN: Spud – cross-platform remote control, optimised for gaming

The Merge

Anthropic co-founder to present AI encyclical alongside Pope Leo XIV

Don't Sign in with Google

Built an API client with age-encrypted vault for secrets

Meta Reassigns 7k Employees to Focus on A.I

MIT 14.12 Economic Applications of Game Theory, Fall 2025

Key landmark regulations against 'forever' toxins removed by Trump admin

Intern gets inspired by Bun to rewrite codebase into assembly

Canceled by Hinge

APIMatic – Type-safe SDKs for 7 languages from an OpenAPI spec

Click

Manchester Code Made Bits Behave

Super-Adaptable Mayhem 10 Swarming Drone Evolved from the Switchblade

My blog was hacked and Claude and I just fixed it

An asteroid discovered days ago will narrowly miss Earth – RNZ News

If AI can translate instantly, why learn another language?

A New Look for Express

Musk says Tesla unsupervised FSD will be 'widespread' in the US by year-end