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

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

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

Paramount Makes Hostile Bid for Warner Bros. Discovery

What Makes Goethe So Special?

7-Eleven to Pay Record $4.5M Penalty for FTC Antitrust Order Violation Case

The Lost Machine Automats and Self-Service Cafeterias of NYC (2023)

Canadian traditional owners fear Australian-style LNG development

Scientific and Technical Amateur Radio

Gig: How our 25G PON investment shatters the limits of today's internet

Mr. Ren Zhengfei's Meeting with ICPC Foundation President

Metacode: The new standard for machine-readable comments for Python

Show HN: Octopii, a runtime for writing distributed applications in Rust

RNA language models can generalize well on structure prediction tasks

Using an AI Mediator Because Humans Are Terrible at Conflict

Show HN: Tool to detect malware left behind after patching CVE-2025-55182

Residents push back as 5G towers rise steps from their homes

Using Citations to Explore Academic Literature

What If Our Ancestors Didn't Feel Anything Like We Do?

Authentication Explained: When to Use Basic, Bearer, OAuth2, JWT and SSO

Show HN: SteadyDancer – First-Frame Identity-Stable Dance Animation

Horses: AI progress is steady. Human equivalence is sudden

I built an AI that learns code transformations from examples (not generative)

Bots, bias, and bunk: How can you tell what's real on the net?

The Military Almost Got the Right to Repair. Lawmakers Just Took It Away

The Universal Weight Subspace Hypothesis

Trump Clears Sale of More Powerful Nvidia A.I. Chips to China

Bringing More Real-Time News and Content to Meta AI

Manual: Spaces

Deprecation: Software Engineering at Google

Scientists devise method to fight aging at the cellular level

Show HN: ZetaCrush – An Intelligent LLM Leaderboard

EU says it will 'make sure' Elon Musk's X pays €120M fine