Ask HN: How can I load test PostgreSQL but avoid changing actual data?

1•LawZiL•8mo 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•8mo 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•8mo 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•8mo 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•8mo ago

Load test against a clone instead of a DB whose contents you care about?

The $75M Opportunity: Consolidating Canada's Fragmented AI Spending

Analytical Chemistry 2.0

Skypilot: Run, manage, and scale AI workloads on any AI infrastructure

Shark 2.0 – a free, open-source poker solver in C++

Sometimes Never Compete on Price

Rethinking Heating

'We got lazy and complacent': abolishing the wealth tax changed Sweden

Zendesk Alternative

'On This Day... 1776'

Show HN: Stripe-no-webhooks – Sync your Stripe data to your Postgres DB

Looking for open-source Python package for AI stock analysis

The European Schuko socket bothers me

OTLO

Expert Book Recommendations

Yawning has an unexpected influence on the fluid inside your brain

Scott Galloway Calls to Cancel OpenAI Subscriptions to Launch Consumer Strike [video]

Book Review of Stewart Brand's Maintenance

Book Review of Steven Pinker's When Everyone Knows That Everyone Knows

Google's 'Project Genie' Is Basically a Plagiarism Tool

Show HN: Xmrcheckout – self-hosted, non-custodial Monero checkout

Parallel evaluation in Nix rolling out to Determinate Nix users

Show HN: Nano Queries, a state of the art Query Builder

Show HN: A causal safety release gate for AI systems

Will Agents replace search teams?

Ruby 4.0 is available in the Microsoft Store

Skills on Tessl: the package manager for agent skills

History of the PT2399 Delay Chip (2025)

We interfaced single-threaded C++ with multi-threaded Rust

In solidarity with victims of ICE violence

Oral history of the 555 timer IC, with its inventor Hans Camenzind