Here's Postgres 9.5: Now Available on Heroku

Heroku has long been committed to making PostgreSQL one of the best relational databases in the world. We’re also committed to giving you the ability to try the latest release as soon as it’s available. Today, we’re pleased to announce the public beta of Postgres 9.5 on Heroku.

PostgreSQL 9.5 brings a bevy of super exciting new features with the most prominent being the new UPSERT functionality. UPSERT gives you the expected behavior of an insert, or, if there is a conflict, an update, and is performant without the risk of race conditions for your data. UPSERT was one of the last few detracting arguments against PostgreSQL. A special thanks goes to Peter Geoghegan on the Heroku Postgres team for committing the better part of two years developing the functionality as its primary author, along with contributions from Andres Freund from Citus Data and Heikki Linnakangas.

You can get started right now with PostgreSQL 9.5 on Heroku by passing in the version flag during a provision request:

heroku addons:create heroku-postgresql --version=9.5

This lets customers who want to try out the new release an easy way to do so, while customers who are happy with the current version can stay on 9.4 until 9.5 matures and we make it generally available. New databases will continue to default to 9.4.

INSERT … ON CONFLICT

UPSERT is meant for use during an INSERT operation in the database. If during that insert procedure a conflict occurs because a record already exists in the database (ON CONFLICT), the existing record would be updated instead of a new one being created. Digging a little deeper, this is what makes PostgeSQL’s UPSERT functionality so special. PostgreSQL UPSERT will determine if there’s a conflict based on a unique index in the table on specified set of columns. But, if that table has other unique indexes or unique constraints, PostgreSQL will throw a violation in the event of duplicates. This results in the database keeping your data safe by respecting all of the unique constraints within the table.

Let’s walk through an example. Assume that a PostgreSQL database has a table called inventory that manages the current state of the amount of products in the warehouse. In that table, there are three columns, product_id, amount, and notes. On top of that, let’s assume that there’s a unique index on product_id. Let’s say that someone in our warehouse is keying in new information about what’s on hand. The application may or may not know if the product being keyed in actually exists in the inventory table. So during an insert, we’d rather update the amount instead of creating a new record.

Getting started with UPSERT is fairly easy. For any of your normal INSERT statements that you’ve created, a few more keywords are added to the end of the statement to invoke UPSERT.

INSERT products (
  product_id,
  amount,
  notes
) VALUES (
  1234,
  10,
  ‘some of the items are damaged’
)
ON CONFLICT (product_id) DO UPDATE notes = ‘arrived on time’;

Once you’ve specified the ON CONFLICT, you have two options. The first is to tell PostgreSQL to update particular columns that you’ve defined and the second is to ignore the insert all together. With the new UPSERT functionality, this should greatly simplify application code.

Other Noteworthy Improvements

• BRIN Indexes - This new type of index is great for very large tables where computing a normal index may be too expensive.
• If you’ve ever wanted to summarize data in different ways, beyond what you could do in a GROUP BY clause, new summarization primitives have been added.

Version Policy

Postgres 9.5 is currently in beta. We encourage customers to wait to put their production applications on this new version until it officially reaches GA. Once we are confident that this new version is stable enough, based on discussions with customers and the community at large, we’ll make 9.5 the default for all new databases created on Heroku.

Getting Starting

UPSERT is only one of the many exciting new improvements in this release of PostgreSQL 9.5. Give UPSERT and all the other new features a shot and let us know what you think!