PostgreSQL 12 Beta 1 Released! - Mailing list pgsql-announce

The PostgreSQL Global Development Group announces that the first beta
release of PostgreSQL 12 is now available for download. This release
contains previews of all features that will be available in the final
release of PostgreSQL 12, though some details of the release could
change before then.

In the spirit of the open source PostgreSQL community, we strongly
encourage you to test the new features of PostgreSQL 12 in your database
systems to help us eliminate any bugs or other issues that may exist.
While we do not advise you to run PostgreSQL 12 Beta 1 in your
production environments, we encourage you to find ways to run your
typical application workloads against this beta release.

Your testing and feedback will help the community ensure that the
PostgreSQL 12 release upholds our standards of providing a stable,
reliable release of the world's most advanced open source relational
database.

PostgreSQL 12 Features Highlights
---------------------------------

### Indexing Performance, Functionality, and Management

PostgreSQL 12 improves the overall performance of the standard B-tree
indexes with improvements to the space management of these indexes as
well. These improvements also provide a reduction of index size for
B-tree indexes that are frequently modified, in addition to a
performance gain.

Additionally, PostgreSQL 12 adds the ability to rebuild indexes
concurrently, which lets you perform a `REINDEX` without blocking any
writes to the index. This feature should help with lengthy index
rebuilds that could cause downtime when managing a PostgreSQL database
in a production environment.

PostgreSQL 12 extends the abilities of several of the specialized
indexing mechanisms. The ability to create covering indexes, i.e. the
`INCLUDE` clause that was introduced in PostgreSQL 11, has now been
added to GiST indexes. SP-GiST indexes now support the ability to
perform K-nearest neighbor (K-NN) queries for data types that support
the distance (`<->`) operation.

The amount of write-ahead log (WAL) overhead generated when creating a
GiST, GIN, or SP-GiST index is also significantly reduced in PostgreSQL
12, which provides several benefits to the disk utilization of a
PostgreSQL cluster and features such as continuous archiving and
streaming replication.

### Inlined WITH queries (Common table expressions)

Common table expressions (aka `WITH` queries) can now be automatically
inlined in a query if they a) are not recursive, b) do not have any
side-effects and c) are only referenced once in a later part of a query.
This removes an "optimization fence" that has existed since the
introduction of the `WITH` clause in PostgreSQL 8.4

If need be, you can force a `WITH` query to materialize using the
`MATERIALIZED` clause, e.g.

    WITH c AS MATERIALIZED (
        SELECT * FROM a WHERE a.x % 4 = 0
    )
    SELECT * FROM c JOIN d ON d.y = a.x;

### Partitioning

PostgreSQL 12 improves on the performance when processing tables with
thousands of partitions for operations that only need to use a small
number of partitions.

PostgreSQL 12 also provides improvements to the performance of both
`INSERT` and `COPY` into a partitioned table. `ATTACH PARTITION` can now
be performed without blocking concurrent queries on the partitioned
table. Additionally, the ability to use foreign keys to reference
partitioned tables is now permitted in PostgreSQL 12.

### JSON path queries per SQL/JSON specification

PostgreSQL 12 now allows execution of [JSON path
queries](https://www.postgresql.org/docs/devel/functions-json.html#FUNCTIONS-SQLJSON-PATH)
per the SQL/JSON specification in the SQL:2016 standard. Similar to
XPath expressions for XML, JSON path expressions let you evaluate a
variety of arithmetic expressions and functions in addition to comparing
values within JSON documents.

A subset of these expressions can be accelerated with GIN indexes,
allowing the execution of highly performant lookups across sets of JSON
data.

### Collations

PostgreSQL 12 now supports case-insensitive and accent-insensitive
comparisons for ICU provided collations, also known as
"[nondeterministic
collations](https://www.postgresql.org/docs/devel/collation.html#COLLATION-NONDETERMINISTIC)".
When used, these collations can provide convenience for comparisons and
sorts, but can also lead to a performance penalty as a collation may
need to make additional checks on a string.

### Most-common Value Extended Statistics

`CREATE STATISTICS`, introduced in PostgreSQL 10 to help collect more
complex statistics over multiple columns to improve query planning, now
supports most-common value statistics. This leads to improved query
plans for distributions that are non-uniform.

### Generated Columns

PostgreSQL 12 allows the creation of generated columns that compute
their values with an expression using the contents of other columns.
This feature provides stored generated columns, which are computed on
inserts and updates and are saved on disk. Virtual generated columns,
which are computed only when a column is read as part of a query, are
not implemented yet.

### Pluggable Table Storage Interface

PostgreSQL 12 introduces the pluggable table storage interface that
allows for the creation and use of different methods for table storage.
New access methods can be added to a PostgreSQL cluster using the
`CREATE ACCESS METHOD command and subsequently added to tables with the
new `USING` clause on `CREATE TABLE`.

A table storage interface can be defined by creating a new table access
method:

https://www.postgresql.org/docs/devel/tableam.html

In PostgreSQL 12, the storage interface that is used by default is the
`heap` access method, which is currently is the only built-in method.

### Page Checksums

The `pg_verify_checkums` command has been renamed to pg_checksums and
now supports the ability to enable and disable page checksums across a
PostgreSQL cluster that is offline. Previously, page checksums could
only be enabled during the initialization of a cluster with `initdb`.

### Authentication & Connection Security

GSSAPI now supports client-side and server-side encryption and can be
specified in the `pg_hba.conf` file using the `hostgssenc` and
`hostnogssenc` record types. PostgreSQL 12 also allows for discovery of
LDAP servers based on `DNS SRV` records if PostgreSQL was compiled with
OpenLDAP.

Noted Behavior Changes
----------------------

There are several changes introduced in PostgreSQL 12 that can affect
the behavior as well as management of your ongoing operations. A few of
these are noted below; for information about other changes, please
review the "Migrating to Version 12" section of the release notes:

https://www.postgresql.org/docs/devel/release-12.html

1. The `recovery.conf` configuration file is now merged into the main
`postgresql.conf` file. PostgreSQL will not start if it detects that
`recovery.conf` is present. To put PostgreSQL into a non-primary mode,
you can use the `recovery.signal` and the `standby.signal` files.

You can read more about archive recovery here:

https://www.postgresql.org/docs/devel/runtime-config-wal.html#RUNTIME-CONFIG-WAL-ARCHIVE-RECOVERY

2. Just-in-Time (JIT) compilation is now enabled by default.

3. OIDs can no longer be added to user created tables using the `WITH
OIDs` clause. Operations on tables that have columns that were created
using `WITH OIDS` (i.e. columns named "OID") will need to be adjusted.

Running a `SELECT *` command on a system table will now also output the
OID for the rows in the system table as well, instead of the old
behavior which required the OID column to be specified explicitly.

Additional Features
-------------------

Many other new features and improvements have been added to PostgreSQL
12, some of which may be as or more important to specific users than
what is mentioned above. Please see the Release Notes for a complete
list of new and changed features.

Testing for Bugs & Compatibility
--------------------------------

The stability of each PostgreSQL release greatly depends on you, the
community, to test the upcoming version with your workloads and testing
tools in order to find bugs and regressions before the general
availability of PostgreSQL 12. As this is a Beta, minor changes to
database behaviors, feature details, and APIs are still possible. Your
feedback and testing will help determine the final tweaks on the new
features, so please test in the near future. The quality of user testing
helps determine when we can make a final release.

A list of open issues is publicly available in the PostgreSQL wiki. You
can report bugs using this form on the PostgreSQL website:

https://www.postgresql.org/account/submitbug/

Beta Schedule
-------------

This is the first beta release of version 12. The PostgreSQL Project
will release additional betas as required for testing, followed by one
or more release candidates, until the final release in late 2019. For
further information please see the Beta Testing page:

https://www.postgresql.org/developer/beta/

Links
-----

* Download: https://www.postgresql.org/download/
* Beta Testing Information: https://www.postgresql.org/developer/beta/
* PostgreSQL 12 Beta Release Notes:
https://www.postgresql.org/docs/devel/release-12.html
* PostgreSQL 12 Open Issues:
https://wiki.postgresql.org/wiki/PostgreSQL_12_Open_Items
* Submit a Bug: https://www.postgresql.org/account/submitbug/