Currently, any ALTER TABLE you run requires an ACCESS EXCLUSIVE lock on the altered table in order to commit. This is a lock which blocks not only writes, but reads as well. You don't see read locks much in PostgreSQL, so one tends to forget they exist until you do something like running an ALTER TABLE in a database which processes 15,000 queries per second. Then, this sort of thing can happen:
- ALTER TABLE waits behind a long-running reporting or write query.
- SELECT queries start to pile-up behind ALTER TABLE statement
- server starts performing badly because of the pile-up, and is effectively locked up for a few minutes.
- the pile-up takes several more minutes to clear, even after the ALTER has gone through.
This means that, for deployments which involve ALTER TABLE, you'll need to plan on a brief downtime or at least watching the database closely if you run an extremely-high-transaction-rate database with many multi-statement transactions. In many cases I get around this by polling for a manual lock on the table, and proceeding with the ALTER once I get one. This doesn't work well with existing schema management tools, though.
I'm not just talking about ALTER TABLE ADD/DROP COLUMN here; this happens for any ALTER TABLE statement, including ALTER TABLE SET STATISTICS. Unfortunately, while PostgreSQL DDL statements are transaction-safe, our system tables themselves are not currently MVCC, which means that the only way we can modify anything in the system tables is to take an exclusive lock on it.
Fortunately, thanks to Robert Haas, that just changed in 9.4 development. In the future, we should be able to do the following operations without taking more than a share lock on the table:
- SET STATISTICS
- DROP TRIGGER/CONSTRAINT
- DROP a foreign key from a referencing table
- SET DEFAULT
- SET NOT NULL
- ADD COLUMN
There is one special case of ALTER TABLE you need to know about in addition to this, though. That's ALTER TABLE ALTER COLUMN ... TYPE <new_data_type>. More on this in a succeeding post.
DDL = Data Definition Language, i.e. CREATE/ALTER/DROP statements.
MVCC = Multi-Version Concurrency Control, the way we ensure that users can see different versions of the database depending on their current transaction context.