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

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

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

Ask HN: Google account on old yahoo.com email hijacked to Google Workspace

AI Agents Can Autonomously Perform Experimental High Energy Physics

Asia boosts coal use as Iran war squeezes global LNG supplies

The Rise of the Ray-Ban Meta Creep

DOGE Goes Nuclear: How Trump Invited Silicon Valley into Nuclear Power Regulator

Coding After Coders: The End of Computer Programming as We Know It

Debunking Zswap and Zram Myths

Building a symbolic math REPL in C

Aletheia – deterministic COBOL verification for mainframe migrations

Porting Doom to AIX on IBM RS/6000 [video]

LLM Neuroanatomy II: Modern LLM Hacking and Hints of a Universal Language?

Ask HN: Anyone trading prediction markets programmatically?

Hack discovered at (NL) Ministry of Finance; Unclear if data was accessed

USA bans all new routers for consumers

Synaps a self-hosted personal health monitor as a weighted knowledge grap

Steve Jobs, speech at the Apple campus (1999) [video]

Can It Resolve Doom? Game Engine in 2k DNS Records

Yann LeCun's LeWorldModel: Stable End-to-End JEPA from Pixels

America's Chief Financial Officers Say AI Is Coming for Admin Jobs

The Wrong Abstraction

A Mysterious Numbers Station Is Broadcasting Through the Iran War

OpenBSD.Amsterdam

The orderbook dynamics of prediction markets

Donut Lab's solid-state battery could barely hold a charge after getting damaged

Show HN: Offline-first UK train planner

curl > /dev/sda: How I made a Linux distro that runs wget | dd

More Magic Math from OpenAI?

Asyncio is neither fast nor slow

Emerging SaaS trends that are accelerating

Show HN: CertPulse – Certificate monitoring for multi-cloud teams