Converting a Column Type with a Shadow Column

The payload column on webhook_events was created as text years ago and holds JSON. Queries now need to filter on fields inside it, which means jsonb and a GIN index. ALTER TABLE webhook_events ALTER COLUMN payload TYPE jsonb USING payload::jsonb would parse and rewrite 300 million rows under an exclusive lock — and fail at the very end if one row contains invalid JSON, after hours of work. The shadow-column technique does the conversion online: build the new column alongside the old one, keep it current, convert existing rows in small batches (dealing with bad rows as they are found), then swap. This guide is the general procedure behind every rewriting type change in Changing Column Types Safely, with the choices you have to make at each step.

Symptom / Error Signatures

You need the shadow-column technique when a direct type change would rewrite a large table. Signs include:

• A test of the ALTER ... TYPE on a production-sized copy runs for minutes or hours, and pg_relation_filenode() changes (a rewrite).
• The conversion can fail on data: ERROR: invalid input syntax for type json, invalid input syntax for type integer, value too long, or numeric field overflow — found only after scanning.
• On MySQL, the change requires ALGORITHM=COPY (ERROR 1846 when you ask for INPLACE).
• Views or functions depend on the column (cannot alter type of a column used by a view or rule).

Root Cause Analysis

A direct type change does three things at once under one exclusive lock: converts every value, rewrites the table, and rebuilds dependent indexes. It is all-or-nothing, so a single bad value wastes all the work, and the lock blocks every query for the duration. The shadow column separates those concerns so that each runs with the least locking possible and each can be retried: conversion happens row by row in small transactions, bad values are handled individually, indexes are built concurrently, and the only exclusive locks are for adding the column, creating the trigger and renaming.

The one real decision is how to keep new writes in sync while the backfill runs.

For conversions that can fail on bad input (text to JSON, text to integer), write the trigger defensively: on a conversion error, leave the new column NULL and record the row for follow-up, rather than failing the user’s write. The general dual-write trade-offs are covered in Dual-Write Synchronization.

Immediate Mitigation

If a direct type change is running and blocking, cancel it — it is transactional in PostgreSQL and rolls back cleanly — then follow the procedure.

1. Add the shadow column and a defensive sync trigger.

-- PostgreSQL · migration role · brief locks
SET lock_timeout = '3s';
ALTER TABLE webhook_events ADD COLUMN payload_new jsonb;
CREATE OR REPLACE FUNCTION webhook_events_payload_sync() RETURNS trigger AS $$
BEGIN
  BEGIN
    NEW.payload_new := NEW.payload::jsonb;
  EXCEPTION WHEN others THEN
    NEW.payload_new := NULL;            -- never fail the application's write
  END;
  RETURN NEW;
END $$ LANGUAGE plpgsql;
CREATE TRIGGER webhook_events_payload_sync BEFORE INSERT OR UPDATE OF payload ON webhook_events
  FOR EACH ROW EXECUTE FUNCTION webhook_events_payload_sync();
-- ROLLBACK PATH: DROP TRIGGER ...; DROP FUNCTION ...; ALTER TABLE webhook_events DROP COLUMN payload_new;

2. Backfill in batches, quarantining failures. A small PL/pgSQL DO block or an external script processes one id range per transaction; rows that fail conversion are copied to a quarantine table for review.

-- PostgreSQL · one batch, run repeatedly by a script advancing :lo · each call is its own transaction
WITH batch AS (
  SELECT id, payload FROM webhook_events
  WHERE id >= :lo AND id < :lo + 5000 AND payload_new IS NULL AND payload IS NOT NULL
)
UPDATE webhook_events w SET payload_new = b.payload::jsonb
FROM batch b WHERE w.id = b.id AND pg_input_is_valid(b.payload, 'jsonb');   -- PG 16+
INSERT INTO webhook_events_quarantine (id, payload)
SELECT id, payload FROM webhook_events
WHERE id >= :lo AND id < :lo + 5000 AND payload_new IS NULL AND payload IS NOT NULL
ON CONFLICT (id) DO NOTHING;

pg_input_is_valid requires PostgreSQL 16; on older versions, wrap the cast in a small function that returns NULL on error. Throttle between batches on replica lag, per throttling backfills to protect OLTP latency.

3. Resolve quarantined rows. Fix, transform or explicitly accept NULL for each; the swap cannot happen while real data would be lost.

Permanent Fix / Long-Term Pattern

4. Verify, then build indexes. Before swapping, prove equivalence and completeness, then build new indexes concurrently.

-- PostgreSQL · read-only verification · both must return 0 before the swap
SELECT count(*) FROM webhook_events WHERE payload IS NOT NULL AND payload_new IS NULL;
SELECT count(*) FROM webhook_events WHERE payload_new IS NOT NULL AND payload_new::text IS DISTINCT FROM (payload::jsonb)::text;
-- then, outside a transaction:
CREATE INDEX CONCURRENTLY IF NOT EXISTS webhook_events_payload_gin ON webhook_events USING gin (payload_new);

5. Swap in one short transaction. Drop the trigger, rename the columns, and recreate any dependent views against the new column.

-- PostgreSQL · migration role · brief ACCESS EXCLUSIVE, metadata only
BEGIN;
SET LOCAL lock_timeout = '3s';
DROP TRIGGER webhook_events_payload_sync ON webhook_events;
ALTER TABLE webhook_events RENAME COLUMN payload TO payload_old;
ALTER TABLE webhook_events RENAME COLUMN payload_new TO payload;
COMMIT;
-- ROLLBACK PATH: rename back in one transaction while payload_old exists; re-sync rows written since the swap.

The application must tolerate the new type at the moment of the swap. For text to jsonb, most drivers return JSON as a string or parsed object depending on type; deploy code that handles both before swapping, following the release ordering in Expand and Contract Methodology.

6. Contract. After a release with no reads of payload_old, drop it and the sync function. Keep a record of quarantined rows and their resolution with the migration.

Verification Checklist

• The sync trigger (or dual-write) was in place before the backfill started, so no new row was missed.
• The completeness and equivalence queries both returned zero immediately before the swap.
• Quarantined rows were resolved and documented.
• Indexes on the new column were built concurrently and are valid.
• Application code tolerant of the new type was deployed before the swap, and the old column was dropped only after a release with no reads.

Frequently Asked Questions

Should the sync be a trigger or application dual-writes? A trigger is the safer default because it covers every writer, including ones you do not control. Application dual-writes avoid trigger overhead but require every writer to be updated and deployed before the backfill begins.

Why not let the trigger raise an error on bad data? Because the error would fail the application’s write for a problem that belongs to the migration. Leave the new column NULL, record the row, and resolve it as part of the backfill.

Can the swap be done without renaming? Yes: deploy application code that reads and writes the new column name, then drop the old column. Renaming keeps the column name stable for every consumer, which is simpler when many services or views use it.

How do I handle views that reference the column? Recreate them in the swap transaction or immediately after, pointing at the renamed column. CREATE OR REPLACE VIEW cannot change a column’s type, so a view whose output type changes must be dropped and recreated.