Changing Column Types Safely

Changing a column’s type is the schema change most likely to look trivial in review and behave catastrophically in production. ALTER TABLE orders ALTER COLUMN id TYPE bigint is one line; on a PostgreSQL table with two billion rows it rewrites every row and every index under an ACCESS EXCLUSIVE lock, and the table is unavailable — not slow, unavailable — for the hours it takes. Yet some type changes really are instant: widening a varchar limit, converting varchar(n) to text, or, on PostgreSQL 12+ with the right session setting, timestamp to timestamptz. The difference is whether the engine can reinterpret the existing bytes or must convert them. This part of Zero-Downtime Schema Evolution Patterns explains how to tell which case you are in, and how to perform the rewriting kind online by building a new column alongside the old one. It serves engineers who have hit an integer overflow, a precision limit or a timezone bug, and the DBAs who must change the type without taking the table offline.

The online technique is Expand and Contract Methodology applied to a single column: add a shadow column of the new type, keep it in sync, backfill it, swap names in a brief transaction, and drop the old column later.

Concept & Mechanism

PostgreSQL decides between a metadata-only change and a rewrite by asking whether the old and new types are binary-coercible — whether existing on-disk values are already valid representations of the new type. varchar(50) to varchar(100), varchar(n) to text, and varchar to unconstrained varchar are binary-coercible, so the change updates the catalog and takes ACCESS EXCLUSIVE only for a moment. Narrowing a limit (varchar(100) to varchar(50)) is not a rewrite but requires a scan to check existing values. Most other changes — integer to bigint, numeric(10,2) to numeric(12,4), text to jsonb, integer to text — require converting every value, which means rewriting the table and rebuilding every index that includes the column, all under ACCESS EXCLUSIVE. timestamp to timestamptz is a special case: since PostgreSQL 12 it is metadata-only when the session TimeZone is UTC, because the stored values are then interpreted identically.

MySQL 8.0 classifies column changes into INSTANT, INPLACE and COPY. Increasing VARCHAR length is INPLACE without a rebuild as long as the number of length bytes does not change — that is, within 0–255 bytes or within 256 bytes and above; crossing the 255-byte boundary (for example VARCHAR(60) to VARCHAR(100) in utf8mb4, where 100 characters can exceed 255 bytes) requires a table copy. Changing the data type of a column (INT to BIGINT) always uses COPY, which blocks concurrent writes. Adding or modifying ENUM members at the end of the list can be instant. For COPY changes on large tables, online schema change tools are the standard answer.

A rewrite has three costs beyond the lock. It needs disk space for a full second copy of the table and its indexes until it commits. It generates WAL or binlog volume roughly equal to the table size, which replicas must replay — on MySQL as a single statement, lagging them by the whole duration. And because the lock is held from start to finish, a lock-timeout safety net does not help: the statement gets its lock and then keeps it.

It is worth being precise about what “online” means for each alternative. The shadow-column method never holds an exclusive lock for longer than a catalog update, but it is not free: the backfill doubles the write volume on the table for its duration, the sync trigger adds a small cost to every write until the swap, and the table temporarily carries two copies of the column’s data, which increases its size until the old column is dropped and the space reclaimed by vacuum (or, for a large reclaim, by pg_repack as described in removing table bloat online with pg_repack). Online schema change tools on MySQL have the same shape at table scale — a full second copy of the table, extra replication traffic, and a brief cut-over lock. These costs are almost always preferable to an outage, but they should be planned: check disk headroom, schedule the backfill away from peak traffic, and budget replica lag.

The last planning question is application compatibility. A type change is invisible to code only when both types serialise identically through the driver; integer to bigint is transparent to most languages, text to jsonb usually is not, and timestamp to timestamptz changes how many drivers represent values. Test the application against the new type before the swap, and deploy any necessary code changes first.

Prerequisites & Decision Criteria

Pick the approach by the cost class and the table size.

Before changing any column type on a live table:

• The change was classified: run it against a copy of the table with \timing and check whether the table’s relfilenode changes (a rewrite creates a new file).
• Every application version that will run during the change can read and write both the old and new types, or the change is staged so they never see a type they do not understand.
• Disk space for a second copy of the table and indexes is available if a rewrite or backfill is involved.
• Replica lag budgets are known, because backfills and rewrites both produce heavy replication traffic.
• Views, functions and foreign keys that depend on the column are listed, because a type change must update or recreate them.

Step-by-Step Procedure

The shadow-column procedure below converts orders.amount from numeric(10,2) to numeric(14,4) on a large PostgreSQL table. The same sequence works for any rewriting change.

1. Add the new column, nullable. Metadata-only; verify with a lock timeout that it completes in milliseconds.

-- PostgreSQL · migration role · instant
SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN amount_new numeric(14,4);
-- ROLLBACK PATH: ALTER TABLE orders DROP COLUMN IF EXISTS amount_new;

2. Keep the columns in sync for new writes. A trigger is the most reliable way, because it covers every writer, including ones you do not control. Verify by updating a row and reading both columns.

-- PostgreSQL · migration role · adds a row-level trigger; brief lock to create it
CREATE OR REPLACE FUNCTION orders_amount_sync() RETURNS trigger AS $$
BEGIN
  NEW.amount_new := NEW.amount;
  RETURN NEW;
END $$ LANGUAGE plpgsql;
SET lock_timeout = '3s';
CREATE TRIGGER orders_amount_sync BEFORE INSERT OR UPDATE OF amount ON orders
  FOR EACH ROW EXECUTE FUNCTION orders_amount_sync();
-- ROLLBACK PATH: DROP TRIGGER IF EXISTS orders_amount_sync ON orders; DROP FUNCTION IF EXISTS orders_amount_sync();

3. Backfill existing rows in batches. Walk the primary key, update a few thousand rows per transaction, and pause between batches while watching replica lag, as described in tuning backfill batch size against replication lag. Verify with a count that no rows remain unsynchronised.

-- PostgreSQL · one batch · run repeatedly from a script, advancing :last_id
UPDATE orders SET amount_new = amount
WHERE id > :last_id AND id <= :last_id + 5000 AND amount_new IS DISTINCT FROM amount;

4. Rebuild indexes and constraints on the new column. Create any index that includes the old column on the new one, concurrently; add NOT NULL via a validated check if needed, as in adding NOT NULL via a CHECK constraint in Postgres.

5. Swap in one short transaction. Rename the columns, drop the sync trigger and — if the application reads by column name — the swap is invisible to it.

-- PostgreSQL · migration role · brief ACCESS EXCLUSIVE; all metadata changes
BEGIN;
SET LOCAL lock_timeout = '3s';
DROP TRIGGER orders_amount_sync ON orders;
ALTER TABLE orders RENAME COLUMN amount TO amount_old;
ALTER TABLE orders RENAME COLUMN amount_new TO amount;
COMMIT;
-- ROLLBACK PATH: reverse the two renames in one transaction (sync trigger must be recreated in the other direction).

6. Drop the old column in a later release. Once no code or view references amount_old, drop it with a lock timeout.

Verification & Observability

Two checks prove a type change is correct and complete. Before the swap, confirm the columns agree everywhere; after, confirm the column has the expected type.

-- PostgreSQL · read-only · before the swap: must return 0
SELECT count(*) FROM orders WHERE amount_new IS DISTINCT FROM amount;
-- after the swap
SELECT column_name, data_type, numeric_precision, numeric_scale
FROM information_schema.columns WHERE table_name = 'orders' AND column_name = 'amount';

To classify a change before running it in production, test it on a copy and compare the table’s storage identity: SELECT pg_relation_filenode('orders') changes when PostgreSQL rewrites the table. During backfills, watch replication lag, dead tuples (pg_stat_user_tables.n_dead_tup) and autovacuum activity, as covered in alerting on replication lag during backfills.

Rollback Path

Each step before the swap is additive and reversible by dropping what was added: the trigger, the function, the new column. The swap itself is reversible by renaming back, which is why the old column should be kept for at least one release after the swap — if the new type causes an application problem, a rename in a short transaction restores the original.

-- PostgreSQL · emergency rollback of the swap, while amount_old still exists
-- WARNING: writes made after the swap exist only in the new column; re-sync before switching back.
BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE orders RENAME COLUMN amount TO amount_new;
ALTER TABLE orders RENAME COLUMN amount_old TO amount;
COMMIT;

Rollback is safe while no data has been written that only the new type can hold (for example, values above the old integer range). After that point, roll forward. The pipeline-level policy is covered in Rollback Automation.

Common Errors & Fixes

ERROR: integer out of range in the application. Root cause: a sequence-backed integer key reached 2,147,483,647. Fix: the long-term fix is bigint; the immediate one is covered in widening int to bigint primary keys without downtime.

ERROR: cannot alter type of a column used by a view or rule. Root cause: a view depends on the column. Fix: drop and recreate the view around the change, or use the shadow-column method and repoint the view after the swap.

A “simple” varchar change on MySQL runs as a table copy. Root cause: the new length crosses the 255-byte boundary for the column’s character set. Fix: specify ALGORITHM=INPLACE, LOCK=NONE to detect it; use an online schema change tool; see changing varchar length without a table rewrite.

timestamp to timestamptz rewrote the table. Root cause: the session TimeZone was not UTC, or the server is older than PostgreSQL 12. Fix: set SET timezone = 'UTC' in the migration session; see migrating timestamp to timestamptz safely.

Child Page Index

The guides under this topic cover the most common type changes. Widening int to bigint primary keys without downtime handles the hardest case: a primary key referenced by foreign keys and sequences. Changing varchar length without a table rewrite explains when length changes are free on each engine. Converting a column type with a shadow column is the general procedure in depth, including dual-writing from the application instead of a trigger. Migrating timestamp to timestamptz safely covers the metadata-only path and its correctness pitfalls. And adding and altering enum values safely handles enum types on both engines.

For MySQL tables too large to copy directly, the tools in Online Schema Change Tools perform the equivalent of the shadow-column swap for the whole table.

Frequently Asked Questions

How can I tell in advance whether a PostgreSQL type change will rewrite the table? Run it against a copy and check whether pg_relation_filenode() changes, or consult the rule: only binary-coercible changes (such as varchar(n) to text or raising a varchar limit) and, on PostgreSQL 12+ in a UTC session, timestamp to timestamptz avoid a rewrite.

Why is int to bigint a rewrite when bigint is just larger? The on-disk representation changes from 4 to 8 bytes, so every row must be rewritten with the new width, and every index containing the column rebuilt. There is no way to reinterpret the existing bytes.

Can I use a trigger or should the application dual-write? Either works. A trigger covers every writer automatically and is easiest to get right; application dual-writes avoid trigger overhead and are more visible in code. The shadow-column guide compares the two.

What about MySQL? MySQL’s column type changes use the COPY algorithm, which blocks writes. On small tables that is acceptable in a quiet window; on large tables use gh-ost or pt-online-schema-change, which effectively perform a whole-table shadow copy and swap.