PostgreSQL – Adding New Null Column to Table Caused Lock

postgresqlpostgresql-9.3

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;

Best Answer

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.

Better Queries

This is just your query reformatted with aliases and some noise removed to clear the fog:

SELECT count(DISTINCT t.id)
FROM   tickets      t
JOIN   transactions tr ON tr.objectid = t.id
JOIN   attachments  a  ON a.transactionid = tr.id
WHERE  t.status <> 'deleted'
AND    t.type = 'ticket'
AND    t.effectiveid = t.id
AND    tr.objecttype = 'RT::Ticket'
AND    a.contentindex @@ plainto_tsquery('frobnicate');

Most of the problem with your query lies in the first two tables tickets and transactions, which are missing from the question. I'm filling in with educated guesses.

t.status, t.objecttype and tr.objecttype should probably not be text, but enum or possibly some very small value referencing a look-up table.

`EXISTS` semi-join

Assuming tickets.id is the primary key, this rewritten form should be much cheaper:

SELECT count(*)
FROM   tickets t
WHERE  status <> 'deleted'
AND    type = 'ticket'
AND    effectiveid = id
AND    EXISTS (
   SELECT 1
   FROM   transactions tr
   JOIN   attachments  a ON a.transactionid = tr.id
   WHERE  tr.objectid = t.id
   AND    tr.objecttype = 'RT::Ticket'
   AND    a.contentindex @@ plainto_tsquery('frobnicate')
   );

Instead of multiplying rows with two 1:n joins, only to collapse multiple matches in the end with count(DISTINCT id), use an EXISTS semi-join, which can stop looking further as soon as the first match is found and at the same time obsoletes the final DISTINCT step. Per documentation:

The subquery will generally only be executed long enough to determine whether at least one row is returned, not all the way to completion.

Effectiveness depends on how many transactions per ticket and attachments per transaction there are.

Determine order of joins with `join_collapse_limit`

If you know that your search term for attachments.contentindex is very selective - more selective than other conditions in the query (which is probably the case for 'frobnicate', but not for 'problem'), you can force the sequence of joins. The query planner can hardly judge selectiveness of particular words, except for the most common ones. Per documentation:

join_collapse_limit (integer)

[...]
Because the query planner does not always choose the optimal join order, advanced users can elect to temporarily set this variable to 1, and then specify the join order they desire explicitly.

Use SET LOCAL for the purpose to only set it for the current transaction.

BEGIN;
SET LOCAL join_collapse_limit = 1;

SELECT count(DISTINCT t.id)
FROM   attachments  a                              -- 1st
JOIN   transactions tr ON tr.id = a.transactionid  -- 2nd
JOIN   tickets      t  ON t.id = tr.objectid       -- 3rd
WHERE  t.status <> 'deleted'
AND    t.type = 'ticket'
AND    t.effectiveid = t.id
AND    tr.objecttype = 'RT::Ticket'
AND    a.contentindex @@ plainto_tsquery('frobnicate');

ROLLBACK; -- or COMMIT;

The order of WHERE conditions is always irrelevant. Only the order of joins is relevant here.

Or use a CTE like @jjanes explains in "Option 2". for a similar effect.

Indexes

B-tree indexes

Take all conditions on tickets that are used identically with most queries and create a partial index on tickets:

CREATE INDEX tickets_partial_idx
ON tickets(id)
WHERE  status <> 'deleted'
AND    type = 'ticket'
AND    effectiveid = id;

If one of the conditions is variable, drop it from the WHERE condition and prepend the column as index column instead.

Another one on transactions:

CREATE INDEX transactions_partial_idx
ON transactions(objecttype, objectid, id)

The third column is just to enable index-only scans.

Also, since you have this composite index with two integer columns on attachments:

"attachments3" btree (parent, transactionid)

This additional index is a complete waste, delete it:

"attachments1" btree (parent)

Details:

Is a composite index also good for queries on the first field?

GIN index

Add transactionid to your GIN index to make it a lot more effective. This may be another silver bullet, because it potentially allows index-only scans, eliminating visits to the big table completely.
You need additional operator classes provided by the additional module btree_gin. Detailed instructions:

Inner join using an array column

"contentindex_idx" gin (transactionid, contentindex)

4 bytes from an integer column don't make the index much bigger. Also, fortunately for you, GIN indexes are different from B-tree indexes in a crucial aspect. Per documentation:

A multicolumn GIN index can be used with query conditions that involve any subset of the index's columns. Unlike B-tree or GiST, index search effectiveness is the same regardless of which index column(s) the query conditions use.

Bold emphasis mine. So you just need the one (big and somewhat costly) GIN index.

Table definition

Move the integer not null columns to the front. This has a couple of minor positive effects on storage and performance. Saves 4 - 8 bytes per row in this case.

                      Table "public.attachments"
         Column      |            Type             |         Modifiers
    -----------------+-----------------------------+------------------------------
     id              | integer                     | not null default nextval('...
     transactionid   | integer                     | not null
     parent          | integer                     | not null default 0
     creator         | integer                     | not null default 0  -- !
     created         | timestamp                   |                     -- !
     messageid       | character varying(160)      |
     subject         | character varying(255)      |
     filename        | character varying(255)      |
     contenttype     | character varying(80)       |
     contentencoding | character varying(80)       |
     content         | text                        |
     headers         | text                        |
     contentindex    | tsvector                    |

Best Answer

Related Solutions

Postgresql – Improve performance on concurrent UPDATEs for a timestamp column in Postgres

PostgreSQL – Improve Full-Text Search Performance with Accurate Row Estimates