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

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

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

Cognitive profiling from speech, not multiple choice

The Philosopher and the Tsar

Ask HN: Why have supply chain attacks become a near daily occurrence?

Mental Health Fashion

Show HN: I built an app after nearly missing a passport expiry

Screenpipe

You can now run a full Linux operating system inside a 6mb PDF

Show HN: Provero – Data quality checks in YAML, compiled to single SQL queries

Office EU: European-owned cloud based office suite

Tokens Are the New Oil: How China Is Quietly Winning the AI Economy

The United States has become a rogue state

Show HN: Tiny Axios Alternative, Fch

Our AI traced the axios NPM attack and found how the payload hid itself

What is 'tokenomics' and how would China gain the edge

Delve – Fake Compliance as a Service – Part II

California to impose new AI regulations in defiance of Trump call

Anthropic, The Pentagon, and the Future of Autonomous Weapons

Show HN: An AI Operating System

Archive review: a gorgeous sci-fi movie that fails the Black Mirror test (2020)

What to Know About California's Executive Order on A.I

Tropical geckos are more adaptable than we thought

Purple Lifeform Photographed Sprouting 'Tentacles' on the ISS

Pairs of atoms observed existing in two places at once for the first time

Americans Have Never Been All That Excited About Going to the Moon

Claude usage limits hitting faster than expected

The mindbending 'cyberpunk city' in China that exists on so many levels

AI Tools Directory – Verified Anti-Hallucination SEO Tools – LogicBalls

How fast can algorithms advance capabilities?

Vaping likely to cause lung and oral cancer, Australian researchers find

Artemis II mission countdown clock started