Change Management Best Practices
High-risk DDL requires automated lock timeout guards. Use this list in migration PR review and CAB packets.
How to Use This List
- Attach checklist completion to every high-risk change ticket.
- Enforce lock timeouts in Flyway callbacks or Liquibase pre-sql, not by memory.
- Rehearse rollback on staging with production-scale row counts.
- Review failed migrations monthly; turn patterns into lint rules.
A - Classification
- Every script has a risk tier (low/medium/high/sev-worthy). No untagged DDL in main.
- High-risk changes require named DBA reviewer. Not only generic platform approval.
- Blast radius documented. Tables, services, and approximate row counts listed.
- Expand/contract default for breaking changes. Avoid single-step rewrites on large tables.
B - Safety Guards
- lock_timeout set on every migration session. Five seconds is a common OLTP default.
- statement_timeout set for batch DDL. Prevent hung migrations overnight.
- CREATE INDEX uses CONCURRENTLY on production tables. Wrapped outside transactions.
- Foreign keys added NOT VALID first. Validate in separate window after data cleanup.
- Invalid index alert wired.
pg_index.indisvalid = falsepages on-call.
C - Windows and Communication
- High-risk DDL only in maintenance windows. With status page and rollback owner.
- Freeze competing deploys during window. One objective per window.
- Replication lag monitored during backfill. Byte lag SLO on primary.
- Customer language states user-visible impact. Not "brief maintenance."
D - Rollback and Learning
- Rollback section in ticket: app revert + schema contract. Forward-only called out explicitly.
- PITR path documented for catastrophic DML. Time target and owner named.
- Post-change verification queries in runbook. Not ad hoc psql after the fact.
- Failed migration post-mortem within five days. Update callbacks and templates.
FAQs
What lock_timeout value is standard?
3-10s for OLTP; longer only on batch tables with explicit window approval.
Should Liquibase run in one transaction?
Disable for CREATE INDEX CONCURRENTLY and some ALTER patterns per vendor docs.
How do we enforce tiers in CI?
Lint migration headers, require CODEOWNERS on db/migrate, block Friday high-risk deploys by policy.
Are views and functions lower risk?
CREATE OR REPLACE can break dependents; classify medium unless purely additive.
Who owns the change calendar?
DBA or platform team with product visibility on blackout dates.
Do extensions need CAB?
Yes when installing on production or upgrading pgvector/PostGIS major versions.
How to handle hotfix migrations?
Same guards; expedited approval does not skip lock_timeout.
What about multi-tenant schemas?
Per-tenant rollout or shard-aware windows; document tenant blast radius.
Can AI-generated SQL skip review?
Never. Generated DDL still needs human tier classification and staging lock test.
What should I read next?
See Change Management Basics for tier examples.
Related
- Change Management Basics - tier matrix and examples
- Maintenance Windows - scheduling and comms
- Rollback for Failed Migrations - reverse paths
- Migration Safety Rules - team policy
- Zero-Downtime DDL - online techniques
Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.