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

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

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

Moiré materials based on M-point twisting

Iota Legacy Migration Tool Issue

Turns out you can just hack any train in the USA

Kawase Hasui Catalogue

Trump announces 30% tariffs on EU and Mexico, starting Aug. 1

Show HN: I built a entire blogging platform just for myself lol

Ask HN: How important is to learn Software Defined Networking?

Govern Amazon Bedrock Gen AI Models in SageMaker Unified Studio

Hill Space: Neural nets that do perfect arithmetic (to 10⁻¹⁶ precision)

Dynamic Chunking for End-to-End Hierarchical Sequence Modeling

Show HN: Invoicesplitter – Split and Rename invoices in seconds

The Jackpot Age

Migrating My NAS from CoreOS/Flatcar Linux to NixOS

Using terminal-notifier in Claude Code to get custom notifications

Commodore 64 Ultimate gets released

AI has killed free developer tools

Azure MCP exploited: Maliciously leaking user's KeyVault secrets to attackers

Python: From Zero to Hero

Show HN: Smart Guardian – a privacy-first Chrome extension to protect kids

Flea Circus

Show HN: MailDropHQ – Free Disposable Email Without Signup

Show HN: Alertify

M&A Is Dead – Long Live Hire&License

BBC gains rare access to the Congolese mine powering mobile phones

SearchLTE don't just search it, solve it

mkcd: The missing shell shortcut

The JPEG XL Image Coding History, Features, Coding Tools, Design Rationale

Show HN: I made a vencord plugin to search for messages across discord

IMO 2025 (International Math Olympiad) starts tomorrow – will DeepMind win Gold?

The largest Git repo on the planet