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?

Mark Zuckerberg buys 440-acre Strancally castle estate in Ireland

Show HN: Mail client optimized for self-hosted email across multiple domains

Hyperliquid trader loses $26M as ETH short unwinds in seconds

A.P.E.X. – An adaptive parallel sorting framework for Java

AI's 'middle class' has gotten dramatically better at hacking

FolderIQ: Local First Drive Intelligence

Jong, Java's giant ship that dwarfs European galleon and its shipbuilding legacy

Could AIs Become Conscious?

Darth Vader defends Flock to San Diego: 'This is what the emperor needs'

KrakenWAF

Can Head Start Be Deregulated Out of Existence?

Creative Thinking by Claude Shannon

Agentic Programming and the Lust for Power(?)

The AI Debate Is About Control, Not AI

The Art of the Self-Deal

Show HN: Post Quantum Liveness, Identity, Provenance. No Biometric Identificatio

Show HN: luoluoAI – OpenAI-compatible and Claude Messages API gateway

Why Are Many GitHub Issues Nonsensical for Visual Studio Code Edge Dev Tools?

Spared execution eight times, oldest inmate in US dies at 101

How Big Tech's A.I. Borrowing Binge Is Driving Up Bond Yields

Turning on airplane mode was never about keeping the plane in the air

Show HN: Getfkd – adults-only dating (swipe, match, meet tonight)

Sing-song: a speakable encoding for long numbers and keys

Founder sues YC over idea in rejected 2018 application

We replaced our 223-node agent graph with a single open-source LLM

Show HN: AI tools with real pricing, submitted in 30 seconds

Cryptomator

Ozone: The Fault Is Not in Our Trees, but in Ourselves

New visualizer: U.S. state obesity trends

You Are Not Fat Enough for Retatrutide