PostgreSQL Alter Table and Long Transactions

A number of articles say that some types of ALTER TABLE statements are perfectly safe, even for live, high-volume applications. Typically this list is composed of:

  • ALTER TABLE ... ADD COLUMN without a default and allowing the column to be NULL.
  • ALTER TABLE ... DROP COLUMN
  • CREATE INDEX ... CONCURRENTLY
  • DROP CONSTRAINT
  • ALTER TABLE ... ADD DEFAULT

These operations aren’t dreamed up. Successful changes to running systems can happen, and the Postgres documentation is clear on what strategies avoid rewriting the table on disk.

Default values for new columns will force a table rewrite:

Adding a column with a default requires updating each row of the table (to store the new column value). However, if no default is specified, PostgreSQL is able to avoid the physical update. So if you intend to fill the column with mostly non-default values, it’s best to add the column with no default, insert the correct values using UPDATE, and then add any desired default as described below.

Adding a default value on an existing column will only affect new rows:

These forms set or remove the default value for a column. Default values only apply in subsequent INSERT or UPDATE commands; they do not cause rows already in the table to change.

Concurrent index creation can be used to avoid blocking table updates:

Adding a constraint using an existing index can be helpful in situations where a new constraint needs to be added without blocking table updates for a long time. To do that, create the index using CREATE INDEX CONCURRENTLY, and then install it as an official constraint using this syntax. See the example below.

This information is all clearly spelled out in the PostgreSQL documentation for Alter Table and Modifying Tables.

What is also spelled out clearly in the documentation, but most discussion about these table modifications leaves out, is that most ALTER TABLE statements require an ACCESS EXCLUSIVE lock on the table being modified.

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.

The point about these operations being “safe” is that while an ACCESS EXCLUSIVE lock is held, that lock is only needed for an incredibly short time. Almost zero transaction blocking should occur. That is, if you have normal short transactions.

I’m currently working on a project that runs transactions into the minutes regularly. We’ve also seen transactions run into the hours as a non-rare occurrence. These are caused by a number of different subsystems—like bulk data imports and merging offline updates—that as written require the guarantees a transaction provides.

Here are two ways to manually reproduce this behavior. First is to use a transaction on one connection while attempting a table modification on another connection. Setting up a very basic table to play with:

josh=# \timing
Timing is on.
josh=# create table lock_me (name varchar(100), age integer);
CREATE TABLE
Time: 4.115 ms

Open a transaction and INSERT a record.

josh=# begin;
BEGIN
Time: 2.224 ms
josh=# insert into lock_me(name, age) values ('Jane Smith', 29);
INSERT 0 1
Time: 3.274 ms

In another connection a table modification will block on obtaining the ACCESS EXCLUSIVE lock it needs to change the table metadata.

josh=# alter table lock_me add column occupation varchar(100) null;

Examining pg_locks we can see the INSERT generated a ROW EXCLUSIVE lock. This type lock conflicts with ACCESS EXCLUSIVE (among other things) required by the ALTER TABLE.

josh=# SELECT pid, relation::regclass, locktype, mode, granted 
FROM pg_locks WHERE locktype = 'relation';
  pid  | relation |   locktype    |        mode         | granted
-------+----------+---------------+---------------------+---------
 29823 | pg_locks | relation      | AccessShareLock     | t
 29480 | lock_me  | relation      | RowExclusiveLock    | t
 29973 | lock_me  | relation      | AccessExclusiveLock | f
(3 rows)

Time: 0.578 ms

Issuing a COMMIT into the first connection the ALTER TABLE will obtain the lock and succeed.

josh=# alter table lock_me add column occupation varchar(100) null;  ALTER TABLE
Time: 12747.821 ms

The table can also be locked manually, and this would be a preferred method for testing table modifications before pushing them to a live application.

josh=# begin;
BEGIN
Time: 0.114 ms
josh=# lock table lock_me in access share mode;
LOCK TABLE
Time: 2.004 ms

Queue up an ALTER TABLE with another metadata-only operation.

josh=# alter table lock_me drop column occupation;

Examining pg_locks again we can see the conflict.

josh=# SELECT pid, relation::regclass, locktype, mode, granted 
FROM pg_locks WHERE locktype = 'relation';
  pid  | relation |  locktype  |        mode         | granted
-------+----------+------------+---------------------+---------
 29823 | pg_locks | relation   | AccessShareLock     | t
 29480 | lock_me  | relation   | AccessShareLock     | t
 29973 | lock_me  | relation   | AccessExclusiveLock | f
(6 rows)

Time: 0.509 ms

A full list of table-level lock compatibility is in the PostgreSQL documentation.

written September 9th, 2017

September 2017

Can’t find what you’re looking for? Try hitting the home page or viewing all archives.