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

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

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

30 Years Ago Windows 95 Changed Everything

The Optics and Image Processing Behind Fundus Cameras

Steps Recorder Deprecation (2023)

How to Train Yourself to Have Lucid Dreams Through VR

How to Use Trending Topic and Keyword Finder

Compressing Embedded Files in Go

Python Software Foundation end-of-year fundraiser

Intermittent Hypoxia Increases Blood Flow and Benefits Executive Function

Just Right FM

1Crossword: Crosswords for Your Password Manager

Show HN: We open-sourced our internal tool for scoring PRs with Claude AI

SAGE: Semi-Automatic Ground Environment Air Defense System

Twins reared apart do not exist

Ask HN: YouTube Only Showing Ads?

Urik – The Privacy focused Android keyboard is now in Open Beta

Japan's first hotel with a human washing machine is now ready for you

What UI do you use on top of data engineering tools to look at data?

Powers of Ten (1977) [video]

3D-Agent

Tourists to US would have to reveal 5 years of social media activity: new plan

Creating a Benevolent Industrial Deployer for Memecoins

Internal RFCs saved us months of wasted work

Replican't

RAM Is Ruining Everything

Transcripts dialectics Gregory Bateson 19th of July 1967 [pdf]

Hark: Voice prompts for LLMs, meeting minutes, quick voice journaling

I Pitched Netflix a Friends-Style Casablanca Sitcom

Show HN: A 2-row, 16-key keyboard designed for smartphones

Verum 2 closed back option

Ask HN: Why is codex suddenly giving me this error?