The latest and greatest release of PostgreSQL, v11, is due out later this week! Here is a quick look at the new features in this version.

Partitioning Improvements

The biggest set of improvements are for partition support. Postgres 11 supports:

  • updating rows can move them across partitions
  • default partitions
  • automatic index creation
  • foreign key constraints are supported on partitioned tables
  • unique indexes
  • aggregation push down to partitions
  • paritition by hash
  • partition children on remote servers (postgres_fdw) can be updated
  • INSERT..ON CONFLICT is supported on partitioned tables
  • more efficient query plans and faster query execution

Read more about these improvements here.

More Parallelism

There are a bunch of improvements across the board related to increased parallelism. Joins, unions, creation of tables and views with “CREATE..AS SELECT”, btree index creation and more benifit from these changes.

Here is an article on index creation performance improvements in Postgres 11.

JIT Complied Queries

Although disabled by default in 11, LLVM-based JIT compilation of queries is now built into Postgres. The server can now choose to compile certain queries (those with execution estimates higher than a threshold) using LLVM, resulting in faster queries. This compilation is automatic and transparent to the application.

Here are some numbers.

Covering Indexes

Covering indexes, available in other RDBMSes but not PostgreSQL, has been added now. With this, it is possible to have an index like this (new syntax):

CREATE INDEX empid_idx ON employees(empid) INCLUDE (empname);

Such an index includes extra attributes (here “empname”) in the index itself, and makes it possible to satisfy queries like:

SELECT empname FROM employees WHERE empid BETWEEN 1000 and 2000;

entirely using index-only scans and not touch the heap at all.

Read more about covering indexes here.

Stored Procedures

Stored procedures are yet another instance of PostgreSQL playing catch up to other RDBMSes. They are of course, similar to stored functions that PostgreSQL has supported since long, but the one big thing possible only using procedures is transaction control. You can commit and rollback transactions from within stored procedures.

Have a look at the CREATE PROCEDURE documentation.

Adding Columns to Tables

Altering a table to add a new NOT NULL column with a DEFAULT value was a bit of a pain in PostgreSQL, as this caused the server to rewrite the entire table. With v11 it is now possible to have DDL statements like this:

ALTER TABLE users ADD COLUMN foo_factor integer NOT NULL DEFAULT 42;

execute in constant time. Rows are not touched when this executed, and are instead updated “lazily”.

Read more about this feature here.

SHA Functions

And finally, those of you who used pgcrypto only for SHA functions can now switch to using the ones built-in into PostgreSQL 11:

bench=# \df pg_catalog.sha*
                          List of functions
   Schema   |  Name  | Result data type | Argument data types | Type
------------+--------+------------------+---------------------+------
 pg_catalog | sha224 | bytea            | bytea               | func
 pg_catalog | sha256 | bytea            | bytea               | func
 pg_catalog | sha384 | bytea            | bytea               | func
 pg_catalog | sha512 | bytea            | bytea               | func
(4 rows)

bench=# SELECT sha256('hello');
                               sha256
--------------------------------------------------------------------
 \x2cf24dba5fb0a30e26e83b2ac5b9e29e1b161e5c1fa7425e73043362938b9824
(1 row)

About pgDash

The latest versions of pgDash and pgmetrics already support PostgreSQL 11.

pgDash is a modern, in-depth monitoring solution designed specifically for PostgreSQL deployments. pgDash shows you information and metrics about every aspect of your PostgreSQL database server, collected using the open-source tool pgmetrics. pgDash provides core reporting and visualization functionality, including collecting and displaying PostgreSQL information and providing time-series graphs, detailed reports, alerting, teams and more.

pgDash Diagnostics

The Diagnostics feature in pgDash examines your PostgreSQL server and databases scanning for potential issues that can impact the health and performance of the deployment. No additional setup is required for Diagnostics - you just need to be actively sending in data to pgDash. Learn more here or signup today for a free trial.