Re: pg_wal fills up on big update query - Mailing list pgsql-general

Hi all,

 

I have a migration where I

·         Add a new nullable column to a table

·         update almost every row in this big table (8 million rows) from another table where I set this new column

 

I have also a replication setup running.

The database has a size of around 20GB.

While the migration is running, it more than doubles is size and fills up all space.

Then the migration fails and is rolled back.

 

What is the best way of keeping this from happening?

My current idea is to lock both tables completely from access (the queried and the updated one) so that postgresql does not have to ensure isolation for concurrent queries by keeping a copy of each row.

Is my thinking here correct?

 

Thanks in advance and Best Regards,