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?

Towards a New House of Lords

CATL now sells batteries online for $63/kWh [video]

As print literacy fades, our perception of truth is warping

Is AI slowing you down?

Light Comes into the Room

Andrew Tate exaggerates and did not own supercars in photos, his lawyers say

Wikipedia and other sites slow or unavailable

Vanillin flavoring in e-cigarettes disrupts embryonic stem cell development

S-Xtrix-S GSM

I migrated a 4 year old PrestaShop store and made a pratical ETL guide on it

Our Electric Assets – Fortescue mining operations

Pgbot: A 5.9 MB read-only Postgres tool for humans and agents

Rollercoaster Tycoon Wonderworks – Official Early Access Announcement Trailer [video]

Space Invaders in COBOL on an IBM 3270 [video]

Key Recovery from Residue-Confined Errors in the Pradhan CRT-RLWE Construction

Report of MIT's Committee on AI Use in Teaching, Learning, and Research Training

Agents on Conductor: Agents Are Workflows

AI Grinding for Fun and Cryptanalysis

OpenBB winding down and open-sourced

Show HN: Wireshark in a TUI

Repent, Harlequin Said the Ticktockman – Harlan Ellison [pdf]

A Message from the Family of Dolly Parton

Ask HN: What business would you build if you couldn't use the internet?

Show HN: Dribbling the AI Watermark Directly In-Prompt

Kids outlearn AI–and we still don't know why

Wayland Found to Be "Intrinsically Faster Than X11, but Not More Efficient"

Show HN: I Designed a Quantum Computer

Show HN: InferIt – a word puzzle where the guesses have been made

A scale invariance property of PCA

Anthropic expected to tell investors it sees over $30T in potential revenue