One-click upgrade your PostgreSQL to version 9.5 and 9.6 now available!

Scalingo provides up-to-date database addons with straightforward upgrade. We just turned on the ability to upgrade your PostgreSQL databases to versions 9.5 and 9.6. In the process, we also updated the "Advanced" tab of the Dashboard with the ability to administrate PostgreSQL extensions. Let's go for a tour on the wonderful world of PostgreSQL 9.5 and 9.6.

Upgrade Process

As a preamble, let's have a look at how to upgrade your database on Scalingo. As usual, the upgrading process on Scalingo is as simple as clicking a button on the database dashboard. The following picture shows the message you will see on your database dashboard if you are running the latest 9.4 version (i.e. PostgreSQL v9.4.10):

This upgrade can take some time for the biggest databases. During the process, your database will be taken offline. You will first have to upgrade to 9.5 before being able to upgrade to 9.6.

We are deploying today versions 9.5.5 and 9.6.1. As usual, you can find the corresponding Docker images on our docker hub repository.

We strongly advise you to test that your code still works with the new database version. To ease this task, you can pull the PostgreSQL version you need from our public docker hub repository and test locally your code. You can also get the same container that we've built for your app and that's running on our infrastructure using our Docker Image Addon.

A more efficient way to automate the upgrade process

We've always think about our database addons as a Database as a Service (DBaaS). That's why it's obvious that we provide a smooth automatic process when you want to upgrade your database (unlike some of our competitors!). Here at Scalingo, we fully assist you with your database.
However we were not completely satisfied with the way we automated the PostgreSQL upgrade. That's why, during our work on PostgreSQL 9.5 and 9.6, our team revamped the way we handle the backup and restore of your data. Up to now, we first stopped the database, did a backup with pg_dump and a restore with psql. For the biggest databases hosted on Scalingo (tens of GB!), the whole process could take several tens of minutes to finish. That's a lot of downtime for a production database! After some modifications in our workflow, we are now able to use the pg_upgrade tool to handle this process. The downtime is only a dozen of seconds!

The workflow is now:

Stopping the running database container: we send a SIGTERM signal to the PostgreSQL process so that it stopped nicely the current transaction and request
Running a specialized pg_upgrade container to handle the migration of the data: this container is open source and is available on our docker hub repository. You can easily test the migration form 9.4 to 9.5 locally with docker run -v /path/to/data:/var/lib/postgresql scalingo/pgupgrade:94to95 /pg_upgrade.
Running the database container with the new version: once again, the Docker image is open source and you can test locally that everything is working fine with a simple docker run -v /path/to/data:/var/lib/postgresql scalingo/postgresql:9.6.1-1.

Manage your PostgreSQL Extensions from the Dashboard

The PostgreSQL dashboard also received some love to provide you with more control over the extensions. First of all, the "Advanced" tab now displays in a block the list of extensions you installed and their version.

Second, the pg_stat_statements gathers statistics about requests. You may need to reset these statistics. To do so, you must query the database with admin rights (using the pg_stat_statements_reset function). As you do not have these rights on Scalingo databases for security reason, we now give you the ability to reset the statistics with a button on the dashboard. If you need to do some other query with admin rights, feel free to ask us at support@scalingo.com. We may consider adding a specific button in the dashboard.

Eventually, the stats on the "Overview" tab (memory and swap usage, database connections and data size) are now automatically updated. No need to reload the page anymore!

You can now talk about the new features introduced in PG 9.5 and PG 9.6.

What is New on PostgreSQL 9.5

PostgreSQL 9.4 was initially released in December 2014. The 9.5 version being released in early 2016, it has been a long year of work for the PostgreSQL Global Development Group to release this version.

The long awaited PostgreSQL 9.5 delivers the not-less-expected UPSERT query. UPSERT is shorthand for INSERT, ON CONFLICT UPDATE. It allows new and updated rows to be treated the same.

A big new security feature, is Row Level Security. It is the fruit of 5 years of work to provide the ability to filter which rows particular users are allowed to update or view. Given a log table, in which each row is a log entry:

CREATE TABLE log (
   id serial primary key,
   username text,
   log_event text);

Thanks to Row Level Security, you can specify that a log entry is only visible for the user who created it:

CREATE POLICY policy_user_log ON log
 FOR ALL
 TO PUBLIC
 USING (username = current_user);

And finally, we enable Row Level Security on the table:

ALTER TABLE log ENABLE ROW LEVEL SECURITY;

A SELECT query on this table will only return rows inserted by the current user. For instance, as the "report" user:

# SELECT * FROM log;
id | username |   log_event
----+----------+----------------
 1 | report   | DELETE issued
 4 | report   | Reset accounts
(2 rows)

Example from the PostgreSQL wiki.

This version also provides better performances for sorting of varchar, text, numeric fields.

For a comprehensive list of what is new in this version, please refer to this blog post from the official website.

What is New on PostgreSQL 9.6

With a shorter gestation, this version still has some really interesting features. The most important being the support for parallelizing some queries operations, in order to return query results faster. The speed up depends on available cores.

Second, PostgreSQL full text search now supports "phrase search". Former version of PostgreSQL allows basic boolean searching on collection of words, but it was not possible to have any kind of relationship between these words. For example, a word search on "Scalingo New Features" returns documents where those three documents are separated by pages of text or tied together.

Using the new tie-fighter <-> you could search this exact phrase using either of the following:

> phraseto_tsquery('scalingo new features')
> to_tsquery('scalingo <-> new <-> features')

If we want to also allow for "Scalingo Great New Databases Features", we might use the search below, allowing the words to have a word in between:

> to_tsquery('scalingo <2> new <2> features')

The work on the performances of such search is still active. Expect more in the upcoming version 10.0!