What’s new in PostgreSQL 9.5

Fedora 24 ships with PostgreSQL 9.5, a major upgrade from version 9.4 that is included in Fedora 23. The new version 9.5 provides several enhancements and new features, but also brings some compatibility changes, as it has been very common between PostgreSQL major versions. Note that in the PostgreSQL versioning scheme, 9.4 and 9.5 are two major versions, while the first number is mostly marketing and increments when major features are introduced in the release.

New features and enhancements in 9.5

GROUPING SETS

PostgreSQL has been traditionally OLTP, rather than OLAP, but this may change in the future; small steps like GROUPING SETS help on this path, since the GROUPING SETS allow to use more complex aggregation operations (grouping). CUBE and ROLEUP are then just specific variants of GROUPING SETS.

ON CONFLICT

Users may also be very happy about ON CONFLICT enhancement, that allows to do something sane in case the current statement would generate a conflict. That is quite general approach with two possible solutions — we can either turn the INSERT statement to UPDATE or ignore the statement at all. This feature is often called UPSERT, and in other DBMS we may know something very similar as MERGE command. However, it is not 100% MERGE implementation of the SQL standard in case of PostgreSQL, so it is not called like that. UPSERT implementation in PostgreSQL should also be more safe because CTE (common table expressions) might lead to race condition if not used properly. An example of inserting new tags into database and ignoring duplicate records may look like this:

INSERT INTO tags (tag) VALUES ('PostgreSQL'),('Database') ON CONFLICT DO NOTHING;

Row-level security control

Another feature that may substantially simplify SQL queries, is row-level security control, that allowing check access on particular rows. For example, if every row includes information about who is owner of the record, we would need to check in the application that the currently logged user equals the owner column. With row-level security feature we may leave this to the DBMS and thus we can not only keep application logic clear, but we can also be a bit more sure that potential attacker would not get around, because it’s checked on one layer further. An example how to use the row-level security control may look like this:

CREATE POLICY policy_article_user ON articles
FOR ALL TO PUBLIC
USING (user = current_user);

ALTER TABLE articles ENABLE ROW LEVEL SECURITY;

SELECT * FROM articles;
 id | user | title 
 ---+------+-------------------
  1 | joe  | How I went to Rome
  4 | joe  | My Favorite Recipe
 (2 rows)

With this, currently logged user can only see items that were created by the user that is logged in.

Other improvements

Have you ever tried connecting two database servers into one instance, so application does not need to care about connections to more servers separately? This was already possible using CREATE FOREIGN TABLE, but one needed to re-define every table with every single column. And of course change it again, once structure of the foreign table changed.

From version 9.5 we can import whole schema as easy as this, so not only we have simpler and less error-prone way to connect two remote databases, but it can be very handy also for data migration:

IMPORT FOREIGN SCHEMA invoices
LIMIT TO (customers, customers_invoices)
FROM SERVER invoice.example.com INTO remote_invoices;

What else we find in 9.5? Of course there are several performance enhancements, but that is almost a must for every release, right? What might not be that common for every release though is a brand new index type — BRIN (Block Range Index). According to the documentation, it is designed for handling very large tables in which certain columns have some natural correlation with their physical location within the table. In such cases the bitmap index scans may be used and performance of especially analytical queries might be substantially better.

Upgrading from PostgreSQL 9.4

Note that upgrading from PostgreSQL 9.4 is not automatic. If you have already used PostgreSQL in some previous version, you need to proceed with upgrade. Upgrade procedure, as it is common in case of PostgreSQL, is not an automatic procedure and admins are required to proceed with the steps manually. Fedora helps here a lot by providing postgresql-setup binary that accepts either –initdb (for initializing the datadir) or –upgrade arguments and helps proceeding with the whole procedure almost automatically.

Warning: Do not forget to back-up all your data before proceeding with the upgrade.

After system upgrade (F23 to F24 in this case), you will probably see something like this after trying to run PostgreSQL server:

$ sudo systemctl start postgresql.service
Job for postgresql.service failed because the control process exited with error code. See "systemctl status postgresql.service" and "journalctl -xe" for details.

That’s because server knows about version of the datadir and refuses to start to not break anything. So let’s proceed with upgrade — install the upgrade subpackage first:

$ sudo dnf install postgresql-upgrade

Then run upgrade itself:

$ sudo postgresql-setup --upgrade
 * Upgrading database.
 * Upgraded OK.
WARNING: The configuration files were replaced by default configuration.
WARNING: The previous configuration and data are stored in folder
WARNING: /var/lib/pgsql/data-old.
 * See /var/lib/pgsql/upgrade_postgresql.log for details.

At this point you should really look at the log file as suggested, but you should also be able to start the service now:

$ sudo systemctl start postgresql.service
$ sudo systemctl status postgresql.service

And that’s all. Easy, right? As always, any feedback is welcome.