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 average cost for a new vehicle in the U.S. hits a record high: over $51,000

JSQLite: Browser-safe TypeScript version of SQLite's read-only implementation

Microsoft Outlook/365 still down after 12 hours

Show HN: PharmA2A, an open protocol for pharmacy-to-supplier orders on A2A

Michelangelo AI

IceWM

Agent Skills

Feather (Codevember)

Copyright-Strike-for-Hire: Lawsuit Targets Service over Fake Meta Takedowns

Shutting the Door on the XCPE Botnet (Threat Intelligence)

Show HN: My Script to Diff iTerm Configs

Show HN: Vending machine uses words, not money

Why Does Pitching Integrators Feel Like a One-Way Street?

US Justice Department has not concluded Alice Huang was Chinese spy

Hacker New Link Extractor

Fake Shemp

Proof Assistant as Platform: Building the Next Generation of Verifiers [pdf]

NASA's Escapade Completes Powered Earth Flybys, Now En Route to Mars

The Scandal Is Bigger Than Jensen Huang

Open Source AI Salons: Shaping the Osaid, Together

Microsoft struggling to restore service as global outage affects services

Are universities on the brink of an Uber-like revolution?

The Lost World of Aquaterra

Outlook still down for anyone else?

Show HN: I built a numbers game for my kids, with no tracking in the app

Jensen Huang's Downfall Is a Sign of Silicon Valley's Power Drain

Huang's fall is an indictment of founder mode at Big Tech

Rocket.Chat ABAC Structure

When Belonging Means Believing, Even Preschoolers Resist Changing Their Minds

Glue work is still harmful in the age of AI