Schedule - PGConf.EU 2013

Concurrency in Postgres

Date: 2013-10-31
Time: 11:50–12:40
Room: Pembroke
Level: Intermediate

PostgreSQL implements some fairly involved rules to ensure correct behavior in the face of concurrent activity. Multi-version concurrency control prevents readers from blocking writers and writers from blocking readers, but even without the pessimistic locking of some systems, locking issues abound in many Postgres client applications. These problems are considerably ameliorated by improvements made in Postgres 9.3, where locks acquired when enforcing foreign keys are more granular, making deadlocks far less frequent and lock waits shorter, while still ensuring correct behavior.

Postgres hasn’t always been the most user-friendly when it comes to exposing this information, and allowing users to act upon it to fix performance problems. Getting the most pertinent information about how to fix problems is tricky, and is the subject of ongoing work on the core system.

This talk gives an overview of:

• How Postgres implements the various isolation levels described by the SQL standard, and the visibility rules and exact set of guarantees made by each level.
• The various locks that Postgres acquires on tables, indexes, rows and transactions, and even query predicates, and how all of this fits together with the isolation modes.
• Other ways in which the isolation modes handle conflict resolution.
• How Postgres locks can be represented as a tree, with cascading lock dependencies, and how this information can be interpreted to find backends that block other backends without being blocked themselves (the “real offenders”) using a recursive SQL query.
• Common scenarios in which race condition bugs can be inadvertently added to applications, and how you can avoid them.
• How to write queries to avoid locking issues such as deadlocks.
• New improvements to foreign key locking added to Postgres 9.3, and what they mean for your application. How to implement UPSERT (i.e. atomic insert-or-update) correctly, in the absence of core functionality to take care of this for you.

Speaker

Peter Geoghegan

Platinum Sponsors

Gold Sponsors

Silver Sponsors