Reducing Contention with Foreign Key Locks

PostgreSQL's Foreign Key support underpins our ability to enforce data quality in the database.

A feature known as "Referential Integrity"allows us to link relational tables together to ensure the values in each table match.

Each lookup requires a lock to be applied to the row. In 9.2 and earlier that lock prevented writes to the row that was being checked. Which meant that UPDATEs against that row would be blocked, reducing performance in some applications. Worse, it also meant that deadlocks could occur in some cases.

The solution was a relatively simple idea: just lock the key value, not the whole row. That allows UPDATEs to proceed and application contention to be reduced in many cases.

Álvaro Herrera just committed a patch to make this happen. The simple idea required many new concepts and changes to make it work. The patch was so large it touched more than 100 files and thousands of changed lines.

Development took more than 2 years, with reviews from many different hackers to bring things to a state where it can be committed. So many people and companies were involved in this its hard to mention one without missing others. It's best to call this a true community effort in support of Álvaro and his work.

So in release 9.3, available in Sept 2013 we expect to see significant improvements in the performance of many applications. And it will make it easier to avoid those problems for new applications.