Adding new null column to table caused lock

Question

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 |              |
 phnkey_auth_token                 | character varying(255)      |                                                         | extended |              |
 payment_provider_name             | character varying(255)      |                                                         | extended |              |
 payment_provider_data             | hstore                      |                                                         | extended |              |
 tenant_portal_key                 | character varying(255)      |                                                         | extended |              |
 uuid                              | uuid                        | default uuid_generate_v4()                              | plain    |              |
 deleted                           | boolean                     | default false                                           | plain    |              |
 deleted_on                        | timestamp without time zone |                                                         | plain    |              |
 deleted_by_id                     | integer                     |                                                         | plain    |              |
 accounting_method                 | character varying(255)      |                                                         | extended |              |
 last_exported_on                  | timestamp without time zone |                                                         | plain    |              |
 public_url                        | character varying(255)      |                                                         | extended |              |
 access_hours_same_as_office_hours | boolean                     | default false                                           | plain    |              |
 logo                              | character varying(255)      |                                                         | extended |              |
 next_lease_number                 | integer                     | default 1                                               | plain    |              |
 email                             | character varying           |                                                         | extended |              |
 fax                               | character varying           |                                                         | extended |              |
 store_number                      | character varying           |                                                         | extended |              |
 custom_portal_url                 | character varying           |                                                         | extended |              |
 brand_name                        | character varying           |                                                         | extended |              |
Indexes:
    "facilities_pkey" PRIMARY KEY, btree (id)
    "index_facilities_on_tenant_portal_key" UNIQUE, btree (tenant_portal_key)
    "index_facilities_on_uuid" UNIQUE, btree (uuid)
    "index_facilities_on_company_id" btree (company_id)

And here's the table after we added the column "landmarks":

                                                                     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 |              |
 phnkey_auth_token                 | character varying(255)      |                                                         | extended |              |
 payment_provider_name             | character varying(255)      |                                                         | extended |              |
 payment_provider_data             | hstore                      |                                                         | extended |              |
 tenant_portal_key                 | character varying(255)      |                                                         | extended |              |
 uuid                              | uuid                        | default uuid_generate_v4()                              | plain    |              |
 deleted                           | boolean                     | default false                                           | plain    |              |
 deleted_on                        | timestamp without time zone |                                                         | plain    |              |
 deleted_by_id                     | integer                     |                                                         | plain    |              |
 accounting_method                 | character varying(255)      |                                                         | extended |              |
 last_exported_on                  | timestamp without time zone |                                                         | plain    |              |
 public_url                        | character varying(255)      |                                                         | extended |              |
 access_hours_same_as_office_hours | boolean                     | default false                                           | plain    |              |
 logo                              | character varying(255)      |                                                         | extended |              |
 next_lease_number                 | integer                     | default 1                                               | plain    |              |
 email                             | character varying           |                                                         | extended |              |
 fax                               | character varying           |                                                         | extended |              |
 store_number                      | character varying           |                                                         | extended |              |
 custom_portal_url                 | character varying           |                                                         | extended |              |
 brand_name                        | character varying           |                                                         | extended |              |
 landmarks                         | character varying           |                                                         | extended |              |
Indexes:
    "facilities_pkey" PRIMARY KEY, btree (id)
    "index_facilities_on_tenant_portal_key" UNIQUE, btree (tenant_portal_key)
    "index_facilities_on_uuid" UNIQUE, btree (uuid)
    "index_facilities_on_company_id" btree (company_id)

Is there something you see here that would lead to a table lock on adding the column "landmarks"? We're just trying to avoid the down time in the future.

Further info: The table has 304 rows and was in a state that our application couldn't use it for 8 minutes.

The application is a rails app and we used a rake task to perform the migration. Here's the code we used:

class AddLandmarkToFacility < ActiveRecord::Migration
  def change
    add_column :facilities, :landmarks, :string
  end
end

Which I believe results in this ALTER statement:

ALTER TABLE "facilities" ADD "landmarks" character varying;

Answer 1

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.