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?

Zedgerr – self-hosted invoicing and bookkeeping for freelancers

Solving Open Research Problems Together

Anthropic commits $100M to Claude Frontier Academy to train 10k FDEs by 2028

Show HN: Halo – Dynamic Island for macOS that also works on external monitors

Former SR-71 Engineer Talks NASA's Blackbird Revival Program

Turso Is Joining Supabase

In One Year I Went from AI Skeptic to AI Pilled

What would BASIC look like if it had never gone out of fashion?

Whistle: Speech to Text in 16.9 MB

New in Llama.cpp: Decision Models

AI Effect

Apple changes full-disk access permissions to curb abuse from AI agents

Show HN: I turned 27 classic behavioral experiments into a browser game

Mark Zuckerberg Profile

French supercomputer maker Bull doubles output to boost Europe's AI ambitions

Ideabrowser meets dark mirror – mobile apps you can build today that are WTF

Co-pilot on Flydubai flight was banned from flying by Oman for extremist views

Understanding Frontier Artificial Intelligence

A Leap in GNU/Linux Usage, Japan's Share in Particular

I got targeted: Trying to get your credentials via a Git post-checkout hook

Ukraine rolls out drone-stopping razor fence to thwart attacks

Would you use Hive-simpler AI agents?

Mass Layoffs at Red Hat Not Limited to This Week or to Oct First

System Design – How SSDs Work Internally

What I worry about with AI – Code Capsule

The College Decision Just Got Harder

Phylogeny, ontogeny and epigenesis: 3 sources of biological inspiration (1996)

Without search, even the best AI models get 1 in 4 answers wrong

Extension to show how much of your email was typed

Do high school students have to work harder nowadays?