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

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

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

Build Windows 11 QEMU VM images deterministically with Nix

Apple's upcoming AI smart glasses are starting to sound more exciting

A 62-byte FLAC file that requests 8.5GB in dr_flac, used by raylib and SDL

Chemical Weapons: A Summary Report of Characteristics and Effects

Ask HN: What do I do now that software engineering is dead?

Can Chain-of-Thought Reasoning Solve Any Computable Task?

The Last Year of Terraform

Jane Street Accused of Insider Trading That Helped Collapse Terraform

People systematically overlook subtractive changes (2021)

Tests Are the New Moat

Show HN: I built a tool that turns Reddit conversations into video scripts

Tell HN: Vibe Coding Taxonomy

Designing APIs for AI Agents

Colorado Lawmakers Push for Age Verification at the Operating System Level

Combien de Bises ?

Show HN: Rampart v0.5 – what stops your AI agent from reading your SSH keys?

AI podcast network publishes 11,000 episodes a day. It rips off media outlets

My Phone Will Spam You If I Fail to Exercise by 3PM

Show HN: Run untrusted WASM plugins with CPU/mem/network/file budgets

Show HN: Your AI agent logged the mistake. Mine wasn't allowed to make it

Wisp – Full Screen Frameless Browser for iOS

I got my 2nd paying customer by doing one thing: spamming about my app

AI bit barns grow climate emergency by turning up the gas

The Agent for Motion Graphics

One Hack Nearly Took Down the Internet (Veritasium) [video]

DSSP and Forth

WebSocket Mode for OpenAI Responses API

SQL vs. NoSQL: How to Answer This Interview Question in 2026

Venom, run integration tests with efficiency

Bending Emacs – Episode 12: agent-shell and Claude Skills [video]