Ask HN: How can I load test PostgreSQL but avoid changing actual data?

1•LawZiL•10mo 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•10mo 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•10mo 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•10mo 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•10mo ago

Load test against a clone instead of a DB whose contents you care about?

Ask HN: Have you successfully treated forward head posture ("nerd neck")?

Snabbit, Urban Company instant maid female gig workers in India work in fear

JPEG Compression

Why Do Humanoid Robots Still Struggle with the Small Stuff?

How Can Governments Pay Open Source Maintainers?

Show HN: I built Wool, a lightweight distributed Python runtime

A Swiss Paperwork Massacre: Why We Fled to Stripe

Ask HN: Will US send ground troops to Iran?

Python JSON SQL DB

Image generation models can think

Why Do Clocks Move Clockwise?

Show HN: Clawscribble – Give your AI agent a 32×32 canvas to paint on

The datacenter where the day starts with topping up cerebrospinal fluid

Cycle 33 of the Training of 'Forge'

Licensed Microwave Link Map

Show HN: Fusionaly, Web Analytics with SQLite

Moscow's Internet Outages Drive Sales of Pagers and Paper Maps

The Forth Language [Byte Magazine Volume 05 Number 08]

Chip material prices double with Middle East crisis

BETO – A protocol that formalizes what LLMs don't know

OctopusOS – an operating system for governed AI agents

unix is the next ai evolution

Show HN: RSSext – Not a reader, a Chrome ext with the orig RSS protocol spirit

Columba: Messages and voice calls without internet

Show HN: Vercel Blob AI SDK Tools

You Can Approximate Pi by Dropping Needles on the Floor

'RAMmageddon' hits labs: AI-driven memory shortage is impacting science

Things we learned building for the first generation of agentic commerce

The Download: how AI is used for military targeting, and The Pentagon's war on

Temperatures expected to remain at or near record levels in coming five years