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

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

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

Agentic Coding 101 – Structured methodology for AI coding on large repos

Show HN: Corvus Robotics (YC S18) Inventory drones with full life-cycle autonomy

Google court filings suggest ChromeOS has an expiration date

FlashAttention-T: Towards Tensorized Attention

Sinofsky to Epstein on Cook and Forstall

Scaling GPU-to-CPU Migration for Efficient Distributed Execution on CPU Clusters

Nintendo Switch is the second-bestselling game console ever, behind only the PS2

Quantum Fiber is now part of the AT&T family

this is me

Diversification Across Time [pdf]

Stay Away from My Screen

MetaAttention: A Unified&Performant Attention Framework across Hardware Backends

Free as in Burned Out: Who Pays for Open Source? [video]

Documentation is more than you think

Show HN: Scrape (auto-discover APIs or HTML) & Monitor changes on any site

Good Engineering Managers Don't Leave the Technology Behind

A Protester, the White House and a Doctored Photo

Securing Transactions in a Trust-less world

Information Is Beautiful

Nature Became a 'Prestige' Journal

Good Old Days: Classic games and more

Tractor

Show HN: Reimplementing PyTorch from scratch (MLP, CNN) to learn the internals

Ask HN: What's your preferred Python tool to convert Markdown to print ready PDF

A Dilettate's Philosophy of Mind

Trump's UAE Chip Deal Is a National Security Risk

Iran's Internet Blackout is Unprecedented [video]

Ask HN: Is Cursor still working for you?

Does Intermittent Fasting Live Up to the Hype?

2 days left to comment on DOT's plan to hike US fuel costs by $23B