Maintenance Windows
Maintenance windows are a contract with users and on-call engineers. Schedule high-impact Postgres work when blast radius is understood, communicated, and reversible.
Recipe
Quick-reference recipe card - copy-paste ready.
## Maintenance Notice Template
Window: 2026-07-12 02:00-04:00 UTC (low-traffic)
Impact: Up to 30s write latency spikes; no planned read outage
Changes: V2050 index rebuild, Patroni switchover rehearsal
Rollback: Revert migration V2050; DNS unchanged
Status page: https://status.example.com/incidents/42-- Pre-window: confirm no unexpected long transactions
SELECT count(*) FROM pg_stat_activity
WHERE state = 'idle in transaction'
AND now() - xact_start > interval '10 minutes';When to reach for this: High-risk DDL, failover drills, storage resize requiring restart, or pg_upgrade cutover.
Working Example
Team schedules VALIDATE CONSTRAINT on 80M-row payments table during Sunday 03:00 UTC window.
# T-7 days: customer email + in-app banner
# T-1 day: freeze other high-risk changes
# T-0: on-call bridge open, scribe assignedSET lock_timeout = '3s';
SET statement_timeout = '2h';
ALTER TABLE payments VALIDATE CONSTRAINT payments_amount_positive;# Post-window: verify error rate and replication lag
psql -c "SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) FROM pg_stat_replication;"What this demonstrates:
- Communicate user-visible impact, not only "database maintenance."
- Freeze competing changes to avoid lock storms.
- Set session timeouts before DDL in the window.
- Verify replicas caught up before declaring success.
Deep Dive
Window Sizing
| Work type | Sizing hint |
|---|---|
CREATE INDEX CONCURRENTLY | 2-3x staging duration |
| Constraint validate | Table scan time + lock retry buffer |
| Failover drill | App cold-start storm + 15 min observation |
pg_upgrade | Vendor doc minimum + 100% buffer first time |
Pick windows from traffic dashboards, not only local time zone convenience.
Honest Impact Language
| Say this | Not this |
|---|---|
| "Checkout may timeout for up to 60 seconds" | "Brief maintenance" |
| "Read-only mode for reporting API" | "No impact expected" |
| "Failover may drop in-flight transactions" | "Seamless upgrade" |
Freeze Policy
During the window:
- No unrelated application deploys.
- No autoscaling policy experiments.
- No certificate rotations unless bundled in the same runbook.
-- Optional: revoke CREATE from app roles during window (extreme cases)
REVOKE CREATE ON SCHEMA public FROM app_migrator;Gotchas
- Stacking multiple high-risk DDLs - One failure mid-window obscures root cause. Fix: One primary objective per window.
- Silent replica drift - Heavy DDL on primary spikes lag; apps reading replicas see stale data. Fix: Monitor byte lag; delay read traffic cutover.
- Missing rollback time - Window ends before verify completes. Fix: Book 30 min buffer; extend status page if needed.
- Global user base - "Low traffic" locally is peak elsewhere. Fix: Use UTC traffic heatmaps.
- Forgotten connection pools - Apps hold connections through brief restarts. Fix: Pre-drain PgBouncer; coordinate max lifetime.
Alternatives
| Alternative | Use When | Don't Use When |
|---|---|---|
| Online DDL only | Mature expand/contract culture | Emergency fix needs immediate rewrite |
| Blue/green database | Zero-downtime cutover budget | Small team without automation |
| Read-only window | Protect data during risky op | Writes required for revenue |
| Immediate change | Sev-1 mitigation | Planned schema improvement |
FAQs
How often should we run maintenance windows?
Monthly for medium-risk batch; ad hoc for high-risk; quarterly for DR drills.
Do we need status page for internal-only DB?
Yes if any customer-facing API depends on the database.
Can we run CONCURRENTLY index outside a window?
Often yes for low tier; still notify on-call and monitor lag.
Who approves window extension?
Incident Commander or change owner plus product duty officer.
What if migration exceeds window?
Communicate new ETA; roll back if past rollback SLA.
Should apps scale down before maintenance?
Sometimes scale up connection workers after restart instead; plan per runbook.
How do we pick timezone?
Lowest global write TPS from metrics, not office location.
Are failover drills maintenance?
Yes. Treat them with the same comms and rollback rigor.
What metrics define success post-window?
Error rate, p95 latency, replication byte lag, failed job queue depth.
What should I read next?
See Uptime SLOs for Datastores for error budget alignment.
Related
- Change Management Basics - risk tiers
- Uptime SLOs for Datastores - error budgets
- Rollback for Failed Migrations - reverse paths
- Zero-Downtime DDL - online patterns
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+.