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?

Ask HN: What things might help me to become inference engineer?

Apple 'Hide My Email' Vulnerability Reveals Peoples' Real Email Addresses

Show HN: One Page Life Calendar

Egg Libor Was Also Manipulated

The Graduate-School Dropout Toppling China's Academic Stars

UK-US trade deal will lead to more than 200k avoidable deaths

Stashing data in WOFF2 color glyphs to get free Brotli decompression via Canvas

MacBasic (1985)

Ask HN: noticed how HN performs sentiment-analysis on everything posted here?

Nvidia Through a Crypto Miner's Eyes

Check Out My Website

Kids learn to bully from adults' threats, manipulation and criticism

Show HN: Rust Backed Fast Dataloader

Meta to sell excess compute, like SpaceX

Databricks LTAP Explained

Konsistent: Enforce consistent code for agents and humans

Show HN: Meow – The 4th and final JavaScript runtime and toolchain

SpaceX Hangover Spreads

Stevie: Vim Predecessor ("ST editor for vi enthusiasts")

Why is Apple asking me to pay more for Big Tech's AI obsession?

Early look at Anthropic's Claude Science app for researchers

Fable Is Back: This Safeguard Has Some AI in It

Making LLMs Better at Creative Writing Using Entropy

Programming Quotes

A new Android malware from Google

The Shilajit Dilemma

Show HN: Techno Kick

How to Tell a Real Win from Noise in a Tiny Eval

Senior SWE-Bench: open-source benchmark that assesses agents as senior engineers

Paper mill cancer studies get double the number of citations as genuine papers