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?

Show HN: FFmpeg API Cloud – Run FFmpeg over HTTP

Apache Cloudberry 2.1.0

A glimpse of the new software engineering

How much do sandboxes cost?

RAG as Similarity Engine

Token usage: Vanity metrics are back

All you need is myformconnect

EU age verification app: "Worry-free package" with security vulnerabilities

Techie buys fake Ledger Nano S+ hardware wallet and almost falls for phishing

Show HN: SharpSkill – I simplified the process of ITWs preparation

Marvin Minsky – Steps Toward Artificial Intelligence (1961) [pdf]

Show HN: Understand Anything – Graphs that teach > graphs that impress

Show HN: Browser Sysinfo – See everything a webpage can learn about you

HN: UmaBot – a multi-agent AI assistant

Modern Rendering Culling Techniques

How (and why) we rewrote our production C++ front end infrastructure in Rust

The Hilbert-Pólya Operator

Under the hood of MDN's new front end

Mythos and Cybersecurity

Robot sprints to victory in Beijing, beating the half-marathon world record

Did Second-Wave Feminists Really "Forget" Motherhood?

The seven programming ur-languages

Tell HN: macOS 26.4.1 bricked my MBP

Language models transmit behavioural traits through hidden signals in data

Night into Day

The RAM shortage could last years

Regolo – European AI Provider

Android 15's hidden Linux Terminal is a real Debian VM – and it runs Claude Code

Bullshit About Bullshit Machines [pdf]

Hyperframes – AI Video Creation for Agents