Adding new null column to table caused lock

Yesterday we ran a schema migration on our production database that we thought would be safe, but ran into an issue. It seems as though adding the new column caused a lock and our application could no longer get access to the table. Here's an explain of the table before the migration:

```
Table "public.facilities"
Column | Type | Modifiers | Storage | Stats target | Description
-----------------------------------+-----------------------------+---------------------------------------------------------+----------+--------------+-------------
id | integer | not null default nextval('facilities_id_seq'::regclass) | plain | |
name | character varying(255) | | extended | |
phone | character varying(255) | | extended | |
time_zone | character varying(255) | | extended | |
company_id | integer | | plain | |
created_at | timestamp without time zone | | plain | |
updated_at | timestamp without time zone | | plain | |
phnkey_id_token | character varying(255) | | extended | |
phnkey_api_key | character varying(255) | | extended |

ALTER TABLE requires an ACCESS EXCLUSIVE lock. From the doc:


ALTER TABLE changes the definition of an existing table. There are
several subforms described below. Note that the lock level required
may differ for each subform. An ACCESS EXCLUSIVE lock is held unless
explicitly noted. When multiple subcommands are listed, the lock
held will be the strictest one required from any subcommand

It does not normally take several minutes to add a column if there is no default value or if the table is small, and in your case, you're good on both.

But in order to be granted that lock, other transactions should not hold locks on the same table. There are several things to consider:

• A simple SELECT on a table requires a lock that will block others from doing an ALTER TABLE.

• Locks are released only at the end of their transaction, not at the end of the instruction that needed them.

• As soon as an ALTER TABLE ...ADD COLUMN is waiting for a lock, other transactions that want to SELECT the table are going to be blocked too.

The consequences are:

-
if there are long running transactions that read the table, they will block the ALTER TABLE of the migration until they're finished.

-
if the migration itself is part of larger transaction, this will block other processes that want to access the table, until it commits.

StackExchange Database Administrators Q#111058, answer score: 10