Why your busiest Postgres table is mostly dead rows
An UPDATE in PostgreSQL writes a new row version and leaves the old one behind. Autovacuum cleans up, but its defaults suit small tables, not big ones.
Category
Database
PostgreSQL, MySQL, SQLite and Redis behaviour that shows up in production.
Partial indexes, and the query that refuses to use them
A partial index is small and fast, and only helps queries whose WHERE clause provably implies the index predicate. That last part is where they get ignored.
Random UUID primary keys in InnoDB: what actually hurts
InnoDB stores the table in primary key order and copies the key into every secondary index. A random 36-character key makes both of those worse.
Time-series data in plain PostgreSQL: partitions and BRIN
For append-only data ordered by time, partitioning turns deletion into a metadata operation and BRIN indexes stay tiny. It is often enough before you need a specialised store.
fsync, write caches and the SSD that lies about durability
A committed transaction is only safe once the bytes reach durable storage. Somewhere between the database and the flash chips, there are caches that may not survive a power cut.
Cache stampedes: what happens when a hot key expires
A popular key expires, and a thousand requests all miss at once and recompute the same expensive value against the database. The cache made things worse.
Schema changes without downtime: the lock queue problem
A fast ALTER TABLE can still take your site down, if it waits behind one slow query while every new query waits behind it.
