Say you have a large table with tens of millions of rows.
You want to
UPDATE large_table SET col=value WHERE col=other_value... but
col is not indexed and an
EXPLAIN shows that this query will perform a seq scan over the whole table.
What is the lock behaviour here? According to most accounts Postgres only locks the affected rows of an UPDATE query and does not have lock escalation. So does it search for the rows to update first, then only lock the found rows? It seems like there would potentially be problems of other queries updating rows concurrently in that case though. Does it lock each row "as it finds them" i.e. locking rows progressively as it goes through the seq scan?
So I think the best case here is it locks rows as it finds them, and the affected rows (only) will be locked for up to as long as the UPDATE query takes to complete.
But I am worried that this query could instead end up blocking all writes to the table until it completes.
I have read this: https://habr.com/en/company/postgrespro/blog/503008/ and I think the worst case will not happen, but here https://blog.heroku.com/curious-case-table-locking-update-query is a possibly inaccurate representation of similar info that gives me some doubts.
The application only uses
SELECT FOR UPDATE and
UPDATE queries (i.e. no other explicit locks taken apart from those). The table has foreign keys to other tables, and other tables have foreign key to this table.
We're on Postgres 11.