Starting today, Postgres 9.5 is now the new default version for all new Heroku Postgres databases. We’ve had hundreds of customers using early beta versions of 9.5 and the feedback has been positive. For many customers, the new UPSERT functionality was the last feature that prevented many of them from moving from other relational databases to Postgres. The engineering staff at Heroku and the Postgres community at large has spent years bringing UPSERT to fruition and the customer feedback is a testament to that hard work. If you want to try out the new version, getting it is as simple as provisioning a new database:
$ heroku addons:create heroku-postgresql -a sushi
More UPSERT
UPSERT, otherwise known as INSERT ... ON CONFLICT, is the functionality that allows records in the database to be updated if one already exists or for a record to be inserted doesn’t. Other relational databases have this same type of functionality but what makes Postgres’ version of it so special is that it will determine if there’s a conflict based on a unique index in the table on specified set of columns. On top of that, if other unique columns exist, Postgres will throw a violation. This means that your data will be safe and there is less risk of data being overwritten by a rogue operation.
Inline Updates to JSONB
When JSONB came to fruition in Postgres 9.4, it opened up the door to Postgres being used like a document data store. Yet, the 9.4 release was just a first step down that path. One of the major complaints with JSONB was that the json could not be updated in place. If you wanted to update the json in a column, the entire column would need to be updated in the application and then the entire column updated in Postgres. Using the jsonb_set function in Postgres 9.5, JSONB columns can now be updated in place without having to serialize the entire field all over again:
SELECT jsonb_set(
'{"name":"Chuck", "contact":{"phone": "111 222 3333" }}'::jsonb,
'{contact,phone}',
'"222 333 4444"'::jsonb);
The first parameter is the field that’s going to be changed, the second is the path within the json to the key whose value should change and the final is the actual value. This should speed up working with JSONB fields.
Get Started Today
The updates to JSONB and the new UPSERT are exciting new additions but Postgres 9.5 has many more features that provide just as much value. Give Postgres 9.5 a shot by provisioning a new Heroku Postgres database and if you have an existing database on the platform, please check out our documentation for upgrading. Get started today and let us know what you think at postgres@heroku.com.