How to Perform ALTER TABLE on Huge Table

Posted on In Views:

How to Perform ALTER TABLE on Huge Table

Avoiding system crashes: How to safely alter large database tables

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.

1
2
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 consume a lot of system resources, and then, of course, the system will be crashed.

But we can’t avoid having to make changes to the table’s columns, especially when the requirements are iterative, so what should we do?

Easy for you to say

Let’s start with an extreme ideal scenario, although I realize that most of them are not.

When we expect the table to get this big, we’ll have to partition it.

Continuing with the Postgres example, suppose our order table grows over time and one day overwhelms Postgres. At some point, we should consider partitioning.

Assume we have the following order table.

1
2
3
4
5
6
CREATE TABLE orders (  
    order_id SERIAL PRIMARY KEY,  
    tenant_id INTEGER,  
    order_date DATE,  
    -- other columns  
) PARTITION BY RANGE (tenant_id);

We can consider partitioning by tenant_id so that the data is spread out in many smaller tables on a tenant basis.

1
2
3
4
5
CREATE TABLE orders_part1 PARTITION OF orders  
FOR VALUES FROM (1) TO (1000);  
  
CREATE TABLE orders_part2 PARTITION OF orders  
FOR VALUES FROM (1001) TO (2000);

In this example, each child table would only have order information for 1000 tenants, which would effectively prevent the table from growing to a huge size.

The reality is

But it’s a bit unrealistic to think about partitioning right from the beginning, because the management of a partitioned table is also a bit complicated.

More often cases is the application we developed has grown to be huge without realizing it, and then there is an urgent requirement to iterate, we have to add the columns in a hurry. At this point, it’s too late to create partitions, move data, and rewrite queries.

What should we do?

Well, we need to minimize the negative impact of ALTER TABLE.

First, let’s ignore the default value and set the column to nullable.

1
2
ALTER TABLE table_name  
ADD COLUMN column_name data_type;

Keep in mind, do not insert in the middle of existing columns.

This operation is pretty lightweight and can be done in a very short time even for huge tables.

Next, we’ll start filling in the default values for this new column in batches with the primary key.

1
2
3
UPDATE table_name  
SET column_name = default_value  
WHERE primary_key_column BETWEEN start_value AND end_value;

Remember, it has to be a batch, i.e. just a few hundred rows at a time, and we’ll do it in batches until all the new columns in the table have values.

The reason for the batch is because the lookup of the primary key is extremely efficient, and each update only locks a portion of rows, which on the one hand improves efficiency, and on the other hand reduces the scope of the affected data.

Finally, we change the columns back to what we wanted at the beginning.

1
2
ALTER TABLE table_name  
ALTER COLUMN column_name data_type DEFAULT default_value;

This operation will take a little longer than the first step, but it is acceptable.

Splitting a command with a huge impact into three steps, each step with a limited range of impacts, will allow the avaiability of the system to be increased dramatically.

Conclusion

In this article we have not only explained the risks of doing an ALTER TABLE on a large table but also suggested specific ways to deal with them.

Ideally, if you’re using an RDBMS, you’ll want to consider partitioning, or even sharding, before your data grows out of control. Most RDBMS don’t have built-in sharding mechanisms, so you’ll need to use an application to do the sharding.

But the reality is that we often have to operate on existing large tables, so this article proposes a feasible solution. To make the most reasonable consideration, be cautious but don’t be overtaken by hesitation.

Originally published on Medium