Thread: 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 <jkatz@postgresql.org> 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:
> On Mon, 13 May 2019 at 03:28, Jonathan S. Katz <jkatz@postgresql.org> 
> 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.

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 <justin@postgresql.org> wrote:
>
> On 2019-05-13 09:47, David Rowley wrote:
> > On Mon, 13 May 2019 at 03:28, Jonathan S. Katz <jkatz@postgresql.org>
> > 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.
>
> Sounds like "partition pruning is now more efficient".  eg less memory
> usage (?), with a side effect of better performance leading from that
> (?)

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:
> On Mon, 13 May 2019 at 13:50, Justin Clift <justin@postgresql.org> wrote:
>>
>> On 2019-05-13 09:47, David Rowley wrote:
>>> On Mon, 13 May 2019 at 03:28, Jonathan S. Katz <jkatz@postgresql.org>
>>> 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.
>>
>> Sounds like "partition pruning is now more efficient".  eg less memory
>> usage (?), with a side effect of better performance leading from that
>> (?)
> 
> 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.

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 <jkatz@postgresql.org> wrote:
>
> 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

Support for JSON path queries per the SQL/JSON specification in SQL-2016, which
can be accelerated by existing (on disk) indexes.

>
> 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
>


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

On Mon, 13 May 2019 at 18:37, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> 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.

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:
> 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.

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
<peter.eisentraut@2ndquadrant.com> 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ó:
> On Mon, May 13, 2019 at 9:22 AM Peter Eisentraut
> <peter.eisentraut@2ndquadrant.com> 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
+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, gilberto.castillo@etecsa.cu wrote:
> El 2019-05-13 12:23, Peter Geoghegan escribió:
>> On Mon, May 13, 2019 at 9:22 AM Peter Eisentraut
>> <peter.eisentraut@2ndquadrant.com> 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
> +1

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:
> 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.

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:
> On 5/13/19 12:25 PM, gilberto.castillo@etecsa.cu wrote:
>> El 2019-05-13 12:23, Peter Geoghegan escribió:
>>> On Mon, May 13, 2019 at 9:22 AM Peter Eisentraut
>>> <peter.eisentraut@2ndquadrant.com> 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
>> +1
> 
> 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?

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:
> On Mon, 13 May 2019 at 18:37, Amit Langote
> <Langote_Amit_f8@lab.ntt.co.jp> 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.
> 
> 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.

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  <bruce@momjian.us>        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 +

On 2019/05/14 11:59, Bruce Momjian wrote:
> On Mon, May 13, 2019 at 10:50:59PM +1200, David Rowley wrote:
>> On Mon, 13 May 2019 at 18:37, Amit Langote
>> <Langote_Amit_f8@lab.ntt.co.jp> 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.
>>
>> 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.
> 
> 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.

Considering the quoted discussion here, maybe it's a good idea to note
that only the operations that need to touch a small number of partitions
are now processed efficiently, which covers both SELECT/UPDATE/DELETE that
benefit from improved pruning efficiency and INSERT that benefit from
improved tuple routing efficiency.  So, maybe:

    Tables with thousands of child partitions can now be processed
    efficiently by operations that only need to touch a small number
        of partitions.

That is, as I mentioned above, as opposed to queries that need to process
all partitions (such as, select count(*) from partitioned_table), which
don't perform any faster in v12 than in v11.  The percentage of users who
run such workloads on PostgreSQL may be much smaller today, but perhaps
it's not a good idea to mislead them into thinking that *everything* with
partitioned tables is now faster even with thousands of partitions.

Thanks,
Amit

On Tue, May 14, 2019 at 06:25:43PM +0900, Amit Langote wrote:
> Considering the quoted discussion here, maybe it's a good idea to note
> that only the operations that need to touch a small number of partitions
> are now processed efficiently, which covers both SELECT/UPDATE/DELETE that
> benefit from improved pruning efficiency and INSERT that benefit from
> improved tuple routing efficiency.  So, maybe:
> 
>     Tables with thousands of child partitions can now be processed
>     efficiently by operations that only need to touch a small number
>         of partitions.
> 
> That is, as I mentioned above, as opposed to queries that need to process
> all partitions (such as, select count(*) from partitioned_table), which
> don't perform any faster in v12 than in v11.  The percentage of users who
> run such workloads on PostgreSQL may be much smaller today, but perhaps
> it's not a good idea to mislead them into thinking that *everything* with
> partitioned tables is now faster even with thousands of partitions.

Agreed, I changed it to your wording.

-- 
  Bruce Momjian  <bruce@momjian.us>        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 +

On 2019/05/14 22:19, Bruce Momjian wrote:
> On Tue, May 14, 2019 at 06:25:43PM +0900, Amit Langote wrote:
>> Considering the quoted discussion here, maybe it's a good idea to note
>> that only the operations that need to touch a small number of partitions
>> are now processed efficiently, which covers both SELECT/UPDATE/DELETE that
>> benefit from improved pruning efficiency and INSERT that benefit from
>> improved tuple routing efficiency.  So, maybe:
>>
>>     Tables with thousands of child partitions can now be processed
>>     efficiently by operations that only need to touch a small number
>>         of partitions.
>>
>> That is, as I mentioned above, as opposed to queries that need to process
>> all partitions (such as, select count(*) from partitioned_table), which
>> don't perform any faster in v12 than in v11.  The percentage of users who
>> run such workloads on PostgreSQL may be much smaller today, but perhaps
>> it's not a good idea to mislead them into thinking that *everything* with
>> partitioned tables is now faster even with thousands of partitions.
> 
> Agreed, I changed it to your wording.

Thank you.

Regards,
Amit

Hi,

On 5/12/19 11:28 AM, Jonathan S. Katz wrote:
> 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.

Thank you everyone for your feedback. Below is v2 of the list for the
Beta 1 announcement. A few things to note:

- Based on feedback I made several INSERT/UPDATE/DELETE operations on
the list ;)

- I know it's listing out a lot of features, but this is the Beta 1
release, which, among several goals, is to make people aware of as many
impactful features as possible for purposes of awareness and getting
people to help test.

- The press release will be better worded - this is just a list :)

Without further ado:

# Feature Highlights

1. 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

2. Partitioning Improvements

- Improved partition pruning, which improves performance on queries over
tables with thousands of partitions that only need to use a few partitions
- Improvements to COPY performance and ATTACH PARTITION
- Allow foreign keys to reference partitioned tables

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

4. Support for JSON path queries per the SQL/JSON specification in the
SQL:2016 standard. A subset of these expressions can be accelerated with
on-disk indexes.

5. Support for case-insensitive and accent-insensitive collations

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

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

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

9. Enable / disable page checksums for an offline cluster

10. Authentication

- GSSAPI client/server encryption support
- LDAP server discovery

# 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

Thanks,

Jonathan

Hi Jonathan,

Thanks for the updated draft.

On 2019/05/15 11:03, Jonathan S. Katz wrote:
> Without further ado:
> 
> # Feature Highlights
> 
> 1. 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
> 
> 2. Partitioning Improvements
> 
> - Improved partition pruning, which improves performance on queries over
> tables with thousands of partitions that only need to use a few partitions
> - Improvements to COPY performance and ATTACH PARTITION
> - Allow foreign keys to reference partitioned tables

About the 1st item in "Partitioning Improvements", it's not just partition
pruning that's gotten better.  How about writing as:

 - Improved performance of processing tables with thousands of partitions
   for operations that only need to touch a small number of partitions

Per discussion upthread, that covers improvements to both partition
pruning and tuple routing.

Also, could the title "2. Partitioning Improvements" be trimmed down to
"2. Partitioning", to look like "1. Indexing" for consistency?

Thanks,
Amit

On Wed, 15 May 2019 at 14:31, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:
>
> On 2019/05/15 11:03, Jonathan S. Katz wrote:
> > 2. Partitioning Improvements
> >
> > - Improved partition pruning, which improves performance on queries over
> > tables with thousands of partitions that only need to use a few partitions
> > - Improvements to COPY performance and ATTACH PARTITION
> > - Allow foreign keys to reference partitioned tables
>
> About the 1st item in "Partitioning Improvements", it's not just partition
> pruning that's gotten better.  How about writing as:
>
>  - Improved performance of processing tables with thousands of partitions
>    for operations that only need to touch a small number of partitions
>
> Per discussion upthread, that covers improvements to both partition
> pruning and tuple routing.

+1. Amit's words look fine to me.   I think if we're including this as
a headline feature then it should be to inform people that
partitioning is generally more useable with larger numbers of
partitions, not just that SELECT/UPDATE/DELETE now perform better when
pruning many partitions. The work done there and what's done to speed
up tuple routing in INSERT/COPY both complement each other, so they're
likely good to keep as one in the headline features list. Mentioning
one without the other might leave some people guessing about if we've
addressed the other deficiencies with partitioning performance.
Sadly, they can't refer to the release notes for more information
since they don't detail what's changed in this area :(

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

Hi David,

On 2019/05/15 11:59, David Rowley wrote:
> Sadly, they can't refer to the release notes for more information
> since they don't detail what's changed in this area :(

Bruce seemed to insist on summarizing all the performance-related
improvements into one item.  Initial wording mentioned just pruning, which
after some back and forth has finally been turned into:

  Improve performance of many operations on partitioned tables
  (Amit Langote, David Rowley, Tom Lane, Álvaro Herrera)

  Tables with thousands of child partitions can now be processed
  efficiently by operations that only need to touch a small number of
  partitions.

I hear you saying that the description may be too vague *for release
notes* even though it now covers *all* the performance improvements made
to address the use cases where small number of partitions are touched.
Maybe, you're saying that we should've mentioned individual items
(partition pruning, tuple routing, etc.) at least in the release notes,
which I did suggest on -committers [1], but maybe Bruce didn't think it
was necessary to list up individual items.

That leaves a few other commits mentioned in the release-12.sgml next to
this item that are really not related to this headline description, which
both you and I have pointed out in different threads [2][3], but I haven't
really caught Bruce's position about them.

Thanks,
Amit

[1]
https://www.postgresql.org/message-id/d5267ae5-bd4a-3e96-c21b-56bfa9fec7e8%40lab.ntt.co.jp

[2]
https://www.postgresql.org/message-id/3f0333be-fd32-55f2-9817-5853a6bbd233%40lab.ntt.co.jp

[3]
https://www.postgresql.org/message-id/CAKJS1f8R6DC45bauzeGF-QMaQ90B_NFSJB9mvVOuhKVDkajehg%40mail.gmail.com

On Wed, May 15, 2019 at 05:36:32PM +0900, Amit Langote wrote:
> Hi David,
> 
> On 2019/05/15 11:59, David Rowley wrote:
> > Sadly, they can't refer to the release notes for more information
> > since they don't detail what's changed in this area :(
> 
> Bruce seemed to insist on summarizing all the performance-related
> improvements into one item.  Initial wording mentioned just pruning, which
> after some back and forth has finally been turned into:
> 
>   Improve performance of many operations on partitioned tables
>   (Amit Langote, David Rowley, Tom Lane, Álvaro Herrera)
> 
>   Tables with thousands of child partitions can now be processed
>   efficiently by operations that only need to touch a small number of
>   partitions.
> 
> I hear you saying that the description may be too vague *for release
> notes* even though it now covers *all* the performance improvements made
> to address the use cases where small number of partitions are touched.
> Maybe, you're saying that we should've mentioned individual items
> (partition pruning, tuple routing, etc.) at least in the release notes,
> which I did suggest on -committers [1], but maybe Bruce didn't think it
> was necessary to list up individual items.
> 
> That leaves a few other commits mentioned in the release-12.sgml next to
> this item that are really not related to this headline description, which
> both you and I have pointed out in different threads [2][3], but I haven't
> really caught Bruce's position about them.

I think the more specific we make the partition description, the more
limited it will appear to be.  I think almost all partition operations
will appear to be faster with PG 12, even if users can't articulate
exactly why.

-- 
  Bruce Momjian  <bruce@momjian.us>        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 +

On 2019-May-16, Bruce Momjian wrote:

> I think the more specific we make the partition description, the more
> limited it will appear to be.  I think almost all partition operations
> will appear to be faster with PG 12, even if users can't articulate
> exactly why.

I don't understand why you want to make things such black boxes.  It is
what it is, and some users (not all) will want to understand.  People
who have already tested pg11 and found it insufficient may give pg12 a
second look if we tell them we have fixed the deficiencies they found.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 2019-05-16 16:08, Alvaro Herrera wrote:
> On 2019-May-16, Bruce Momjian wrote:
>
>> I think the more specific we make the partition description, the more
>> limited it will appear to be.  I think almost all partition operations
>> will appear to be faster with PG 12, even if users can't articulate
>> exactly why.
> I don't understand why you want to make things such black boxes.  It is
> what it is, and some users (not all) will want to understand.  People
> who have already tested pg11 and found it insufficient may give pg12 a
> second look if we tell them we have fixed the deficiencies they found.

yep, I agree with Alvaro, I'm one of these users...

On Thu, May 16, 2019 at 12:08 PM Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:
> I don't understand why you want to make things such black boxes.  It is
> what it is, and some users (not all) will want to understand.  People
> who have already tested pg11 and found it insufficient may give pg12 a
> second look if we tell them we have fixed the deficiencies they found.

+1. The major features list should be accessible, but otherwise I
think that we should add more detail.

80% - 90% of the items listed in the release notes aren't interesting
to users, but *which* 80% - 90% it is varies. The release notes don't
have to function as a press release, because we'll have an actual
press release instead.

-- 
Peter Geoghegan

On Thu, May 16, 2019 at 12:35:25PM -0700, Peter Geoghegan wrote:
> On Thu, May 16, 2019 at 12:08 PM Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
>> I don't understand why you want to make things such black boxes.  It is
>> what it is, and some users (not all) will want to understand.  People
>> who have already tested pg11 and found it insufficient may give pg12 a
>  second look if we tell them we have fixed the deficiencies they found.
>
> +1. The major features list should be accessible, but otherwise I
> think that we should add more detail.

+1.  Another thing which may matter is that if some people do not see
some improvements they were expecting then they can come back to pg13
and help around with having these added.  Things are never going to be
perfect for all users, and it makes little sense to make it sound like
things are actually perfect.
--
Michael

On Thu, May 16, 2019 at 4:52 PM Michael Paquier <michael@paquier.xyz> wrote:
> +1.  Another thing which may matter is that if some people do not see
> some improvements they were expecting then they can come back to pg13
> and help around with having these added.  Things are never going to be
> perfect for all users, and it makes little sense to make it sound like
> things are actually perfect.

Speaking of imperfections: it is well known that most performance
enhancements have the potential to hurt certain workloads in order to
help others. Maybe that was explicitly considered to be a good
trade-off when the feature went in, and maybe that was the right
decision at the time, but more often it was something that was simply
overlooked. There is value in leaving information that helps users
discover where a problem may have been introduced -- they can tell us
about it, and we can fix it.

I myself sometimes look through the release notes in an effort to spot
something that might have had undesirable side-effects, especially in
areas of the code that I am less knowledgeable about, or have only a
vague recollection of. Sometimes that is a reasonable starting point.

-- 
Peter Geoghegan

On Thu, May 16, 2019 at 03:08:06PM -0400, Alvaro Herrera wrote:
> On 2019-May-16, Bruce Momjian wrote:
> 
> > I think the more specific we make the partition description, the more
> > limited it will appear to be.  I think almost all partition operations
> > will appear to be faster with PG 12, even if users can't articulate
> > exactly why.
> 
> I don't understand why you want to make things such black boxes.  It is
> what it is, and some users (not all) will want to understand.  People
> who have already tested pg11 and found it insufficient may give pg12 a
> second look if we tell them we have fixed the deficiencies they found.

The release notes are written so it is clear to the average reader, and
is as short as it can be while remaining clear.  If there is some other
criteria the community wants to use, I suggest you start a thread on the
hackers list.

-- 
  Bruce Momjian  <bruce@momjian.us>        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 +

On Fri, May 17, 2019 at 08:52:50AM +0900, Michael Paquier wrote:
> On Thu, May 16, 2019 at 12:35:25PM -0700, Peter Geoghegan wrote:
> > On Thu, May 16, 2019 at 12:08 PM Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> >> I don't understand why you want to make things such black boxes.  It is
> >> what it is, and some users (not all) will want to understand.  People
> >> who have already tested pg11 and found it insufficient may give pg12 a
> >  second look if we tell them we have fixed the deficiencies they found.
> > 
> > +1. The major features list should be accessible, but otherwise I
> > think that we should add more detail.
> 
> +1.  Another thing which may matter is that if some people do not see
> some improvements they were expecting then they can come back to pg13
> and help around with having these added.  Things are never going to be
> perfect for all users, and it makes little sense to make it sound like
> things are actually perfect.

What changes in the current description would cause people not to retest
to see if it is better?  If anything, the description is too broad.

-- 
  Bruce Momjian  <bruce@momjian.us>        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 +

On Thu, May 16, 2019 at 05:05:13PM -0700, Peter Geoghegan wrote:
> On Thu, May 16, 2019 at 4:52 PM Michael Paquier <michael@paquier.xyz> wrote:
> > +1.  Another thing which may matter is that if some people do not see
> > some improvements they were expecting then they can come back to pg13
> > and help around with having these added.  Things are never going to be
> > perfect for all users, and it makes little sense to make it sound like
> > things are actually perfect.
> 
> Speaking of imperfections: it is well known that most performance
> enhancements have the potential to hurt certain workloads in order to
> help others. Maybe that was explicitly considered to be a good
> trade-off when the feature went in, and maybe that was the right
> decision at the time, but more often it was something that was simply
> overlooked. There is value in leaving information that helps users
> discover where a problem may have been introduced -- they can tell us
> about it, and we can fix it.
> 
> I myself sometimes look through the release notes in an effort to spot
> something that might have had undesirable side-effects, especially in
> areas of the code that I am less knowledgeable about, or have only a
> vague recollection of. Sometimes that is a reasonable starting point.

The release notes are written for the _average_ reader.  The commits are
there as comments and making them more visible would be a nice
improvement.

We have already adjusted the partition item, and the btree item.  If
there is more detail people want, I suggest you post something to the
hackers list and we will see if can be added in a consistent way.  You
must also get agreement that the rules I am using need to be adjusted so
all future release notes will have that consistency.

-- 
  Bruce Momjian  <bruce@momjian.us>        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 +

On Thu, May 16, 2019 at 6:32 PM Bruce Momjian <bruce@momjian.us> wrote:
> The release notes are written for the _average_ reader.  The commits are
> there as comments and making them more visible would be a nice
> improvement.

I don't think that making it possible to read the release notes from
start to finish is a useful goal. Users probably just skim them. I
certainly don't read them from start to finish, nor do I expect to
understand every item.

> We have already adjusted the partition item, and the btree item.

I was making a general point. This comes up every year.

> If there is more detail people want, I suggest you post something to the
> hackers list and we will see if can be added in a consistent way.

I think that it should be discussed at the developer meeting.

-- 
Peter Geoghegan

On Thu, May 16, 2019 at 06:49:29PM -0700, Peter Geoghegan wrote:
> On Thu, May 16, 2019 at 6:32 PM Bruce Momjian <bruce@momjian.us> wrote:
> > The release notes are written for the _average_ reader.  The commits are
> > there as comments and making them more visible would be a nice
> > improvement.
> 
> I don't think that making it possible to read the release notes from
> start to finish is a useful goal. Users probably just skim them. I
> certainly don't read them from start to finish, nor do I expect to
> understand every item.

I think we expect people to read them fully because how would they know
what changed and what new features were added.  I do think allowing more
detail by showing the commit messages would be helpful for people who
want to drill down on an item.

> > We have already adjusted the partition item, and the btree item.
> 
> I was making a general point. This comes up every year.
> 
> > If there is more detail people want, I suggest you post something to the
> > hackers list and we will see if can be added in a consistent way.
> 
> I think that it should be discussed at the developer meeting.

OK, that makes sense.

-- 
  Bruce Momjian  <bruce@momjian.us>        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 +

On 2019-May-16, Bruce Momjian wrote:

> The release notes are written for the _average_ reader.

I disagree with this assertion, and frankly I cannot understand why you
think that's the most useful thing to do.  The release notes are not a
press release, where you have to make things pretty or understandable to
everyone.  Users can skip items they don't understand or don't care
about; but would at least be given the option.  If we don't document,
we're making the decision for them that they must not care.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Thu, May 16, 2019 at 10:21:35PM -0400, Bruce Momjian wrote:
> I think we expect people to read them fully because how would they know
> what changed and what new features were added.  I do think allowing more
> detail by showing the commit messages would be helpful for people who
> want to drill down on an item.

This point is interesting.  The release notes include in the sgml
sources references to the related commits.  Could it make sense to
have links to the main commits of a feature which can be clicked on
directly from the html docs or such?  I understand that producing the
notes is a daunting task already, and Bruce does an awesome job.  Just
wondering if we could make that automated in some way if that helps
with the user experience...  Anyway, this is unrelated to the topic of
this thread.  My apologies for the digression.
--
Michael

On 2019/05/17 3:26, Bruce Momjian wrote:
> I think the more specific we make the partition description, the more
> limited it will appear to be.  I think almost all partition operations
> will appear to be faster with PG 12, even if users can't articulate
> exactly why.

I agree that the current description captures at a high level the many
changes that made it possible.  Although, a couple of commits listed with
this item don't have much to do with that description, AFAICT.  Especially
959d00e9d [1], which taught the planner to leverage the ordering imposed
on partitions by range partitioning.  With that commit, getting ordered
output from partitioned tables is now much quicker, especially with a
LIMIT clause.  You can tell that it sounds clearly unrelated to the
description we have now, which is "processing thousands of partitions is
quicker when only small numbers of partitions are touched".  Some users of
partitioning may not be interested in the use case described as vastly
improved, but they may be delighted to hear about items such as the
aforementioned commit.  Also, I suspect that the users whose use cases
pushed them to use partitioning in the first place may also be the ones
who do some of their own research about partitioning and eventually know
many optimizations that are possible with it.  So, it's perhaps a good
idea to let them know about such optimizations through release notes if
it's the only place to put them, which I believe is the case with this
particular item.  There are not that many commits to be taken out of the
existing item and described separately, just this one I think.

That said, my intention is only to point out that the commit is being
mixed with an unrelated item.  Whether or not to list it as a separate
item, I can only give my vote in its favor.

Thanks,
Amit

[1] [959d00e9d] Use Append rather than MergeAppend for scanning ordered

On Thu, May 16, 2019 at 10:34:13PM -0400, Alvaro Herrera wrote:
> On 2019-May-16, Bruce Momjian wrote:
> 
> > The release notes are written for the _average_ reader.
> 
> I disagree with this assertion, and frankly I cannot understand why you
> think that's the most useful thing to do.  The release notes are not a
> press release, where you have to make things pretty or understandable to
> everyone.  Users can skip items they don't understand or don't care
> about; but would at least be given the option.  If we don't document,
> we're making the decision for them that they must not care.

The press release is not an exhaustive list of all features, so we can't
just fall back on the press release as a way for non-internals readers
to understand all the features in this release.

Frankly, when I am reading a document, if I hit a few items I don't
understand, I stop reading.  This is why I tend to write in a
generally-accessible level of detail.  You can see this in all my
writings, e.g., blogs.  I don't know how to write differently without
feeling I am being inconsiderate to the reader.

Also, when I say I write for the average reader, I write for the average
person who is likely to read the document, not for the average person in
general.

I suggest you look at how Tom Lane writes the minor release notes for an
example that is better or worse than my style.

-- 
  Bruce Momjian  <bruce@momjian.us>        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 +

On Fri, May 17, 2019 at 12:50:12PM +0900, Michael Paquier wrote:
> On Thu, May 16, 2019 at 10:21:35PM -0400, Bruce Momjian wrote:
> > I think we expect people to read them fully because how would they know
> > what changed and what new features were added.  I do think allowing more
> > detail by showing the commit messages would be helpful for people who
> > want to drill down on an item.
> 
> This point is interesting.  The release notes include in the sgml
> sources references to the related commits.  Could it make sense to
> have links to the main commits of a feature which can be clicked on
> directly from the html docs or such?  I understand that producing the
> notes is a daunting task already, and Bruce does an awesome job.  Just
> wondering if we could make that automated in some way if that helps
> with the user experience...  Anyway, this is unrelated to the topic of
> this thread.  My apologies for the digression.

Yes, I am sure it can be done.  The commits are always in a the same
style as output by src/tools/git_changelog, and always to the far left.
This has been discussed before, but no one felt it was urgent enough.

The good news is that once we do this, all release notes, even the
previous ones, will have this feature.

-- 
  Bruce Momjian  <bruce@momjian.us>        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 +

On Fri, May 17, 2019 at 08:14:33AM -0400, Evan Macbeth wrote:
> Long time lurker, first time poster. :) 
> 
> 
>     80% - 90% of the items listed in the release notes aren't interesting
>     to users, but *which* 80% - 90% it is varies. The release notes don't
>     have to function as a press release, because we'll have an actual
>     press release instead.
>     --
>     Peter Geoghegan
> 
> 
> I think Peter's point here is really important. I read the release notes and
> the press releases, both, from a very different perspective than others, but I
> find both very valuable. The release notes benefit from completeness and
> detail, in my observer's opinion.

As I just stated, if the press release was exhaustive, we could have the
release notes be more detailed, but this is not the case.  I don't think
we want to get into a case where the items listed on the preess release
get a different level of detail from the items not listed.


-- 
  Bruce Momjian  <bruce@momjian.us>        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 +

On 5/14/19 10:31 PM, Amit Langote wrote:
> Hi Jonathan,
>
> Thanks for the updated draft.
>
> On 2019/05/15 11:03, Jonathan S. Katz wrote:
>> Without further ado:
>>
>> # Feature Highlights
>>
>> 1. 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
>>
>> 2. Partitioning Improvements
>>
>> - Improved partition pruning, which improves performance on queries over
>> tables with thousands of partitions that only need to use a few partitions
>> - Improvements to COPY performance and ATTACH PARTITION
>> - Allow foreign keys to reference partitioned tables
>
> About the 1st item in "Partitioning Improvements", it's not just partition
> pruning that's gotten better.  How about writing as:
>
>  - Improved performance of processing tables with thousands of partitions
>    for operations that only need to touch a small number of partitions

Thanks, I will use some wording like this:

- Improved performance of processing tables with thousands of partitions
for operations that only need to use a small number of partitions

>
> Per discussion upthread, that covers improvements to both partition
> pruning and tuple routing.
>
> Also, could the title "2. Partitioning Improvements" be trimmed down to
> "2. Partitioning", to look like "1. Indexing" for consistency?

These are just my rough notes and do not reflect how it will read in the
PR itself; however I will keep that in mind.

Thanks,

Jonathan

On 5/17/19 10:19 AM, Bruce Momjian wrote:
> On Fri, May 17, 2019 at 08:14:33AM -0400, Evan Macbeth wrote:
>> Long time lurker, first time poster. :) 
>>
>>
>>     80% - 90% of the items listed in the release notes aren't interesting
>>     to users, but *which* 80% - 90% it is varies. The release notes don't
>>     have to function as a press release, because we'll have an actual
>>     press release instead.
>>     --
>>     Peter Geoghegan
>>
>>
>> I think Peter's point here is really important. I read the release notes and
>> the press releases, both, from a very different perspective than others, but I
>> find both very valuable. The release notes benefit from completeness and
>> detail, in my observer's opinion.
>
> As I just stated, if the press release was exhaustive, we could have the
> release notes be more detailed, but this is not the case.  I don't think
> we want to get into a case where the items listed on the preess release
> get a different level of detail from the items not listed.

To step back, it's important to understand the intended goals of the
press release.

I view the primary goal of the press release for a major version to be a
springboard to further dive into what is in the release. It helps to
give some global context to new features / changes and is an effective
"getting started" point. It needs just enough details for people to be
interested, and if they want more info, they can go to the release notes
+ docs.

The release notes then can provide additional details on what said
features (effectively a readable "diff" between versions) are, and, if
needed, the documentation can provide even greater details.

(The "diff" point is important: multiple times in recent weeks I've had
to refer back to the PG10 notes to highlight how the behavior for set
returning functions changed in the SELECT lists. So having that there
was certainly helpful.)

In my more heavy practitioner days, I needed both to help analyze and
make decisions on say, when to upgrade to a major version, or

Bringing it full circle, I view the primary goal of the Beta 1 press
release to be a springboard to _testing_ the release: we need people to
test to ensure the GA release is as stable as possible. It should be a
bit more exhaustive than the final GA press release - ideally I want
people to think of uses cases they can test the new features in -- but
sure, it should not be too much more exhaustive.

Thanks,

Jonathan

On Fri, May 17, 2019 at 07:56:55PM +0900, Amit Langote wrote:
> On 2019/05/17 3:26, Bruce Momjian wrote:
> > I think the more specific we make the partition description, the more
> > limited it will appear to be.  I think almost all partition operations
> > will appear to be faster with PG 12, even if users can't articulate
> > exactly why.
> 
> I agree that the current description captures at a high level the many
> changes that made it possible.  Although, a couple of commits listed with
> this item don't have much to do with that description, AFAICT.  Especially
> 959d00e9d [1], which taught the planner to leverage the ordering imposed
> on partitions by range partitioning.  With that commit, getting ordered
> output from partitioned tables is now much quicker, especially with a
> LIMIT clause.  You can tell that it sounds clearly unrelated to the
> description we have now, which is "processing thousands of partitions is

Yes, it does.  I added this text and moved the commit item:

        Avoid sorting when partitions are already being scanned in the
        necessary order (David Rowley)

I certainly strugged to understand the maze of commits related to
partitioning.

> quicker when only small numbers of partitions are touched".  Some users of
> partitioning may not be interested in the use case described as vastly
> improved, but they may be delighted to hear about items such as the
> aforementioned commit.  Also, I suspect that the users whose use cases
> pushed them to use partitioning in the first place may also be the ones
> who do some of their own research about partitioning and eventually know
> many optimizations that are possible with it.  So, it's perhaps a good
> idea to let them know about such optimizations through release notes if
> it's the only place to put them, which I believe is the case with this
> particular item.  There are not that many commits to be taken out of the
> existing item and described separately, just this one I think.

Yes.

-- 
  Bruce Momjian  <bruce@momjian.us>        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 +

On Sat, 18 May 2019 at 03:36, Bruce Momjian <bruce@momjian.us> wrote:
> Yes, it does.  I added this text and moved the commit item:
>
>         Avoid sorting when partitions are already being scanned in the
>         necessary order (David Rowley)

Thank you for moving that out.

> I certainly strugged to understand the maze of commits related to
> partitioning.

With the utmost respect, because I certainly do appreciate the work
you did on the release note, I think if that's the case, then it
should only make you more willing to listen to the advice from the
people who are closer to those commits.  However I understand that
consistency is also important, so listening to the heckling of
individuals sometimes won't lead to a good overall outcome.  That
being said, I don't think that's what happened here, as most of the
people who had a gripe about it were pretty close to the work that was
done.

FWIW, I do think the release notes should be meant as a source of
information which give a brief view on changes made that have a
reasonable possibility of affecting people (either negative or
positively) who are upgrading. Leaving out important details because
they might confuse a small group of people seems wrong-headed, as it
means the people who are not in that group are left to look at the
commit history to determine what's changed, or worse, they might just
assume that the thing has not changed which could either cause them to
1) Skip the upgrade because it's not interesting to them, or; 2)
having something break because we didn't list some incompatibility.
I know you know better than most people that extracting a useful
summary from the commit history is a pretty mammoth task, so I doubt
we should put that upon the masses.

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

On Fri, May 17, 2019 at 6:39 PM David Rowley
<david.rowley@2ndquadrant.com> wrote:
> FWIW, I do think the release notes should be meant as a source of
> information which give a brief view on changes made that have a
> reasonable possibility of affecting people (either negative or
> positively) who are upgrading. Leaving out important details because
> they might confuse a small group of people seems wrong-headed

If the only thing that comes out of the developer meeting discussion
item on release notes is that we make the commits behind each listing
*discoverable* from the web, then that will still be a big
improvement. I would prefer it if we went further, but that seems like
it solves a lot of problems without creating new ones.

--
Peter Geoghegan

On Sat, May 18, 2019 at 01:38:49PM +1200, David Rowley wrote:
> On Sat, 18 May 2019 at 03:36, Bruce Momjian <bruce@momjian.us> wrote:
> > Yes, it does.  I added this text and moved the commit item:
> >
> >         Avoid sorting when partitions are already being scanned in the
> >         necessary order (David Rowley)
> 
> Thank you for moving that out.
> 
> > I certainly strugged to understand the maze of commits related to
> > partitioning.
> 
> With the utmost respect, because I certainly do appreciate the work
> you did on the release note, I think if that's the case, then it
> should only make you more willing to listen to the advice from the
> people who are closer to those commits.  However I understand that
> consistency is also important, so listening to the heckling of
> individuals sometimes won't lead to a good overall outcome.  That
> being said, I don't think that's what happened here, as most of the
> people who had a gripe about it were pretty close to the work that was
> done.
> 
> FWIW, I do think the release notes should be meant as a source of
> information which give a brief view on changes made that have a
> reasonable possibility of affecting people (either negative or
> positively) who are upgrading. Leaving out important details because
> they might confuse a small group of people seems wrong-headed, as it
> means the people who are not in that group are left to look at the
> commit history to determine what's changed, or worse, they might just
> assume that the thing has not changed which could either cause them to
> 1) Skip the upgrade because it's not interesting to them, or; 2)
> having something break because we didn't list some incompatibility.
> I know you know better than most people that extracting a useful
> summary from the commit history is a pretty mammoth task, so I doubt
> we should put that upon the masses.

No one has suggested new wording, so I don't know what you are
complaining about.  In fact, the wording we have now is by Amit Langote.

-- 
  Bruce Momjian  <bruce@momjian.us>        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 +

On Sat, 18 May 2019 at 14:30, Bruce Momjian <bruce@momjian.us> wrote:
> No one has suggested new wording, so I don't know what you are
> complaining about.  In fact, the wording we have now is by Amit Langote.

https://www.postgresql.org/message-id/CAKJS1f8R6DC45bauzeGF-QMaQ90B_NFSJB9mvVOuhKVDkajehg@mail.gmail.com

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

On Sat, May 18, 2019 at 11:34:34PM +1200, David Rowley wrote:
> On Sat, 18 May 2019 at 14:30, Bruce Momjian <bruce@momjian.us> wrote:
> > No one has suggested new wording, so I don't know what you are
> > complaining about.  In fact, the wording we have now is by Amit Langote.
> 
> https://www.postgresql.org/message-id/CAKJS1f8R6DC45bauzeGF-QMaQ90B_NFSJB9mvVOuhKVDkajehg@mail.gmail.com

The second item I added yesterday at the prompting of Amit:

    commit 05685897f0
    Author: Bruce Momjian <bruce@momjian.us>
    Date:   Fri May 17 11:31:49 2019 -0400
    
        docs:  split out sort-skip partition item in PG 12 release notes
    
        Discussion:
    https://postgr.es/m/0cf10a27-c6a0-de4a-cd20-ab7493ea7422@lab.ntt.co.jp

    > Avoid sorting when partitions are already being scanned in the
    > necessary order (David Rowley)

The first one needs to reference visible effects, so I would add:

    Consider additional optimizations for queries referencing
    partitioned tables that only affect a single partition

Does that work?

It seems you never got a reply to that email, perhaps because I thought
Amit's next email was covering the same issue.  I am sorry for that.

-- 
  Bruce Momjian  <bruce@momjian.us>        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 +

On Tue, May 14, 2019 at 09:19:45AM -0400, Bruce Momjian wrote:
> On Tue, May 14, 2019 at 06:25:43PM +0900, Amit Langote wrote:
> > Considering the quoted discussion here, maybe it's a good idea to note
> > that only the operations that need to touch a small number of partitions
> > are now processed efficiently, which covers both SELECT/UPDATE/DELETE that
> > benefit from improved pruning efficiency and INSERT that benefit from
> > improved tuple routing efficiency.  So, maybe:
> > 
> >     Tables with thousands of child partitions can now be processed
> >     efficiently by operations that only need to touch a small number
> >         of partitions.
> > 
> > That is, as I mentioned above, as opposed to queries that need to process
> > all partitions (such as, select count(*) from partitioned_table), which
> > don't perform any faster in v12 than in v11.  The percentage of users who
> > run such workloads on PostgreSQL may be much smaller today, but perhaps
> > it's not a good idea to mislead them into thinking that *everything* with
> > partitioned tables is now faster even with thousands of partitions.
> 
> Agreed, I changed it to your wording.

I tightened up the wording on this item, and removed 'touch' since that
could suggest 'write':

        Allow tables with thousands of child partitions to be processed
        efficiently by operations that only affect a small number of
        partitions.

-- 
  Bruce Momjian  <bruce@momjian.us>        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 +

On Sun, 19 May 2019 at 01:20, Bruce Momjian <bruce@momjian.us> wrote:
> The first one needs to reference visible effects, so I would add:
>
>         Consider additional optimizations for queries referencing
>         partitioned tables that only affect a single partition
>
> Does that work?

Thanks, typing that up.  I think it lacks a bit detail about what's
actually changed.  The change is fairly evident and people can see
when it takes effect when they look at EXPLAIN and see that the
Append/MergeAppend node is missing. Also, an Append/MergeAppend may
exist for inheritance tables and an Append can exist for a simple
UNION ALL. Both of those cases can have subplans removed to leave only
a single subplan, to which the additional parallel paths will be
considered.

I think something like:

* Make the optimizer only include an Append/MergeAppend node when
there is more than one subplan to append.

This reduces execution overheads and allows additional plan shapes
that were previously not possible.

or a little more brief:

* Allow the optimizer to consider more plan types by eliminating
single subplan Append and MergeAppends.

I understand that you might be trying to avoid details of plan node
types, but really anyone who's got inheritance or partitioned tables
and has looked at an EXPLAIN should have an idea.

Also, I think Tom should be the main author of this as he rewrote my
version of the patch to such an extent that there was next to nothing
of it left. I just tagged a few extra things on before he committed
it.

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

On 2019/05/18 0:36, Bruce Momjian wrote:
> On Fri, May 17, 2019 at 07:56:55PM +0900, Amit Langote wrote:
>> I agree that the current description captures at a high level the many
>> changes that made it possible.  Although, a couple of commits listed with
>> this item don't have much to do with that description, AFAICT.  Especially
>> 959d00e9d [1], which taught the planner to leverage the ordering imposed
>> on partitions by range partitioning.  With that commit, getting ordered
>> output from partitioned tables is now much quicker, especially with a
>> LIMIT clause.  You can tell that it sounds clearly unrelated to the
>> description we have now, which is "processing thousands of partitions is
> 
> Yes, it does.  I added this text and moved the commit item:
> 
>         Avoid sorting when partitions are already being scanned in the
>         necessary order (David Rowley)

Thank you Bruce.

Regards,
Amit

On Sun, May 19, 2019 at 02:26:48AM +1200, David Rowley wrote:
> On Sun, 19 May 2019 at 01:20, Bruce Momjian <bruce@momjian.us> wrote:
> > The first one needs to reference visible effects, so I would add:
> >
> >         Consider additional optimizations for queries referencing
> >         partitioned tables that only affect a single partition
> >
> > Does that work?
> 
> Thanks, typing that up.  I think it lacks a bit detail about what's
> actually changed.  The change is fairly evident and people can see
> when it takes effect when they look at EXPLAIN and see that the
> Append/MergeAppend node is missing. Also, an Append/MergeAppend may
> exist for inheritance tables and an Append can exist for a simple
> UNION ALL. Both of those cases can have subplans removed to leave only
> a single subplan, to which the additional parallel paths will be
> considered.

This brings up a few points.  First, it seems the change affects
partitioned tables and UNION ALL, which means it probably needs to be
listed in two sections.   Second, is it only parallelism paths that are
added?  I am not sure if people care about a node being removed,
especially when the might not even know we do that step, but they do
care if there are new optimization possibilities.

> I think something like:
> 
> * Make the optimizer only include an Append/MergeAppend node when
> there is more than one subplan to append.
> 
> This reduces execution overheads and allows additional plan shapes
> that were previously not possible.
> 
> or a little more brief:
> 
> * Allow the optimizer to consider more plan types by eliminating
> single subplan Append and MergeAppends.
> 
> I understand that you might be trying to avoid details of plan node
> types, but really anyone who's got inheritance or partitioned tables
> and has looked at an EXPLAIN should have an idea.

I would like to have something that people who don't study EXPLAIN will
still be excited about.

> Also, I think Tom should be the main author of this as he rewrote my
> version of the patch to such an extent that there was next to nothing
> of it left. I just tagged a few extra things on before he committed
> it.

Uh, Tom listed you as author, but I don't have to follow that if you
feel it is inaccurate.

-- 
  Bruce Momjian  <bruce@momjian.us>        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 +

On 2019/05/21 23:55, Bruce Momjian wrote:
> On Sun, May 19, 2019 at 02:26:48AM +1200, David Rowley wrote:
>> Thanks, typing that up.  I think it lacks a bit detail about what's
>> actually changed.  The change is fairly evident and people can see
>> when it takes effect when they look at EXPLAIN and see that the
>> Append/MergeAppend node is missing. Also, an Append/MergeAppend may
>> exist for inheritance tables and an Append can exist for a simple
>> UNION ALL. Both of those cases can have subplans removed to leave only
>> a single subplan, to which the additional parallel paths will be
>> considered.
> 
> This brings up a few points.  First, it seems the change affects
> partitioned tables and UNION ALL, which means it probably needs to be
> listed in two sections.

How about putting this only in E.1.3.1.3. Optimizer?

Thanks,
Amit

On Wed, 22 May 2019 at 02:55, Bruce Momjian <bruce@momjian.us> wrote:
>
> On Sun, May 19, 2019 at 02:26:48AM +1200, David Rowley wrote:
> > On Sun, 19 May 2019 at 01:20, Bruce Momjian <bruce@momjian.us> wrote:
> > > The first one needs to reference visible effects, so I would add:
> > >
> > >         Consider additional optimizations for queries referencing
> > >         partitioned tables that only affect a single partition
> > >
> > > Does that work?
> >
> > Thanks, typing that up.  I think it lacks a bit detail about what's
> > actually changed.  The change is fairly evident and people can see
> > when it takes effect when they look at EXPLAIN and see that the
> > Append/MergeAppend node is missing. Also, an Append/MergeAppend may
> > exist for inheritance tables and an Append can exist for a simple
> > UNION ALL. Both of those cases can have subplans removed to leave only
> > a single subplan, to which the additional parallel paths will be
> > considered.
>
> This brings up a few points.  First, it seems the change affects
> partitioned tables and UNION ALL, which means it probably needs to be
> listed in two sections.   Second, is it only parallelism paths that are
> added?  I am not sure if people care about a node being removed,
> especially when the might not even know we do that step, but they do
> care if there are new optimization possibilities.

Like Amit, I think the optimizer section is fine.  Another thing that
is affected is that you may no longer get a Materialize node in the
plan.  Previously you might have gotten something like Merge Join ->
Materialize -> Append -> Seq Scan, now you might just get Merge Join
-> Seq Scan.  This is because Append / MergeAppend don't support mark
and restore. Removing them would allow the materialize node to be
skipped in cases where the single subpath of the Append does support
mark and restore.

> > I think something like:
> >
> > * Make the optimizer only include an Append/MergeAppend node when
> > there is more than one subplan to append.
> >
> > This reduces execution overheads and allows additional plan shapes
> > that were previously not possible.
> >
> > or a little more brief:
> >
> > * Allow the optimizer to consider more plan types by eliminating
> > single subplan Append and MergeAppends.
> >
> > I understand that you might be trying to avoid details of plan node
> > types, but really anyone who's got inheritance or partitioned tables
> > and has looked at an EXPLAIN should have an idea.
>
> I would like to have something that people who don't study EXPLAIN will
> still be excited about.

hmm. I guess it's a pretty hard balance between writing something
where there's so little detail that nobody really knows what it means
vs adding some detail that some people might not understand.

> > Also, I think Tom should be the main author of this as he rewrote my
> > version of the patch to such an extent that there was next to nothing
> > of it left. I just tagged a few extra things on before he committed
> > it.
>
> Uh, Tom listed you as author, but I don't have to follow that if you
> feel it is inaccurate.

I'd say he was being very generous. I'm happy to come 2nd on this one.

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

On Wed, May 22, 2019 at 12:33:10PM +1200, David Rowley wrote:
> On Wed, 22 May 2019 at 02:55, Bruce Momjian <bruce@momjian.us> wrote:
> > This brings up a few points.  First, it seems the change affects
> > partitioned tables and UNION ALL, which means it probably needs to be
> > listed in two sections.   Second, is it only parallelism paths that are
> > added?  I am not sure if people care about a node being removed,
> > especially when the might not even know we do that step, but they do
> > care if there are new optimization possibilities.
> 
> Like Amit, I think the optimizer section is fine.  Another thing that
> is affected is that you may no longer get a Materialize node in the
> plan.  Previously you might have gotten something like Merge Join ->
> Materialize -> Append -> Seq Scan, now you might just get Merge Join
> -> Seq Scan.  This is because Append / MergeAppend don't support mark
> and restore. Removing them would allow the materialize node to be
> skipped in cases where the single subpath of the Append does support
> mark and restore.

How is this patch for the item?  I put it in the Optimizer section.

-- 
  Bruce Momjian  <bruce@momjian.us>        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 +

On Fri, 14 Jun 2019 at 15:24, Bruce Momjian <bruce@momjian.us> wrote:
>
> On Wed, May 22, 2019 at 12:33:10PM +1200, David Rowley wrote:
> > On Wed, 22 May 2019 at 02:55, Bruce Momjian <bruce@momjian.us> wrote:
> > > This brings up a few points.  First, it seems the change affects
> > > partitioned tables and UNION ALL, which means it probably needs to be
> > > listed in two sections.   Second, is it only parallelism paths that are
> > > added?  I am not sure if people care about a node being removed,
> > > especially when the might not even know we do that step, but they do
> > > care if there are new optimization possibilities.
> >
> > Like Amit, I think the optimizer section is fine.  Another thing that
> > is affected is that you may no longer get a Materialize node in the
> > plan.  Previously you might have gotten something like Merge Join ->
> > Materialize -> Append -> Seq Scan, now you might just get Merge Join
> > -> Seq Scan.  This is because Append / MergeAppend don't support mark
> > and restore. Removing them would allow the materialize node to be
> > skipped in cases where the single subpath of the Append does support
> > mark and restore.
>
> How is this patch for the item?  I put it in the Optimizer section.

That looks fine. Thank you.

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

On Fri, Jun 14, 2019 at 03:57:45PM +1200, David Rowley wrote:
> On Fri, 14 Jun 2019 at 15:24, Bruce Momjian <bruce@momjian.us> wrote:
> >
> > On Wed, May 22, 2019 at 12:33:10PM +1200, David Rowley wrote:
> > > On Wed, 22 May 2019 at 02:55, Bruce Momjian <bruce@momjian.us> wrote:
> > > > This brings up a few points.  First, it seems the change affects
> > > > partitioned tables and UNION ALL, which means it probably needs to be
> > > > listed in two sections.   Second, is it only parallelism paths that are
> > > > added?  I am not sure if people care about a node being removed,
> > > > especially when the might not even know we do that step, but they do
> > > > care if there are new optimization possibilities.
> > >
> > > Like Amit, I think the optimizer section is fine.  Another thing that
> > > is affected is that you may no longer get a Materialize node in the
> > > plan.  Previously you might have gotten something like Merge Join ->
> > > Materialize -> Append -> Seq Scan, now you might just get Merge Join
> > > -> Seq Scan.  This is because Append / MergeAppend don't support mark
> > > and restore. Removing them would allow the materialize node to be
> > > skipped in cases where the single subpath of the Append does support
> > > mark and restore.
> >
> > How is this patch for the item?  I put it in the Optimizer section.
> 
> That looks fine. Thank you.

Done, thanks.

-- 
  Bruce Momjian  <bruce@momjian.us>        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 +