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?

Scientists vote to end leap seconds, redefining how the world keeps time

Self-Evolving Agent Harness

Greece drafts law requiring data centers source 60% of power from renewables

Japan business manager visa holders leaving without reentry permits surge

Instagram Reel and Video Downlaoder

No AI. Build your own tree directory viewer in C by hand

Tesla makes cars that fly? [video]

Chirp Pong – uses microphones and audio for networked game play

Faster Post-Quantum Cryptography with JDK Intrinsics

SPJ: A Taste of Verse (Programming Language) [video]

Modernizing the VS Code UI

What does the (Fire)fox say?

A College-by-College Guide to Which Majors Pay Off – Or Don't

Earthquake alert falsely reports magnitude 9.9 and intensity 7 in Japan

Show HN: Lobby Domains: Find Domains with Chrome's On-Device AI

The Russian Bots Went Silent. Or Did They?

Dotnetup

The Thinking Leakage in Reinforcement Learning for LLMs

Sergey Brin says Google's AI models are reaching recursive self-improvement

Researchers discover rogue Chinese AI agent fleet and talk with it

Linus Torvalds on "don't use AI for X"

Local and Distributed Large LLM Inference with MLX and RDMA on Mac Studio

All.net Analyst Report and Newsletter [pdf]

3DCodeVerse

CoreWeave Founders Shield Fortunes with $3.3B Stock Sales

Artificial Intelligence, Economics, Sustainability, and Irreversibility

The Cuckoo's Egg

Show HN: Kitaphone – Call mobile phones and landlines directly from the browser

Major AI Copyright Ruling Lawyer Explains [video]

Researchers Stranded an AI Agent on a Virtual Island. Things Got Weird