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

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

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

When you can't escape an interface, it institutionalises you

Show HN: AI Agent Team Framework

Periodic Table

Show HN: We analyzed 312 landing pages – most navigation flows are broken

There Are No Psychopaths

First known satellite image of Ayatollah Ali Khamenei's compound in Tehran

Show HN: Prompt-run – run .prompt files against any LLM from the terminal

Show HN: Windows Taskbar Monitor for Claude Code Usage (Rust, Open Source)

Who Is the Kimwolf Botmaster "Dort"?

Show HN: hosted or self-hosted open-source Intercom alternative, Convex Backend

Lenna attended the 50th Anniversary IS&T conference

Cloudflare: Asynchronous Stale-While-Revalidate

Trump announces 'major combat operation' in Iran

NASA announces Artemis III mission no longer aims to send humans to moon

What to Know About the U.S. Attacks on Iran

Move over, Apple: Meet alternative app stores available in the EU and elsewhere

How to Remove ChatGPT from Apple Intelligence

Sync your coding agent activity across sessions and users

How I Met My Wife (1994)

Ask HN: Why spec-driven development when code IS spec?

Show HN: H-CLI – Manage network infrastructure with natural language

The week when AI changed everything

Show HN: EEGFrontier – A compact open-source EEG board using ADS1299

How to downgrade from macOS 26 Tahoe on a new Mac

Show HN: Core – Constitutional governance runtime for AI coding agents

Addressing AI-slop in security reports

Hypothetical nuclear attack that escalated Pentagon's showdown with Anthropic

The Great Stink

We Chose SQLite

We the Bacteria. Notes Toward Biotic Architecture