Now available on Scalingo - PostgreSQL 12
We just released the latest PostgreSQL database major version. PostgreSQL 12 is now the new default version when you provision a database. As usual, upgrading is an easy one-click process.
What is new in PostgreSQL 12?
With 30 years of experience, the database management system PostgreSQL keeps improving and innovating with this new major release. For a comprehensive list of the novelties of this release, you can refer to the official changelog. We will cover in this blog article the most notable ones.
Overall Performance Improvements
The most significant performance improvement included in this release relates to how frequently modified indices are stored. Some benchmarks highlight 40% space utilization reduction on average and better query performance!
Queries on partitioned tables has also been improved. If you use this feature, you should definitely upgrade to benefit from a significant improvement in the read queries, but also to write data in partitioned tables.
Better SQL Convention Implementation
PostgreSQL always tried to follow the SQL standard as much as possible. This release improves this implementation in a few ways.
First of all, it is now possible to run queries over JSON documents thanks to
the SQL/JSON path
language.
You may want to use this language to query documents stored in the JSONB
format.
This version also adds the support for generated columns. This type of column computes values based on other columns in the same table.
A Few Compatibility Changes
As with any new major version, upgrading to PostgreSQL 12 must be carefully studied as a few incompatibility exit with previous version. You can find a comprehensive list in the official documentation. You may want to go through this document to ensure that your code will still run with PostgreSQL 12.
Upgrade Process
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 11 version (i.e. PostgreSQL 11.6 at the time of writing):
This upgrade can take some time for the biggest databases. It will induce a period of downtime, even for databases using a Business plan, because PostgreSQL requires a full restart of all nodes to perform a major version upgrade. The length of the period of downtime will depend on the volume of data stored in the database.
For minor version upgrade (ie 11.5 to 11.6 or 10.10 to 10.11), databases using a Business plan will see the advantage of having nearly no downtime during the uprade procedure.
How To Check That Your Project Is Working With PG 12?
You can find the corresponding Docker images on our Docker hub repository. Note that the images 10.10.0-6 and 11.5.0-5 failed to run on a development setup. We fixed this in the images 10.11.0-1 and 11.6.0-1. The image 12.1.0-1 is also working locally.
You are strongly advised 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 it locally, with your code, on your workstation before updating. You can also get the same container that we have built for your app and which is running on our infrastructure using our Docker Image Addon.
