Adding a NOT NULL Column to a Large PostgreSQL Table: Constant Default, Backfill or NOT VALID

Adding a NOT NULL column to a large PostgreSQL table involves careful consideration of the default value and the migration approach. On PostgreSQL 11 and later, adding a column with a constant non-null default, such as NOW(), can be done quickly, often within milliseconds, as it only changes the table's metadata. However, using a volatile default like gen_random_uuid() requires rewriting the entire table, which can take around 10-12 seconds for a 5 million row table.

When the column value must be computed per row, a better approach is to add the column as nullable, backfill it in batches, and then prove it is NOT NULL. This method ensures that the full table scan does not block reads, as each batch of updates holds a short lock. Setting lock_timeout for all these statements is recommended, as they can be affected by open transactions.

The choice of migration method depends on the nature of the new column's values for existing rows. If the values are constant, the ALTER TABLE command with the NOT NULL DEFAULT clause is sufficient. However, if each row requires a unique value, splitting the process into steps is advised. First, add the column as nullable, backfill it in manageable batches, and then make the column NOT NULL.

This approach avoids the full table scan that occurs when trying to make a column NOT NULL without first ensuring there are no NULL values.

For backfilling, updating the column with a value that depends on another table's data can be done in batches to minimize locks. Finally, after backfilling, set the column to NOT NULL. On PostgreSQL versions 12 and later, adding a NOT VALID constraint check can skip the full table scan, making the process more efficient. VALIDATE CONSTRAINT, while necessary for confirming no NULL values exist, incurs a significant scan time. Once validated, the check constraint can be dropped to improve performance.