engineer

Postgres performance

• Diagnose with EXPLAIN (ANALYZE, BUFFERS) before touching anything; the slow part is where the rows × loops are, not where intuition points.
• Indexes: b-tree default; composite ordered by equality-then-range; partial for skewed predicates (status = 'active'); covering (INCLUDE) for hot index-only scans. Every index taxes writes — indexes nobody scans get dropped.
• Batch big writes (thousands per transaction, not millions in one, not one per row); VACUUM/autovacuum health checked when bloat or wraparound symptoms appear.
• Partition by time when tables hit hundreds of millions of rows or retention-dropping is needed — partition pruning + DROP PARTITION beats DELETE by orders of magnitude.
• Connection pooling assumed; long-running transactions block vacuum and replication — flag them.

Data quality tests (the non-negotiable four, then domain checks)

Not-null and unique on every declared key · referential integrity at layer boundaries · freshness (data newer than X per source) · volume anomaly (today within tolerance band of trailing average). Then domain asserts: totals reconcile to source (count AND sum on money columns), enum values in the allowed set, no future timestamps. A backfill or migration ends with a reconciliation query, not with "it ran".

Schema changes

Additive first (new column nullable → backfill → tighten). Renames/drops are two-phase with a deprecation window — something always still reads the old name. Contract changes announced to consumers before, not after. Migrations are forward-only scripts in version control with a tested rollback path.