Subject: PostgreSQL 12: Feature Highlights

From: "Jonathan S. Katz" <[email-masked]>
Date: Sun, 12 May 2019 11:28:49 -0400

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

---

From: Darren Duncan <[email-masked]>
Date: Sun, 12 May 2019 14:04:06 -0700

> 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

---

From: David Rowley <[email-masked]>
Date: Mon, 13 May 2019 11:47:12 +1200

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

---

From: Justin Clift <[email-masked]>
Date: Mon, 13 May 2019 11:50:00 +1000

> 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 
(?)

---

From: David Rowley <[email-masked]>
Date: Mon, 13 May 2019 14:19:45 +1200

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

---

From: Amit Langote <[email-masked]>
Date: Mon, 13 May 2019 15:37:05 +0900

Hi David,

> 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

---

From: Oleg Bartunov <[email-masked]>
Date: Mon, 13 May 2019 12:14:48 +0300

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

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

---

From: David Rowley <[email-masked]>
Date: Mon, 13 May 2019 22:50:59 +1200

> are now faster in v12, whereas AFAIK, they perform perform more or less

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.

---

From: Peter Eisentraut <[email-masked]>
Date: Mon, 13 May 2019 13:39:31 +0200

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

---

From: Peter Eisentraut <[email-masked]>
Date: Mon, 13 May 2019 13:40:28 +0200

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

---

From: "Jonathan S. Katz" <[email-masked]>
Date: Mon, 13 May 2019 08:54:47 -0400

> 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

---

From: Peter Eisentraut <[email-masked]>
Date: Mon, 13 May 2019 18:21:22 +0200

> greatly expands the possibilities of the types of partition bounds that

How so?

---

From: Peter Eisentraut <[email-masked]>
Date: Mon, 13 May 2019 18:22:13 +0200

> # Feature Highlights

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

---

From: Peter Geoghegan <[email-masked]>
Date: Mon, 13 May 2019 09:23:08 -0700

> is probably of interest to many users.

+1

-- 
Peter Geoghegan

---

From: [email-masked]
Date: Mon, 13 May 2019 12:25:53 -0400

+1

Rgds,
Gilberto Castillo

---

From: Andres Freund <[email-masked]>
Date: Mon, 13 May 2019 09:37:41 -0700

Hi,

+1

> greatly expands the possibilities of the types of partition bounds that

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

---

From: "Jonathan S. Katz" <[email-masked]>
Date: Mon, 13 May 2019 13:19:19 -0400

>>> is probably of interest to many users.

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

---

From: "Jonathan S. Katz" <[email-masked]>
Date: Mon, 13 May 2019 13:21:30 -0400

> 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

---

From: Peter Eisentraut <[email-masked]>
Date: Mon, 13 May 2019 21:50:34 +0200

> missed it on my pass in the release notes. Where I can find more

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.

---

From: Bruce Momjian <[email-masked]>
Date: Mon, 13 May 2019 22:59:18 -0400

> 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  <[email-masked]>        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 +

---

From: Amit Langote <[email-masked]>
Date: Tue, 14 May 2019 18:25:43 +0900

> 	Tables with thousands of child partitions can now be processed

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

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 14 May 2019 09:19:45 -0400

> partitioned tables is now faster even with thousands of partitions.

Agreed, I changed it to your wording.

---

From: Amit Langote <[email-masked]>
Date: Wed, 15 May 2019 09:34:10 +0900

> Agreed, I changed it to your wording.

Thank you.

Regards,
Amit

---

From: "Jonathan S. Katz" <[email-masked]>
Date: Tue, 14 May 2019 22:03:16 -0400

Hi,

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

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

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

Thanks,

Jonathan

---

From: Amit Langote <[email-masked]>
Date: Wed, 15 May 2019 11:31:32 +0900

Hi Jonathan,

Thanks for the updated draft.

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

---

From: David Rowley <[email-masked]>
Date: Wed, 15 May 2019 14:59:48 +1200

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

---

From: Amit Langote <[email-masked]>
Date: Wed, 15 May 2019 17:36:32 +0900

Hi David,

> 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

---

From: Bruce Momjian <[email-masked]>
Date: Thu, 16 May 2019 14:26:28 -0400

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

---

From: Alvaro Herrera <[email-masked]>
Date: Thu, 16 May 2019 15:08:06 -0400

> will appear to be faster with PG 12, even if users can't articulate

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

---

From: Stephen Amell <[email-masked]>
Date: Thu, 16 May 2019 16:18:42 -0300

> second look if we tell them we have fixed the deficiencies they found.

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

---

From: Peter Geoghegan <[email-masked]>
Date: Thu, 16 May 2019 12:35:25 -0700

> 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

---

From: Michael Paquier <[email-masked]>
Date: Fri, 17 May 2019 08:52:50 +0900

On Thu, May 16, 2019 at 12:35:25PM -0700, Peter Geoghegan wrote:
> On Thu, May 16, 2019 at 12:08 PM Alvaro Herrera <[email-masked]> 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.
--
Michael

---

From: Peter Geoghegan <[email-masked]>
Date: Thu, 16 May 2019 17:05:13 -0700

> 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

---

From: Bruce Momjian <[email-masked]>
Date: Thu, 16 May 2019 21:27:40 -0400

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

---

From: Bruce Momjian <[email-masked]>
Date: Thu, 16 May 2019 21:29:10 -0400

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

---

From: Bruce Momjian <[email-masked]>
Date: Thu, 16 May 2019 21:32:55 -0400

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

---

From: Peter Geoghegan <[email-masked]>
Date: Thu, 16 May 2019 18:49:29 -0700

> there as comments and making them more visible would be a nice

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

---

From: Bruce Momjian <[email-masked]>
Date: Thu, 16 May 2019 22:21:35 -0400

> certainly don't read them from start to finish, nor do I expect to

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.

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

OK, that makes sense.

---

From: Alvaro Herrera <[email-masked]>
Date: Thu, 16 May 2019 22:34:13 -0400

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.

---

From: Michael Paquier <[email-masked]>
Date: Fri, 17 May 2019 12:50:12 +0900

> 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

---

From: Amit Langote <[email-masked]>
Date: Fri, 17 May 2019 19:56:55 +0900

> will appear to be faster with PG 12, even if users can't articulate

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

---

From: Evan Macbeth <[email-masked]>
Date: Fri, 17 May 2019 08:14:33 -0400

Long time lurker, first time poster. :)

> have to function as a press release, because we'll have an actual

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.

Back to my cave.

Evan

-- 
Evan Macbeth - Director of Support - Crunchy Data
+1 443-421-0343 - [email-masked]

---

From: Bruce Momjian <[email-masked]>
Date: Fri, 17 May 2019 10:16:26 -0400

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

---

From: Bruce Momjian <[email-masked]>
Date: Fri, 17 May 2019 10:18:15 -0400

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

---

From: Bruce Momjian <[email-masked]>
Date: Fri, 17 May 2019 10:19:30 -0400

On Fri, May 17, 2019 at 08:14:33AM -0400, Evan Macbeth wrote:
> 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.

---

From: "Jonathan S. Katz" <[email-masked]>
Date: Fri, 17 May 2019 10:20:29 -0400

>    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

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

---

From: "Jonathan S. Katz" <[email-masked]>
Date: Fri, 17 May 2019 10:30:58 -0400

> 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

---

From: Bruce Momjian <[email-masked]>
Date: Fri, 17 May 2019 11:36:53 -0400

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

> existing item and described separately, just this one I think.

Yes.

---

From: David Rowley <[email-masked]>
Date: Sat, 18 May 2019 13:38:49 +1200

>         necessary order (David Rowley)

Thank you for moving that out.

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

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.

---

From: Peter Geoghegan <[email-masked]>
Date: Fri, 17 May 2019 18:47:37 -0700

On Fri, May 17, 2019 at 6:39 PM David Rowley
<[email-masked]> wrote:
> 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

---

From: Bruce Momjian <[email-masked]>
Date: Fri, 17 May 2019 22:30:17 -0400

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

---

From: Magnus Hagander <[email-masked]>
Date: Sat, 18 May 2019 10:13:17 +0200

> needed, the documentation can provide even greater details.

There is in fact a third layer to consider as well, which is the release
announcement. This can (and sometimes are) different from the press release.

So you have the press release. Then more details can be added to the
release announcement. Then even more details can be added to the release
notes.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ <http://www.hagander.net/>
 Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>

---

From: David Rowley <[email-masked]>
Date: Sat, 18 May 2019 23:34:34 +1200

> complaining about.  In fact, the wording we have now is by Amit Langote.

https://www.postgresql.org/message-id/[email-masked]

---

From: Bruce Momjian <[email-masked]>
Date: Sat, 18 May 2019 09:20:31 -0400

> https://www.postgresql.org/message-id/[email-masked]

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

	commit 05685897f0
	Author: Bruce Momjian <[email-masked]>
	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/[email-masked]

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

---

From: Bruce Momjian <[email-masked]>
Date: Sat, 18 May 2019 09:24:28 -0400

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

---

From: David Rowley <[email-masked]>
Date: Sun, 19 May 2019 02:26:48 +1200

>         partitioned tables that only affect a single partition

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.

---

From: Amit Langote <[email-masked]>
Date: Tue, 21 May 2019 09:47:54 +0900

>         necessary order (David Rowley)

Thank you Bruce.

Regards,
Amit

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 21 May 2019 10:55:09 -0400

> a single subplan, to which the additional parallel paths will be

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.

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

> of it left. I just tagged a few extra things on before he committed

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

---

From: Amit Langote <[email-masked]>
Date: Wed, 22 May 2019 09:23:13 +0900

On 2019/05/21 23:55, Bruce Momjian wrote:
> listed in two sections.

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

Thanks,
Amit

---

From: David Rowley <[email-masked]>
Date: Wed, 22 May 2019 12:33:10 +1200

> 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 would like to have something that people who don't study EXPLAIN will

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.

> Uh, Tom listed you as author, but I don't have to follow that if you

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

---

From: Bruce Momjian <[email-masked]>
Date: Thu, 13 Jun 2019 23:24:39 -0400

> skipped in cases where the single subpath of the Append does support

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

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +
diff --git a/doc/src/sgml/release-12.sgml b/doc/src/sgml/release-12.sgml
new file mode 100644
index 76abc95..aa3845f
*** a/doc/src/sgml/release-12.sgml
--- b/doc/src/sgml/release-12.sgml
*************** Author: Tom Lane <[email-masked]>
*** 842,847 ****
--- 842,859 ----
        <listitem>
  <!--
  Author: Tom Lane <[email-masked]>
+ 2019-03-25 [8edd0e794] Suppress Append and MergeAppend plan nodes that have a s
+ -->
+ 
+        <para>
+         Improve optimization of partition and <literal>UNION ALL</literal>
+         queries that have only a single child (David Rowley)
+        </para>
+       </listitem>
+ 
+       <listitem>
+ <!--
+ Author: Tom Lane <[email-masked]>
  2018-12-13 [04fe805a1] Drop no-op CoerceToDomain nodes from expressions at plan
  -->

---

From: David Rowley <[email-masked]>
Date: Fri, 14 Jun 2019 15:57:45 +1200

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

That looks fine. Thank you.

---

From: Bruce Momjian <[email-masked]>
Date: Fri, 14 Jun 2019 09:30:41 -0400

> That looks fine. Thank you.

Done, thanks.