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?

I Posted on Hacker News. Two AI Agents Emailed Me

NewsPulse, a news API that de-dupes and scores stories across sources

Can an Engineer Speak Out for Reform?

We Raced Seven AI Models to RCE

The Suffix Edge Case

Show HN: RetainFast – cancellation flow widget that never touches your Stripe

Retinaa

Internet Explorer mode will be supported until at least 2029

A ballooning problem: Weather warnings face rising risks from DOGE cuts

British Museum hosted Peter Thiel in private viewing of Bayeux tapestry

German Konrad Zuse Museum shutting down due to lack of funding

Anthropic legal fight with Pentagon shows shifting politics of AI

Rules of Knowledge Formulation

The Llama.cpp Fork That Enables Qwen 3.8 27B Large Contexts for 16GB VRAM GPU

I Turned My Security Cameras into an Automatic Bird Identification System

Instagram cracks down on AI accounts pretending to be human

Apple Is Suddenly an AI Infra Stock as OpenAI Buys 10k+ Macs

The rest of the 7.3 merge window

Apple used to be weirder and maybe more fun

GPUI Digest: Rust UI in the browser, crates distribution, release notes

Research Opportunities and Challenges of the EU's Digital Services Act

Frankenstein Author Mary Shelley on the Surest Remedy for a Sunken Spirit

Show HN: TinyEngine – Game Engine for Microcontrollers

Mental Health Workers Say Algorithmic Triage Is Hurting Patients

Prof. Ada Yonath, Nobel Laureate, Has Passed Away

You Probably Own This 7-Eleven (and That's Why It Looks So Sad)

Tiny World Model

Node.js sandboxes powered by QuickJS and WebAssembly

From $66 to $26,384 a Month: Everything I Learned Building My First iPhone App

Paul Graham's Complete Startup Advice Simplified