Online DDL — Locks, CONCURRENTLY, gh-ost & pt-osc
Online DDL applies schema changes while the database serves traffic, with locks held only for brief metadata steps. Postgres has CREATE INDEX CONCURRENTLY and a leftover INVALID index on failure. MySQL has INSTANT, INPLACE, and COPY. Hot rewrites move to gh-ost (binlog) or pt-osc (triggers).
- 1Gist
- 2Maps
- 3Q&A
- 4Sandbox
Voice readout needs Web Speech Synthesis in this browser.
Hot MySQL table that must be rewritten
Prefer
gh-ost
Reads the binlog and applies changes onto a ghost table. No triggers on the write path. You can throttle, pause, and postpone cutover.
- Write amplification stays off the original table's trigger path.
- Pause actually stops copy writes on the primary.
- A leftover ghost table is retryable if you abort.
Alternative
pt-online-schema-change
Percona Toolkit's classic. Triggers on the original table keep the copy updated, then a RENAME swaps.
- Familiar, and it works where binlog access for gh-ost is painful.
- Every row change pays the trigger. A bad abort can leave triggers behind.
Overview
Online DDL means applying a schema change while the database keeps serving traffic. Lock duration should be a brief metadata operation, not a full table rewrite under an exclusive lock. That is a lock budget. It is not a promise about disk, replica lag, or application correctness.
Postgres gives you CREATE INDEX CONCURRENTLY and REINDEX CONCURRENTLY, with sharp edges. MySQL InnoDB exposes ALGORITHM=INSTANT, INPLACE, and COPY. When the server's online DDL is not enough on a huge hot table, you step outside to gh-ost (binlog) or pt-online-schema-change (triggers).
Expand/contract is still required. Online DDL does not make old pods understand a new column name. What a snapshot contains is the MVCC page. Here you only need the operational fact: a transaction that still holds an old snapshot can stall a concurrent index build.
Flow
- 1
1. Hot table needs a schema change
- next2. Postgres index: CONCURRENTLY
- 2
2. Postgres index: CONCURRENTLY
- next3. Watch INVALID index and old txns
- 3
3. Watch INVALID index and old txns
- next4. Rewrite ALTER: short lock or rebuild
- 4
4. Rewrite ALTER: short lock or rebuild
- next5. MySQL: INSTANT, else INPLACE
- 5
5. MySQL: INSTANT, else INPLACE
- next6. Huge rewrite: choose gh-ost or pt-osc
- 6
6. Huge rewrite: choose gh-ost or pt-osc
- next7. gh-ost: binlog, no write triggers
- 7
7. gh-ost: binlog, no write triggers
- next8. pt-osc: triggers on the original
- 8
8. pt-osc: triggers on the original
- next9. Gate on lag, disk free, lock waits
- 9
9. Gate on lag, disk free, lock waits
Lesson map
Online DDL — Locks, CONCURRENTLY, gh-ost & pt-osc
Online DDL applies schema changes while the database serves traffic, with locks held only for brief metadata steps. Postgres has CREATE INDEX CONCURRENTLY and a leftover INVALID index on failure. MySQL has INSTANT, INPLACE, and COPY. Hot rewrites move to gh-ost (binlog) or pt-osc (triggers).
Architecture. Architecture
Select a node to see why it exists, or an edge to see the protocol, direction, effect, and consequence.
Mermaid export
flowchart TB a["1. Hot table needs a schema change"] b["2. Postgres index: CONCURRENTLY"] c["3. Watch INVALID index and old txns"] d["4. Rewrite ALTER: short lock or rebuild"] a -->|1. Hot table needs a schema change| b b -->|2. Postgres index: CONCURRENTLY| c c -->|3. Watch INVALID index and old txns| d
What lock you actually take
Postgres
- Many
ALTER TABLEforms takeACCESS EXCLUSIVE, even the ones people call fast. While that lock is held, readers and writers wait. - A normal
CREATE INDEXtakes aSHARElock that blocks writes for the whole build. CREATE INDEX CONCURRENTLYavoids a long write block. It takes longer, uses more resources, cannot run inside a transaction block, and can leave an INVALID index if it fails.- Transactions that still hold old snapshots delay the concurrent build. It waits until those snapshots end.
MySQL / InnoDB
ALGORITHM=INSTANT— metadata only, when that operation is supported on your version (someADD COLUMNcases). Check the matrix. Do not memorize one version's answer.ALGORITHM=INPLACE— rebuild or change without a full table copy when the operation allows it. You can still take a brief lock.LOCK=NONEonly when the operation permits it.ALGORITHM=COPY— build a new table copy. This is the classic pain path.- Server online DDL is often not enough on a huge hot table. That is when gh-ost or pt-osc earns its place.
| Tool | Change capture | Write-path cost | Cutover | Failure leftover |
|---|---|---|---|---|
| gh-ost | Reads the binlog | No triggers | Controlled; throttle and pause | Ghost table, retryable |
| pt-online-schema-change | Triggers on the original table | Extra writes per row change | RENAME swap; watch the lock queue | Triggers if you abort poorly |
gh-ost is the GitHub-born tool: explicit control, test on a replica, postpone the swap until a human is awake. pt-osc is the Percona Toolkit classic. It still wins when the team already runs it well, or when giving gh-ost binlog access is the harder problem.
CREATE INDEX CONCURRENTLY
- Not in a transaction. Migration runners that wrap every statement in
BEGINwill fail the concurrent build. - Two scans, then a wait. The build must wait out transactions that could see a broken intermediate index.
- Unique violations show up late. The index is left invalid.
- Clean up before retry.
DROP INDEX CONCURRENTLYthe invalid index, fix the cause, then build again. - Replicas still pay. Concurrent builds write WAL. Lag can spike.
REINDEX CONCURRENTLYis the safer way to rebuild a bloated index than a blockingREINDEX. It is still I/O heavy.
-- Do not wrap this in BEGIN/COMMIT.
CREATE INDEX CONCURRENTLY IF NOT EXISTS users_email_lower_idx
ON users (lower(email));
-- After a failure, or before you trust the index:
SELECT indexrelid::regclass AS index_name, indisvalid, indisready
FROM pg_index
WHERE indrelid = 'users'::regclass;
-- Invalid index left behind: drop, then retry.
-- DROP INDEX CONCURRENTLY users_email_lower_idx;A failed concurrent build is not a no-op. indisvalid = false means the planner must not trust it, and you must drop it before the next attempt.
When the copy tools are the product
Building the shadow table is the long part. The swap is the short, dangerous part. gh-ost's advantage on a hot write table is the absence of trigger amplification: the primary write path does not do extra work per row to maintain the copy. pt-osc's triggers do. Both can still lose if you copy so fast that replicas fall behind, or if the ghost table's disk math was hopeful.
Pause conditions belong in the runbook, not in a dashboard you look at after the disk alert.
Press Run. Snippets must be self-contained — no network, files, or native modules.
Press Run. Snippets must be self-contained — no network, files, or native modules.
Disk free under the floor is an abort: copy algorithms will spend the remaining space. Lag or a pile of lock waits is a throttle: pause gh-ost or shrink the chunk. Healthy lag, no lock pile, and spare disk is proceed.
Interview Q&A
Why is CREATE INDEX CONCURRENTLY slower, and why do you still want it in production?
Answer
It avoids a long-lived write lock. You trade CPU, I/O, and wall time for availability. A blocking index build is fine on a small table or an offline one. On a hot table it is an outage you scheduled by accident.
What is the difference between INSTANT, INPLACE, and COPY in MySQL?
Answer
INSTANT is metadata when the server supports that operation. INPLACE changes the table without a full copy when the operation allows it. COPY builds a new table and moves rows. Always read the operation matrix for your version. A blog that says "ADD COLUMN is instant" is a version claim, not a law.
gh-ost versus pt-osc, in one sentence each?
Answer
gh-ost applies row changes from the binlog onto a ghost table and does not install triggers. pt-osc uses triggers on the original table to keep the copy updated, which adds write amplification. Prefer gh-ost on a hot write path. Prefer pt-osc when binlog access is the harder constraint or the team already operates it.
Online DDL finished and the app is still slow. Why?
Answer
The new index changed plans. A cutover lock queue is still draining. Replicas are lagged and your reads use them. The cache is cold after a rewrite. "The DDL statement returned" is not "the system is healthy."
Can online DDL replace expand/contract?
Answer
No. Online DDL reduces database lock pain. Expand/contract handles two application versions during a rolling deploy. A concurrent index does not stop old code from selecting a column you dropped.
A concurrent index failed halfway. What do you check?
Answer
pg_index.indisvalid and indisready. An invalid index is still an object. Drop it with DROP INDEX CONCURRENTLY, fix the unique violation or the session that was holding a snapshot, and retry. Do not re-run the create and hope the name collision is the only problem.
Why can't CREATE INDEX CONCURRENTLY run inside a transaction?
Answer
The build needs multiple transactions: scans, a wait for old snapshots, and a final validation. A single wrapping BEGIN cannot do that. Migration frameworks that open one transaction per file will fail this statement. Split it out.
What do you monitor while the copy runs?
Answer
Replica lag, free disk, and lock waits. Abort when a second copy would fill the volume. Throttle when lag crosses the SLO or lock waits pile up. Also watch the cutover window: a RENAME swap under a wait queue is the moment users feel it.
Pitfalls
- Wrapping
CREATE INDEX CONCURRENTLYinBEGIN. - Leaving an INVALID index in place and wondering why the planner ignores it, or why the retry errors on the name.
- Assuming
ALGORITHM=INPLACEfor an operation your version only knows how toCOPY. - Running pt-osc triggers on a table that is already write-bound.
- Copying a 1 TB table onto a volume with 400 GB free.
- Declaring the migration done when the statement ends and replicas are minutes behind.
You need an index on lower(email) in Postgres, and a column type change on a 2 TB MySQL orders table that takes 20k writes per second. Say which statement or tool you use for each, what you refuse to wrap in a transaction, and which metric aborts the MySQL copy.
Go deeper
The Postgres CREATE INDEX page documents CONCURRENTLY, including the invalid-index outcome. REINDEX documents the concurrent rebuild. The MySQL online DDL operations matrix is the version-specific source for INSTANT, INPLACE, and COPY. gh-ost's README is the triggerless design. Percona's pt-online-schema-change page is the trigger design.
"Online" is not "free." Bound the locks, watch lag and disk, and pick the tool that matches the write rate. Next: Dual write / dual read.