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 COLUMNwithout a default and allowing the column to be
ALTER TABLE ... DROP COLUMN
CREATE INDEX ... CONCURRENTLY
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
UPDATEcommands; 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.
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 TABLEchanges 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 EXCLUSIVElock 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;
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
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
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;
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.