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

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

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

Abstract Machines for Logic Programs

Rex is a secure script execution engine that uses Cedar policies

Do houseplants improve air quality?

Analysis points to a unexpected cause of reading difficulties

Grok 4.3

AI creates a fearsome cold-war-style dilemma

Visit a Mosque in video game format and learn things about Islam

Build Your Own ALU

Why saying hello to strangers can be good for you

DeepInfra raises $107M Series B

Some gene therapies no longer require clinical trials, thanks to new FDA rule

What Software Engineers Can Learn from the Aviation Industry

Chasing Chicago's movable bridges (2014)

Windows 11 is getting faster the lazy way

A multiplayer poll where you have 10 seconds to answer

Did the Soviets Collude in the 1953 Candidates Tournament?

Docker Containers Are Leaking (and How to Plug the Hole)

Engineering Recovery

Mark Zuckerberg Told 8k Employees Their Layoffs Are a Line Item in AI Bill

60fps Video on a CGA? – The GlyphBlaster

Xi's Forever Purge

Physics experiment hints at the existence of 'anyon' particles

Catatumbo Lightning

Deskbrid – Linux desktop control over a Unix socket, for agents and scripts

Kendrick Lamar to an Italian Poet

24/7 AI-powered radio station. Generates music, writes hosted breaks,speaks them

New York Stock Exchange to open private members' club on Wall Street

Abbey Library of Saint Gall

Maryland Power Bills: $1.6B Data Center Subsidy

I built a private memory system for remembering people