Split View: 무중단 스키마 변경 완전 가이드: DDL이 잡는 잠금 등급과 안전 실행 절차

The Complete Guide to Zero-Downtime Schema Changes: The Lock Level Each DDL Takes and How to Run It Safely

• Introduction
• 1. What Actually Breaks Zero Downtime Is the Lock Queue
• 2. The Eight Table Lock Modes
• 3. Lock Level by ALTER TABLE Variant
• 4. Changes That Trigger a Rewrite, and Changes That Do Not
• 5. NOT VALID and VALIDATE — Splitting a Constraint into Two Phases
• 6. A Safe Execution Procedure — lock_timeout and Retries
• 7. Zero-Downtime Work on Indexes and Partitions
• 8. Gating Migrations
• Quiz: Check Your Understanding
• Conclusion
• References
• Further reading

Introduction

This blog already has several posts about zero-downtime schema changes. The Expand-Contract Pattern covers the design pattern of breaking a change into small, reversible steps, and Online Schema Changes on Large Tables covers tools like gh-ost and pt-online-schema-change.

This post is the layer underneath those. What lock does PostgreSQL actually take for each DDL statement, and how does that lock collide with production traffic? Knowing the patterns and the tools is not enough — miss this layer and you get an incident. This is exactly why a deploy someone described as "it's just adding one column" can actually take a service down for five minutes.

The reference engine is PostgreSQL 18, and every lock level and rewrite behavior stated here has been confirmed against the PostgreSQL 18 documentation. MySQL's online DDL uses a completely different model built around algorithm choice (INPLACE, COPY, INSTANT), so it is not mixed into this post.

1. What Actually Breaks Zero Downtime Is the Lock Queue

There is a very common misunderstanding: "the DDL takes 10 seconds, so the service will just be 10 seconds slower." In reality, that is not what happens. While the DDL waits to acquire its lock, the ordinary queries that arrive behind it stop too.

A lock request in PostgreSQL joins a queue. If a session requests an ACCESS EXCLUSIVE lock and cannot get it because of a long-running SELECT already in progress, it waits — and every new SELECT that arrives afterward also ends up requesting a lock that conflicts with the DDL, so it lines up behind the DDL in the same queue. The result: the DDL itself takes one second, but a single 30-second query ahead of it blocks every new request for the full 30 seconds.

Once you understand this mechanism, three principles for zero-downtime schema changes follow naturally.

1. Avoid DDL that demands a strong lock whenever you can, and when you cannot avoid it, make it as short as possible.
2. Confirm there is no long-lived transaction before running the DDL.
3. Always attach a lock_timeout to the DDL so it cannot block the queue for long.

2. The Eight Table Lock Modes

PostgreSQL has eight table-level lock modes. Listed from weakest to strongest, with conflicts exactly as the documentation's table states them.

There are two lines worth memorizing for daily work.

• ACCESS SHARE is the lock an ordinary SELECT takes, and it conflicts only with ACCESS EXCLUSIVE. In other words, a DDL statement that does not take ACCESS EXCLUSIVE does not block reads.
• ROW EXCLUSIVE is the lock INSERT, UPDATE, DELETE, and MERGE take. A DDL statement that takes a level conflicting with this (SHARE or stronger) blocks writes.

Put together: everything up through SHARE UPDATE EXCLUSIVE lets both reads and writes pass. Starting at SHARE ROW EXCLUSIVE, writes get blocked, and at ACCESS EXCLUSIVE, reads are blocked too.

The documentation states which lock each command takes:

• ACCESS EXCLUSIVE — DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL, REFRESH MATERIALIZED VIEW without CONCURRENTLY, and a good many ALTER TABLE and ALTER INDEX variants
• SHARE UPDATE EXCLUSIVE — VACUUM without FULL, ANALYZE, CREATE INDEX CONCURRENTLY, REINDEX CONCURRENTLY, CREATE STATISTICS, COMMENT ON, and some ALTER TABLE variants
• SHARE ROW EXCLUSIVE — CREATE TRIGGER, and some ALTER TABLE variants
• ROW EXCLUSIVE — UPDATE, DELETE, INSERT, MERGE

3. Lock Level by ALTER TABLE Variant

The documentation's own wording is explicit: "The lock level required may differ for each variant. Where a lock level is not explicitly mentioned, ACCESS EXCLUSIVE lock is acquired."

In other words, the default is the worst case. All you need to memorize are the exceptions — the variants for which the documentation specifies a lower level.

A few details matter here.

ADD FOREIGN KEY is an exception the documentation specifically calls out: "most forms of ADD table_constraint require ACCESS EXCLUSIVE lock, but ADD FOREIGN KEY requires only SHARE ROW EXCLUSIVE lock." Writes are still blocked, though.

ATTACH PARTITION takes only SHARE UPDATE EXCLUSIVE on the parent, but according to the documentation, it takes ACCESS EXCLUSIVE on the table being attached itself, and on the DEFAULT partition (if one exists). If a DEFAULT partition exists, that partition is locked completely, so if you run a large DEFAULT partition in production, adding a partition becomes an incident every single time.

4. Changes That Trigger a Rewrite, and Changes That Do Not

Just as important as the lock level is whether the table gets rewritten. Holding ACCESS EXCLUSIVE for one millisecond and holding it for one hour are completely different stories.

Here is the single most useful fact the documentation states. Adding a column with a non-volatile default does not rewrite the table. In the documentation's own words: adding a column with ADD COLUMN and specifying a non-volatile DEFAULT means the default value is evaluated at the time of the statement and stored in the table's metadata, to be returned whenever an existing row is accessed. Because this value is only actually applied once the table is rewritten, ALTER TABLE runs very fast even on a large table.

-- No rewrite: only metadata is updated (since PostgreSQL 11)
ALTER TABLE orders ADD COLUMN channel text DEFAULT 'WEB' NOT NULL;

This behavior was introduced in PostgreSQL 11. If you are running version 10 or earlier, the same statement rewrites the entire table, so never run it as-is there.

The documentation also lists the cases that do trigger a rewrite. Adding a volatile DEFAULT (for example, clock_timestamp()), a stored generated column, an identity column, or a column of a domain type with a constraint rewrites the table and every index on it.

Type changes work the same way. Changing the type of an existing column normally rewrites the entire table and its indexes. As an exception, no rewrite is needed when the USING clause does not change the column's contents and the old type is either binary-coercible to the new type, or an unconstrained domain over the new type.

Warning: ALTER TABLE ... ALTER COLUMN ... TYPE rewrites the entire table, in most cases while holding an ACCESS EXCLUSIVE lock. On a 100-million-row table, that blocks even reads completely for tens of minutes. The safe alternative is an expand-contract procedure: add a new column, backfill it, cut the application over, and only then drop the old column. Relaxing only a length constraint — for instance widening varchar(50) to varchar(100) — is handled without a rewrite, but narrowing it is a rewrite.

5. NOT VALID and VALIDATE — Splitting a Constraint into Two Phases

Adding a constraint is the best-organized area of zero-downtime schema change. The documentation spells out both the method and the reasoning.

Scanning a large table to verify a new foreign key, check, or NOT NULL constraint can take a long time, and other updates to that table are blocked until the ALTER TABLE ADD CONSTRAINT command commits. The main purpose of the NOT VALID constraint option is to reduce the impact adding a constraint has on concurrent updates. With NOT VALID, the ADD CONSTRAINT command does not scan the table and can commit immediately. After that, a VALIDATE CONSTRAINT command can verify that existing rows satisfy the constraint. This validation step does not need to block concurrent updates, because it already knows the constraint is being enforced on any row another transaction inserts or updates. Since only existing rows need to be checked, validation acquires only a SHARE UPDATE EXCLUSIVE lock on the target table.

-- Phase 1: commits immediately. The constraint is enforced starting with the next insert/update
ALTER TABLE order_items
  ADD CONSTRAINT fk_order_items_order
  FOREIGN KEY (order_id) REFERENCES orders (id) NOT VALID;

-- Phase 2: validate existing rows. Takes only SHARE UPDATE EXCLUSIVE, so it does not block reads/writes
ALTER TABLE order_items VALIDATE CONSTRAINT fk_order_items_order;

The same idea applies to adding NOT NULL. According to the documentation, SET NOT NULL normally scans the entire table during ALTER TABLE to check it. However, if a valid CHECK constraint already exists that proves no NULL can be present, the table scan is skipped.

-- Phase 1: register immediately with a NOT VALID CHECK
ALTER TABLE users
  ADD CONSTRAINT chk_users_email_nn CHECK (email IS NOT NULL) NOT VALID;

-- Phase 2: validate under a weak lock
ALTER TABLE users VALIDATE CONSTRAINT chk_users_email_nn;

-- Phase 3: SET NOT NULL now skips the full scan
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- Phase 4: clean up the now-redundant CHECK constraint
ALTER TABLE users DROP CONSTRAINT chk_users_email_nn;

Phases 3 and 4 still take ACCESS EXCLUSIVE, but because there is no scan, they finish instantly. This is a strategy that shortens how long the strong lock is held, not one that lowers the lock level.

6. A Safe Execution Procedure — lock_timeout and Retries

Now we return to the queueing problem from section 1. Even a DDL statement that holds ACCESS EXCLUSIVE only briefly will, if it waits a long time just to acquire that lock, block every request behind it for that entire wait.

The fix is: "try briefly, give up if you cannot get it, and try again a little later." The tool for this is lock_timeout. The documentation defines it as aborting any statement that waits longer than the specified duration while attempting to acquire a lock on a table, index, row, or other database object, and its default is 0, meaning the timeout is disabled. On default settings, that means a DDL statement waits indefinitely.

-- A template for running DDL safely
BEGIN;
SET LOCAL lock_timeout = '3s';
SET LOCAL statement_timeout = '30s';

ALTER TABLE orders ADD COLUMN channel text DEFAULT 'WEB' NOT NULL;

COMMIT;

Using SET LOCAL applies the setting only to this transaction, and it reverts automatically on commit or rollback. If the lock cannot be acquired within 3 seconds, the statement ends in an error, so it never blocks the queue for long. On failure, retry a few seconds later — it succeeds the moment traffic happens to thin out for an instant.

Build a check for long-lived transactions into the procedure too, before you run the DDL.

-- A check to run right before DDL: transactions open 5 minutes or longer
SELECT pid, state, now() - xact_start AS xact_age,
       wait_event_type, left(query, 80) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND now() - xact_start > interval '5 minutes'
ORDER BY xact_start;

Turning on log_lock_waits logs an entry whenever a lock wait exceeds deadlock_timeout (default 1 second). Checking this log after a deploy lets you catch, after the fact, the deploy that "looked fine but actually blocked things for 3 seconds."

7. Zero-Downtime Work on Indexes and Partitions

Warning: CREATE INDEX without CONCURRENTLY blocks writes to the target table until it finishes. In the documentation's words, it "locks out writes but not reads." REINDEX is worse — it takes ACCESS EXCLUSIVE, so it blocks reads too. On a production table, always use CREATE INDEX CONCURRENTLY and REINDEX INDEX CONCURRENTLY. Both commands take only SHARE UPDATE EXCLUSIVE.

CONCURRENTLY comes with three constraints.

First, it cannot run inside a transaction block. If your migration tool wraps every migration in a single transaction by default, it fails. Find the setting your tool provides to turn transactions off for this case.

Second, a failure leaves behind an invalid index. In the documentation's words, such an index "is ignored for querying purposes because it might be incomplete," but "it will still consume update overhead." Automate a check for this after every migration.

-- Automated post-deploy check: invalid indexes
SELECT c.relname AS index_name, t.relname AS table_name
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_class t ON t.oid = i.indrelid
WHERE NOT i.indisvalid;

Third, it cannot be used directly on a partitioned table. The documentation states that concurrent index creation on a partitioned table is currently not supported, and offers a workaround: build the index on each partition individually with CONCURRENTLY, then build it non-concurrently on the parent last — this reduces how long writes stay locked.

Detaching a partition has a concurrent mode too. ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY runs, in the documentation's words, with a reduced lock level so as not to block other sessions accessing the partitioned table. This option is central to any retention policy that periodically detaches old partitions.

8. Gating Migrations

Leaving the rules covered so far to human memory means they eventually get bypassed. It is better to put a gate in code review and CI.

As a checklist:

• Does every CREATE INDEX / REINDEX / DROP INDEX on a production table have CONCURRENTLY attached?
• Does every DDL statement have SET LOCAL lock_timeout in front of it?
• Is adding a constraint split into the two NOT VALID + VALIDATE CONSTRAINT phases?
• Does it avoid a column type change? (If one is present, replace it with expand-contract.)
• Is a column drop kept out of the same release as the deploy? (An older application instance may still reference that column.)
• Is the default on every ADD COLUMN non-volatile?
• Is the migration free of being wrapped as one giant transaction?

One more note on dropping columns. ALTER TABLE ... DROP COLUMN does not actually erase the data — it just hides the column — so it is fast. But during a rolling deploy where an older application instance is still alive, that instance's SELECT * breaks. Always push a column drop to a release after the application deploy has fully finished.

You need a rollback plan ready too. Rolling back a schema change is not "the reverse DDL" — it is "the reverse DDL plus handling whatever data accumulated in between." If you drop a column and then bring it back, the data from in between is gone. That is why the rule is to always push destructive changes to the very last step.

Quiz: Check Your Understanding

ALTER TABLE orders ADD COLUMN memo text;

ALTER TABLE users ADD CONSTRAINT chk_email_nn
  CHECK (email IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT chk_email_nn;
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
ALTER TABLE users DROP CONSTRAINT chk_email_nn;

-- A
ALTER TABLE events ADD COLUMN created_by text DEFAULT 'system' NOT NULL;
-- B
ALTER TABLE events ADD COLUMN created_at timestamptz DEFAULT clock_timestamp() NOT NULL;

Conclusion

Zero-downtime schema change is not a tooling problem — it is a problem of knowing the lock level. Cases that actually need a tool like gh-ost are fewer than people think, and in PostgreSQL, most changes can be handled safely using nothing but the methods written in the documentation. NOT VALID and VALIDATE CONSTRAINT, CONCURRENTLY, and SET LOCAL lock_timeout — these three replace most of what a tool would otherwise do for you.

The rule worth leaving with your team fits in one sentence: run every DDL statement knowing which lock it takes and for how many seconds. If you do not know, turn on log_lock_waits in staging, measure it, and only then run it.

If you want to check a migration order visually, use the DB Migration Explorer; if you want to run DDL yourself and watch what happens, use the PostgreSQL Playground.

References

• PostgreSQL 18, ALTER TABLE: https://www.postgresql.org/docs/18/sql-altertable.html (retrieved 2026-08-15)
• PostgreSQL 18, Explicit Locking: https://www.postgresql.org/docs/18/explicit-locking.html (retrieved 2026-08-15)
• PostgreSQL 18, CREATE INDEX: https://www.postgresql.org/docs/18/sql-createindex.html (retrieved 2026-08-15)
• PostgreSQL 18, Client Connection Defaults: https://www.postgresql.org/docs/18/runtime-config-client.html (retrieved 2026-08-15)
• PostgreSQL 18, Lock Management: https://www.postgresql.org/docs/18/runtime-config-locks.html (retrieved 2026-08-15)
• PostgreSQL 18, Table Partitioning: https://www.postgresql.org/docs/18/ddl-partitioning.html (retrieved 2026-08-15)
• PostgreSQL 18, REFRESH MATERIALIZED VIEW: https://www.postgresql.org/docs/18/sql-refreshmaterializedview.html (retrieved 2026-08-15)

Further reading

• Previous: The Complete Guide to Transaction Isolation Levels — the operational contract of isolation levels
• Next: The Complete Guide to Partitioning and Sharding — the order for outgrowing a single node
• The Expand-Contract Pattern — a design that splits a change into phases
• Online Schema Changes on Large Tables — gh-ost and pt-online-schema-change
• Migration Rollback and Verification — designing a safety net
• DB Migration Explorer — check a migration order
• PostgreSQL Playground — experiment with DDL locks