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?

The most official water costs $120k a gallon

AI Workspace for Your PC

Verisign Cooperative Agreement

Nvidia in Talks with OpenAI to Guarantee $250B Financing for Data Center

Show HN: 3D music visualization on Apple Vision Pro

Show HN: Watch random code typed out on an MS-DOS IDE

Sand battery: Finland's answer to a renewable energy headache

Why AI Doesn't Replace Software Architecture – It Makes It More Valuable

Malicious sites use JavaScript to build malware in browser memory

Show HN: CarbonSheets – Host your own customizable cheatsheet portal In GitHub

Physicists build a computer from 400 particles orbiting in liquid

Sticky notes on a storyboard became my savior productivity app

The Optimization Theory of Everything

Show HN: Quintile – keyboard-only window grids for Mac

Foxconn drops VMware, adopts hyperconverged upstart Arcrfra

Show HN: ASL V6 – Open-source AST red-teaming engine for Python AI agents

Show HN: Injectable time and timers with custom Date types for TypeScript

Multimodal foundation model for image and video understanding from Microsoft

Bullwhip Effect

It is empowering to hand off execution

Palette Vault – Color Palettes and Hex Codes

Porffor: An ahead-of-time JavaScript-to-native/WASM compiler

Newpipe Extractor in WASM

Paywall-free News Aggregator

Show HN: a Rust, Wayland, Vulkan 3D CRT, screencast application, retroarch cores

India cuts internet, then demands removal of bitchat's code

Improving Terminal Ergonomics

Why Lean is faster than Rust

Renaissance Man: imago ex machina

Genvap – Turn a screen recording into a cinematic app showcase video