Deep-dives on PostgreSQL — MVCC and vacuum behaviour, reading EXPLAIN ANALYZE without guessing, index strategy for real workloads, and the subtle failure modes around long-running transactions and bloat.
11 articles · updated regularly
PostgreSQL's INSERT ... ON CONFLICT DO UPDATE is commonly treated as an atomic, concurrency-safe upsert. Under high concurrent write load with multi-row batches or multiple unique indexes, it can trigger silent 40P01 deadlocks. Here is how Postgres locks index tuples and how to eliminate the deadlock.
One endpoint went from 4 ms to 1.2 seconds with no code change and no schema change. The index was still there, the query still looked identical, and EXPLAIN insisted on a sequential scan over 40 million rows. The cause was a single implicit type cast that Postgres could not push through the index, introduced by a client library that started sending a parameter as text.
Postgres started rejecting every write with 'database is not accepting commands to avoid wraparound data loss'. No disk was full, no lock was held, replication was fine. We had quietly burned through the 32-bit transaction ID counter on one table because a long-running process had been blocking vacuum from doing the one job that keeps that counter from running out.
LISTEN/NOTIFY looks like a free message bus hiding inside the database you already run. It is, until a worker reconnects, a transaction rolls back, or the notify queue overflows. Here is everything that bit me and the pattern that actually survives.
An UPDATE-heavy table that's barely touched by readers was somehow generating index bloat and burning IO. The fix wasn't more indexes or a faster disk. It was understanding HOT updates and one storage parameter almost nobody sets: fillfactor.
Before you reach for Kafka or SQS, Postgres can be a correct, exactly-once-ish job queue — if you know SKIP LOCKED. Here is why the naive queue table deadlocks under load and the one clause that fixes it.
Every durable change in Postgres is written to the WAL before the data files. That one rule is what makes crash recovery, fsync tuning, and streaming replication all the same mechanism.
Each Postgres connection is a backend process with real memory cost. Past a few hundred, throughput collapses. Pooling is how you serve thousands of clients with a hundred connections.
B-Tree is the default for good reason, but reaching for it on a JSONB column or a geo query wastes the four other index types Postgres ships with. When each one wins.
The plan tells you exactly why a query is slow — if you know which numbers to trust and which to ignore.
Postgres never updates a row in place — it writes a new version and tombstones the old one. Understanding that is how you stop fearing VACUUM.