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?

Compress any LLM 3x in one click, near-lossless, vLLM successor

Stanislav Kurilov

Show HN: Apron Chat Protocol

Inception's Diffusion LLM API Is Shutting Down

Business as Usual

Tear Down Those Guardrails: Renegades Freeing Chatbots from Corporate Enslavers

Show HN: LessonFolk – Learn AI from an AI tutor, through MCP (open source)

Show HN: A better UX for app permission grants on macOS

Running Doom as a Temporal Workflow

Claude the Business Analyst

Ask HN: What are alternatives to LinkedIn to share good works

Show HN: A small Firefox add-on to compare 2 similar pages

The World Builder the AI Labs Haven't Called Yet

Image Formats for Photo Archiving

Is Time a Stubbornly Persistent Illusion?

Vibe Slopped Cryptography

Show HN: JVM Library to create styled PDFs with easy DSL

Overengineered Highlighters [video]

Does it help code quality if agents encode their intents?

What's Going on at Inception AI?

Your internal apps are now external

You could do it alone

Perplexity Teammates: always-on AI agents that message your coworkers in Slack

FreeBSD Developer AI Survey Results

Agents aren't humans (but they kinda are)

Conductor is joining Meta; Conductor Cloud shuts down Nov 20

The Hard Part of AI Coding Is the Second Change

Windows 10 Start menu and Alt+Tab slow after screen sharing

Nukerat Framework Explained: Advanced Red Team Operations and Post-Exploitation

'Cruise for life' ship seized in Singapore