How to Perform ALTER TABLE on Huge Table

Avoiding system crashes: How to safely alter large database tables

My girl

I’ve often heard engineers say if the RDBMS table is so large, let’s say millions or even tens of millions of rows, that just adding a new column with a default value will cause the system to crash.

Actually, it’s not that bad.

Well, it’s not that bad if you do it correctly.

Let’s start by understanding why we all have a given idea that this is a dangerous thing to do.

In Postgres, for example, when we want to add a new column with a default value, we execute the following command.

ALTER TABLE table_name
ADD COLUMN column_name data_type DEFAULT default_value;

When Postgres recognizes this is an ALTER TABLE command and affects the entire table by default, it requests an AccessExclusiveLock. The lock is a table-level lock and is so exclusive that all other locks cannot coexist, including the lowest-level AccessShareLock, and all SELECTs require at least AccessShareLock, which results in the entire table being stuck.

When this ALTER TABLE is executed on a large table, it means all rows have to be modified and updated to the default values, which also means that it will take a long time and…