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?

A Visual Journey Through Async Rust

AGI Constitution 01~02

Humans Haven't Stopped Evolving

Montana ETRB

Show HN: MutexCam – Global Camera and Mic Control on Mac for Zoom, Teams, Meet

Agentic test processes, LLM benchmarks, and other notes on agentic coding

Open Source Repo

JetZero

India's 'Cockroach' Protesters Declare Rare Victory over Government

We built the new fastest API for GLM-5.2

Show HN: Raise a virtual pet with your friend/partner

Google ignored my site for 13 weeks, so I audited everyone who outranks me

An ESP32 based plane radar for my desk

Lessons from Building My Own Products as a Freelance Engineer

Four Proposals to Improve Fuel Economy Standards (2021)

Show HN: Rules that stop AI coding agents from breaking working code

Show HN: I enhanced the UX for TemplatesOn, what do you think?

Multiple LLMs trying to reach a consensus

Show HN: I mapped every US golf course – 16k+ courses, free, no signup

What Colorado consumers should know about the cyclospora outbreak

Ajay Kumar on running MCP in production [audio]

When You Build the Wrong Thing

Show HN: What 180k words look like as a temporal knowledge graph (Oz series)

Do People Swallow 8 Spiders a Year While They Sleep? (2014)

How Many Bits Can an Adapter Write? Capacity and Memorization of LoRA

Dartmouth Workshop on Artificial Intelligence (1956)

1209X Agentic Engineering

Sally the Robot Was Coming to a New York School. Then the Plug Was Pulled

Show HN: Fitter – declarative web-data extraction,running in the browser also

NYU Stern pricing expert: The hidden 'AI tax' hitting your next phone