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?

Postgres mirroring in Snowflake: what it costs

A database of temperatures for large inland lakes across the coterminous U.S.

Room for Ambition

Show HN: I re-read the terms of 175 AI tools twice; 60% changed in 15 weeks

How to build a SaaS product with Claude Code

Jev for Every Slack Message

AI researchers stranded Claude on a virtual desert island. It started to "play."

Top six AI spenders are spending more than the Russian govt. this year

America Is Now an Ally of Russia

Ask HN: What are the things LLM models fail at?

Quisnap, Somewhere between live streaming and screensharing

Asking minified JSON can reduce output token costs by 30%

Ask HN: Do you know Bitbucket are chaning their SSH endpoints?

Wine Now Runs on OpenBSD

Show HN: Twenty1 – Would You Rather and 21 Questions with Friends

Let's Encrypt is adding two additional remote perspectives for domain validation

Who does the work, the model or the software?

Study: ML using speech can estimate chronological and biological age

Scientists Find a 'Ghostly Green' Star, and That's Not the Only Crazy Color

Atom Echo on your own ESPHome build: push to talk, a doorbell, no internet

How AI is creating a lopsided US economy

Show HN: CanaryGuard – A <0.1ms deterministic AI prompt firewall with $5 bounty

Documents: Police officer gave her Flock login to her ICE agent husband

The Weak Points in AI Bonds Are Becoming Clearer

I have a thing for fast trains

Celebrating 35 Years of Linux: The Open Source Engine Powering Global Innovation

Training a Chess Reasoning Agent

What's on That Access Port?

Speeding up code review with `small-change`

Russian milsat closes from 200 miles to 3 miles of US satellite aiding Ukraine