Field guide 04

Do not assume ALTER COLUMN TYPE is a metadata change.

The cast, PostgreSQL version, indexes, constraints, table size, and USING expression determine whether the change is a short catalog operation or a full rewrite.

The risky shape

ALTER TABLE accounts
  ALTER COLUMN external_id TYPE bigint
  USING external_id::bigint;
Release verdict: BLOCK until rewrite behavior and cast validity are proven.A rewrite on a large table can hold a strong lock, generate substantial I/O and WAL, rebuild dependent indexes, and leave a narrow rollback path.

First ask whether it is truly metadata-only

Use the exact source type, target type, typmod, collation, USING expression, PostgreSQL major version, constraints, indexes, and default expression. Test the generated SQL against production-like schema and scale. A general compatibility assumption is not evidence.

The expand-contract fallback

1. Add a new nullable column

ALTER TABLE accounts
  ADD COLUMN external_id_bigint bigint;

2. Make writes compatible

Deploy dual writes or one authoritative write plus a synchronization mechanism. Define how cast failures are handled rather than silently discarding them.

3. Backfill in keyset batches

UPDATE accounts
SET external_id_bigint = external_id::bigint
WHERE id > :last_id
  AND id <= :next_id
  AND external_id_bigint IS NULL;

4. Reconcile and switch reads

SELECT count(*)
FROM accounts
WHERE external_id_bigint IS DISTINCT FROM external_id::bigint;

Build required indexes safely, validate constraints, switch reads, observe the new path, stop old writes, and delay removal or rename until the rollback window closes.

Evidence gate

  • Rewrite behavior is demonstrated on the exact target PostgreSQL version.
  • Every existing value casts successfully, including edge cases and invalid strings.
  • Index, constraint, default, view, function, and replica effects are inventoried.
  • Backfill throughput, WAL, lag, bloat, retry, and pause thresholds are measured.
  • Old and new application versions remain compatible throughout rollback.

Rollback boundary

The additive phase is intentionally easy to retreat from: stop reading the new column and preserve both representations. After destructive cleanup or lossy conversion, rollback needs retained source data or a tested recovery path. A generated down migration cannot recreate discarded information.

Primary references