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?

West Memphis Three

YouTrackDB is a general-use object-oriented graph database

Show HN:PandaClip a local mcp tool for an agents working state

I made a neural network that I can train using my fingers

Come Friendly SBOMs

Show HN: An autonomous SOC built to refuse causation it can't prove

Jamkernelp2p: A sovereign P2P microkernel for ultimate lightness and control

Claude Science, an AI workbench for scientists

CTA-Pipelining: A Latency-Oriented Spatial Scaling Method for Multi-GPU Systems

Rendering Fonts Quickly on the GPU

Why South America Is So Good at Football

Microsoft Promises to Fix Search with Major Windows 11 Overhau

AI Isn't Human. Stop Talking About It Like It Is

Wither ACM? Publish and Perish?

A high-performance JavaScript/TypeScript compiler toolchain written in Zig

The EU is not about to censor access to the internet

America's Other Elections Problem

Informing Ourselves to Death (1990)

Show HN: A MCP for Agents to reverse engineer binary code

Stop the GUARD Act and age verification laws worldwide

India halts WhatsApp global username feature over cyberfraud risk

Agents.md – Dumb Human

Our Amish Language

Geo‑historical platform reconstructing political entities from 3499 BCE to today

Electric cars are taking off quickly in Latin America

The value of AI is through the programmers

GPT-5.6 Sol, Terra, and Luna: A Real-World Benchmark for Developers

DiScoFormer: One transformer for density and score, across distributions

Every man a VC - On public deep-tech startups

Japan develops a method to recover up to 90% of lithium from used EV batteries