Ask HN: How can I load test PostgreSQL but avoid changing actual data?

1•LawZiL•6mo 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•6mo 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•6mo 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•6mo 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•6mo ago

Load test against a clone instead of a DB whose contents you care about?

The Penicillin Myth

Ghostty on macOS now has search

Investors expect AI use to soar. That's not happening

I forced 4 Big AI to admit structural failure in complex coding. Here is the fix

iOS Web Browser Latency

The Codebase Is Decadent and Depraved

The Doom Spenders

The exascale offensive: America's race to rule AI HPC

ArkRegex: RegExp with TypeScript Types

Bamboo Scaffolding in Hong Kong

Show HN: Trinity – A self-healing static site generator that fixes its own CSS

Four Product Discovery Models: A Practical Map

At least 36 killed in Hong Kong fire with 279 still missing, authorities say

Chat Control is not dead, it is just being privatized

Show HN: I built an MCP server to connect AI agents to your DWH

I tried to build a WhatsApp bot. Meta banned me before it left the drawing board

AI Just Took My Product Photographer's Job

We Ran Agent User Research with Agents (It Worked)

Scaleway turns Mac minis into high‑density, Raspberry Pi–managed servers

Effective harnesses for long-running agents

Show HN: Ghostty-Web – Ghostty in the Browser

The Moron Filter Effect

The Griswold Effect: How the Holiday Economy Makes Joy Expensive

AgentLens: The Future of Evaluation Is Agentic

Chinese researchers simulate large-scale electronic warfare against Starlink

Is it disruption, or is it theft?

Show HN: We built a free tool to help game devs better set Steam regional prices

One mile on bike is a 42¢ economic gain to society, a mile driving is a 20¢ loss

Why Content Is King in Tech Events

NASA Rover Makes a 'Shocking' Discovery: Lightning on Mars