Postgres
-
Tuning pgvector HNSW without giving up
Defaults are fine for toys. Here is what I changed to make pgvector work on a real dataset.
-
Fast row-count estimates without COUNT(*)
pg_class.reltuples for when you need an answer in milliseconds.
-
Autovacuum tuning, one table at a time
Global autovacuum settings are a lie. I tune per-table now.
-
Logical replication slot lag ate our WAL
A forgotten logical replication slot accumulated 380GB of WAL before we caught it. Here's what we changed.
-
When GIN on JSONB is wrong
The default JSONB index in Postgres is GIN, and there's a very common case where that's the wrong choice.
-
Postgres 12 changed my CTEs and nobody told me
In Postgres 12 the optimization fence around CTEs came down. For us, that was mostly bad.
-
TIL: pg_try_advisory_lock for cheap, per-key mutual exclusion
Turns out Postgres has application-level locks you can use without a lock table.
-
Find foreign-key violations before a migration
A query that tells you exactly which child rows will fail when you add the FK.
-
pgbouncer transaction pooling broke our prepared statements
A multi-day outage-adjacent incident caused by prepared statements not making it across pool boundaries.
-
Partition attach locks and the bloat we didn't see coming
We moved a 1.2TB table to native range partitioning. The migration was the easy part.
-
The bitmap heap scan that ate our p99
A query that should have been a boring index scan turned into a full-table shuffle because the planner guessed wrong.
-
Gaps-and-islands SQL for compressing event streaks
A window-function pattern that collapses consecutive states into runs.
-
Cancel long-idle Postgres transactions
A query plus a loop to find idle-in-transaction sessions and kindly end them.
-
Postgres advisory locks for cross-process deduplication
A cheap mutex across N service replicas that does not need Redis.
-
TIL: pg_stat_user_tables.n_mod_since_analyze is a thing
Shows exactly how many rows have changed since the last ANALYZE. Great for 'do I need to analyze?'