PostgreSQL 11 is here with improved partitioning performance, query parallelism, and JIT compilation

After releasing PostgreSQL 11 beta 1, back in May, the PostgreSQL Global Development Group finally released PostgreSQL 11, yesterday. PostgreSQL 11 explores features such as increased performance for partitioning, support for transactions in stored procedures, improved capabilities for query parallelism, and Just-in-Time (JIT) compilation for expressions among other updates.

PostgreSQL is a popular open source relational database management system that offers better reliability, robustness, and enhanced performance measures. Let’s have a look at these features in PostgreSQL 11.

Increased performance for partitioning

PostgreSQL 11 comes with an ability to partition the data using a hash key, which is known as hash partitioning. This adds to the already existing ability to partition data in PostgreSQL using a list of values or by a range. Moreover, PostgreSQL 11 also improves the data federation abilities by implementing functionality improvements for partitions using PostgreSQL foreign data wrapper, and postgres_fdw.

For managing these partitions, PostgreSQL 11 comes with a “catch-all” default partition for data that doesn’t match a partition key. It also comes with an ability to create primary keys, foreign keys, indexes as well as triggers on partitioned tables. The latest release also offers support for automatic movement of rows to the correct partition, given that the partition key for that row is updated.

Additionally, PostgreSQL 11 enhances the query performance when reading from partitions with the help of a new partition elimination strategy. It also offers support for the popular "upsert" feature on partitioned tables. The upsert feature helps users simplify the application code as well as reduce the network overhead when interacting with their data.

Support for transactions in stored procedures

With PostgreSQL 11 comes newly added SQL procedures that help perform full transaction management within the body of a function. This enables the developers to build advanced server-side applications like the ones that involve incremental bulk data loading.

Also, SQL procedures can now be created using the CREATE PROCEDURE command which is executed using the CALL command. These SQL procedures are supported by the server-side procedural languages such as PL/pgSQL, PL/Perl, PL/Python, and PL/Tcl.

Improved capabilities for query parallelism

PostgreSQL 11 enhances the parallel query performance, using the performance gains in parallel sequential scans and hash joins. It also performs more efficient scans of the partitioned data.

PostgreSQL 11 comes with added parallelism for a range of data definitions commands, especially for the creation of B-tree indexes generated by executing the standard CREATE INDEX command. Other data definition commands that either create tables or materialize the views from queries are also enabled with parallelism. This includes the CREATE TABLE .. AS, SELECT INTO, and CREATE MATERIALIZED VIEW.

Just-in-Time (JIT) compilation for expressions

PostgreSQL 11 offers support for Just-In-Time (JIT) compilation, This helps to accelerate the execution of certain expressions during query execution. The JIT expression compilation uses the LLVM project to boost the execution of expressions in WHERE clauses, target lists, aggregates, projections, as well as some other internal operations.

Other Improvements

ALTER TABLE .. ADD COLUMN .. DEFAULT ..have been replaced with a not NULL default to rewrite the whole table on execution. This offers a significant performance boost when running this command.

Additional functionality has been added for working with window functions, including allowing RANGE to use PRECEDING/FOLLOWING, GROUPS, and frame exclusion.