PostgreSQL 12 Released!

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

PostgreSQL 12 enhancements include notable improvements to query
performance, particularly over larger data sets, and overall space
utilization. This release provides application developers with new
capabilities such as SQL/JSON path expression support, optimizations for
how common table expression ("WITH") queries are executed, and generated
columns. The PostgreSQL community continues to support the extensibility
and robustness of PostgreSQL, with further additions to
internationalization, authentication, and providing easier ways to
administrate PostgreSQL. This release also introduces the pluggable
table storage interface, which allows developers to create their own
methods for storing data.

"The development community behind PostgreSQL contributed features for
PostgreSQL 12 that offer performance and space management gains that our
users can achieve with minimal effort, as well as improvements in
enterprise authentication, administration functionality, and SQL/JSON
support." said Dave Page, a core team member of the PostgreSQL Global
Development Group. "This release continues the trend of making it easier
to manage database workloads large and small while building on
PostgreSQL's reputation of flexibility, reliability and stability in
production environments."

PostgreSQL benefits from over 20 years of open source development and
has become the preferred open source relational database for
organizations of all sizes. The project continues to receive recognition
across the industry, including being featured for the second year in a
row as the "DBMS of the Year" in 2018 by DB-Engines and receiving the
"Lifetime Achievement" open source award at OSCON 2019.

Overall Performance Improvements
--------------------------------

PostgreSQL 12 provides significant performance and maintenance
enhancements to its indexing system and to partitioning.

B-tree Indexes, the standard type of indexing in PostgreSQL, have been
optimized in PostgreSQL 12 to better handle workloads where the indexes
are frequently modified. Using a fair use implementation of the TPC-C
benchmark, PostgreSQL 12 demonstrated on average a 40% reduction in
space utilization and an overall gain in query performance.

Queries on partitioned tables have also seen demonstrable improvements,
particularly for tables with thousands of partitions that only need to
retrieve data from a limited subset. PostgreSQL 12 also improves the
performance of adding data to partitioned tables with "INSERT" and
"COPY", and includes the ability to attach a new partition to a table
without blocking queries.

There are additional enhancements to indexing in PostgreSQL 12 that
affect overall performance, including lower overhead in write-ahead log
generation for the GiST, GIN, and SP-GiST index types, the ability to
create covering indexes (the "INCLUDE" clause) on GiST indexes, the
ability to perform K-nearest neighbor queries with the distance operator
("<->") using SP-GiST indexes, and CREATE STATISTICS now supporting
most-common value (MCV) statistics to help generate better query plans
when using columns that are nonuniformly distributed.

Just-in-time (JIT) compilation using LLVM, introduced in PostgreSQL 11,
is now enabled by default. JIT compilation can provide performance
benefits to the execution of expressions in WHERE clauses, target lists,
aggregates, and some internal operations, and is available if your
PostgreSQL installation is compiled or packaged with LLVM.

Enhancements to SQL Conformance & Functionality
-----------------------------------------------

PostgreSQL is known for its conformance to the SQL standard - one reason
why it was renamed from "POSTGRES" to "PostgreSQL" - and PostgreSQL 12
adds several features to continue its implementation of the SQL standard
with enhanced functionality.

PostgreSQL 12 introduces the ability to run queries over JSON documents
using JSON path expressions defined in the SQL/JSON standard. Such
queries may utilize the existing indexing mechanisms for documents
stored in the JSONB format to efficiently retrieve data.

Common table expressions, also known as "WITH" queries, can now be
automatically inlined by PostgreSQL 12, which in turn can help increase
the performance of many existing queries. In this release, a WITH query
can be inlined if it is not recursive, does not have any side-effects,
and is only referenced once in a later part of a query.

PostgreSQL 12 introduces "generated columns." Defined in the SQL
standard, this type of column computes its value from the contents of
other columns in the same table. In this version, PostgreSQL supports
"stored generated columns," where the computed value is stored on the disk.

Internationalization
--------------------

PostgreSQL 12 extends its support of ICU collations by allowing users to
define "nondeterministic collations" that can, for example, allow
case-insensitive or accent-insensitive comparisons.

Authentication
--------------

PostgreSQL expands on its robust authentication method support with
several enhancements that provide additional security and functionality.
This release introduces both client and server-side encryption for
authentication over GSSAPI interfaces, as well as the ability for
PostgreSQL to discover LDAP servers if PostgreSQL is compiled with OpenLDAP.

Additionally, PostgreSQL 12 now supports a form of multifactor
authentication. A PostgreSQL server can now require an authenticating
client to provide a valid SSL certificate with their username using the
"clientcert=verify-full" option and combine this with the requirement of
a separate authentication method (e.g. "scram-sha-256").

Administration
--------------

PostgreSQL 12 introduces the ability to rebuild indexes without blocking
writes to an index via the "REINDEX CONCURRENTLY" command, allowing
users to avoid downtime scenarios for lengthy index rebuilds.

Additionally, PostgreSQL 12 can now enable or disable page checksums in
an offline cluster using the "pg_checksums" command. Previously page
checksums, a feature to help verify the integrity of data stored to
disk, could only be enabled at the time a PostgreSQL cluster was
initialized with "initdb."

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/12/release-12.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.

Press Release Translations
--------------------------

* Chinese: https://www.postgresql.org/about/press/presskit12/zh/
* French: https://www.postgresql.org/about/press/presskit12/fr/
* German: https://www.postgresql.org/about/press/presskit12/de/
* Hebrew: https://www.postgresql.org/about/press/presskit12/he/
* Japanese: https://www.postgresql.org/about/press/presskit12/ja/
* Portuguese: https://www.postgresql.org/about/press/presskit12/pt/
* Russian: https://www.postgresql.org/about/press/presskit12/ru/
* Spanish: https://www.postgresql.org/about/press/presskit12/es/

Links
-----

* Download: https://www.postgresql.org/download/
* Release Notes: https://www.postgresql.org/docs/12/release-12.html
* Press Kit: https://www.postgresql.org/about/press/
* Security Page: https://www.postgresql.org/support/security/
* Versioning Policy: https://www.postgresql.org/support/versioning/
* Follow @postgresql on Twitter: https://twitter.com/postgresql