Changing varchar Length Without a Table Rewrite

A customer’s company name is 81 characters long and companies.name is varchar(80), so their sign-up fails with value too long for type character varying(80). The fix is obviously to widen the column. The question is whether ALTER TABLE companies ALTER COLUMN name TYPE varchar(255) is a millisecond catalog update or an hour-long table rewrite — and the answer depends on the engine, the direction of the change and, in MySQL, on the character set. This guide explains exactly when length changes are free, when they are not, and how to structure string columns so future limit changes never need DDL at all. It belongs to Changing Column Types Safely.

Symptom / Error Signatures

The trigger for the change is usually an application error:

ERROR:  value too long for type character varying(80)                  -- PostgreSQL, SQLSTATE 22001
ERROR 1406 (22001): Data too long for column 'name' at row 1            -- MySQL strict mode

The risk shows up if the change is not metadata-only: on MySQL the processlist shows copy to tmp table for the ALTER and writes to the table wait; on PostgreSQL a narrowing change scans the table under ACCESS EXCLUSIVE. Asking MySQL for the online algorithm explicitly surfaces the problem before it bites:

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

Root Cause Analysis

PostgreSQL stores varchar(n) and text identically on disk — a length header followed by the bytes. The n is only a check applied on write. Raising n, removing it, or converting to text changes nothing about stored data, so PostgreSQL treats it as binary-coercible and updates the catalog only. Lowering n requires proving that no existing value exceeds the new limit, which means a full scan under ACCESS EXCLUSIVE, though not a rewrite.

MySQL/InnoDB stores a VARCHAR with a length prefix of one byte if the column’s maximum byte length is up to 255, and two bytes otherwise. Increasing the declared length in place is supported only when the prefix size does not change. The maximum byte length is characters × bytes per character: 4 for utf8mb4, 3 for utf8mb3, 1 for latin1. So in utf8mb4, VARCHAR(63) (252 bytes) and below uses a one-byte prefix, and VARCHAR(64) and above uses two. Widening VARCHAR(40) to VARCHAR(60) stays under 255 bytes and is in place; widening VARCHAR(60) to VARCHAR(100) crosses it and requires a table copy; widening VARCHAR(100) to VARCHAR(500) stays above it and is in place. Decreasing length and changing to TEXT always copy.

Immediate Mitigation

1. On PostgreSQL, widen directly with a lock timeout. It is a catalog change, but it still needs a brief ACCESS EXCLUSIVE lock and can queue behind long transactions.

-- PostgreSQL · migration role · metadata only, milliseconds
SET lock_timeout = '3s';
ALTER TABLE companies ALTER COLUMN name TYPE varchar(255);
-- ROLLBACK PATH: narrowing back requires a scan; leave the wider limit in place instead.

2. On MySQL, state the algorithm so a copy cannot happen by surprise.

-- MySQL 8.0 · migration session · fails fast if the change would copy the table
SET SESSION lock_wait_timeout = 5;
ALTER TABLE companies MODIFY name VARCHAR(255) NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;
-- ROLLBACK PATH: not needed for a widening; narrowing requires a copy.

MODIFY restates the whole column definition — keep NOT NULL, the default, the character set and collation exactly as they were, or the change stops being a pure length increase.

3. If MySQL refuses, choose between a quiet-window copy and an online tool. Small tables can take the copy; large ones should use gh-ost or pt-online-schema-change, as described in Online Schema Change Tools.

Permanent Fix / Long-Term Pattern

On PostgreSQL, most teams avoid the problem permanently by using text (or unlimited varchar) and enforcing business limits with a CHECK constraint. Changing a check constraint’s limit is a drop-and-add that can use the NOT VALID / VALIDATE pattern from Adding Constraints Without Downtime, so even narrowing the limit becomes an online operation, and the storage type never changes.

-- PostgreSQL · migration role · convert once (metadata only), then manage limits with checks
SET lock_timeout = '3s';
ALTER TABLE companies ALTER COLUMN name TYPE text;
ALTER TABLE companies ADD CONSTRAINT companies_name_len CHECK (char_length(name) <= 255) NOT VALID;
ALTER TABLE companies VALIDATE CONSTRAINT companies_name_len;
-- ROLLBACK PATH: ALTER TABLE companies DROP CONSTRAINT companies_name_len;

On MySQL, choose lengths with the byte boundary in mind at table-creation time: if a column might ever need more than 63 utf8mb4 characters, start it at 64 or more so later widening stays in place. Always include ALGORITHM and LOCK clauses in migrations so CI tells you which changes copy. Remember index limits too: InnoDB index key prefixes are limited (3072 bytes for DYNAMIC row format), so very long indexed VARCHAR columns may need prefix indexes. ORMs that generate MODIFY statements should be reviewed for accidental changes to nullability or collation, as in Migration Linting & Static Analysis.

A final caution applies to both engines: length changes cascade. A widened column that is copied into other tables, used in a view, passed to a function with a typed parameter, or mirrored in an application validation rule needs those changed too. Views in PostgreSQL that select a varchar(80) column keep reporting the old type until recreated, and API schemas that still say 80 will reject the longer values the database now accepts. Search for the column across the schema and the codebase before closing the ticket.

Verification Checklist

• The change was classified before running: metadata-only on PostgreSQL, or accepted with ALGORITHM=INPLACE, LOCK=NONE on MySQL.
• The migration ran with lock_timeout or lock_wait_timeout and completed in milliseconds.
• information_schema.columns.character_maximum_length shows the new limit.
• On MySQL, the MODIFY statement preserved nullability, default, character set and collation.
• The original value too long errors stopped after the deploy.

Frequently Asked Questions

Does increasing a varchar length lock the table in PostgreSQL? Only for an instant. It takes ACCESS EXCLUSIVE to update the catalog but does not scan or rewrite, so it completes in milliseconds — provided it does not queue behind a long transaction, which is why a lock timeout still matters.

Is text slower than varchar(n) in PostgreSQL? No. They share the same storage and performance characteristics; varchar(n) only adds a length check on write. Many teams use text with a CHECK constraint for flexibility.

Why did widening VARCHAR(60) to VARCHAR(100) copy my MySQL table? In utf8mb4, 60 characters is at most 240 bytes (one-byte length prefix) while 100 characters can be 400 bytes (two-byte prefix). Changing the prefix size requires rewriting every row, so InnoDB uses a table copy.

Can I shrink a varchar online? On PostgreSQL, shrinking scans under an exclusive lock; using text with a CHECK constraint makes it online. On MySQL, shrinking always copies the table; use an online schema change tool for large tables.