Postgresql

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. ...

Everything I wish someone had told me before I stored a meeting time in a timestamp column. There is a specific kind of bug that only shows up in production, only in October, and only for users in one country. It gets closed as “not reproducible” twice before someone finally sits down with a coffee and reads the column definition. It is almost always a datetime bug. The frustrating part is that both Java and PostgreSQL have excellent date/time support. java.time is one of the best-designed APIs in the JDK. PostgreSQL’s temporal types are precise and well documented. The bugs happen at the seam — in the assumptions each side makes about what the other means, and in the two or three words in the type names that mean the opposite of what you’d guess. ...

When a migration hangs, a query slows to a crawl, or a deploy just sits there, most people reach for the same first move: kill it and try again. That works about as often as it doesn’t. Postgres already knows what’s wrong — it’s just keeping the answer in a handful of system catalogs and stats views that almost nobody looks at until things are already on fire. This is a walkthrough of the tables I actually check, in the order I check them, when something breaks — with a running example: a migration that adds a NOT NULL column and backfills it on a 20-million-row orders table, which has been “running” for 25 minutes with no sign of finishing. ...