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?

Meloni's push to revive nuclear power runs into Italy's old ghosts

Forward Deployed Engineering 101

Meta Lays Off 8k Employees, as A.I. Casualties Mount

No JavaScript. No npms. Make realtime web apps in modern Java

Ad Infinitum

Google Health 5.0 rolling out with new stats widget on Android

Harvard faculty votes to make it more difficult for undergrads to earn A's

Cows can recognize familiar human faces and match them to voices

Walter Benjamin's Would-Be Rescuers

Iran is consolidating control of Hormuz with checkpoints, deals, and 'fees'

Obsidian plugins are (mostly) dangerous

Standard Charter CEO Replaces 8000 "Lower Value Humans" with AI

The Case for Compilers: A Look at SPEC CPU 2026 on LLVM 22

Tokenspeed – How fast is 10 tokens per second really?

Rule of Thirds

NPM invalidates use of fine-grained tokens that bypass 2FA

How to Kill a Blog

Your Read Model Doesn't Always Need a Database

Show HN: ISSforAPUSH is conducting an "aggregated interview"

Show HN: QuiteGPT – makes GPT response shorter

I made an AI Interior consultant

Bitcoin's Power Law: Weak Structure, Strong Forecasts

Consciousness, Gödel, and the Boundary of the Box

YAML? That's Norway Problem

New on Platform

Ye Olde RFC

Nostr-VPN Is one of the most useful things in open source

70% of Faculty Vote to Overhaul Harvard Grading with a Cap

List of Alleged Extraterrestrial Beings

Software engineering is the tipping point [video]