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?

Archaeology team unearths 'prototype' of world-famous Stonehenge monument

I made a free Islamic prayer app to quit saying "I'll pray in 5 minutes"

Why recruiters can't find workers and new grads can't find jobs (it's not AI)

Nuclear war survival guide reveals seven everyday items if disaster strikes

Crayola Marker Airbrush Kit

MacSurf 2.0 – Netsurf for Mac OS 9 Released

Show HN: PitchTrail – Perfect Pitch for Kids

UN adopts first binding framework for automated driving systems

What if Berlin needs its bunkers back?

Kode Dot Programmable pocket device for makers, pentesters and geeks

Anthropic extends Fable 5 access through July 19

Ask HN: List of functional institutions fighting towards a free internet

Ask HN: What Are You Working On? (July 2026)

Show HN: Tenclicker – a WebLLM clicker game about whatever you want

Analysis: Popular Chrome Extension ModHeader Exfiltrates User Data

Free Community Radio Player (Win, Linux, Android, iOS)

Uber is lobbying to keep robotaxi rides 85% human in New Jersey

Show HN: Capn-hook for coding agents – don't grep the same mystery twice

We're extending Claude Fable 5 access through July 19

Show HN: Agent-run – Run a coding agent in a sandboxed environment

Since Chronium 148, Math.tanh is now fingerprintable to link underlying OS

The Meta Glasses backlash is changing how (or if) people use them

Show HN: Adaptive Recall, persistent memory for AI assistants over MCP

Don't Build an RL Environment Startup

Adapt, Improvise, and Overcome

Storm clouds gather over America's financial supremacy

AI shorting penny stock based on human psychology

Show HN: A subjective AI eval. Arcade games built by AI

Show HN: Summarize – local-first tool that turns videos into notes

UK has 'no future' if it fails to act on ecosystem collapse