5

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)?

Improve this question
1

2 Answers 2

Reset to default
7

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.

Improve this answer
8
  • 2
    To my understanding, adding a new column in MySQL means creating a temporary copy of the table, applying concurrent changes to it and then putting it into the place of the old one. In contrast, adding a new column in PostgreSQL is practically a change in the catalog - not online in the strict sense of the word, but if done carefully causing only minimal disturbance through the exclusive lock. So, from a practical point of view, postgres has been already there for quite a long time...
    – András Váczi
    Commented Jul 3, 2015 at 9:58
  • Adding a new nullable column is. You have to be careful when adding a not-null col with a default. Add it as nullable with no default, ALTER to add the default, UPDATE to assign the default to existing rows, and finally ALTER TABLE ... SET NOT NULL the col. Even then the SET NOT NULL takes an ACCESS EXCLUSIVE lock that even blocks SELECTs, totally unnecessarily, until 9.5, and it might take a while on a big table. I'm impressed by what MySQL's done here.
    – Craig Ringer
    Commented Jul 3, 2015 at 10:53
  • Yes, the SET NOT NULL can be a pain, just like creating foreign keys (and maybe other operations, too).
    – András Váczi
    Commented Jul 3, 2015 at 11:03
  • The lock strength reductions in 9.5 will help a bit, at least
    – Craig Ringer
    Commented Jul 3, 2015 at 11:04
  • 2
    MySQL 5.6 can do many ALTERs without creating a temporary copy.
    – Rick James
    Commented Jul 4, 2015 at 2:28
5

As Craig Ringer mentioned PostgreSQL started to support some ALTER operations without lock long ago.

But since version 11 ALTER TABLE ... ADD COLUMN ... DEFAULT ... NOT NULL also avoids table rewrite (and so long lock), so you can safely use it in your migrations.

You can read more about it in this article Fast Column Creation with Defaults

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.