Schema changes are where MySQL and PostgreSQL operational cultures differ most. MySQL teams spent a decade building muscle around online DDL: check the ALGORITHM, estimate the rebuild time, reach for gh-ost or pt-osc when the table is big or replication lag matters. PostgreSQL teams built a different muscle: most DDL is instant, so the game is not avoiding long rebuilds — it is avoiding a short exclusive lock at the wrong moment. Bring MySQL habits to PostgreSQL unadjusted and you will either be too scared of operations that are actually free, or too casual about a one-line ALTER that can freeze your application for minutes without writing a single row.
I have run schema changes on both engines, and neither model is simply better. MySQL's is more work per change but degrades predictably. PostgreSQL's is nearly free most of the time with a sharp edge case you must engineer around. This guide translates between them.
MySQL online DDL: the algorithm matrix
Modern MySQL classifies every ALTER TABLE by algorithm. INSTANT (8.0.12+) changes only metadata — adding a column, renaming a column, setting a default — and returns immediately regardless of table size. INPLACE rebuilds or modifies the table within InnoDB while permitting concurrent DML for most operations; adding a secondary index is the classic case: no table copy, reads and writes continue, but real I/O and CPU for the duration. COPY is the legacy path — full table copy with writes blocked — still required for some type changes. The professional habit is to never guess: state your expectation and let the server refuse if it cannot comply.
-- MySQL: fail fast if the operation cannot be done online
ALTER TABLE orders
ADD COLUMN fraud_score DECIMAL(5,2),
ALGORITHM=INSTANT;
ALTER TABLE orders
ADD INDEX idx_orders_customer (customer_id),
ALGORITHM=INPLACE, LOCK=NONE;Two footnotes MySQL veterans already know. Even online DDL needs brief metadata locks at the start and end, so an ALTER can still queue behind a long-running query and stall traffic behind it — a small preview of the PostgreSQL problem. And on replicated setups, a long INPLACE operation replays on replicas and can create lag, which is a big part of why gh-ost and pt-osc exist: they copy to a shadow table, keep it current through triggers (pt-osc) or binlog tailing (gh-ost), and cut over with a rename, trading DDL risk for tooling complexity and doubled disk during the copy.
PostgreSQL DDL: usually instant, always lock-hungry
PostgreSQL's story inverts the concern. Most ALTER TABLE forms take an ACCESS EXCLUSIVE lock — the strongest lock, conflicting with everything including plain SELECTs — but hold it only long enough to update the catalogs. Adding a nullable column: catalog-only, instant on any table size. Since PostgreSQL 11, adding a column with a constant default is also instant; the default is stored once in the catalog rather than rewritten into every row. Dropping a column, instant — data is reclaimed lazily later. DDL is also transactional: you can run several changes in one transaction and roll them all back, something MySQL cannot do because its DDL commits implicitly.
The operations that do rewrite the table — most column type changes, adding a column with a volatile default like now() or a generated identity backfill — hold ACCESS EXCLUSIVE for the entire rewrite, and everything on that table stops until it finishes. The full operation-by-lock breakdown is worth studying; the ALTER TABLE lock reference maps out which forms are instant and which rewrite. The habit shift: on MySQL you asked "how long will this rebuild take?"; on PostgreSQL you ask "what lock does this take, and for how long?" — and for most changes the answer is delightfully boring.
The lock queue: PostgreSQL's sharpest DDL edge
Here is the trap that catches every team migrating from MySQL. An instant ALTER TABLE still has to acquire ACCESS EXCLUSIVE first, and lock requests queue. If a reporting query has been reading the table for four minutes, your ALTER waits behind it — and because PostgreSQL queues lock requests fairly, every new query on the table, including trivial single-row SELECTs, now waits behind your waiting ALTER. A one-millisecond schema change plus one slow query equals a full application stall on that table. Nothing is wrong, nothing is deadlocked; the queue is just doing what queues do. The dynamics and diagnosis are covered in depth in lock queues in DDL migrations; the pg_stat_activity view of it is a growing crowd of sessions waiting on a lock held by nobody-obvious.
CREATE INDEX CONCURRENTLY and NOT VALID constraints
For the genuinely long operations, PostgreSQL provides online variants with their own rules. A plain CREATE INDEX blocks writes for the whole build — never acceptable on a hot table. CREATE INDEX CONCURRENTLY builds without blocking reads or writes, at the cost of scanning the table twice, taking longer, refusing to run inside a transaction block, and leaving an INVALID index behind if it fails, which you must drop and retry. Constraints have a parallel pattern: add CHECK constraints and foreign keys as NOT VALID, which is quick, then VALIDATE CONSTRAINT later — validation takes a much weaker lock that allows normal reads and writes to continue.
-- PostgreSQL: the online patterns
CREATE INDEX CONCURRENTLY idx_orders_customer
ON orders (customer_id);
ALTER TABLE orders
ADD CONSTRAINT orders_amount_positive
CHECK (amount >= 0) NOT VALID;
ALTER TABLE orders
VALIDATE CONSTRAINT orders_amount_positive;These are the moral equivalents of gh-ost: the built-in ways to do heavy work without stopping traffic. The difference is that they are native, single-command, and free of shadow tables — but they are opt-in, and an ORM migration tool that emits the plain forms will happily block production for you.
The lock_timeout rollout pattern
The standard PostgreSQL defense against the lock queue is to make the ALTER give up quickly instead of poisoning the queue. Set lock_timeout to a small value for the migration session; if the ALTER cannot get its lock within, say, two seconds, it fails, releases its queue position, traffic flows again, and your migration tool retries in a loop until it wins a quiet moment. Wrap the pattern into your migration framework once — SET lock_timeout, attempt, backoff, retry with a bounded count, alert a human on exhaustion — and instant DDL becomes genuinely safe to ship at any hour. Pair it with a statement_timeout for the rewriting operations so a mistakenly heavy ALTER cannot run unbounded, and audit long-running queries before large rollouts. The complete playbook, including multi-step patterns for type changes and backfills, is laid out in zero-downtime migrations.
Translating the habits
The mapping for a migrating team: ALGORITHM=INSTANT maps to PostgreSQL's default catalog-only DDL — most changes need no special handling at all. ALGORITHM=INPLACE for index builds maps to CREATE INDEX CONCURRENTLY. gh-ost and pt-osc mostly map to nothing, because the rewrites they avoid are rarer on PostgreSQL, and where a rewrite is unavoidable you use expand-and-contract: add the new column, backfill in batches, swap, drop. The one habit with no MySQL ancestor is lock-queue awareness — checking for long-running queries and setting lock_timeout before every DDL, every time. Teams that adopt it find PostgreSQL schema changes faster and less ceremonial than what they left; teams that skip it usually get one memorable outage first.
How MonPG helps once you are on PostgreSQL
MonPG monitors PostgreSQL only, and schema-change safety is one of its sharpest use cases. Before a rollout, it shows the long-running queries and idle-in-transaction sessions that would trap your ALTER in the lock queue. During one, lock waits and blocking chains are live evidence, so a stalled migration is diagnosed in seconds rather than by folklore. Afterward, retained pg_stat_statements history shows whether the new index is used and whether any query family regressed — the question every migration review actually asks. The PostgreSQL monitoring guide covers the lock, query, and session signals that make DDL on a busy database a routine event instead of a gamble.