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:

https://www.postgresql.org/docs/11/static/release-11.html

About PostgreSQL
---------------

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 https://www.postgresql.org.

Links
-----
- Download: https://www.postgresql.org/download/
- Release Notes: https://www.postgresql.org/docs/11/static/release-11.html
- Security Page: https://www.postgresql.org/support/security/
- Versioning Policy: https://www.postgresql.org/support/versioning/
- Follow @postgresql on Twitter: https://twitter.com/postgresql