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?

My suitcase robot gets high off a real gas sensor wired into the LLM sampler

Project Valhalla, Explained: How a Decade of Work Arrives in JDK 28

Computer Chronicles

Understanding Geometric Algebra for Electromagnetic Theory

Future of Software Engineering (2003)

The Lonely Work of Moderating Hacker News (2019)

GLM 5.2 vs. Opus

Climate change: the CO2 emitter you may not know about (2018)

Mark Warren's favorite novels about a child's immersion into wilderness

My favorite classic working-class novels of all time

Iran Delays U.S. Peace Talks over Israeli Attacks in Lebanon

Bobby Prince Jr (composer of classic Doom soundtrack) has passed away

So You Want to Define a Well-Known URI

World Cup AI: which AI model is winning the World Cup?

Ongoing: Cloudflare customers seeing incorrect invoice payments for 2.5 days

The ISA Doesn't Matter Where It Counts

Ask HN: Is anyone else leaving AUR?

Apple A12 and A13 Chips: New Unpatchable Exploit

DNI does press release on Covid-19 origins and coverup

I reinvented website FAQ sections

The comfortable slow boil of LLM assisted coding

Renting vs. Buying – A case study for Bangalore

Show HN: `pbi`, an image aware pbcopy/paste for macOS command line users.

Maven Central introduces publishing limits

Looking to connect by helping founders find their first users

Someone at NPM needs see this – to stop the madness

Show HN: Protect your Go, Arch and AUR from Malware with a free firewall

I asked an online tracking company for my data and here's what I found (2018)

Generative AI Is Having Its Herbalife Moment