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?

The SEC plans to end quarterly reporting

I scanned 100 random Supabase projects. 22% leak user data anonymously

When GPT 5.5 flags your chat for possible cybersecurity risk–ask it to help you

The Vercel breach wasn't just a hack, it was a trust failure

The future of work isn't human vs. AI, it's human with AI

7 lines of code, 3 minutes: Implement a programming language (2010)

Microbenchmark-Driven Analytical Performance Modeling Across Modern GPUs

Baidu ERNIE 5.1 just dropped

RPCS3 says "learn to code" as it bans AI agents from project

I want to archive but DMCA stopped me

The eye in your pocket: digital devices are made to track you

PyTorch DevLog

Lie-to-Children

I reverse engineered macOS to disable built-in display

Ask HN: How much does Gemini API cost for a simple n8n workflow?

MCP server that gives a forensic verdict on biopharma catalyst plays

Addressing GitHub's recent availability issues

Plenty of Hours in the Day

Show HN: Kheeper, a registry designed for bootable images

Trump's Border Spending Spurs Boom in AI-Infused Surveillance

America the Undammed

University student arrested in Taiwan for radio hack that halted high speed rail

SmartScreen warnings triggered after migration from EOC. EV certs affected too

Open Source Volunteer Opportunities

SVG Frame-by-Frame Animation Generator

The Bottom-Up Building of a Language for Subleq with Text Macros

The Greatest Shot in Television: James Burke Had One Chance to Nail This Scene

Copilot Builder enables welding with AI

University Claims Withholding Water from Data Center 'Unlawfully Discriminatory'

I Got Tired of Rewriting Prompts So I Built This