Change Management Basics

8 examples to get you started with Postgres change management - 5 basic and 3 intermediate.

Prerequisites

# Migrations run through your standard tool with review
flyway -url="$DATABASE_URL" info
# or
liquibase --changelog-file=db/changelog.xml status

• A written DDL risk matrix in the repo docs/ or runbooks/.
• Staging data volume within 10x of production row counts for realistic lock tests.
• CAB or async approval for high-risk changes per your compliance tier.

Basic Examples

1. DDL Risk Tier Matrix

Assign every migration a tier before merge.

• Tag Flyway/Liquibase scripts with -- risk: medium in header comments.
• Block deploy if tier exceeds the current window policy.

Related: Change Management Best Practices - enforcement checklist

2. Lock Timeout Guard on Migrations

Prevent migrations from waiting forever on locks.

-- Set at start of migration session (Flyway callback or Liquibase pre-sql)
SET lock_timeout = '5s';
SET statement_timeout = '30min';
SET idle_in_transaction_session_timeout = '5min';
 
ALTER TABLE orders ADD COLUMN IF NOT EXISTS priority smallint;

• Failed lock timeout aborts fast instead of freezing checkout.
• Retry medium-risk migrations in a quieter window.
• Log lock timeout failures to the change ticket automatically.

3. Expand Phase: Add Nullable Column

Safe first step of expand/contract.

-- Expand: add column (instant in PostgreSQL 11+ for nullable default-null)
ALTER TABLE customers ADD COLUMN IF NOT EXISTS tier text;
 
-- App deploy writes tier on new rows only (feature flag)
-- Backfill in batches
UPDATE customers SET tier = 'standard' WHERE tier IS NULL AND id BETWEEN 1 AND 10000;

• Never add NOT NULL without default in one step on large tables.
• Batch UPDATE with primary key ranges to avoid long locks.
• Monitor replication lag during backfill.

4. Contract Phase: Drop Unused Column

Only after code no longer reads the column.

-- Verify zero reads in pg_stat_user_tables (approximate) and app metrics
SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE relname = 'customers';
 
-- Contract
ALTER TABLE customers DROP COLUMN IF EXISTS legacy_code;

• Two-release rule: deploy code that ignores column, then drop column.
• Take logical backup or ensure PITR before high-risk drops.

5. Change Ticket Template

Every production DDL links evidence.

## CHG-2044 Add orders.priority
- Risk tier: Medium
- Migration: V2044__orders_priority.sql
- Blast radius: orders table (42M rows), checkout service
- Rollback: DROP COLUMN priority (after app revert)
- Lock test: passed on staging 2026-07-08
- On-call aware: yes

• Attach EXPLAIN for any new index.
• Note expected duration from staging stopwatch.

Related: Maintenance Windows - when to schedule

Intermediate Examples

6. NOT VALID Foreign Key Pattern

Add enforcement without blocking writes on existing rows.

ALTER TABLE line_items
  ADD CONSTRAINT line_items_order_id_fkey
  FOREIGN KEY (order_id) REFERENCES orders(id)
  NOT VALID;
 
-- Validate later in smaller window (still takes ShareUpdateExclusiveLock)
ALTER TABLE line_items VALIDATE CONSTRAINT line_items_order_id_fkey;

• NOT VALID adds metadata only; existing bad rows remain until validate.
• Run data cleanup before VALIDATE CONSTRAINT.
• Document orphan row query in the ticket.

7. Zero-Downtime Index Addition

Concurrent build for OLTP tables.

CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_customer_id_idx
  ON orders (customer_id);
 
-- Flyway: disable transaction wrapper for this script

# Verify index valid
psql -c "SELECT indisvalid FROM pg_index WHERE indexrelid = 'orders_customer_id_idx'::regclass;"

• Invalid concurrent indexes require REINDEX CONCURRENTLY (see troubleshooting).
• Do not wrap CREATE INDEX CONCURRENTLY in a transaction block.

8. Sev-Worthy Change: Major Version Upgrade

Treat pg_upgrade as a product launch, not a patch.

## Pre-flight
- [ ] Logical backup + PITR checkpoint verified
- [ ] Extension compatibility matrix (pgvector, PostGIS)
- [ ] Rollback = keep old cluster stopped, DNS revert
- [ ] App connection string tested on PG 18.4 staging

• Run pg_upgrade --check on clone first.
• Book 2x expected window for first major upgrade on the team.

Related: Before/After: Major Version Upgrade - narrative reference

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+.