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?

Parent and Child Screen Use and the Effects of Phubbing

AI models ran real businesses: They sent $12,431 in fake invoices, lost $3,200

Show HN: HomeCat – Design your backyard office

Another Heart Drug Fails Cardiologists

Show HN: Can you tell a real tweet of a certain user from an AI one?

Adobe ends an 18-year era as AI pressure mounts

Quotes

Show HN: Office42, a Clone of Excel

It's Time to Change the Math Calculus

Proton protocols: The next chapter of WireGuard at Proton VPN

Show HN: See why Google and AI ignore your site and how to fix it

Liquid Network Pauses After $320M Bitcoin Withdrawal

Huawei, Xiaomi launch new foldable smartphones ahead of Apple event

Europe's biggest mobile operators in talks for satellite-to-mobile venture

Volkswagen to cut 100,000 jobs by end of decade

VMtree is a fast and easy way to provision self-hosted ephemeral VMs

€33M Raised in Final Edition of French Streamers' Charity Event

Show HN: Possess, a TUI to browse and transfer coding sessions

What We Can Learn from Claude's Fable 5.1 System Prompt Changes

Why Labor's Share of Wealth Is Shrinking

The Artificial Analysis Intelligence Index v4.3

TrenchBoot DRTM Launch of Xen on Intel Sapphire Rapids

Arabizi

Arguments in Favor of AI Fair Use

One place to build, run and govern your company's AI agents

I've operated petabyte-scale ClickHouse clusters for 5 years

After IndexNow: 6 Launch Channels Indie Founders Skip, and How to Hit Them

Getting started with the Mantis harness to find and fix bugs

GPT-6 Astra and the Fourth Exponential

Halide – a language for fast, portable computation on images and tensors