Postgres row locks in practice

For years I used SELECT ... FOR UPDATE without really thinking about it. This week I actually read the lock conflict table and it explained a lot.

Default locking practices

What I now do by default:

• FOR UPDATE SKIP LOCKED for queue-like tables
• Short transactions, no user input inside them
• lock_timeout so a stuck query fails fast instead of piling up
• Monitor pg_locks when something feels slow

The SKIP LOCKED change alone let us drop a whole queue service for a table with tens of thousands of rows. Postgres was already the queue; we just didn't trust it.

I'm reading about SQLite internals too, because the locking model is so different. It's a good way to understand what Postgres gives you.