Your DELETE + INSERT Is Burning PostgreSQL’s CPU. Here’s What’s Actually Happening on the Page.
A deep dive into MVCC, heap pages, vacuum, and what to do when you have billions of rows to update. The symptom You have a table that stores user state. Something happens — an event arrives — and your code does the obvious thing: BEGIN; DELETE FROM user_state WHERE user_id = $1; INSERT INTO user_state (user_id, state, score, updated_at) VALUES ($1, $2, $3, now()); COMMIT; It’s clean. It’s idempotent. It doesn’t require you to think about which columns changed. For a long time it works fine. ...