PostgreSQL schema-change gotchas
This article describes a few cases where a migration might look good, but it can cause downtime anyway. I’ve included a “what to do” section for each of these cases.
My name is Viktoriia Romanchenko and I’m a Software Engineer at Preply.com. I wrote this article because I made one of the mistakes described below, and of course production was down. Let’s get started!
Oopsies covered
- Add column with default value
- Add column to heavily used table
- Change column type
- Create index
- Add check constraint
- Add not-null constraint
Add column with default value
ALTER TABLE table ADD COLUMN "new_column" BOOLEAN DEFAULT FALSE;
ALTER TABLE .. ADD COLUMN .. DEFAULT
can cause downtime for PostgreSQL versions lower than 11.
If your PostgreSQL version is 11 or higher, you can add columns with a default safely, but not with a dynamic one.
From PostgreSQL docs:
From PostgreSQL 11, adding a column with a constant default value no longer means that each row of the table needs to be updated when the
ALTER TABLE
statement is executed. Instead, the default value will be returned the next time the row is accessed, and applied when the table is rewritten, making theALTER TABLE
very fast even on large tables.
If your version is lower than 11 or the default value is volatile (e.g., clock_timestamp()
), PostgreSQL is actually rewriting the whole table, adding the column to each row, and filling it with default value. It might be okay for small tables, but bear in mind that the whole table will be locked until the operation completes. And it doesn’t matter if this new column is nullable.
What to do instead
- Add the new column (without the default value).
- Set the default value on the column. This operation locks the table too, but it’s bad only if the statement takes a long time.
SET DEFAULT
operation on an existing column is fast, because it doesn’t scan the table. - Backfill all existing rows separately.
ALTER TABLE table ADD COLUMN "new_column" BOOLEAN;ALTER TABLE table ALTER COLUMN new_column SET DEFAULT FALSE;--- backfill the existing rows ---
Add column to heavily used table
ALTER TABLE table ADD COLUMN "new_column" INT;
Believe it or not, a simple ALTER TABLE... ADD COLUMN
can cause downtime. (Even without a DEFAULT
or NOT NULL
constraint!)
This happens because:
ALTER TABLE
sets theACCESS EXCLUSIVE LOCK
before waiting for any other currently executing reads or writes to finish.ACCESS EXCLUSIVE LOCK
locks everything until the lock is gone. So all other reads and writes are blocked.- PostgreSQL waits for all previous queries on that table to finish execution; all new queries on that table are blocked, which causes downtime.
What to do instead
It seems that for really busy systems, the only safe way to add a column is to retry in a loop with an explicitly short lock_timeout
value. Take a look at the explanation and the solution by depesz.
Change column type
ALTER TABLE table ALTER COLUMN name TYPE INT;
Changing a column’s type can be a reason of downtime because in the general case it requires holding an ACCESS EXCLUSIVE LOCK
on a table while the entire table is rewritten with the new type. That blocks all usage of the table.
What to do #1
The first solution is suggested by an article on the same topic by Braintree:
- Add a new column
new_<column>
. - Dual write to both columns (e.g., with a
BEFORE INSERT/UPDATE
trigger). - Backfill the new column with a copy of the old column’s values.
- Rename
<column>
toold_<column>
andnew_<column>
inside a single transaction and explicitLOCK <table>
statement. - Drop the old column.
What to do #2
The second solution is less sweaty, that’s why I prefer it more. Also, there is a good chance that the flow with renaming columns will cause downtime during rollback. So… how about not changing column types at all?
- Create a new column with desired type.
- Deprecate the old column.
- Start using the new column, but keep the old one for some time to ensure you are backward compatible.
Create index
CREATE INDEX idx_name ON table_name (column_name);
This statment can cause downtime because CREATE INDEX
blocks writes (but not reads) on the table until it’s done.
When CREATE INDEX CONCURRENTLY
option is used, it takes significantly longer to complete. But since it allows normal operations (both reads and writes) to continue while the index is built, this method is used for adding new indexes in a production environment.
What to do
CREATE INDEX CONCURRENTLY idx_name ON table_name (column_name);
From PostgreSQL docs:
A regular
CREATE INDEX
command can be performed within a transaction block, butCREATE INDEX CONCURRENTLY
cannot.
Add check constraint
ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (something);
Adding check constraints can cause downtime because it requires an ACCESS EXCLUSIVE LOCK
that doesn’t allow read and write operations on a table. However, it can be broken down into two alter table statements.
What to do
Divide et impera.
- Add
CHECK CONSTRAINT
withNOT VALID
option. TheNOT VALID
tells PostgreSQL that it doesn’t need to scan the entire table to verify that all rows satisfy the condition. Future inserts or updates, however, will be enforced. This operation still grabs anACCESS EXCLUSIVE LOCK
but since it doesn’t scan the table, it will be quick. - Validate the constraint. Validation requires a
SHARE UPDATE EXCLUSIVE LOCK
on the altered table so can run concurrently with read and write queries.
ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (something) NOT VALID;ALTER TABLE table_name VALIDATE CONSTRAINT constraint_name;
Add not-null constraint
ALTER TABLE table ALTER COLUMN column SET NOT NULL;
Adding a not-null constraint to an existing column can cause downtime because it requires an ACCESS EXCLUSIVE LOCK
on the table when PostgreSQL fully scans the table to check that the constraint is valid on all the rows.
What to do instead
From PostgreSQL docs:
A not-null constraint is always written as a column constraint. A not-null constraint is functionally equivalent to creating a check constraint
CHECK (column_name IS NOT NULL)
.
So NOT NULL
constraint can be replaced with CHECK CONSTRAINT
. And we do everything like in the case above:
- Add
CHECK CONSTRAINT
withNOT VALID
option. - Manually verify that all rows have non-null values in the column.
- Validate the constraint.
ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (column IS NOT NULL) NOT VALID;---- verify that all rows have non-null values ---ALTER TABLE table_name VALIDATE CONSTRAINT constraint_name;
You can find more about this case in an article from Doctolib.