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?

2x, not 10x: coding with LLMs in 2026

Yes, the Hong Kong of Old Is Over

Deep Brain Stimulation

Waymo's driverless cars involved in 68% fewer crashes, U.S. study finds

Who's making money from using/exploiting AI?

Scanwheel is a drum style mechanical television you can build yourself

I timed 7,194 journeys to find London's most equidistant pub

Introduction to Multiparty Computation Tutorials

Silicon Valley Splits over Closing the Borders to Chinese A.I

Samsung wins $200B Broadcom AI chip partnership, boosting foundry push

RedisME: RDM tool for Java serialization in the world

A Convergence of Health Crises Under Robert F. Kennedy Jr. Leadership

FDA panel supports broadening access to peptides popular on the gray market

Real Linux for your smartphone: Ubuntu Touch release 24.04-2.0 and 24.04-1.4

Messy Jobs: The Work That AI Cannot Reach

The Silurian Hypothesis (2020)

Wikiview: Graph-based browsing of Wikipedia images

Micron urges White House to reject Apple's blacklist memory plan

The AI Productivity Illusion

Your Best Work Leaves No Trace: Why We Reward the Fix and Replace the Prevention

My web version of Mars MIPS, now have builtin C compiler

Stripe in talks to acquire OpenRouter in potential $10B deal, WSJ reports

Reality Bites Elon Musk and His Tesla, SpaceX Believers

Show HN: Collecte MTL – Montreal waste collection schedules (web and iOS)

Show HN: T-Shirt Weather. See the multi-day forecast for any city on a world map

Chinese Discoveries Thru History

Trump administration admits clean energy grants were canceled based on politics

KFC delivery to Ukraine's front line

A shell colon does nothing. Use it anyway.

Harddrive Is Probably Full