Extremely long postgres query

Question

I've run a migration query within a transaction, it went like this:

alter table big_and_loaded_table
    add column col1 bool;

update big_and_loaded_table set col1 = false;

It seems quite innocent, but it took about two minutes to execute. Moreover, it "locked" big_and_loaded_table: any query in my application that involved it -- both read and write -- took oh so long to execute, the same minute or so. I don't use transactions in my application at all, as well as any kind of explicit locks.

So I have two questions:

1. Why did that migration query take so long to execute? Was it because of a transaction? Or was it because of the query itself?
2. Why did it lock application queries that involved table big_and_loaded_table?
3. How should I run such kinds of migrations in the future? Probably this point won't make any sense when I'll figure out the first two though.

Postgres version is 11.6.

Answer 1

Postgres 11 added a feature where a table rewrite is not necessary if you add a column with a default value.

If you had used

alter table big_and_loaded_table
    add column col1 boolean default false;

the statement would have executed nearly instantaneous with the same effect.

But an ALTER TABLE statement will acquire an exclusive lock to prevent access to the table while it's being changed, there is no way around it.