Re: Add column with default value in big table - splitting of updates can 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(dot)klaver(at)aklaver(dot)com