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?

Secure On-Premise Deployment of Open-Weights Large Language Models in Radiology

Craig Federighi Explains Why Apple Pivoted to a Siri Chatbot App

I Hate (Most) Keyboard 'Fn' Keys

Collective of economists considers 'growth' a doomed strategy

macOS 27 Golden Gate Is the Last to Support Intel Apps via Rosetta 2

LinkedIn wants to own B2B creator discovery with new creator marketplace

Oh Good, Screwworms Are Back

Quantum error correction with the toric code on neutral atoms

Show HN: GripeFind – find product pain points without Reddit API

Ask HN: Are most corporate SWE jobs performative?

Scientists Precisely Edit Human Embryo Genes

Coding Agent Memory Benchmarks

Commonwealth Fusion makes the physics case for its 400 MW reactor

Show HN: Employment contract analysis for Ontario's Workforce

Show HN: Orange Crumbs - Wikipedia articles filtered by Hacker News

"I've lost sentences of text to this incompetent implementation."

Cybersecurity Product Launch Specifically Keeping AI in Focus

Building a multi-tenant SaaS for an emerging market, what nobody tells you

Not Wordle

Meta's attempts to play catch-up with AI

Show HN: Ray Hosting – Topology-aware game server orchestrator made from scratch

OWASP Dependency-Track 5.0 Is Now Generally Available

Re-quantizing a local LLM 14x faster by skipping the tensors that didn't change

The Tick-Tock AI Development Cycle

Paris Hilton is Android's first icon in residence

Google Gemini down? Users report problems with AI chatbot

Show HN: Scribix – Transcribe audio and video in the browser

Let's Encrypt Certificate Rules Now Include U.S. Sanctions Warranties

Dependency Graph Is a Liability You Haven't Priced Yet

WebCLI – Agent Interface Device for the World Wide Web