7 min read
A tenant-scoped range scan over the events table read 4.1 million pages to return columns that fit in 500 bytes per row — the other 1.4 KB was a jsonb payload nobody selected, sitting inline in every heap page. Setting…
Database Topic Archive
Vacuum and Bloat Articles
Autovacuum, dead tuples, bloat, wraparound, MVCC, freeze age, and visibility.
7 min read
A schema with 40 utf8mb4 VARCHAR(255) columns failed to create on MySQL with ERROR 1118 while PostgreSQL accepted the equivalent table without comment, then rejected its index with a 2704-byte limit nobody had heard of. Both engines store big values…
10 min read
Our orders table got vacuumed three times a day and its relfrozenxid still crept 2 million xids a week — until one Tuesday the anti-wraparound vacuum read all 420 GB of it in a single afternoon. The freeze knobs, the…
7 min read
Dead tuples climbed for days, autovacuum ran constantly, and pg_stat_activity showed nothing. The culprit was one row in pg_prepared_xacts, eleven days old, left by a Java service that crashed mid-commit.
7 min read
PostgreSQL's 32-bit transaction IDs wrap around after roughly two billion transactions of visibility horizon. How freezing prevents it, how to monitor age(datfrozenxid), and how to dig out of the emergency.
6 min read
Four hundred requests a minute, three temp tables each: three months later pg_attribute weighed 9 GB with six million dead tuples. Why per-request DDL churns the catalogs, and the patterns that stop it.
8 min read
InnoDB hides old row versions in undo logs and reclaims space with rebuilds; PostgreSQL keeps dead tuples in the heap and reclaims them with VACUUM. Two different bloat models, two maintenance disciplines.
6 min read
The default autovacuum cost settings throttle vacuum to a pace modern hardware laughs at. On big write-heavy tables, that means dead tuples win. Here is how to retune.
9 min read
PostgreSQL compresses big values silently, and since version 14 you get to pick the codec. Here is how to find toasted columns, weigh pglz against lz4, and switch without surprises.
5 min read
TOAST quietly moves your large values off-page and compresses them. It's invisible until a query that only touches small columns suddenly drags, and the cause is a wide column you forgot was there.