Why PostgreSQL 11?

PostgreSQL 11 includes a number of key partitioning improvements. Version 11 introduces hash partitioning, the ability to partition by hash key, which adds to the current ability to partition data in PostgreSQL by a list of values or by a range. PostgreSQL 11 also brings improvements to partitioning management, including the ability to create primary keys, foreign keys, indexes, and AFTER triggers on partitioned tables that are passed down to all partitions. Version 11 improves query performance when reading partitions, with faster partition pruning and partition pruning at execution time. Finally, PostgreSQL 11 brings data federation improvements, including the ability to shard data using the postgres_fdw module.

PostgreSQL 11 adds a number of performance improvements for parallelism. PostgreSQL 11 enables parallel hash joins and improves the performance of parallel sequential scans. PostgreSQL 11 allows you to execute SELECT queries that use UNION in parallel even if the underlying queries are unable to be parallelized. Version 11 adds parallelism to several additional data definition commands, including CREATE INDEX (for generating B-tree indexes), CREATE TABLE .. AS, SELECT INTO, and CREATE MATERIALIZED VIEW. Version 11 also introduces Just-in-Time (JIT) compilation, which speeds up expressions during query execution, including WHERE clauses, target lists, aggregates, and projections.

PostgreSQL 11 introduces the ability for stored procedures to provide transaction management for users to commit and roll back transactions. User-defined functions have been supported in PostgreSQL for more than 20 years, but prior to PostgreSQL 11, these functions were unable to manage their own transactions. PostgreSQL 11 adds SQL procedures that can perform transaction management within the body of a function, enabling developers to create more advanced server-side applications, such as those involving incremental bulk data loading. SQL procedures can be created using the CREATE PROCEDURE command, executed using the CALL command, and are supported by the server-side procedural languages PL/pgSQL, PL/Perl, PL/Python, and PL/Tcl.