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?

The Failed Fight to Unionize One of Gaming's Most Idealized Studios

Writing a Demoscene Intro for the ZX Spectrum

Show HN: I built a banking demo with LavinMQ to test messaging failures

Formally Verifying a Compiler Using Automated Research

Show HN: Figranium – Build browser automations visually and run them as APIs

Evidence theater: how AI fooled smart entrepreneurs

The Name Is the Spec

Anatomy · a Skill for Claude Code

Show HN: An open source AI leaderboard of the best models your machine can run

Rackspace Email Issues

Britain to get first look at new AI systems after White House U-turn

Visualizing Crossbeam Epoch Based Reclamation

900-Page Pre-Pantone Guide to Color from 1692 (With Complete High-Res Scan)

Show HN: I made an AI free version of my directory of open-source alternatives

The Role of Nuclear in the Energy Transition: Complementary but Limited

I posted 100 tiktoks as introverted engineer

X Once Again Marks the Spot

Fake reviews on the Google Play store

Ask HN: What desk chair would you recommend for a home office?

Slop City

Show HN: Test UI Hypotheses for Mobile Paywalls

Building my own expense tracker

Catch a Bird – I made it for my own sanity

Heldearly

Whacky Japanese moving keyboard may be the future or not

DraftKings Says It Doesn't Want Problem Gamblers. I Bet $22K to find out [video]

Trump Mobile hack and apparent lack of FCC authorization raise security alarms

Ask HN: How are you handling auth token storage in React Native in 2026?

Leading Chinese mathematicians unite behind use of AI in research

Videopowers – A factory plugin for making YouTube videos with Claude Code