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.
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.
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.
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.
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.
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:
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.INCLUDE clause and are helpful for performing index-only scans,
especially on data types that are not indexable by B-tree indexes.RANGE to use PRECEDING/FOLLOWING, GROUPS, and frame exclusionFor a full list of features included in this release, please read the release notes, which can be found at:
https://www.postgresql.org/docs/11/static/release-11.html
PostgreSQL is the world's most advanced open source database, with a global community of thousands of users, contributors, companies and organizations. The PostgreSQL Project builds on over 30 years of engineering, starting at the University of California, Berkeley, and has continued with an unmatched pace of development. PostgreSQL's mature feature set not only matches top proprietary database systems, but exceeds them in advanced database features, extensibility, security, and stability. Learn more about PostgreSQL and participate in our community at PostgreSQL.org.