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

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

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

Why Are BART and MUNI always broke(n)?

Where do AI-built apps usually break when moving from prototype to production?

Third-party Claude harnesses will now draw from extra usage

Resurrecting Supermaven: Trad coding is not dead. Just different

Show HN: SpeechSDK – free, open-source SDK that unifies all AI voice models

Compliance at scale and why TAM distracts with Christina Cacioppo of Vanta

Trump announces 'fraud' crackdown in Democratic states as arrests begin in CA

Forest Service Will Close Research Stations That Study Wildfire Risk

Anthropic no longer allowing Claude Code subscriptions to be used with OpenClaw

Researchers develop new way to detect breakthroughs in science: study

Tokenizer That Outperform Tiktoken with O200k_base

Tinnitus: At a crossroad between phantom perception and sleep (2022)

AI startup envisions '100M new people' making videogames

Meta Pauses Work with Mercor After Data Breach Puts AI Industry Secrets at Risk

'Hacks' Star Hannah Einbinder Blasts AI Creators as 'Losers'

Show HN: Standalone TurboQuant KV Cache Inference

Darkness can "travel" faster than light speed

Soros: The anatomy of an agentic geopolitical simulation engine

Fake Fans

The Subprime AI Crisis Is Here

Why Millennials Love Prenups

Run Linux containers on Android, no root required

How the Claude Mythos leak happened

The house is a work of art: Frank Lloyd Wright

SpaceX Files FCC Complaint over Ariane 64 Amazon Leo Launch

Just 'English with Hanzi'

Show HN: Open-source benchmark for transcription APIs on meeting audio

The Cloud is a Scam (2019) [video]

Did Impacts from Meteors Help Start Life on Earth?

Floating Point from Scratch