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

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

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

Can you get a star in Super Mario 64 using only one button? [video]

The Department of Defense Wants Less Proof Its Software Works

The Situation: How Much Less Free Are We?

Will Paramount Cancel Jon Stewart?

Interesting Research Programs from the 2010s (2020)

TeraAgent: Simulating Half a Trillion Agents

Show HN: I've built a web based pdf/docx/pptx editor, the format .ldf

Interactively explore FastAPI endpoints with FastAPI-Voyager

Site and Formation Selection for CO2 Geological Sequestration

Defeating the Training-Inference Mismatch via FP16

The Psychology of Curiosity: A Review and Reinterpretation (1994) [pdf]

OpenAI updates terms to forbid usage for medical and legal advice

Show HN: Strange Attractors

Fix Your FODs

Introduction to Parallelism in PyTorch

Web of Science company involved in dubious awards in Iraq

Why tequila depends on Mexico's bats

Big Tech's market dominance is becoming ever more extreme

Fed 'Chorus' Comes Out Against Latest Cut, Citing Inflation

Show HN: Durbat – Write Rust Code Using Black Speech

Al Qaeda Is on the Brink of Taking over a Country

Show HN: Modern PHP development with Vite, it's ecosystem and PHP components

The Geometry of Schemes [pdf]

Photographing the rare brown hyena stalking a diamond mining ghost town

S.a.r.c.a.s.m: Slightly Annoying Rubik's Cube Automatic Solving Machine

Best Practices for Registration Forms

Hobbies Through Acquisition

Self-hostable open source MS Word editor with real-time collaboration

Behind y-s2: serverless multiplayer rooms

Auditing Permissions for All Shared Files in Google Drive