mir-database-postgreslisted

Make It Right (PostgreSQL module). Postgres mechanics the engine-agnostic pillar omits: which ALTER TABLE subforms take ACCESS EXCLUSIVE and the lock queue where a blocked DDL stalls every read behind it; lock_timeout/statement_timeout migration discipline; NOT VALID then VALIDATE; CREATE INDEX CONCURRENTLY and invalid-index cleanup; MVCC bloat, autovacuum, XID wraparound; isolation levels and 40001/40P01 retry; SELECT FOR UPDATE vs FOR NO KEY UPDATE, SKIP LOCKED queues, advisory locks; b-tree/GIN/GiST/BRIN choice, partial and covering indexes, why an index is unused; PgBouncer pooling modes and transaction-pooling breakage; RLS multi-tenancy and its bypass paths. Chains: mir-database (the gates) → this. TRIGGER when the engine is PostgreSQL or Postgres-compatible (RDS/Aurora, Cloud SQL, Neon, Supabase) and the task writes DDL, a migration, an index, a locking or isolation decision, a partitioning or RLS layout, or diagnoses a slow query, EXPLAIN plan, missing index, bloat, vacuum, deadlock, or connection poo
anantbhandarkar/make-it-right · ★ 15 · API & Backend · score 81

Install: claude install-skill anantbhandarkar/make-it-right

# /mir-database-postgres · Make It Right (PostgreSQL) Bottom tier of a two-tier chain: `mir-database` decides **what is true of the data** (any engine) → **this** carries Postgres mechanics. Run the gates first. Reach for this at Gate 5 (design decisions that depend on lock behaviour and index types), Gate 6 (DDL), and Gate 7 (review). **Engine state, verified 13 August 2026.** | Release | Status | What matters | |---|---|---| | **18** (18.4, released 2026-05-14) | current stable | Async I/O (`io_method`); b-tree **skip scan**; `uuidv7()`; generated columns default to **VIRTUAL**; `NOT NULL` stored in `pg_constraint` so it can be named and marked `NOT VALID`; `WITHOUT OVERLAPS`/`PERIOD` temporal constraints; `NOT ENFORCED` CHECK/FK; `OLD`/`NEW` in `RETURNING`; data checksums on by default; `pg_upgrade` keeps planner statistics; `VACUUM`/`ANALYZE` now recurse into partitions unless you write `ONLY`; `idle_replication_slot_timeout` | | 17.10 · 16.14 · 15.18 · 14.23 (all 2026-05-14) | supported | 14 reaches **end of life 12 Nov 2026** — plan the upgrade now | | 13 and older | end of life | no security patches | | **19** | **Beta 2** (2026-07-16), no GA date confirmed at time of writing | `REPACK` / `REPACK CONCURRENTLY` in core, `MERGE`/`SPLIT PARTITIONS`, parallel autovacuum, `pg_stat_lock`, `log_lock_waits` on by default, JIT off by default, logical sequence sync. Do not design against a beta | Run the current minor. The May 2026 batch (CVE-2026-6472 … CVE-2026-6638) and t