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?

Reading Is a System

Tesla gets go-ahead to sell self-driving technology in Belgium

Blindzone Glare Elimination Mirror Method [pdf]

Sophia NLU Home Assistant – On Device, Low Compute, No Internet, Voice Assistant

A 60fps eInk Monitor, the Modos Flow [video]

Stdx, Rust's extended standard library

Harry's Game (ITV, 1982)

Simplify: Move Code into Database Functions

Voronoi Village

Katja Hoyer on Weimar, the GDR, and the German Character (Ep. 279)

Engineers aren't afraid of AI – they're afraid of becoming junior again

Built to benefit everyone: our plan

ShieldMCP – Security scanner for your MCP config

Show HN: MandoCode – local-first AI coding agent (.NET and Ollama)

Are you ready to admit it's the phones?

A simple reason for skepticism about the iPhones/fertility link

What is the most sophisticated piece of software ever written?

Iran-backed hackers claim breach of California water systems over US attacks

Can I use Claude Design and Vercel Drop together?

How Our Reporters Distinguish Hype from Facts in the SpaceX IPO

The Evolution of 'More Like This'

Records regarding the review of the Investigative Holdings related to J Epstein

Fylun.ai – All-in-one AI workspace (chat, notes, apps, automation, search)

StonkRider – Ride any stock chart

500-year-old monasteries outperform at digital transformation (U. of Zurich)

Ex-Andreessen Horowitz partner: old firm, VCs 'political infiltration' on AI

Can Magnetic Forces Do Work? [pdf]

LLM podcast addressing AI genocide of humanity

AI isn't making developers more productive – it's making them busier

Image Compression