Add column with default value in big table - splitting of updates can help?

From: Durumdara <durumdara(at)gmail(dot)com>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Add column with default value in big table - splitting of updates can help?
Date: 2020-01-30 15:51:02
Message-ID: CAEcMXhk6sutuZR5BAXriBO_2Yj7ObOVbeT8Hw_J1aZwD83Ufrg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Dear Members!

I've read this article, but I need your experience in this theme.

https://leopard.in.ua/2016/09/20/safe-and-unsafe-operations-postgresql#.XjL3fcqYXDc

alter table tk
add colum field1 default 'MUCH';

The table tk have 200 million rows. The autovacuum is no problem, only the
long update.

But as I read the alter makes table lock, so this update locks the table
for long time.

The article said we need to do this:

1. add column without default - fast.
2. set default on column.
3. update it where is null.

What we can save with this method?

As I suppose the table lock substituted with long update (row locks on
whole table).

The article suggested to replace long update to shorter sequences
(10000-100000 records by cycle).

We used to execute these SQL-s (alter, default, update) in one transaction.
So I can't make commit there.

What is the difference between "full update" and "updates by 10000 records"
when I'm in a single transaction?

Is it possible that this pseudo code makes less locks WITHOUT INNER COMMIT?

Pseudo:
----------------------
begin
while True loop
update tk set field1 = ' MUCH' when field1 is NULL and id in
(select id from tk where field1 is NULL limit 100000);
if not FOUND then
break;
end while;
end
----------------------

Is there any advance when I split updates? I'm in one transaction.

TR. START
----
1. alter add col
2. set default
3. updates
---
TR. COMMIT

Or it isn't help me?

Because the whole transaction locks the other users also, just like "alter
add colum wit hdefault statement"?

Thank you for your and help!

Best regards
dd

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2020-01-30 15:56:19 Re: Add column with default value in big table - splitting of updates can help?
Previous Message Achilleas Mantzios 2020-01-30 11:46:31 Re: Exclude constraint on ranges : commutative containment : allow only complete containment