Afaik, it only locks the row(s) in question. Do you have a setup where the same row is being updated by multiple clients all the time?
SELECT FOR UPDATE means two queries can’t _read_ the same row at the same time, right?
To my understanding, yes. If you have broad select queries across all the rows, that would slow things down quite a bit.
You can use SKIP LOCKED normally. That’s how popular queue implementations usually work. I believe pg even calls this out in their docs
SQL Server will escalate locks. Because keeping a million rows locked is expensive.
This can easily happen if you have multiple services that can write/update in the same table/rows and rely on database coordination instead of using an external queue like Kafka.
Postgres (presumably other databases) can propagate these locks to table locks [1] and cause contention for the whole infra.