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?

Database Turing Award Winner Mike Stonebraker [video]

SportScore MCP – free live sports data for Claude

Show HN: Stenobird, podcast transcription service for your agent

Z.ai phasing out original subscription plans

Quantum 'Jamming' Explores the Fundamental Principles of Nature

Beyond Memorization: Violating Privacy via Inference with Large Language Models [pdf]

You Can't Game Your Way to a Real Education

My family tried to eat fewer ultra-processed foods

Trade-school graduates often enter the workforce with significant debt

xAI is buying cursor for $60B and the real play isnt code editors

Fundamental Theorem of Calculus

OpClave- council of LLMs to get one synthesized answer

Tempest vs. Tempest: The Making and Remaking of Atari's Iconic Video Game

Small regulatory RNAs show changes in schizophrenia and bipolar disorder

Hermes Agent – A self-improving AI agent

AI Tokens Optimization

Stalwart v0.16: A New Foundation

A deadly bacteria is creeping up the Atlantic Coast

Ask HN: Feedback request on new external network attack surface discovery tool

Aegis Solis Archive: Hash manifest for cross-platform verification

How to turn off Google AI overview

The D.C. devotees of a niche arcade game (Killer Queen)

China backs orbital data center startup with $8.4B in credit lines

Show HN: Kumbukum, open source memory infrastructure for teams

Taktile's Agentic Decision Platform

OpenAI's response to the Axios developer tool compromise

CubeSandbox: Tencent's sandbox for ai agents

A Open Source Repo to Track Elected Officials Investments

Fraud and the false optimism of AI for science

Hershey's Electric Railway in Cuba