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 Lasting Influence of John Berger

Foreign Comiket attendees locked out as Ticketpay geoblocks non-Japanese IPs

Show HN: Rusty Valve – Chemical plant valve benchmark where ACT scores 0/36

NRC Issues First Construction Permit in Nearly 60 Years for Gas-Cooled Reactor

The Forever-Day Problem: Unsupported Software on the Public Internet

Ring Robotics

Show HN: App to help parents find, plan, and review outings with their kids

Data Centers and Local Labor Markets: Evidence from the Texas Experience

Richard Posner, influential and iconoclastic federal judge, dies at 87

When LIMIT 10 Means 500

Bad ideas that aren't bad anymore

Show HN: Eyes-free spoken N-Back where you choose the words (single HTML file)

Neurons as an Interpretable Basis

Some Memories of Early Anthropic

The Myth of "Neutral Code"

Fortell DSP Pipeline

Show HN: Paper Radar – daily ArXiv triage, now runs on Claude Haiku too

Show HN: Four Seasons in Real Time

Show HN: Ramasite, a Self-Contained Laravel/Filament/SQLite CMS

Show HN: VpsPilot, Manage a Linux VPS from Android, with undo for config changes

Upcoming meeting could determine if US loses its measles elimination status

A look at what building with the Web Models API looks like in JavaScript

Show HN: OpenSwells – free, open-source surf forecasts for 5,800 spots

Why Do Astra and Opus 5.5 Fail Simple 2D Mazes?

Can't Be Pumped. Can't Be Dumped. Can't Predict It. Can't Manipulate It

Lean Grimoire

Social Proof

Compromised GitHub accounts push CI secret stealer to 61k repositories

Lessons from 59 Linux Kernel Bug Reports

Memory safety bugs in Rust std/core