Re: Add column with default value in big table - splitting of updatescan help?

On 1/30/20 7:51 AM, Durumdara wrote:
> 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.

What version of Postgres are you using?

I ask because:

https://www.postgresql.org/docs/11/release-11.html

"Many other useful performance improvements, including the ability to 
avoid a table rewrite for ALTER TABLE ... ADD COLUMN with a non-null 
column default"

> 
> 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


-- 
Adrian Klaver
adrian.klaver@aklaver.com