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

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

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

Toolchain Horizons: Exploring Rust Dependency-Toolchain Compatibility

The predictable failure of the QDay Prize

Staying a Spell with the Exidy Sorcerer

A weekend with LoRA on Gemma 4 E2B: instrumenting what fine-tuning changes

New robotic control software avoids jamming their joints

The West forgot how to make things, now it's forgetting how to code

The Visible Zorker: Zork 1

One last trip to the internet in 2009 with The Rough Guide 14

I worked just as hard, failed just as hard–then saw it was rigged

pvlib: Open-source Python library for solar power modeling

Invincat – terminal AI coding agent with tiered, auditable long-term memory

MCP Server and CLI for Accessing Work IQ

IMaySellIt – A marketplace where every listing is offer-only

Mali's Tuareg rebels announce deal for Russian Africa Corps withdrawal

Show HN: Jigs-tiny Rust framework for interactive maps of composable pipelines

AI Reverses the Political Logic of the Internet

Arctic Temperatures

LLM Anxiety

Show HN: Kadō – habit tracker app for iOS, open source and privacy-friendly

An AI agent deleted our production database. The agent's confession is below

Show HN: I made GAI to have LLM agents in Go without heavy frameworks

Tech's richest plan to save themselves after the apocalypse (2018)

AI vibe coding vs. AI vibe cracking

The left is missing out on AI

Blitting the Night Away: Nichibutsu's Mahjong Koi No Magic Potion

Software engineering may no longer be a lifetime career

Europe–not US–first to authorize Moderna's combo mRNA flu-Covid vaccine

Show HN: Store, play and stream your iOS/macOS videos on your smart TV with Kino

Show HN: J-RAY PRO – A fast visual JSON profiler in Rust

Sawe breaks two-hour barrier with 1:59:30 world record at London Marathon