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?

Looking Ahead to Postgres 19

A Missing Piece in Climate Models: Nature's Own Emissions

Eot-bench: Open benchmark suite for end-of-turn detection in voice AI

GPT-5 writing a Singularity scenario (2025)

iOS 27 Adds Four New Features to Notes App, Including Markdown Copy-and-Paste

Infection found in wildlife evolved to spread between humans

PyQt6 now ships the Designer plugin on conda-forge, fixing a 6-year gap

Migrating from GNU Stow to Chezmoi

Problems I'm having with Opus and regression

The Download: a new hunt for dark matter and Kenya's case for going solar

Show HN: Prompt Foundry-tasks with focused context & instructions = sharper AI

Why Deleted Mac Apps Leave Files Behind

Performance Has Layers

A PostgreSQL Database for Every Agent: In-Database RAG, Graph, and Multitenancy

Death by 1k Compromises: How to Tap into Founder Mode

Where do migrants live, and where were they born?

Making budget models punch above their weight with a smart Rust harness

Show HN: A small, crazy fast hybrid search engine written in Rust

Why AI Is Incorrigibly Didactic

Third time's the charm for a row of faint galaxies without dark matter

Wiki Spy

Pnpm temp paths broke lifecycle sockets

Developers React to AI-Scented Blog Posts

Show HN: SDK for embedding zot coding agent in your Node.js applications

Commodore Unveils Linux Powered Flip Phone

Apple's A12 and A13 Chips Facing New Unpatchable Exploit

Is it time for a new Embedded Linux build system?

Europe Scales Down AI Ambitions with Smaller Data Center Tender

Show HN: Foglamp – Open-source o11y for AI SDK

The founder of Craigslist has given away half a billion dollars