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?

Analysis: EVs are now nine times cheaper than petrol or diesel to drive in UK

Rickrolling with a Pharmacy Cross

Bitcoin-rs: an independent Bitcoin node written in Rust

Liquid Glass UI-Inspired Glass Effects Library

SpaceX's Starship launching to orbit for first time ever today

Reverse Engineering the iPod Classic's Undocumented Mikey Chip

Three Days in August: What a DDoS Attack Exposed in Our Network

Large Scale Threat Actor Attribution from Infostealer Logs

Google: AI-Assisted Rewrites of C/C++ Dependencies to Rust

Personal Continuity Plans (Digital Legacy)

Big AI's content problem: Take the work, keep the money

LLMs in Professional Software Engineering

Can Life Be Explained by Physics? (Featuring Prof. Brian Cox) [video]

ETH Computer Science enrolment saw a 15 percent decline

Unified U.S. Site Blocking Bill Targets ISPs and DNS Resolvers but Spares VPNs

Show HN: Free alternative to graphics design giants

Are Big Tech bonds crowding out the US Treasury?

The Risks of Ignoring API Security During Mobile App Security Testing

How I Use "AI" (2024)

Show HN: Redthread – autonomous LLM pentesting with proof-of-concept exploits

Pixi adds non-Python deps to Python notebooks

Show HN: Impressive hand detection AI algorithm

The Cybersecurity Siege Economy

European industry is doing better than you may think

Doing math together in the age of AI

Show HN: Caspian – Desktop app to build and launch small vibe-coded projects

What if AGI was invented in Ancient Greece?

AI companies in race to demonstrate their model most threatening to humanity

37,500 border drawings: a map of the world as people remember it

iOS 27's Reworked Stub Islands