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?

Computer (Occupation)

An AI-only labor market with atomic escrow and anti-Sybil reputation

I Am Bullish on ChatGPT Ads

In the ruins of the AI gold rush, we choose craft

Half Moon Bay to end use of Flock license plate readers

Uploading Files to the Internet in Order to Cite Them

Building the Materials Foundation for AI

The illusion of illusions: there are no optical corrections in the Parthenon

Raven's Blog

Write Your Novel in a Calendar

A return to two-pizza culture

Faster NumPy in the Browser

Show HN: Compute:Arena – Community submitted local AI benchmarks

Show HN: Alidade – an open-source Web-GIS where the map is one JSON document

Open-jev: One-pass option scoring with Gemma 3 4B, similar to jev

Trae cuts monthly usage allowance to $200 (50% reduction)

Show HN: Clippety – Private video clipping in the browser

Free invoice generator, 100% client-side – no signup, no server storage

Great Plains Shelterbelt (1934 geoengineering project)

Show HN: Ear Training Exercise

Thinking Hard Above AI

We Urgently Need a New Major Search Engine

Shakespeare Skill: Make agents sound like William Shakespeare characters

My Chinese Internet Is Talking About AI Doom

OpenAI reveals six more safety issues and unveils plan to disclose incidents

Arch-Based Omarchy 4.0.4 Makes Its Custom Linux Kernel the Default

Using Blender with coding agents on macOS

Linux Finally Reached My Desk

Show HN: Puffdoro – A squishy 3D focus companion built with WebGL and Web Audio

The story Scientific American wouldn't let me tell