How can I add a field to a large PostgreSQL table without locking?

Question:

There is a table like this:

create table email_stats (
    id bigserial primary key,
    mailing_list_id int not null,
    sended_date timestamp not null,
    emails_send bigint not null default 0,
    emails_clicks bigint not null default 0
);

Now you need to add a new field to it. So the task is simple,

alter table email_stats 
    add column emails_paid_clicks bigint not null default 0;

Here's just a problem: the table is already several tens of gigabytes in size and this alter table locks the entire record in the table for a long time.

How can you add a field without system downtime?

PS: strange, but did not find such a common question here

Answer:

If you are a happy user of PostgreSQL 11 or newer (but you don’t need to put the beta version in working projects), then all the tricky tricks are now hidden inside and it is enough to directly make an alter table :

set statement_timeout to '1s';
alter table email_stats 
    add column emails_paid_clicks bigint not null default 0;

The patch adopted in postgresql 11 makes it possible to no longer copy the entire table again when adding a new field with a default value. And the problem with long-term locking of a table can only be a situation when alter table forced to wait for an exclusive lock due to the fact that this lock is held by some other transactions. Therefore, it makes sense to set a small statement_timeout and repeat attempts to execute the alter table .


If you have postgresql versions lower than 11, then everything is not so simple. In the specified request

alter table email_stats 
    add column emails_paid_clicks bigint not null default 0;

default 0 leads to longterm table locking. PostgreSQL takes a lock on the table and begins to iterate over all the records in the table, assign it at the data level, and only then release the lock. But if you insert NULL default, then PostgreSQL will update only the table metadata in the service catalog, which is very fast, but this is not what is required.

Therefore, it is possible to make the desired change, but it will not look like this at all, but in many actions.

Beginning to gently make a change

First, add the new field as null by default – it's quick, just change the table metadata. Then we set the desired default value – this value will already be applied for new inserted rows.

begin;
set local statement_timeout = '1s';
alter table email_stats 
    add column emails_paid_clicks bigint;
alter table email_stats 
    alter column emails_paid_clicks set default 0;
commit;

Why I changed statement_timeout in a transaction deserves a separate explanation. This setting limits the maximum query execution time. This alter table still requires a lock on the table, albeit for a short time, and there are hidden rakes here: what if the alter table cannot take the lock due to some other transaction in progress? For example, a simple insert in another transaction will not allow you to take a lock to change the structure. But at the same time, the running alter table already block all subsequent writing requests to this table. A short statement_timeout will quickly kill the alter table and the service will continue working. And an attempt to add a field can be repeated painlessly a little later until it eventually succeeds.

There is also an interesting rake that an explicit indication of default null in an alter table in some cases can be considered a NULL value of an not equivalent data type and the database will start rewriting the table again. In order not to catch this rare rake, it is better not to specify default null explicitly, but to leave the one that is implied by add column . By default, NULL assumed there.

Added the field, now default in the data

Time-consuming process, required by inquiries

update email_stats 
    set emails_paid_clicks = default 
    where emails_paid_clicks is null 
        and /* следующий кусочек данных */

put down the desired value in the available data in the table. It is necessary to update the data in pieces (for which purpose I left an open condition in the request), pauses between updates, monitor the replication lag (if any) and autovacuum processes. There are several approaches to the update itself, the simpler one will be to update by the primary or any unique key. We take any programming language or scripting language to our liking and do something like this:

$current_id = (select min(id) from email_stats)
$maxid = (select max(id) from email_stats)

while ($current_id < $maxid) {
    $batch_to = $current_id + 10000 // максимальный размер пачки для одного обновления
    update email_stats 
        set emails_paid_clicks = default 
        where emails_paid_clicks is null 
            and id >= $current_id and id < $batch_to

    $current_id = $batch_to
    sleep(5) -- задержка между обновлениями чтобы меньше мешать сервису
}

During the execution of such a script, you can occasionally vacuum email_stats so that the plate does not grow much in size. Especially if autovacuum not configured aggressively enough and does not have time to clean up after the script.

The size of one update and the length of the pause between updates must be selected according to the load profile of a particular service. Small updates and long pauses will not bother anyone, but it will just take a very long time for the script to execute.

The sample query is not the most efficient, its execution time will float a lot from missing id in the data and because the data will most likely be in different memory pages, but it is simple and you can easily adjust the maximum size for one update.

From the pitfalls at this stage: it is much easier to catch a deadlock by fighting with an application for updating rows if the application wants to update several rows from the same batch, but in a different order. You can set the connection, in which the script of this update is deadlock_timeout = 100ms , with the deadlock_timeout deadlock_timeout = 100ms , then when a deadlock deadlock_timeout = 100ms , as a rule, our script will be killed, and not a useful application transaction.

We put not null

Now we should not have null values ​​in the added field in the table, we can put not null.

alter table email_stats alter column emails_paid_clicks set not null;

This request will unfortunately put a write lock. But the execution time is much less than updating the entire table overwriting the default value.

With minimal write locking, you will have to abandon the native not null property, but you can add a check constraint with a similar property. First, we add a constraint indicating not valid (similar to the statement timeout for a transaction)

begin;
set local statement_timeout = '1s';
alter table email_stats 
     add constraint emails_paid_clicks_not_null 
     check (emails_paid_clicks is not null) not valid;
commit;

Then in another transaction already without statement_timeout

alter table email_stats validate constraint emails_paid_clicks_not_null;

Checking the constraint will not block the recording.

For postgresql 11 and newer, this entire instruction has become much shorter, but for beauty I'll add a little more, it's not in vain that I tried, I wrote the patch . Starting from postgresql 12, alter table set not null can skip data validation in the table if the existing constraints are sufficient to believe that there is no NULL in this field. Yes, I'm just talking about the check constraint that I just described how to create without long blocking. After the constraint has passed the validate constraint you can call set not null and remove the unnecessary check . This will still require a table lock, but now it's a short one.

begin;
set local statement_timeout = '1s';
alter table email_stats alter column emails_paid_clicks set not null;
alter table email_stats 
     drop constraint emails_paid_clicks_not_null;
commit;

Ready

That's it, the field has been added


By the way, it makes sense to think about whether to use NULL by default. Such a field is not only much easier to add, but it is also much more compact in storage. A NULL value is one bit in the row header bitmask, a bigint value of 0 already occupies 8 bytes.

Scroll to Top