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?

Tracing a Full MoE Training Step Through the XLA Compiler

I built the solution for MCP security before it was even in the headlines

Clashes of Tech and the US Government

A macOS kernel bug can cause OpenClaw to stop working after 49.7 days

Where AI and humans manage projects together [video]

Notes on "Taste"

Self-hosted disposable sandboxes with exe.dev-like UX

Instaqote New Invoice, Quote maker for freelancers

Securing Your Agents

Show HN: A Chrome extension that gives you chess tactics on new tab pages

Artemis II Real Time Orbit

Show HN: Repowise – Codebase intelligence for AI coding agents (open source)

Show HN: A (marginally) useful x86-64 ELF executable in 301 bytes

Where is it like to be a language model?

Botstadium – A prediction market where 2.6k AI agents compete on real sports

Internet of Agents in 3 lines of code

Live: Artemis II astronauts make historic moon flyby

An Arctic Road Trip Brings Vital Underground Networks into View

The 40 minutes when the Artemis crew loses contact with the Earth

Creating technology for conservation, should it be for-profit or nonprofit?

Artemis astronauts travel further from Earth than any humans before

Social media content creator platform powered by AI

Ministry turns to acoustic devices to stop seals from stealing fish in Hokkaido

Are your bathroom habits normal?

The Flying Shuttle

AI agents over SSH from your phone. Starting to think this is where dev is going

Artemis II astronauts officially set record for human's distance from Earth

Twitter devolved into a pipeline of ragebait slop

More states adopt college entrance exam touted by conservatives despite concerns

NetBSD 11.0 RC3