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?

Ask HN: How do non-resident founders meet others in SF?

China Stockpiled Oil, and Now It Could Dominate the Energy Landscape

Proactive Error Log Triage Agents

The innovators under 35 shaping climate tech

Show HN: Reader – I made a Mac workspace for books, browser tabs, notes, and AI

Does AI Assistance Enhance or Erode Expertise? Evidence from 3-Month Experiment

Jev example use cases from community

Checkout offered by banks and credit unions. Alternative to OneLink

Montessori gave us great start. Still switched to public school for first grade

The paradox at the heart of AI and science – Terence Tao [video]

Lawsuit: Illegal Agreement of Anthropic, OpenAI, SpaceXAI, Google on AI Slowdown

Unblock AI Shield – Non-invasive perimeter hardening with zero-disk RAM retentio

My company let candidates use AI tools in interviews. Here's what I saw

Maximizing Return-on-Feedback

Post-Quantum Cryptography for Healthcare Professionals

In 2024 I fine-tuned an LLM. Jev could have removed the side quests

DRAM and NAND Price Surges Set to Drive the 2026 Chip Market to a Record $1.65T

AI agent governance patterns as executable decision tables (MIT license)

Almost Never Use AI to Write Anything Substantive

Uber Ordered to Pay $40M to Parents of Passenger After Driver Left Her

CNN, MS Now and Politico Barred from White House After Trump Announces Ban

Ask HN: ICLR went from 143 submissions to 62,301 submission IDs – what happened?

Britain created Iraq to control its oil, then installed its own king [video]

Props Are Not a Design System

Russia reports 'powerful' cyberattacks on second day of parliamentary vote

Infillion Buys Foursquare

A Machine That Can Have Grandchildren

Show HN: Built a local guardrail layer for Claude Code

Regolith Desktop status updates Sep 2026

Trump announces agreement to secure 'permanent' control over Greenland security