What's New in PostgreSQL 12

On October 3rd 2019 a new version of the world’s most advanced open source database was released. PostgreSQL 12 is now available with notable improvements to query performance (particularly over larger data sets and overall space utilization) among other important features. 

In this blog we’ll take a look at these new features and show you how to get and install this new PostgreSQL 12 version. We’ll also explore some considerations to take into account when upgrading.

PostgreSQL 12 Features and Improvements

Let’s start mentioning some of the most important features and improvements of this new PostgreSQL version.

Indexing

• There is an optimization to space utilization and read/write performance for B-Tree indexes.
• Reduction of WAL overhead for the creation of GiST, GIN, and SP-GiST indexes.
• You can perform K-nearest neighbor queries with the distance operator (<->) using SP-GiST indexes.
• Rebuild indexes without blocking writes to an index via the REINDEX CONCURRENTLY command, allowing users to avoid downtime scenarios for lengthy index rebuilds.

Partitioning

• There are improvements over queries on partitioned tables, particularly for tables with thousands of partitions that only need to retrieve data from a limited subset.
• Performance improvements for adding data to partitioned tables with INSERT and COPY.
• You will be able to attach a new partition to a table without blocking queries.

SQL

• You can now run queries over JSON documents using JSON path expressions defined in the SQL/JSON standard and they can utilize the existing indexing mechanisms for documents stored in the JSONB format to efficiently retrieve data.
• WITH queries can now be automatically inlined by PostgreSQL 12 (if it is not recursive, does not have any side-effects, and is only referenced once in a later part of a query), which in turn can help increase the performance of many existing queries.
• Introduces “generated columns.” This type of column computes its value from the contents of other columns in the same table. Storing this computed value on this is also supported. 

Internationalization

• PostgreSQL 12 extends its support of ICU collations by allowing users to define “nondeterministic collations” that can, for example, allow case-insensitive or accent-insensitive comparisons.

Authentication

• Introduces both client and server-side encryption for authentication over GSSAPI interfaces.
• The PostgreSQL service is able to discover LDAP servers if it is compiled with OpenLDAP.
• Multi-factor authentication, using the clientcert=verify-full option and an additional authentication method configured in the pg_hba.conf file.

If you want to take advantage of these new features and improvements, you can go to the download page and get the last PostgreSQL version. If you require an HA setup, here is a blog to show you how to install and configure PostgreSQL for HA.

How to Install PostgreSQL 12

For this example, we are going to use CentOS7 as the operating system. So, we need to go to the RedHat based OS download site and install the corresponding version.

$ yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

It will install the PostgreSQL repository with stable, testing, and source packages.

$ head /etc/yum.repos.d/pgdg-redhat-all.repo

# PGDG Red Hat Enterprise Linux / CentOS stable repositories:

[pgdg12]

name=PostgreSQL 12 for RHEL/CentOS $releasever - $basearch

baseurl=https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-$releasever-$basearch

enabled=1

gpgcheck=1

gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG

...

Then, install the client and server PostgreSQL12 packages. It will install some python dependencies.

$ yum install postgresql12 postgresql12-server

Now, you can initialize your new PostgreSQL 12 database.

$ /usr/pgsql-12/bin/postgresql-12-setup initdb

Initializing database ... OK

And enable/start the PostgreSQL service.

$ systemctl enable postgresql-12

Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-12.service to /usr/lib/systemd/system/postgresql-12.service.

$ systemctl start postgresql-12

And that’s it. You have the new PostgreSQL version up and running.

$ psql

psql (12.0)

Type "help" for help.

postgres=# select version();

                                                 version

---------------------------------------------------------------------------------------------------------

 PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit

(1 row)

Now you have installed the last PostgreSQL version, you could migrate your data into this new database node.

Upgrading to PostgreSQL 12

If you want to upgrade your current PostgreSQL version to this new one, you have three main options which will perform this task.

• pg_dump: It’s a logical backup tool that allows you to dump your data and restore it in the new PostgreSQL version. Here you will have a downtime period that will vary according to your data size.. You need to stop the system or avoid new data in the master node, run the pg_dump, move the generated dump to the new database node and restore it. During this time, you can’t write into your master PostgreSQL database to avoid data inconsistency.
• Pg_upgrade: It’s a PostgreSQL tool to upgrade your PostgreSQL version in-place. It could be dangerous in a production environment and we don’t recommend this method in that case. Using this method you will have downtime too, but probably it will be considerably less than using the previous pg_dump method.
• Logical Replication: Ever since PostgreSQL 10 you have been able to use this replication method which allows you to perform major version upgrades with zero (or almost zero) downtime. In this way, you can add a standby node in the last PostgreSQL version, and when the replication is up-to-date, you can perform a failover process to promote the new PostgreSQL node. 

Considerations Before Upgrading to PostgreSQL 12

In general, for all upgrade process, and in all technology, there are several points to take into account. Let’s see some of the main ones.

• Data types abstime, reltime, and tinterval were removed.
• The recovery.conf settings are into the postgresql.conf file and it is no longer used. If you have this file created the server will not start. The files recovery.signal and standby.signal files are now used to switch into non-primary mode. The trigger_file setting has been renamed to promote_trigger_file and the standby_mode setting has been removed.
• The multiple conflicting recovery_target specifications are not allowed.
• The specification of “-f” to send the dump contents to standard output is required in pg_restore.
• The maximum index entry length is reduced by eight bytes in the B-Tree indexes, to improve the handling of duplicate entries. REINDEX operation on an index pg_upgrade’d from a previous version could fail.
• DROP IF EXISTS FUNCTION/PROCEDURE/AGGREGATE/ROUTINE generates an error if no argument list is supplied and there are multiple matching objects.

For more detailed information about the new PostgreSQL 12 features and consideration before migrating to it, you can refer to the Official Release Notes web page.