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?

Neural Representation of Minimal Surfaces

CTO / Head of Eng / VPE folks at startups are leaving / burning out

Meta disregarded its own research on teen harm, Tennessee tells jury

PyTorch: A Reference Language

Migrating 6.5B Rows from MongoDB

AI Tools Uncover Record Software Flaws in Tech Sector, Database Shows

API Testing Chrome Extension(REST, Soap, WebSockets, SSE)

Why Agentic Systems Need Ontologies [video]

Half-Life on a Real PlayStation 1 – Full Hazard Course, Uncut [video]

Token Use by Model

Show HN: Etymology Database from Wiktionary

Day 0 Kimi-K3 Inference Deployment with Atom on AMD Instinct MI355X GPUs

Capturing the Narrative

CastToAny – Turn podcasts and videos into publish-ready content

Show HN: A 6M-token movable window on a single 46GB GPU

eBay and former execs will pay $56M to settle harassment of Natick couple

What will more intelligence do for us

Would You Go Back to a Time Before Smartphones If You Could?

Another Symptom of Growing Isolation

Protocol-Level Attacks on Agentic Commerce Platforms: Taxonomy and Defense

"Opus 5 is a really bad model"

A Science of Reality: Observers, Interfaces, and Traces [pdf]

Why do we think we understand the world more than we actually do?

Claude Fable 5: What It Is and Why the Hype?

The age of token efficiency, the age of libraries

Ex-JPMorgan banker Chirayu Rana files fed civil rights suit; racism/sexual abuse

Manticore Search 28.4.4:Faster KNN, conversational search, installs and faceting

VibeUE: Unreal Engine Vibe Coding Tool

Newspeak

Show HN: I taught Claude Code to file Indian income-tax returns (ITR)