Download PostgreSQL 11

PostgreSQL is an open source relational database management system, which can be run on various operating systems. This makes it widely applicable in different environments. The developers released version 11 a few days ago. The corresponding PostgreSQL announcement looks like this:

PostgreSQL 11 Released!

The PostgreSQL Global Development Group today announced the release of PostgreSQL 11, the latest version of the world’s most advanced open source database.

PostgreSQL 11 provides users with improvements to overall performance of the database system, with specific enhancements associated with very large databases and high computational workloads. Further, PostgreSQL 11 makes significant improvements to the table partitioning system, adds support for stored procedures capable of transaction management, improves query parallelism and adds parallelized data definition capabilities, and introduces just-in-time (JIT) compilation for accelerating the execution of expressions in queries.

“For PostgreSQL 11, our development community focused on adding features that improve PostgreSQL’s ability to manage very large databases,” said Bruce Momjian, a core team member of the PostgreSQL Global Development Group. “On top of PostgreSQL’s proven performance for transactional workloads, PostgreSQL 11 makes it even easier for developers to run big data applications at scale.”

PostgreSQL benefits from over 20 years of open source development and has become the preferred open source relational database for developers. The project continues to receive recognition across the industry, and has been featured as the “DBMS of the Year 2017” by DB-Engines and in the SD Times 2018 100.

PostgreSQL 11 is the first major release since PostgreSQL 10 was released on October 5, 2017. The next update release for PostgreSQL 11 containing bug fixes will be PostgreSQL 11.1, and the next major release with new features will be PostgreSQL 12.

Increased Robustness and Performance for Partitioning
PostgreSQL 11 adds the ability to partition data by a hash key, also known as hash partitioning, adding to the current ability to partition data in PostgreSQL by a list of values ​​or by a range. PostgreSQL 11 further improves its data federation abilities with functionality improvements for partitions that use the PostgreSQL foreign data wrapper, postgres_fdw.

To help with managing partitions, PostgreSQL 11 introduces a catch-all default partition for data that does not match a partition key, and the ability to create primary keys, foreign keys, indexes, and triggers on partitioned tables that are passed down to all partitions . PostgreSQL 11 also supports automatically moving rows to the correct partition if the partition key for that row is updated.

PostgreSQL 11 improves upon query performance when reading from partitions by using a new partition elimination strategy. Additionally, PostgreSQL 11 now supports the popular “upsert” feature on partitioned tables, which helps users to simplify application code and reduce network overhead when interacting with their data.

Transactions Supported in Stored Procedures
Developers have been able to create user-defined functions in PostgreSQL for over 20 years, but prior to PostgreSQL 11, these functions were unable to manage their own transactions. PostgreSQL 11 adds SQL procedures that can perform full transaction management within the body of a function, enabling developers to create more advanced server-side applications, such as ones 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.

Enhanced Capabilities for Query Parallelism
PostgreSQL 11 improves parallel query performance, with performance gains in parallel sequential scans and hash joins along with more efficient scans of partitioned data. PostgreSQL can now execute SELECT queries that use UNION in parallel if the underlying queries are unable to be parallelized.

PostgreSQL 11 adds parallelism to several data definition commands, notably for the creation of B-tree indexes that are generated by executing the standard CREATE INDEX command. Several data definition commands that either create tables or materialized views from queries are also parallel capable now, including the CREATE TABLE .. AS, SELECT INTO, and CREATE MATERIALIZED VIEW.

Just-in-Time (JIT) Compilation for Expressions
PostgreSQL 11 introduces support for Just-In-Time (JIT) compilation to accelerate the execution of certain expressions during query execution. JIT expression compilation for PostgreSQL uses the LLVM project to speed up the execution of expressions in WHERE clauses, target lists, aggregates, projections, and some internal operations.

To take advantage of JIT compilation, you will need to install the LLVM dependencies and enable JIT compilation in either your PostgreSQL settings file by setting jit = on or from your PostgreSQL session by executing SET jit = on.

General User Experience Improvements
The enhancements to the PostgreSQL relational database are not possible without the feedback from an active user community and the hard work by the people who work on PostgreSQL. Below highlights a few of the many features included in PostgreSQL 11 designed to improve the overall user experience:

• Removing the need for ALTER TABLE .. ADD COLUMN .. DEFAULT .. with a not NULL default to rewrite the whole table on execution, which provides a significant performance boost when running this command.
• “Covering indexes,” which allows a user to add additional columns to an index using the INCLUDE clause and are helpful for performing index-only scans, especially on data types that are not indexable by B-tree indexes.
• Additional functionality for working with window functions, including allowing RANGE to use PRECEDING/FOLLOWING, GROUPS, and frame exclusion
• The inclusion of the keywords “quit” and “exit” in the PostgreSQL command-line interface to help make it easier to leave the command-line tool

For a full list of features included in this release, please read the release notes, which can be found at: