• 6:00PM Doors are open, feel free to mingle
• 6:30 Presentation start
• 8:00 Off to a nearby watering hole (Cascade Room?) for a pint, food, and/or breakout discussions
Concurrency in PostgreSQL
-- Peter Geoghegan
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.
About Peter Geoghegan
Peter Geoghegan is a database architect, and PostgreSQL Major contributor. Based in Vancouver, he works at Heroku on Postgres database-as-a-service.
By transit there a number of high frequency buses (check Google Maps or the Translink site for your particular case) that will get you there. For the drivers, there is a fair bit of street parking (free and pay) in the area, especially after 6.
How to Contact Us / Re Comments
Please note any comments you add to this event (below) will be e-mailed to all members of the group. We're trying to avoid spamming the list, so please do not use comments for jokes, job postings, requests for help programming something or anything else off topic. If you have questions or need to contact us, use the 'contact us' link on the left. Thanks!