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

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

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

Uncrossy

Show HN: Constela – Build web pages using JSON instead of JavaScript

Which programming languages are most token-efficient?

Fed Chair Powell says he's under criminal investigation

Men Who Are Super Competitive About Sleep

Karmic Tail Calculator – a free, explainable Matrix of Destiny tool

AI models were given four weeks of therapy: the results worried researchers

I made a simple agent for PR reviews. Don't use it

Show HN: Fetch – A minimalistic and super fast macOS app launcher

Show HN: Tmp.tf – Cross-device text transfer with just a channel name, no signup

BowMac Sign

Show HN: Web CLI v0.2.4 – Added YAML/JSON validators with auto-fix

Show HN: Neurop Forge – AI executes verified blocks instead of writing code

Chasing mythical creatures: a critique of entrepreneurship's unicorns obsession

Statement by Federal Reserve Chair Jerome F. Powell [video]

Maybe the database got it right

Jerome Powell Responds

The Living Internet

Internet History – One Page Summary

Additively Manufactured Dragonfly-Inspired Wings for Flapping Vehicles

Profession by Isaac Asimov (1957)

Defense Secretary tours SpaceX missile startup

Criminal charges against Fed Chairman for defying POTUS wishes

The U.S. Government Just Followed Through on Its Ban of DJI Drones

Tintcd: Directory-aware terminal theming for PowerShell. CD, but colorful

The Good Club

Show HN: 2k or Nothing – A Social Platform Dedicated to Long-Form Content

Jon Skeet Facts

OAuth 2.1 Dynamic Client Registration for AWS BedrockAgentCore Gateway

Serverless RAG and MCP on AWS with S3Vectors and Agentcore