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

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

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

Travelers wear pajamas to airports in protest of government request

Show HN: AQUA – model agnostic lightweight command line agent coordinator

Russia blocks Apple's FaceTime in mounting push against foreign tech platforms

Spotlight on the Artist/Producer

A small group of women changed the UK law on deepfake porn

A Look at the PowerVR Graphics Architecture: Tile-Based Deferred Rendering

Meta poaches Apple design exec Alan Dye to lead new Reality Labs studio

PyTogether: Collaborative lightweight real-time Python IDE for teachers/learners

Ask HN: Best way to start coding with tools available today?

I Built a Custom GPT to Improve Developer Resumes

Associated Press 100 Photos of 2025

Show HN: We instrumented Claude Agent SDK using a tiny Rust proxy

Google taps Replit in challenge to Anthropic and Cursor

Someone said on Lemmy that he wanna kill "bad people" to protest agegating laws

Unison: A new approach to Distributed programming

Show HN: Gihtub Wrapped 2025

We Built ExoQuery

Don't Track Tasks. Track Interruptions

Ising-Conway Entropy Game (ICEg)

Who let the dogs out? Beeple unleashes uncanny robot canines at Art Basel Miami

An AI toy meant for kids was happy to chat about sexual fetishes

Redrawing the Poverty Line: Why America's Most Quoted Number Is Wrong

Do you have an AI companion?

Is Pixelfed sawing off the branch that the Fediverse is sitting on?

AI Takes over Boring Code: Is Software Engineering Losing Its Soul?

Effects of microgravity on edible and model insects for spaceflight food system

Nvidia lobbies White House and wins loosened AI GPU export control to China

Terminally ill baby ants emit chemical signal prompting adult ants to kill them

Show HN: Open security analytics for your product

Has Meta "Poached" Apple's Top Interface Design Executive?