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

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

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

0.12949 This is not randomness this is Determinism HST

The Era of the Tiger Mom Is Over. Enter the Beta Mom

Show HN: WfmOxide a Rust parser (.wfm/.isf) with CLI and time axis

FreeBSD – A Lesson in Poor Defaults

Bill Gates' Mosquito Factory in Colombia and Its Contribution to Health

Lego raises age limit to 100 for David Attenborough's birthday

AI Act Article 50 transparency rules. Heading for another cookie consent moment?

Simpler Agent Orchestration WTF

Foo on You, Asparagirl! (2002)

I made a better viewer for the WAR.GOV/UFO Files

Open-source Express.js dev panel for routes and request logs

Engineering as Humanity's Highest Achievement

Three Cultures of Math

Chess.com produces 3500 ton of CO2 in air for loading JavaScript bundles

Mlx-serve – run LLMs natively on your Mac

Outlook on Windows silently scales your email by 1.5×

Show HN: Query years of HN discussions and comments as a knowledge graph

AI cost optimization tool "distillfast.com"

UtaForth – 303-byte 16-bit Forth in pure Netwide Assembler

LLMs Won't Replace Programming Languages

Rotten Dot Com

Machine Learning Offers Faster, More Reliable Analysis of Fermi Surfaces

Arm, the UK and Apple

LLMorphism: When humans come to see themselves as language models

Real signals or artificial stereotypes? Adventures with a cultural Copilot

In a quest to becoming AI independent

Show HN: Tinyblok – publishes conditioned sensor data to NATS from an ESP32

RISC-V Serial Debug Protocol Specification (Draft)

Gen Z Resentment Toward AI Grows as Adoption Stagnates and Workplace Fears Mount

Eve