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?

An Introduction to Galois Fields and Reed-Solomon Coding [pdf]

Show HN: Save Context from MCP Bloat

Additive FFT Explained: Fast Fourier Transforms over Binary Fields

The Streaming Latency Tradeoff: Why Some TTS Models Lose Accuracy in Real Time

The Mythical Agent-Month

Programmers Spend Their Time – Probably Dance

Global Energy Flow – Real-Time Energy Intelligence

Show HN: Codiff, a local diff review tool

How we made Notion available offline

We let four AIs run radio stations. Here's what happened.

Show HN: Give your AI agent a brain that understands your codebase

Compared a few OpenClaw hosting setups

Forum

A blog post is a query for people who will route interesting stuff to you

Useful Security Tooling

Molecular Dynamics on Apple M4

My indie app was named too close to competitors and I burnt my fingers

Ask HN: What LLM models are you using and why?

Ask HN: Why do hotels etc. WiFi networks all use captive portals?

What right has a "personal fortune" to be anything but working capital?

Private Hosted OpenClaw that can connect to your data with included AI models

Show HN: Machine – per-project dev VMs with session-only secrets

Directory of Blogs with a /Now Section

Five LLM agents play Werewolf in-browser, each with a private DuckDB

My Wi-Fi Was Faster Than Ethernet So I Fixed It

An example of functional slop code

Driving

Groww beat every odd to get here. Now what?

AI Poop Analysis App Offered to Sell Me Database of Its Users' Poops

Tesla Solar Roof is on life support as it pivot to panels