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?

How to think about span of control

Typocalypse – A free online game I made

War Atlas: An interactive cartography of every named war in human history

SpaceX wants to launch 100k more Starlink satellites – for 100x the bandwidth

Uiua - A tacit array programming language

Train the Agent, Then Replace It

"Disk Not Ejected Properly": What It Means

Generating Effects

Bun vs. Deno vs. Node.js 2026: Real Benchmarks Mislead

The Work of Helping A.I. Destroy Work

The 2025–2026 Evolution of Generative Spatial AI

Alarm over launch of facial recognition in UK shops that instantly alerts police

Why Stone-Faced Fascists Keep Getting Antiquity Wrong

Property Graphs in PostgreSQL

Boom Prize: $750k to the first amateur RC plane that breaks the sound barrier

Disable autoplay and infinite scroll or risk fines, EU tells Meta

I Think I Know How to Solve the Video Game Industry's Problems

MagicView Advertising for Startups

The Road to Housing Act Might Pass

China recaptures the first stage of a rocket to reuse it

Dangerzone: Convert potentially dangerous documents into safe PDFs

PQC Readiness and Migration Tool

The Difference Between Watercolor and Gouache Paints

Big Tech Doubles Debt Load to $350B in AI Spending Spree

Hackachu: Building Pokémon-inspired games to earn hardware

Blood Cell 'Nurseries' Upend Understanding of Human Physiology

How to Read Scientific Papers (Or Give Good Dating Advice)

Lessons from the UK's first trial scheme for electrical repairs

The Joy of Old-School Coding After a Stupid Mistake

Ethical NBA Championships