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?

The Chinese AI labs are making four pretty different bets. I work at one of them

Ask HN: Should the SFPL Have Tailscale

Show HN: MCP Server for Biblical Corpus

SK Hynix and SanDisk Unveil First High Bandwidth Flash Standard Specifications

Show HN: Marketing Team Eve Template

The Invariant Is Hiding

Ancient, never-before-seen 'earthworks' discovered in the Amazon

Search for Life on Europa Just Got Harder

Ukraine – Life and Death of Innovation in the Drone War

Show HN: LarpIn – LinkedIn, but everyone admits they're larping, one global feed

Ask HN: Does AI research need "world models" more than bigger LLMs?

Attic: Move stale files to an external SSD, keep them in Finder

Unsloth: Run and Train Local LLMs

For sale: early access to Trump's Truth Social posts

Show HN: GHScan – Paste a GitHub URL, get a free security report in the browser

Show HN: Launch – A Mac Script Launcher

Solving the Multi-Plane Telemetry Challenge with Argo CD and OSS MyDecisive

LLMs Can't Jump

Why did we wait so long for the bicycle? (2019)

Robinhood Private Company Fund to Back Y Combinator Startups

This Week in AI Native Companies #2: The context layer gets funded

When the apartment listing is too good to be true

Palantir soars 12% on blowout quarter, with US commercial revenue soaring ~150%

Chiral diffraction from aperiodic monotile structure

Agent needs a computer, not a container – introducing Cloudflare/computer

AI website builders: 10 prompt-to-app tools compared by pricing and code export

Show HN: mob.so – vibecode with friends in Discord

The Shape of Things to Come, Part 2: Model Welfare for Agentic Engineers

There Will Come Soft Rains [pdf]

A Dad's Solution to His Son's Difficulty with Watches