Does PostgreSQL support online schema modification (DDL)?

Recent versions of MySQL (using InnoDB) support many alter table queries without exclusive locking the table.

Does PostgreSQL support this to any level? That is, can I add/drop columns from a table while I concurrently read/write rows to it (even if performance is degraded)?

TL;DR: No, except for some basic cases.

Some lock-strength reductions for ALTER TABLE have been added to PostgreSQL 9.5. You can't do anything that requires a full table rewrite without an exclusive lock though, in 9.5 or below.

Some operations, like ALTER TABLE ... DROP COLUMN or ALTER TABLE ... ADD COLUMN ... without a DEFAULT and NOT NULL can be done with a very short exclusive lock even in older versions. A brief moment is needed where queries don't run, but it's almost instantaneous, since no table rewrite is required.

There are some things that could be optimised, like ALTER TABLE ... ADD COLUMN ... DEFAULT ... NOT NULL, by storing the default for old rows in the table metadata and looking it up when reading old rows. This has not been implemented in PostgreSQL.

In theory PostgreSQL could use its MVCC catalogs to support what you describe by background-writing new versions of rows into a table while continuing to use the old versions for queries. I'm not aware of anyone attempting to do that, and it'd require some special handling because PostgreSQL's code currently assumes that table rows only have one possible structure at any given time.

StackExchange Database Administrators Q#105847, answer score: 7