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:
- Why did that migration query take so long to execute? Was it because of a transaction? Or was it because of the query itself?
- Why did it lock application queries that involved table
- 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.