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?

Google ordered to put clearer links in AI search and let UK publishers opt out

Show HN: Switch skills between agents, locally manage multiple configs

Casual experiment hint that models seem to search for different stuff

Big Tech's big flop on primary night

In first, California city overwhelmingly votes to permanently ban datacenters

Ask HN: What are all the ways to punch through NAT?

BQN: Primitive Overloading

VoidZero Is Joining Cloudflare

The Worst-Case Future for White-Collar Workers

The Fullers of Ancient Rome

Show HN: Hydron – Hardware-aware coding agent

'Bots have now passed human traffic online,' Cloudflare boss laments

Nemotron 3 Ultra: Open Moe Hybrid Mamba-Transformer for Agentic Reasoning [pdf]

Data Science Weekly – Issue 654

An Interview with Brian Daugherty (Of Google)

Enforcing the First as in BGP AS_PATHs

Entanglement Builds Space-Time. Now "Magic" Gives It Gravity

The Miracle on Mount Everest – Hillary Dawa Sperpa Found Alive

Truncated Photon

Docs AI that routes context with a nano model instead of a vector DB

Being a Human Engineer in the AI Era

Everything You Need to Know about the Tickler File

Bitcoin Has Longest Losing Streak Since August in Bruising Week

Researchers show how AI-powered worms could wreak havoc on the internet

AI Info Remover for Images

PostgreSQL 19 Beta 1 Released

Cadillac – a coding agent that runs the app and tests user flows b4 shipping

Design from the Inside

VoidZero Is Joining Cloudflare

It Was Never About Coding. It Was Always About Design Thinking

Ask HN: How can I load test PostgreSQL but avoid changing actual data?

Comments

Google ordered to put clearer links in AI search and let UK publishers opt out

Show HN: Switch skills between agents, locally manage multiple configs

Casual experiment hint that models seem to search for different stuff

Big Tech's big flop on primary night

In first, California city overwhelmingly votes to permanently ban datacenters

Ask HN: What are all the ways to punch through NAT?

BQN: Primitive Overloading

VoidZero Is Joining Cloudflare

The Worst-Case Future for White-Collar Workers

The Fullers of Ancient Rome

Show HN: Hydron – Hardware-aware coding agent

'Bots have now passed human traffic online,' Cloudflare boss laments

Nemotron 3 Ultra: Open Moe Hybrid Mamba-Transformer for Agentic Reasoning [pdf]

Data Science Weekly – Issue 654

An Interview with Brian Daugherty (Of Google)

Enforcing the First as in BGP AS_PATHs

Entanglement Builds Space-Time. Now "Magic" Gives It Gravity

The Miracle on Mount Everest – Hillary Dawa Sperpa Found Alive

Truncated Photon

Docs AI that routes context with a nano model instead of a vector DB

Being a Human Engineer in the AI Era

Everything You Need to Know about the Tickler File

Bitcoin Has Longest Losing Streak Since August in Bruising Week

Researchers show how AI-powered worms could wreak havoc on the internet

AI Info Remover for Images

PostgreSQL 19 Beta 1 Released

Cadillac – a coding agent that runs the app and tests user flows b4 shipping

Design from the Inside

VoidZero Is Joining Cloudflare

It Was Never About Coding. It Was Always About Design Thinking