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?

Discretizing Reward Models

Exploring The Spectrum -Dr. John Ott documentary on the health effects of light [video]

Guardians of the Agents Formal verification of AI workflows. (Dec 2025)

Oʻahu Needs More Solar, Not New Power Plants

WhiteHouse lifts export control on Anthropic that froze its most advanced models

The Future of Software Engineering Is Here but Not Evenly Distributed (Updated)

Taiwan raids Super Micro in widening China chip smuggling probe

White House picks Avi Loeb with polarizing alien theories to lead UFO council

The President Made More Than $1Billon in Crypto Deals

A Fusion First: Realta Demos Direct Energy Conversion

Show HN: Explore Ethereum by the Shape of a Transaction

Faster Gemma 4 on MLX with multi-token prediction

Why Jet Engines Aren't "Made in China"

Scott Alexander as a modern-day Edmund Wilson

Frame Error

Wikipedia: Deleted Articles with Freaky Titles

Tor’s Arti 2.5.0 Released: Stable Counter Galois Onion

Llmaker – spin up a working LLM app from a single prompt, right in your terminal

The Threat of Residential Proxies

JWST spots mature galaxy cluster, redefining "cosmic noon."

China's Great Green Wall: 66B trees growing faster than natural forests

The Green Metrics Tool

A new AIs-heading technique to track grain smuggling

Hackers Broke into Madison Square Garden

Donald Trump made more than $1B last year in return to presidency

Ovid: A pi extension that makes it record proof its features actually work

Ironsmith – A full grammar, parser, and compiler for Magic: The Gathering cards

OpenMontage: an open-source agent that edits real footage into video

Show HN: Fruitbox – Docker Compose for Apple's container runtime

Keybr: Adaptive Touch Typing Trainer