Ask HN: How can I load test PostgreSQL but avoid changing actual data?

1•LawZiL•6mo 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•6mo 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•6mo 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•6mo 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•6mo ago

Load test against a clone instead of a DB whose contents you care about?

URL in C Puzzle

Finding the grain of sand in a heap of Salt

Is psql's scripting language Turing complete? Or: Fibonacci in psql

DNS Firewalling with MISP and Technitium DNS Server

Ray Marching Soft Shadows in 2D

Git commit-based annual performance reviewer [video]

You Are Insignificant. That's a Good Thing

The High Price of Environmental Responsibility

An LED panel that shows the aviation around you

Artificial muscles, or Robotics 2.0 (RU) (Anthropomorphic robotics)

iPhone Fold Will Be Creaseless and Cost $2,400, Report Says

Generative UI: A rich, custom, visual interactive user experience for any prompt

Ask HN: Ever done large contract work?

Meditation as Wakeful Relaxation: Unclenching Smooth Muscle

Turn Claude threads into Notion-grade assets you can trust

Immigrations: Gestiona Tu Visa LLC exposed 67GB of immigrant's data

A Deep Dive into MCP and the Future of AI Tooling

Claude Is Broken in Armenian

California prosecutors used AI to file inaccurate motion in criminal case

The Emoji Layer

Palo Alto Networks to Acquire Chronosphere (Creators of M3DB)

Mixpanel Security Breach

The Nerd Reich – Silicon Valley Fascism and the War on Democracy

Intellect-3: A 100B+ MoE trained with large-scale RL

3D visualization of audio latent spaces (AI Vector Map of Audio)

Families tracked victims and survivors in real time in Hong Kong tower fire

Elevating Intelligence via Efficient Model and Tool Orchestration

Ask HN: How many screens do you usually work with?

Show HN: GitHub Activity Analytics Powered by ClickHouse

Agentic Learner with Grow-and-Refine Multimodal Semantic Memory