PostgreSQL 12: Feature Highlights

Hi,

Now that a draft of the release notes are available[1] this seems like a
good time to begin determining what features we want to highlight prior
to the Beta 1 announcement. First, a small editorial :)

Reading through the list a few times, it is quite impressive the breadth
of features that are available for PostgreSQL 12 and the impact they can
have on our user workloads. I think this is very exciting and I think
our users will be very impressed with this release :) It also presents
some challenges for coming up with features to highlight, but I call
this a "good problem."

(I am less inclined to "trim the list" for the sake of doing so for a
Beta 1 announcement, as based on an analysis of the data, often what
people read are the announcements itself and not the release notes, so
trying to get as much info in front of people without making it too
tedious is the goal.)

Knowing that the target audience of the announcements are users of
PostgreSQL, and knowing the main goals of the beta announcement is to
both make people aware of features and to encourage testing, I think we
need to divide things into a few groups:

- Feature Highlights
- Changes that could affect existing operating environments

Also note below that the way I am listing them out does not constitute a
rank order as this list is just an initial compilation.

With further ado...

# Feature Highlights

1. Partitioning Improvements

- Performance, e.g. enhanced partition pruning, COPY performance, ATTACH
PARTITION
- Foreign Keys
- Partition bounds now support expressions

2. Query parallelism is now supported in SERIALIZABLE transaction mode

3. Indexing

- Improvements overall performance to standard (B-tree) indexes with
writes as well as with bloat
- REINDEX CONCURRENTLY
- GiST indexes now support covering indexes (INCLUDE clause)
- SP-GiST indexes now support K-NN queries
- WAL overhead reduced on GiST, GIN, & SP-GiST index creation

4. CREATE STATISTICS now supports most-common value statistics, which
leads to improved query plans for distributions that are non-uniform

5. WITH queries (CTEs) can now be inlined, subject to certain restrictions

6. Support for JSON path queries per the SQL/JSON standard as well as
support for indexing on equality expressions

7. Introduction of generated columns that compute and store an
expression as a value on the table

8. Enable / disable page checksums for an offline cluster

9. Authentication

- GSSAPI client/server encryption support
- LDAP server discovery

10. Introduction of CREATE ACCESS METHOD that permits the addition of
new table storage types

# Changes That Can Affect Existing Operating Environments

1. recovery.conf merged into postgresql.conf;
recovery.signal/standby.signal being used for switching into non-primary
mode

2. JIT enabled by default

As always, constructive feedback welcome. With the goal in mind that
this will be turned into a Beta 1 announcement, please indicate if you
believe something is missing or if something does not belong on this list.

Thanks!

Jonathan

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

On 2019-05-12 8:28 a.m., Jonathan S. Katz wrote:
> 7. Introduction of generated columns that compute and store an
> expression as a value on the table

It seems to me that one of the things that's valuable about this feature is that
one can make as regular visible data any calculated-from-a-row value that is
used for indexing purposes rather than that being hidden or not possible.  I
assume or hope that generated columns can be used in all index types like
regular columns right? -- Darren Duncan

On Mon, 13 May 2019 at 03:28, Jonathan S. Katz <[hidden email]> wrote:
> - Performance, e.g. enhanced partition pruning, COPY performance, ATTACH

I don't think it's very accurate to say that the performance of
partition pruning has been improved. Really the improvement there is
due to the change in the order of operations, where we now perform
pruning before fetching partition meta-data. Pruning itself, I don't
believe became any faster in PG12. There were, however various tweaks
to improve performance of some operations around run-time partition
pruning both in the planner and during execution, these, however, are
not improvements to pruning itself, but more the operations around
setting up pruning and handling what happens after pruning takes
place. Bruce has now changed the release notes to mention "Improve
performance of many operations on partitioned tables", which seems
like a more accurate generalisation of what was improved, although, I
still think it's overly vague.

--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

On 2019-05-13 09:47, David Rowley wrote:
Sounds like "partition pruning is now more efficient".  eg less memory
usage (?), with a side effect of better performance leading from that
(?)

On Mon, 13 May 2019 at 13:50, Justin Clift <[hidden email]> wrote:
I think the headline item needs to be about the fact that partitioning
can now more easily handle larger numbers of partitions. To say
pruning is more efficient is just a chapter in the story. The big
users of partitioning want and need the entire book.

Perhaps something along the lines of:

- Improve optimizer and executor to allow them to more easily handle
larger numbers of partitions.
- Allow ATTACH PARTITION to work without blocking concurrent DML.

--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Hi David,

On 2019/05/13 11:19, David Rowley wrote:
It's true that optimizer and executor can now handle larger number of
partitions efficiently, but the improvements in this release will only be
meaningful to workloads where partition pruning is crucial, so I don't see
why mentioning "pruning" is so misleading.  Perhaps, it would be slightly
misleading to not mention it, because readers might think that queries
like this one:

  select count(*) from partitioned_table;

are now faster in v12, whereas AFAIK, they perform perform more or less
the same as in v11.

Thanks,
Amit

On Sun, May 12, 2019 at 6:28 PM Jonathan S. Katz <[hidden email]> wrote:
Support for JSON path queries per the SQL/JSON specification in SQL-2016, which
can be accelerated by existing (on disk) indexes.


--
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

On Mon, 13 May 2019 at 18:37, Amit Langote
<[hidden email]> wrote:
This is true, but whether partitions are pruned or not is only
relevant to one of the many items the headline feature is talking
about. I'm not sure how you'd briefly enough mention that fact without
going into detail about which features are which and which are
affected by partition pruning.

I think these are the sorts of details that can be mentioned away from
the headline features, which is why I think lumping these all in one
in the main release notes is a bad idea as it's pretty hard to do that
when they're all lumped in as one item.

--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

On 2019-05-12 17:28, Jonathan S. Katz wrote:
> - Partition bounds now support expressions

This is relatively trivial compared to the rest and probably doesn't
belong in a highlights list.

--
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 2019-05-12 23:04, Darren Duncan wrote:
> On 2019-05-12 8:28 a.m., Jonathan S. Katz wrote:
>> 7. Introduction of generated columns that compute and store an
>> expression as a value on the table
>
> It seems to me that one of the things that's valuable about this feature is that
> one can make as regular visible data any calculated-from-a-row value that is
> used for indexing purposes rather than that being hidden or not possible.  I
> assume or hope that generated columns can be used in all index types like
> regular columns right? -- Darren Duncan

The answer to your question is that, yes, they can, but I don't
understand what your first sentence is trying to say.

--
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 5/13/19 7:39 AM, Peter Eisentraut wrote:
> On 2019-05-12 17:28, Jonathan S. Katz wrote:
>> - Partition bounds now support expressions
>
> This is relatively trivial compared to the rest and probably doesn't
> belong in a highlights list.

Why? This is incredibly helpful from a development standpoint; it
greatly expands the possibilities of the types of partition bounds that
can be utilized.

Jonathan

On 2019-05-13 14:54, Jonathan S. Katz wrote:
How so?

--
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 2019-05-12 17:28, Jonathan S. Katz wrote:
> # Feature Highlights

The ability to create case-insensitive and accent-insensitive collations
is probably of interest to many users.

--
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Mon, May 13, 2019 at 9:22 AM Peter Eisentraut
<[hidden email]> wrote:
> On 2019-05-12 17:28, Jonathan S. Katz wrote:
> > # Feature Highlights
>
> The ability to create case-insensitive and accent-insensitive collations
> is probably of interest to many users.

+1

--
Peter Geoghegan

El 2019-05-13 12:23, Peter Geoghegan escribió: +1

Rgds,
Gilberto Castillo

Hi,

On 2019-05-13 08:54:47 -0400, Jonathan S. Katz wrote:
> On 5/13/19 7:39 AM, Peter Eisentraut wrote:
> > On 2019-05-12 17:28, Jonathan S. Katz wrote:
> >> - Partition bounds now support expressions
> >
> > This is relatively trivial compared to the rest and probably doesn't
> > belong in a highlights list.

+1


> Why? This is incredibly helpful from a development standpoint; it
> greatly expands the possibilities of the types of partition bounds that
> can be utilized.

You can say that about a lot of features. But we've limited space in the
top items... It doesn't strike me as a enabling that many cases.

Greetings,

Andres Freund

On 5/13/19 12:25 PM, [hidden email] wrote: That sounds like a great feature and one we should promote, sorry I
missed it on my pass in the release notes. Where I can find more
information on it?

Thanks,

Jonathan

On 5/13/19 12:37 PM, Andres Freund wrote: I've been bit by it when trying to create some partitions on my own, but
that's not a good enough reason when there are other things to highlight.

With grouping things together, it could be something that's mentioned in
passing -- it's not a bullet point on its own for sure. However, if the
feeling is to drop it completely, I can drop it completely --  I don't
feel strongly enough to argue for it.

Thanks,

Jonathan

On 2019-05-13 19:19, Jonathan S. Katz wrote:
https://www.postgresql.org/docs/devel/collation.html#COLLATION-NONDETERMINISTIC

That chapter might need to be reorganized a bit, because it's difficult
to get a direct link to this.

--
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Mon, May 13, 2019 at 10:50:59PM +1200, David Rowley wrote:
I think the point is that partition pruning and tuple _routing_ to the
right partition is also improved.  I updated the release note items to
say:

        Tables with thousands of child partitions can now be processed
        efficiently.

--
  Bruce Momjian  <[hidden email]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +