TL;DR: You can now run PostgreSQL 9.6 on Compose, PostGIS has been upgraded and now pgRouting is also available.

PostgreSQL 9.6 is now available as the latest version of PostgreSQL on Compose allowing users to run the most recent version, 9.6.3, along with the updated appropriate version of the PostGIS extension and a new addition, the pgRouting extension.

The change means that most Compose users can benefit from improved vacuuming with reporting, full-text search for phrases, enhanced GIN indexing, faster sorting and a smarter Foreign Data Wrapper for PostgreSQL databases which can pass work to other servers. These features, combined with hundreds of enhancements and bug-fixes in 9.6, 9.6.1, 9.6.2 and 9.6.3, offers the richest PostgreSQL experience to date.

Parallel

One of the big features of PostgreSQL 9.6 is the introduction of parallel queries. This allows multiple cores to take part in the processing of queries. Parallel querying is disabled by default on 9.6. We'll be taking a deeper look into this and showing the benefits and trade-offs in hosted Compose and Compose Enterprise in a future article.

Geodata

The PostGIS extension has already provided Compose users with extensive geographic support and the 2.3.2 release has built on that with Block Range Index (BRIN) support and new functions such as ST_GeneratePoints, ST_MinimumClearance and ST_GeometricMedian. We've also brought online pgRouting (2.4.1) which adds routing algorithms to the PostgreSQL/PostGIS support that can balance route length and cost when working out how to get from A to B.

Getting PostgreSQL 9.6

All new PostgreSQL deployments on Compose now default to 9.6.3 when being created.

Whether you currently run PostgreSQL 9.5 or 9.4 deployments, this is a major update and we recommend that you use the restore-to-a-new-deployment route. You start the process by creating an on-demand backup.

Compose backups have the ability to be restored into new deployments, so to upgrade you restore your on-demand backup to a new PostgreSQL deployment running the latest version. This also ensures your current database is untouched and the new, fresh deployment is automatically loaded with your data.

Step by step

• Go to the Compose Console and select your database
• Select Backups tab and then click Backup Now

• Wait for the on-demand backup to complete and return to the Backups tab
• Click on the restore button (the looped arrow) in the on-demand backup row.

• On the displayed form, enter a new name for the deployment (if you want to), select a datacenter for the deployment and ensure that the version selector is set to the new database version.

• Then click Create Deployment and the process will automatically deploy a new database, load that backup into it and start it up. You can either wait or carry on in the console; it all takes place in the background.

You'll be up and running with PostgreSQL 9.6, ready for testing or for production. Don't forget to backup your old 9.5 deployment and, when you are done with it, delete it.

If you have any feedback about this or any other Compose article, drop the Compose Articles team a line at articles@compose.com. We're happy to hear from you.

attribution Larry Li