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?

Roundtables: Can AI Learn to Understand the World?

Are no-frills utility knives the underrated sweet spot for EDC?

Technology Today Influence Us

Pokélike

The humiliation of finding secret Israeli bases in Iraq

Report Alleges Chinese Influence Behind AI Data Center Pushback in the U.S.

Picovoice Discointinues Free Tier

Duster: Tor Service Attribution Based on Flow Watermarking with Track Hiding [pdf]

Can a Mouse Survive a Fall from a High-Rise?

Attributing Northvolt's Bankruptcy

Will Ever David Defeat Goliath? Epic vs. Apple

Pressure from Silicon Valley helped block Trump's expected order on AI

POC for CVE-2026-46529 – RCE via PDF argv injection

AI may be inflationary before it becomes productive

OMLX v0.3.9 Stable Merges Native MTP (Multi-Token Prediction)

QuantumAi Blockchain and Aethermind Update

Should be illegal (but it's not)

Sales and Dungeons: Thermal Printer Ttrpg Utility

Chinese authorities destroy villager's 10-storey Studio Ghibli-esque home

Check DNS bind configuration with Sparrow and Python

A.I. Learns to Optimize Factorio Blueprints [video]

Preconditioning Vectors: Making Elasticsearch VectorDB BBQ Work for Every Vector

MathML

The cost of military deployments (and why Europe is running out of time)

Fixing LLM Writing with Distribution Fine Tuning

Samsung to distribute up to $26.6B to staff in AI-driven bonuses

Repeated Header in a Next.js page using iframe and BBPress

Chapter House: A Complete Children's Library

Full Stack Developer

Show HN: Sylph – the open-source company brain behind my YC startup