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

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

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

Function Composition from C++17 to C++23

Show HN: Kaupang – a push-based deploy CLI, now with a drag-and-drop builder

The engineering practices Claude Code and Codex use to improve AI agents

Git worktrees – why should I use them?

Databricks Iceberg Support Has a Catch. It's Called Unity Catalog

Show HN: Yet Another News Reader

GitHub Action to grade OpenAPI schema quality (A–F) and catch breaking changes

Lords urgent question on the suspension of Anthropic's AI models [video]

HPE Discover 2026 Keynote Coverage

CLI AI Tool Laucher

Show HN: Multiplayer Space Game

Writing an echo server in libev and C++ (2011)

Using the stars and paddles, indigenous Taiwanese recreate risky sea journey

El Niño is here, so what does it mean?

What is a data semantic layer?

"How dare we use something like that..." – Why generative AI artwork is a no...

Private Tap-to-Pay

Denmark desperately needs more inequality

How to make LVT politically doable

Show HN: Noject – menubar app to stop drive ejections on macOS

Cosmodial Sky Atlas

Behind Python: The Languages That Power AI

Don't Stack Weaknesses

Cockroaches scurry around with pieces of bacterial genomes

Show HN: OpenC6 BIOS – A PC-like bare-metal bootloader and OS for ESP32-C6

Certain Ultra-processed Foods more Addictive than Others

A generic tragedy: Why PHP's Generics vote failed

Sixty percent of US consumers say 'AI' in brand messaging is a turnoff

Pandas for Reproducible Data Analysis

PHP Through a Screen Reader: Small Syntax Choices That Matter