Databases · Flashcard
Do plain SELECT readers block writers in PostgreSQL?
- ANo — SELECTs read a snapshot and take no locks that conflict with data modification
- BYes — every SELECT takes shared row locks that writers must wait for and acquire
- COnly inside explicit transactions — autocommitted reads skip the locking entirely
- DOnly on indexed tables — the index maintenance serializes readers with writers
Why this is the answer
Thanks to MVCC, plain reads never block UPDATE or DELETE (and writers don't block readers). SELECT takes no row locks (SELECT FOR UPDATE is the explicit exception), transaction wrapping doesn't change that, and indexes don't serialize readers with writers.
Official docs