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?

A Tech Founder Wanted to Start a New Country. An Actual Country Got in the Way

Show HN: I don't code – I run a self-correcting protocol across 4 projects

Pixel 11 specs and price leak with no surprises

U.S. sanctions Iranian firms for Bitcoin maritime insurance operation in Hormuz

Game Industry Hardship Fund

ebookfs – a 9P eBook library filesystem

ao486: x86-compatible Verilog core implementing all features of a 486 SX (2014)

Challenge GPT and Claude to Run Their Own Lemonade Stands[video]

Strangers pretrained a language model with HF PRs and a cron job

'Naked Dressing' Is Spreading and It's Making Things Awkward

Moochacha

J3blocks

Show HN: Nuking the crap Claude left in the codebase – CCN

Running Kimi K3 on MI355X at Better Performance per Dollar Than B300

Booting Fuchsia's Zircon on a Homebrew RISC-V SoC, on an Artix-7 FPGA

Sick of waiting for agents to run and then having to debug

Ask HN: Collection of Best Pitch Decks?

PostgreSQL and the Linux OOM Killer: A Better Default

Don't credit the LLM

Safeguards Based on Copyable Context Cannot Provide Reliable Safety for LLMs

Karpathy's Pelican

Canadians are leaving the country at record levels. Can anyone solve this?

Simple Outlier Detection in a Kalman Filter

Futurism says Meta has "almost nothing" to show for AI. The numbers say

AI Models as Commodities

Play Neuromancer

OpenAI's claimed disproof of Connes' Rigidity Conjecture is invalid [pdf]

Foundational ternary-model inference and training – CUDA, CPU, BitNet/TQ

Windows XP Original

ModRetro M64 – Nintendo 64 that plays original cartridges with 4K HDMI