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?

Show HN: A chess.com profiler that runs Stockfish in the browser

Microsoft Exchange, Windows 11 hacked on second day of Pwn2Own

Frink – programming language designed to make physical calculations simple

Show HN: I scanned Cal.com for architecture drift caused by AI coding tools

Auto-Improving Software

Bluesky Radio – Hosted by Opus 4.7

Ruby-charts: Generate pie, bar, and line charts from data files

iOS-Linuxkit: Linux on iPad, the Hard Way

Do you spend 1h+ time a week just reading?

Is the Secret to Men's Longevity a Great Butt?

Redux maintainer's thoughts on AI

OpenAI seals deal in Malta to give all Maltese access to ChatGPT Plus

Recent Developments in LLM Architectures: KV Sharing, MHC, Compressed Attention

I spent my whole career building passive income. Here's what I got wrong

Rikkahub-agent OpenClaw/hermes for Android phones

What it would have been like to experience the dinosaur-killing asteroid

The Changing Landscape of CTF-Based Learning

Show HN: A Claude Skill to render resume templates. CV/Resumes are HTML and JSON

Repositories Are Human/Agent Knowledge Factories – Sigplan Blog

GitHub takes aim at Claude Code and Codex with its new Copilot app

How to Use LLMs as Teachers?

UFO files nod to growing belief in aliens and mistrust in institutions

The Protein Shortage Is Coming

Accelerate

Using static websites for tiny archives

It's OK to want to have a good time [pdf]

MCP-stdio-guard – MCP stdio sanity checks

The Accidental Bestseller (2014)

Norwich Community Kitchen – Community Input Survey

Stera: Open-Source Infra That Turns iPhones into Spatial Data for World Models