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?

Cyber Letters of Marque and Reprisal – Digital Privateers to Combat Cyber Crime

L3 Simulator: agent orchestrator over GH issues

Google Gemini Launch Delayed as Tech Falls Short of Internal Goals

California authorizes its DMV to join a national ID database

JD Vance: Jeffrey Epstein had clear connection to Mossad

Airbus migrating 70 critical apps from AWS to France's Scaleway

AI disruption in private credit: exposure to software firms in BDCs (BIS)

NASA Dragonfly - a rotorcraft to explore Saturn's moon Titan

Connect more of your apps to Search

Linus Torvalds rebukes anti-AI stances in the Linux kernel code review process

Nintendo Switch Joy-Con Removable Battery Replacement Procedure

Please Stop Making Me Opt Out of AI

MergeStat: Query your Git repo with SQL

Young Drivers Are Going 'Psycho' on the Road–and Filming It

CLR: Checker of Lifetimes and other Refinement types for Zig

SPCX is now Wall Street's most shorted new stock

Minefield Quiz: Countries of the World with No Borders

Pricklypear: Vibeslop Interpreted Lisp with Postgres AST, Written in OCaml

React Native in 2026

Show HN: easy-tz – A fast, 10KB, dependency-free getTimeZonesAt(timestamp)

OneCommander: Modern File Manager for Windows 11 and 10

Switzerland turned its train tracks into solar power plants

Show HN: A Claude Code skill that finds dev tools – or stays silent

Kbd-1.0-Codex-Micro

Can AI reliably generate dashboards from Excel or CSV files?

Choosing GPT-5.6 Sol, Terra, or Luna in Codex

Show HN: Pagora AI, WordPress AI page builder that outputs plain HTML/CSS/JS

Show HN: MechArchive – an open, sourced catalog of the robots

The Moment Steven Bartlett Realizes His Podcast Is Compromised [video]

Capture Clauses as Effects