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?

Node-RED Version 5.0 released

Ask HN: Do you install other people agent skills?

Show HN: A static SPA to query ATF / BATFE historic AFMER data

Rules Before Tools in Critical Infrastructure

AI Is a Confidence Booster

Hurdles to a hobby: climate change and runfluencer culture impact our daily jog

Nvidia's AI PC push banks on unproven demand beyond niche users

Glucosamine supplement may accelerate dementia and mortality

RubyLLM 1.16: concurrent tool execution, Rails-style instrumentation, and more

Apple failed to make its AI tool to comply to EU regulations, EU Commission says

Why az login ROPC ignores custom ports (CONNECT proxy workaround)

StumbleUpon Is Back

AV2 video codec released: 30% more efficient than AV1

Integrate on-device AI models into your app using Core AI [video]

Franz: Hacker News sent 10x the traffic

Show HN: fftext - quick summary, fact check, ELI5 on CPU

I Made a Vibe Coded Operating System

1979: Will Word Processors Start a Home Working Revolution?

Show HN: Squish – client-side video compression with WebAssembly

Fear of AI bubble ahead of SpaceX IPO

Loop Engineering

China's Unitree Will Dominate Global Robotics

AI Billionaires Brace for Pitchforks

Learning to lead in a hybrid human-AI enterprise

Musk's SpaceX IPO Narrative Is a Whole New Level of Bullshit

Is AI-written code buggier than human code?

China's all-round dominance, from batteries to medicine, from trains to AI

Decentralized AI Inference Marketplace

Show HN: Simple FFmpeg Download

Show HN: You have public speaking anxiety, so do I