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

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

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

Relaxation of US day-trading rules opens door to YOLO trading, higher risk

"SaaS became chat". This isn't just lazy design

Beirut: Now and Then

Airwallex is about to take on the payments industry – in the physical world

EU Launches Age Verification App

Claude Opus 4.7 Model Card

LLM risk spreading misinformation to humans who are least able to identify it

Our AI agent generates schema-aware, PII-filtered SQL across 700 tables

Two inmates at an Ohio prison built a secret hacking operation from behind bars

There's yet another study about how bad AI is for our brains

The World in 1975 (2007)

The Case for Doing Real, Hard Things

Claude Opus 4.7

Is Ireland the worst run country in Europe?

Show HN: NoPII – One line of code to protect PII before it hits your LLM

Largest study of pregnancy sickness uncovers six new genetic links

Rusting Rivers: Alarm Grows over Uptick in Acidic Arctic Waters

What Should Smart People Think About UFOs?

Claude Opus 4.7

A faster, cheaper Claude Code alternative with automatic provider failover

Channel Hosting on Prefix.dev

When El Salvador's Prisoners Go Free, She's There to Pick Them Up

Show HN: I've Added Gdbstub to UVM32

Sperm Whales Speak with a Complex Alphabet and Even Have 'Vowels,' Study Finds

Dependencies Are Someone Else's Attack Surface

Show HN: Sentinel – LLM browser automation using 10x fewer tokens

The Common AI Provider: LLM and AI Agent Support for Apache Airflow

Apple's 'AI coding bootcamp' could help its engineers fix Siri

A system that maps relationships across mixed data sources

You Own Your Role, We Own the Outcome

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

Comments

Relaxation of US day-trading rules opens door to YOLO trading, higher risk

"SaaS became chat". This isn't just lazy design

Beirut: Now and Then

Airwallex is about to take on the payments industry – in the physical world

EU Launches Age Verification App

Claude Opus 4.7 Model Card

LLM risk spreading misinformation to humans who are least able to identify it

Our AI agent generates schema-aware, PII-filtered SQL across 700 tables

Two inmates at an Ohio prison built a secret hacking operation from behind bars

There's yet another study about how bad AI is for our brains

The World in 1975 (2007)

The Case for Doing Real, Hard Things

Claude Opus 4.7

Is Ireland the worst run country in Europe?

Show HN: NoPII – One line of code to protect PII before it hits your LLM

Largest study of pregnancy sickness uncovers six new genetic links

Rusting Rivers: Alarm Grows over Uptick in Acidic Arctic Waters

What Should Smart People Think About UFOs?

Claude Opus 4.7

A faster, cheaper Claude Code alternative with automatic provider failover

Channel Hosting on Prefix.dev

When El Salvador's Prisoners Go Free, She's There to Pick Them Up

Show HN: I've Added Gdbstub to UVM32

Sperm Whales Speak with a Complex Alphabet and Even Have 'Vowels,' Study Finds

Dependencies Are Someone Else's Attack Surface

Show HN: Sentinel – LLM browser automation using 10x fewer tokens

The Common AI Provider: LLM and AI Agent Support for Apache Airflow

Apple's 'AI coding bootcamp' could help its engineers fix Siri

A system that maps relationships across mixed data sources

You Own Your Role, We Own the Outcome