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?

Unicode variation selector 15 and text vs. emoji presentation styles

Show HN: YC Has It – describe your problem, find the YC startup that solves it

The Ride Is the Ride

Polish-Lithuanian Commonwealth

LPC for Speech (1993)

Show HN: A useful service for back end devs

The Demographics of Central Europe [video]

Why Garmin Bought TrainingPeaks

AMD EPYC 9006 Venice Announced and Looks Poised to Be a Grand Slam

Everybody Wants to Be a Dev

Show HN: Chrome extension for clean, readable SEC filings (10-K, 10-Q, 8-K)

First wave power plant receives DNV certification

How to Check Aadhaar Card Status Online – SMFG India Credit

Orwell on AI

My football predictor scores 0.203 vs. the bookies' 0.198 – and loses

A VSCode extension for AI to understand your project

Cloudflare Nimbus – Docs for the agentic web

The IBM PC, Part 1: Arrival

A For-Profit Path to Keeping Housing Affordable

AI generated PRs can hide malicious intent across several PRs

Fraudulent Dialer (Wikipedia)

Microsoft pressures LG into killing unwanted McAfee ads

I Made the Perfect Outline Asset for Unity

Emergent Misalignment Recruits a Pre-Existing Persona Subspace

Volkswagen CFO addresses plant closures, job losses as profits sink

Two things every MCP author should add

Axon, a TypeScript framework for agent development

Gen Z protests in India demanding resignation of Education Minister

Dynamic JS function environments at runtime. Zero deps, total control with Nexus

European researchers want to study social media's harms but can't get the data