addressalign-toparrow-leftarrow-rightbackbellblockcalendarcameraccwchatcheckchevron-downchevron-leftchevron-rightchevron-small-downchevron-small-leftchevron-small-rightchevron-small-upchevron-upcircle-with-checkcircle-with-crosscircle-with-pluscrosseditemptyheartexportfacebookfolderfullheartglobegmailgoogleimageimagesinstagramlinklocation-pinmagnifying-glassmailminusmoremuplabelShape 3 + Rectangle 1outlookpersonplusprice-ribbonImported LayersImported LayersImported Layersshieldstartickettrashtriangle-downtriangle-uptwitteruseryahoo

[PostgreSQL, Concurrent]: Concurrency in PostgreSQL


• 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.

Getting There

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!

Join or login to comment.

  • A former member
    A former member

    1 · September 12, 2013

    • Kamil K.

      Getting a not found on the link.

      September 12, 2013

    • A former member
      A former member

      Sorry. I forgot to set the "published" flag, which is apparently necessary in addition to specifying that it's a public presentation.

      September 12, 2013

  • theo a.

    is the presentation uploaded somewhere?

    September 10, 2013

  • Chris M.

    I first used postgres on the cobalt raq. Now a bit dated

    August 8, 2013

Our Sponsors

  • Hootsuite

    Venue, Speaker Wrangling & General Support

  • Unbounce

    Venue and speaker wrangling

People in this
Meetup are also in:

Sign up

Meetup members, Log in

By clicking "Sign up" or "Sign up using Facebook", you confirm that you accept our Terms of Service & Privacy Policy