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