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

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

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

The Khan Ted Institute [video]

Contra Benn Jordan, data center (and all) sub-audible infrasound issues are fake

Where Did FIFA Seat Lottery Winners?

Deception is strongly amplified in the job market (a statistical model)

Why every developer needs their own agent-skills

Vercel Incident Response Playbook

Third New Glenn launch suffers upper stage malfunction

Google should allow third-party search engines access to data, EU says

Show HN: TikTok-style mobile scroller for Civitai, self-hosted recs on a $5 VPS

Earthset

Salesforce Announces AI Initiative and Calls It 'Headless 360'

Show HN: A free, offline-capable math quiz app

AAVE price sinks 18% as KelpDAO exploit spreads

China's 7-tonne cargo drone completes maiden flight

"In Your Darkest Days, How Did You Keep Going?"

Show HN: Vastlint, inline linting for ad tags in VS Code, Kiro, Cursor

The insider trading suspicions looming over Trump's presidency

Claude-desktop-buddy [small ESP32 hardware companion software]

Vinjari – Decentralized Browser

GPT-6 released: Symphony architecture unifies text/image/audio/video

Mosaico: A fast, configurable tiling window manager for Windows

Chamath Palihapitiya's 30 Years of Business Advice in 13 Minutes

Show HN: I built a spaced-repetition vocab app tied to the books you read

Screenless Smartphone Patent

Show HN: Modular – drop AI features into your app with two function calls

Show HN: I built a local Elixir/Python pipeline to curate 14,000 RAW photos

How to Set Up OneBusAway Maglev with Bay Area Transit Data

Linux Foundation: Building a RISC-V CPU Core (LFD111x)

Humanoid robots race runners at Beijing half marathon [video]

Electric Wind-Up Plane Uses Supercapacitors for Free Flight Fun