Zero-downtime ALTER TABLE in YugabyteDB and where it stops being online

The problem is a deploy that needs a new column without pausing writes

You have a YSQL table serving live traffic. Product wants a new nullable column for a feature flag. In a traditional distributed store you either take the table offline, run a backfill, or accept a risky migration window. The useful takeaway with YugabyteDB 2.12 and later is that ADD COLUMN, DROP COLUMN and many type alterations can run online: the cluster keeps both old and new schema versions live and routes reads and writes to the correct version while a two-phase commit propagates the change across tablets.

The feature exists for both YSQL and YCQL APIs. It is not magic. It works for metadata-only changes and deliberately refuses operations that would require rewriting every row.

How online DDL stays non-blocking

YugabyteDB stores schema metadata per table and per tablet. When you issue an ALTER TABLE, the master coordinates a two-phase commit across the tablets that own the table.

During the transition the cluster maintains two schema versions. A schema version table records which tablets have acknowledged the new definition. Clients continue to read and write using the version they see. New columns are treated as nullable with a default, so existing rows are readable without a backfill. Writes that include the new column are accepted once the tablet has moved to the new version; writes that omit it are filled with the default on the server side.

Because the change is metadata-first, read latency is generally unchanged during the transition. Write latency can see a modest increase from the extra metadata tracking, reported as under 5% overhead in YugabyteDB documentation. The operation requires a brief metadata lock at start and finish, but it does not block concurrent DML.

A verifiable local check

You can observe the behavior on a local cluster started with docker-compose or Helm. The steps below are intended for verification, not production.

Run the cluster and connect as a superuser. Create a test table:

-- run in psql on the YSQL port, e.g. localhost:5433\nCREATE DATABASE testdb;\n\\c testdb\nCREATE TABLE orders (\n  id BIGSERIAL PRIMARY KEY,\n  customer_id BIGINT NOT NULL,\n  amount NUMERIC NOT NULL\n);\n

In one terminal start a continuous insert client, for example psql with a loop. In a second terminal issue the DDL:

ALTER TABLE orders ADD COLUMN feature_flag BOOLEAN DEFAULT FALSE;

While the ALTER is in flight, inserts should continue to succeed. The DDL will not block the insert stream. Required permissions are standard DDL privileges on the table and superuser for system catalog inspection.

To confirm the schema version moved, query catalog tables after the command completes:

SELECT relname, relfilenode FROM pg_class WHERE relname = 'orders';
SELECT * FROM yb_table_properties('orders');

You would expect the schema version to increment and the new column to appear in the table definition. The risk here is issuing the ALTER on a very large table in a multi-region deployment while nodes are unstable. If a node fails mid-change, the two-phase commit can roll back automatically, and in multi-region setups manual intervention may be needed to resolve split-brain metadata states.

Where online DDL stops

Online schema change cannot be used for operations that require rewriting the entire table data. Changing a data type on a large existing dataset, altering the primary key, or adding a NOT NULL column without a default are examples that either require a brief exclusive lock or are rejected.

Changing a primary key still requires a brief lock in current releases. The practical implication is to design schema evolution around additive, nullable columns first, and defer destructive changes to maintenance windows.

Performance impact is limited to metadata work. The extra coordination adds latency to writes during the transition, not to reads. For very wide tables with thousands of columns, catalog bloat can increase planning time, so keep column counts reasonable.

Actionable closing checklist

• Confirm you are on YugabyteDB 2.12 or later. Online DDL behavior is version dependent.
• Prefer ADD COLUMN with DEFAULT NULL for zero-downtime additions.
• Run the ALTER during normal load in staging and measure write latency before, during, and after with a simple benchmark script.
• Check catalog visibility after completion and ensure application clients handle the new column as optional until rollout is complete.
• Plan primary key and type rewrites as offline migrations with a compatibility window.

Online DDL removes the need to stop writes for common additive changes, but it does not remove the need to understand which changes are metadata-only and which require full data rewrite.