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?

Fugu – A multi-agent LLM orchestrator delivered as a single API

Vibe-coding platform Base44 launches own model as AI startups seek defensibility

Tripadvisor AI summaries give glowing reviews to dangerous hotels

Immich v3.0.0 Released

Show HN: Social and context-aware AI platform to do math

Trouble Transitioning (2025)

Speech and Noise Corpora for Pitch Estimation of Human Speech

Cursed circuits #5: capacitance multiplier

Understanding B-Tree Indexes in PostgreSQL: A Comprehensive Guide– Part 1

How to Get a Healthy, Shiny Coat on Your Dog: The Ultimate Guide

New contributors to GNU Emacs over time

WebGlean – API that turns any site into clean Markdown for LLMs

Slow Tuesday Night (1965)

The Strange Locomotion of Spirocuta

Show HN: 3·6·9 COMMANDER, a turn-based strategy card game

The Mental Models I Use to Work with AI

State Sponsored Media? No Thanks [video]

View from the Shifting Mound

Contributor Visualization for Superset: top contributors own 90% of lines

Social media management for AI Agents

Eclipse Enclave

A 3D-printed Raman spectrometer

CommaAgents V2 Sharable Agent Orchestrator Release Candidate

Turn Your AI Agent into an MCP Server for ChatGPT, Claude and Cursor

The full stack of terminals explained

Large planets lighter than cotton candy

We'll fight the platform war against big AI

Raylib 6.x gamejam – Make a 720x720 wasm game with raylib in 6 days

Group project, but make it 1776 – Google Workspace ad [video]

Delta flight hit by firework while landing at Midway Airport on Fourth of July