Detecting Table Rewrites Before They Ship

Whether an ALTER TABLE rewrites the table is the single most important fact about its production impact, and it is surprisingly hard to know from the statement alone. ALTER COLUMN amount TYPE numeric(14,4) rewrites; ALTER COLUMN name TYPE varchar(255) from varchar(80) does not; ALTER COLUMN created_at TYPE timestamptz rewrites or not depending on the session time zone; ADD COLUMN ... DEFAULT now() does not on PostgreSQL 11+ but ADD COLUMN ... DEFAULT clock_timestamp() does; SET LOGGED and SET TABLESPACE always do. Static rules can flag the suspicious shapes, but the reliable answer comes from running the migration on a scratch copy and checking whether the table’s storage file changed. This guide combines both — a static pre-check and a cheap dynamic check in CI — and gates on table size so a rewrite of a tiny table passes while a rewrite of a large one blocks. It belongs to Migration Linting & Static Analysis.

Symptom / Error Signatures

A rewrite that reached production looks like a long, total outage of one table:

• pg_stat_activity shows the ALTER TABLE active for minutes or hours, with every other query on the table waiting on Lock.
• Disk usage on the primary grows by roughly the table’s size during the statement (a second copy of the table and its indexes).
• WAL volume spikes and replicas fall behind.

In CI, the check reports it directly:

REWRITE DETECTED: public.orders relfilenode 16552 → 17991
  statement: ALTER TABLE orders ALTER COLUMN amount TYPE numeric(14,4)
  production size: 142 GB (threshold 1 GB) → BLOCK

Root Cause Analysis

PostgreSQL stores each table in files identified by its relfilenode. An ALTER TABLE that must change every row’s physical representation builds a new copy of the table under a new relfilenode, then swaps — that is what a rewrite is. Changes that only touch the catalog leave the relfilenode unchanged. So comparing pg_relation_filenode() before and after running a migration on any copy of the schema answers the rewrite question exactly, for the PostgreSQL version and settings of that copy. The copy does not need data: rewrite decisions depend on types, defaults and settings, not row counts — which makes the check fast.

Rewrites are only a problem on tables that are large or hot. Combining the detection with production table sizes — which the CI job can read from a statistics export rather than from production directly — lets small tables through without ceremony.

Immediate Mitigation

1. Before running a suspect migration, test it on a scratch copy of the schema. No data is needed.

# Shell · CI job or workstation · scratch database only; production schema, same major version
pg_dump --schema-only --no-owner "$PROD_READONLY_URL" > schema.sql
createdb rewrite_check && psql -q -d rewrite_check -f schema.sql

2. Capture every table’s filenode, apply the migration, compare.

-- PostgreSQL · scratch database · before the migration
CREATE TABLE _filenodes_before AS
SELECT c.oid::regclass AS rel, pg_relation_filenode(c.oid) AS filenode
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'm') AND n.nspname NOT IN ('pg_catalog', 'information_schema');

-- PostgreSQL · scratch database · after applying the migration file
SELECT b.rel, b.filenode AS before, pg_relation_filenode(b.rel) AS after
FROM _filenodes_before b
WHERE pg_relation_filenode(b.rel) IS DISTINCT FROM b.filenode;

Any row returned is a rewritten table. Run the migration under the same session settings production will use (SET timezone, lock_timeout), because some decisions depend on them.

3. If a large table would be rewritten, redesign. Use the shadow-column technique from converting a column type with a shadow column or the specific guidance in Changing Column Types Safely.

Permanent Fix / Long-Term Pattern

Automate the dynamic check as a CI job that runs on every pull request with migrations. Keep a nightly-refreshed, schema-only dump of production (or build the schema from the full migration history) as the job’s starting point, and a size table exported from production statistics (pg_total_relation_size per table) so the job can decide without touching production. Block when a rewritten table exceeds a size threshold; warn below it.

# Shell · CI job · block rewrites of tables larger than 1 GB using an exported sizes file (rel,bytes)
REWRITTEN=$(psql -At -d rewrite_check -f detect_rewrites.sql | cut -d'|' -f1)
for rel in $REWRITTEN; do
  size=$(awk -F, -v r="$rel" '$1==r {print $2}' prod_table_sizes.csv)
  if [ "${size:-0}" -gt 1073741824 ]; then
    echo "BLOCK: $rel would be rewritten (prod size ${size} bytes)"; exit 1
  fi
  echo "WARN: $rel rewritten (small table, ${size:-0} bytes)"
done

Pair it with static linting for fast feedback, per linting Postgres migrations with Squawk, and with a full rehearsal on a production-like snapshot for timing, per testing migrations against production-like snapshots. The same idea works for indexes (compare index filenodes to spot rebuilds) and for MySQL, where asking for ALGORITHM=INPLACE or INSTANT explicitly makes the server refuse a copying change instead.

Two refinements make the check more precise. Include TOAST tables and indexes in the comparison — an index whose filenode changes was rebuilt, which on a large table is as costly as a rewrite even when the heap was untouched. And run each migration file separately with a fresh filenode snapshot in between, so the report attributes each rewrite to the file and statement that caused it rather than to the whole pull request.

Verification Checklist

• CI applies each migration to a schema-only copy of production on the same major version and records filenode changes.
• The migration runs under the same session settings as production (time zone, search path).
• Rewritten tables above the size threshold fail the check; smaller ones produce a warning.
• Production table sizes come from an exported statistics file, not a live query from CI.
• A canary migration that rewrites a large table fails the job.

Frequently Asked Questions

Does the check need production data? No. Whether PostgreSQL rewrites a table depends on the statement, the column types, defaults and session settings — not on the number of rows. An empty copy of the schema gives the same answer in seconds.

Why compare relfilenode rather than timing the migration? Timing on an empty database is meaningless, and timing on a copy of production is slow. The filenode comparison answers “did it rewrite?” exactly, and table sizes answer “does it matter?”.

Can static linting alone catch rewrites? It catches the common shapes, but it cannot account for settings such as the session time zone or for version-specific optimisations, and it flags safe widenings. Use static rules for early feedback and the dynamic check as the gate.

What about MySQL? Specify ALGORITHM=INSTANT or ALGORITHM=INPLACE in the migration. MySQL refuses the statement if it would need a table copy, which turns the rewrite question into an immediate error in CI.