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 beNULL
.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
orUPDATE
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. AnACCESS 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.