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 assigned

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

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

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 assigned

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

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

Maintenance Windows

Search Documentation

Maintenance Windows