The new and newer PostgreSQL

PostgreSQL 9.6 has been available for quite some time and has served our websites valiantly. But technology never stops moving, and neither does the PostgreSQL development team. In the last year or so, they've released two new versions of the Free Software database.

To close out the holiday season, we're happy to report that we now support both of them. Hello, PostgreSQL 10 and 11!

What's new?

Compared to PostgreSQL 9.6, 10 and 11 have added a number of new features to help with scaling data sets.

Most notably, they've improved feature support for table partitioning natively. Rather than partitioning with complex constraints, tables can now be defined explicitly as a partition of another.

For the XML crowd, PostgreSQL now supports XMLTable, an SQL-standard feature that lets view your XML as a table, and thus, mix and match it with other relational data. That can be especially helpful when doing ongoing imports or "extract-transform-load" (ETL) processes.

JSON fans, meanwhile, can look forward to full text search on JSON and JSONB fields. And everyone can appreciate transaction-safe stored procedures in PostgreSQL 11.

There are also a wide array of improvements relating to performance to help make PostgreSQL 11 the fastest and most tunable release yet.

See the release notes for PostgreSQL 10 and PostgreSQL 11 for more details, as well as our own documentation.

How do I use it?

As usual, upgrading to a new version of a service on Platform.sh is delightfully simple. Just go into your services.yaml file and change 9.6 to 11. Like so:

mydb:
    type: postgresql:9.6
    disk: 2048

becomes:

mydb:
    type: postgresql:11
    disk: 2048

PostgreSQL includes an upgrade utility that can convert databases from previous versions to version 10 or 11, and we run that automatically, if needed. That means no extra work is required to upgrade your database to the latest version of PostgreSQL. (Note: the utility works only when migrating to versions 10 or 11; upgrading from 9.3 to 9.6 is not turnkey. We recommend just upgrading to version 11.)

Please be aware that downgrades aren't supported by PostgreSQL or by us, so we suggest testing your upgrade in a separate branch before upgrading production.

Fortunately, Platform.sh makes it super easy to clone your entire site (data and all) to test upgrades before they go live. Happy upgrading!