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?

56k.rip – the 1996 dial-up internet experience

The Multiplayer AI Manifesto

Show HN: Chrome tab group cleaner for Claude Code created tab groups

NYC mayor Mamdani imposes 1 year ban on AI for schools through 8th grade

GTP-6 Astra

Show HN: If I had to design a nuclear fusion power plant(Idea Sketch)

4K Analog Video Feedback Device

macOS 27 Simulator by OpenAI Astra

Coder's registry infrastructure compromised to push malicious modules

(George) Lucas Museum of Narrative Arts

Star Trek S01e20 "Court Marshall"

Protecting Engineers' Skills in the AI Era

There are many agent harnesses, but this one is mine

Anxious or outgoing? Study links Big Five personality traits to genetic variants

Spending deal comes with a bonus: Blocking political control of grants

I found an AI-driven browser that builds workflows from playbooks

Oura S-1

The Unsung Inventor Who Chased the LED Rainbow

Modal's rise: sandboxes, cold starts, and what the SEC paperwork reveals

Dash for Windows: Now in Beta

ZJIT GC: Inlining GC Fastpath in ZJIT (Ruby)

Show HN: Generate a Claude Code or Codex agent fleet from one HTML file

here is how We're All Going to Die

Bitcoinica

SpaceXAI apologizes for outage that affected Grok and other 'compute partners'

I tracked 7,022 MCP tool definitions. 14 servers changed within 27 hours

GLP-1s Are Being Linked to Fewer Serious Infections, Including TB

Rich vs. King in the Real World: Why I sold my company

Matching Features, Not Tokens: Energy-Based Fine-Tuning of Language Models

Blockchain und Bitcoin erklärt in 6 Episoden – Episode 4