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?

Tsiolkovsky's 51k-page archive, machine-transcribed with measured accuracy

Show HN: Planetary Energy Routing Protocol (PERP)

Show HN: Submit your good SaaS for free

Post-scarcity abundance begins by manifesting first as youth unemployment

"Functional unemployment" nearing 25% in U.S., analysis finds [video]

Graphene can be made using a kitchen blender, a mobile phone and a newspaper

New technology lets cloud AI models process your data without privacy leaks

'Don't ask me to print your ChatGPT birthday card': Hitting back at AI 'slop'

Hot Chips 2026: Intel's Wildcat Lake

Open-Weight Masked Introspection: Measuring What LMs Can Report About Their Own

Actually Queryable Executables

DefCon 34 – Stalking the Wily Hacker: 40 years later – Cliff Stoll [video]

Comparing the two holograms on the Windows 95 box

Show HN: CookWing, an AI chef that doesn't hallucinate quantities

Shazam.el

Omarchy Exploit

Speculative Programmatic Tool Calling

Show HN: ShrinkRay – HandBrake for TinyML

Five spending categories absorbed 88% of the US middle fifth's raise (2013-2023)

LLMPanel Deploy vLLM to RunPod or Vast.ai Without Kubernetes

Silence Is a Feature

Beyond China's humanoid robots, a quieter machine revolution is unfolding

National Police Union Blasts Flock CEO for Turning Against Police

Artificial Intelligence Market (2026 – 2033)

Show HN: Tmtail – Multi-log tail with coloring, search, and history via tmux

Surge in AI-assisted complaints putting extra strain on councils and schools

Avery–MacLeod–McCarty Experiment

Ukraine hands Britain 'goldmine' of secret battlefield intelligence

Moral Realism

I made HACKER AI for you vibecoded app