Excited for Postgres 9.5(Locking)

I was reading about some of the new things coming up in the Postgres 9.5 release and I was very excited about some new locking options that will make concurrency even better in Postgres.

I remember several years ago I thought it would be a good idea to use Postgres as Queue service. At the time I did not want to add a new Service to our infrastructure, and I though Postgres would be up to the challenge. This was the first time I ran into the problem that the first locking feature we are going to talk about solves.

In my early days of trying to make this work I did something along the lines of

select * from queue_table
where processing = false
limit 1
for update;

The problem with this is that the first call will lock the first row for writing and the second call will sit and wait for the lock to release on the row before continuing. In Postgres 9.5 we get a new option:

select * from queue_table
where processing = false
limit 1
for update skip locked;

Problem solved. I still wouldn't use Postgres as a Queue system given the many things I have learned since then, however there are other places where you will run into this problem.

Here is another one that would save us a lot of headaches at my previous employer. We had data where you had a data_table that you joined on the users table. You would then want to select for update the data in the data_table while joining on the users table. This would previously row lock the row in the user table and could cause issues.

select * from data_table
join users on data_table.user_id = users.id where data_table.id = 12345
for update;

The new solution to this is:

select * from data_table
join users on data_table.user_id = users.id where data_table.id = 12345
for update of data_table for share of users;

I hope I have shown you some new things that are going to make Postgres just that much better.