Download slides

Postgres uses locks at every level in order to support concurrent operations. These include spinlocks, lightweight locks, and regular (heavyweight) locks. Each of these lock types has different properties and applications, but they all have a direct impact on performance and scalability. Understanding how these locks are used and how they interact can help to understand how your server will perform with an increase in the number of concurrent clients.

This talk will cover the various types of locks in Postgres, discuss their scope and behaviour, and look at how they can be used and monitored at the SQL level. We will talk about the different levels of locks that internal and application code can acquire, and how they can conflict, and when and how this can lead to deadlocks. We will also briefly discuss some special considerations around locking when streaming replication is in use.

(Disclaimer: this talk may include sketchy diagrams with garish colours.)