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?

CRTs and other ewaste can no longer be exported from the USA

The AI slowdown is coming

The Event of 2033 – An AI-Imagined Second Coming

90 second power loss causing second day of train disruption

Why and how the Austrian Military moved to LibreOffice (Part 1)

DeepSeek V4 Flash 0731: Is It Cheaper to Run It at Home or Pay per Token?

All memory manufactured in 2027 has been sold out

Is your Bitcoin seed safe after Coldcard accident? What about iancoleman/bip39?

Truckslop

Marque Terminal

The Ones Who Look Outward

Tsang Tsou-Choi

Show HN: Remembrane – agent memory in one SQLite file, zero dependencies

Windows Tracks You with Hidden ID. We Built DeGDID to Block It

AI model achieves breakthrough in forecasting cyclones – Google DeepMind

Operational Tropical Cyclone Forecasting with AI – Nature

Don't get rejected by Apple anymore

Show HN: CapyToolkit – Browser-native developer and hardware diagnostic tools

High-Quality Free Transparent PNG Collections

I won't read LLM authored fiction

The offline messaging apps challenging internet shutdowns

Ask HN: DeepSeek Alternatives

Ask HN: After 20 years of coding it is not a fun hobby anymore. How about you?

Russian spy 'honeytraps' lure Ukrainian soldiers to their deaths

Ball Scratch Simulator: release date, gameplay, VR, and PC requirements

Passkeys by default and retirement of Microsoft SMS and voice authentication

How to read the Attention formula from the Attention is all you need paper

Americans Are Richer Than Everyone Else

INT2 KV-cache quantization is getting surprisingly good

Canadian imports for data centre equipment up 88% from prior year