|
|
123
|
|
Hi,
Now that a draft of the release notes are available[1] this seems like a
good time to begin determining what features we want to highlight prior
to the Beta 1 announcement. First, a small editorial :)
Reading through the list a few times, it is quite impressive the breadth
of features that are available for PostgreSQL 12 and the impact they can
have on our user workloads. I think this is very exciting and I think
our users will be very impressed with this release :) It also presents
some challenges for coming up with features to highlight, but I call
this a "good problem."
(I am less inclined to "trim the list" for the sake of doing so for a
Beta 1 announcement, as based on an analysis of the data, often what
people read are the announcements itself and not the release notes, so
trying to get as much info in front of people without making it too
tedious is the goal.)
Knowing that the target audience of the announcements are users of
PostgreSQL, and knowing the main goals of the beta announcement is to
both make people aware of features and to encourage testing, I think we
need to divide things into a few groups:
- Feature Highlights
- Changes that could affect existing operating environments
Also note below that the way I am listing them out does not constitute a
rank order as this list is just an initial compilation.
With further ado...
# Feature Highlights
1. Partitioning Improvements
- Performance, e.g. enhanced partition pruning, COPY performance, ATTACH
PARTITION
- Foreign Keys
- Partition bounds now support expressions
2. Query parallelism is now supported in SERIALIZABLE transaction mode
3. Indexing
- Improvements overall performance to standard (B-tree) indexes with
writes as well as with bloat
- REINDEX CONCURRENTLY
- GiST indexes now support covering indexes (INCLUDE clause)
- SP-GiST indexes now support K-NN queries
- WAL overhead reduced on GiST, GIN, & SP-GiST index creation
4. CREATE STATISTICS now supports most-common value statistics, which
leads to improved query plans for distributions that are non-uniform
5. WITH queries (CTEs) can now be inlined, subject to certain restrictions
6. Support for JSON path queries per the SQL/JSON standard as well as
support for indexing on equality expressions
7. Introduction of generated columns that compute and store an
expression as a value on the table
8. Enable / disable page checksums for an offline cluster
9. Authentication
- GSSAPI client/server encryption support
- LDAP server discovery
10. Introduction of CREATE ACCESS METHOD that permits the addition of
new table storage types
# Changes That Can Affect Existing Operating Environments
1. recovery.conf merged into postgresql.conf;
recovery.signal/standby.signal being used for switching into non-primary
mode
2. JIT enabled by default
As always, constructive feedback welcome. With the goal in mind that
this will be turned into a Beta 1 announcement, please indicate if you
believe something is missing or if something does not belong on this list.
Thanks!
Jonathan
[1] https://www.postgresql.org/docs/devel/release-12.html
|
|
|
On 2019-05-12 8:28 a.m., Jonathan S. Katz wrote:
> 7. Introduction of generated columns that compute and store an
> expression as a value on the table
It seems to me that one of the things that's valuable about this feature is that
one can make as regular visible data any calculated-from-a-row value that is
used for indexing purposes rather than that being hidden or not possible. I
assume or hope that generated columns can be used in all index types like
regular columns right? -- Darren Duncan
|
|
|
On Mon, 13 May 2019 at 03:28, Jonathan S. Katz < [hidden email]> wrote:
> - Performance, e.g. enhanced partition pruning, COPY performance, ATTACH
I don't think it's very accurate to say that the performance of
partition pruning has been improved. Really the improvement there is
due to the change in the order of operations, where we now perform
pruning before fetching partition meta-data. Pruning itself, I don't
believe became any faster in PG12. There were, however various tweaks
to improve performance of some operations around run-time partition
pruning both in the planner and during execution, these, however, are
not improvements to pruning itself, but more the operations around
setting up pruning and handling what happens after pruning takes
place. Bruce has now changed the release notes to mention "Improve
performance of many operations on partitioned tables", which seems
like a more accurate generalisation of what was improved, although, I
still think it's overly vague.
--
David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
|
|
|
On 2019-05-13 09:47, David Rowley wrote:
> On Mon, 13 May 2019 at 03:28, Jonathan S. Katz < [hidden email]>
> wrote:
>> - Performance, e.g. enhanced partition pruning, COPY performance,
>> ATTACH
>
> I don't think it's very accurate to say that the performance of
> partition pruning has been improved. Really the improvement there is
> due to the change in the order of operations, where we now perform
> pruning before fetching partition meta-data. Pruning itself, I don't
> believe became any faster in PG12. There were, however various tweaks
> to improve performance of some operations around run-time partition
> pruning both in the planner and during execution, these, however, are
> not improvements to pruning itself, but more the operations around
> setting up pruning and handling what happens after pruning takes
> place. Bruce has now changed the release notes to mention "Improve
> performance of many operations on partitioned tables", which seems
> like a more accurate generalisation of what was improved, although, I
> still think it's overly vague.
Sounds like "partition pruning is now more efficient". eg less memory
usage (?), with a side effect of better performance leading from that
(?)
|
|
|
On Mon, 13 May 2019 at 13:50, Justin Clift < [hidden email]> wrote:
>
> On 2019-05-13 09:47, David Rowley wrote:
> > On Mon, 13 May 2019 at 03:28, Jonathan S. Katz < [hidden email]>
> > wrote:
> >> - Performance, e.g. enhanced partition pruning, COPY performance,
> >> ATTACH
> >
> > I don't think it's very accurate to say that the performance of
> > partition pruning has been improved. Really the improvement there is
> > due to the change in the order of operations, where we now perform
> > pruning before fetching partition meta-data. Pruning itself, I don't
> > believe became any faster in PG12. There were, however various tweaks
> > to improve performance of some operations around run-time partition
> > pruning both in the planner and during execution, these, however, are
> > not improvements to pruning itself, but more the operations around
> > setting up pruning and handling what happens after pruning takes
> > place. Bruce has now changed the release notes to mention "Improve
> > performance of many operations on partitioned tables", which seems
> > like a more accurate generalisation of what was improved, although, I
> > still think it's overly vague.
>
> 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 < [hidden email]> wrote:
>>
>> On 2019-05-13 09:47, David Rowley wrote:
>>> On Mon, 13 May 2019 at 03:28, Jonathan S. Katz < [hidden email]>
>>> wrote:
>>>> - Performance, e.g. enhanced partition pruning, COPY performance,
>>>> ATTACH
>>>
>>> I don't think it's very accurate to say that the performance of
>>> partition pruning has been improved. Really the improvement there is
>>> due to the change in the order of operations, where we now perform
>>> pruning before fetching partition meta-data. Pruning itself, I don't
>>> believe became any faster in PG12. There were, however various tweaks
>>> to improve performance of some operations around run-time partition
>>> pruning both in the planner and during execution, these, however, are
>>> not improvements to pruning itself, but more the operations around
>>> setting up pruning and handling what happens after pruning takes
>>> place. Bruce has now changed the release notes to mention "Improve
>>> performance of many operations on partitioned tables", which seems
>>> like a more accurate generalisation of what was improved, although, I
>>> still think it's overly vague.
>>
>> 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 < [hidden email]> 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.comThe Russian Postgres Company
|
|
|
On Mon, 13 May 2019 at 18:37, Amit Langote
< [hidden email]> 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
< [hidden email]> wrote:
> On 2019-05-12 17:28, Jonathan S. Katz wrote:
> > # Feature Highlights
>
> The ability to create case-insensitive and accent-insensitive collations
> is probably of interest to many users.
+1
--
Peter Geoghegan
|
|
|
El 2019-05-13 12:23, Peter Geoghegan escribió:
> On Mon, May 13, 2019 at 9:22 AM Peter Eisentraut
> < [hidden email]> wrote:
>> On 2019-05-12 17:28, Jonathan S. Katz wrote:
>> > # Feature Highlights
>>
>> The ability to create case-insensitive and accent-insensitive
>> collations
>> is probably of interest to many users.
>
> +1
+1
Rgds,
Gilberto Castillo
|
|
|
Hi,
On 2019-05-13 08:54:47 -0400, Jonathan S. Katz wrote:
> On 5/13/19 7:39 AM, Peter Eisentraut wrote:
> > On 2019-05-12 17:28, Jonathan S. Katz wrote:
> >> - Partition bounds now support expressions
> >
> > This is relatively trivial compared to the rest and probably doesn't
> > belong in a highlights list.
+1
> Why? This is incredibly helpful from a development standpoint; it
> greatly expands the possibilities of the types of partition bounds that
> can be utilized.
You can say that about a lot of features. But we've limited space in the
top items... It doesn't strike me as a enabling that many cases.
Greetings,
Andres Freund
|
|
|
On 5/13/19 12:25 PM, [hidden email] wrote:
> El 2019-05-13 12:23, Peter Geoghegan escribió:
>> On Mon, May 13, 2019 at 9:22 AM Peter Eisentraut
>> < [hidden email]> wrote:
>>> On 2019-05-12 17:28, Jonathan S. Katz wrote:
>>> > # Feature Highlights
>>>
>>> The ability to create case-insensitive and accent-insensitive collations
>>> is probably of interest to many users.
>>
>> +1
> +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, [hidden email] wrote:
>> El 2019-05-13 12:23, Peter Geoghegan escribió:
>>> On Mon, May 13, 2019 at 9:22 AM Peter Eisentraut
>>> < [hidden email]> wrote:
>>>> On 2019-05-12 17:28, Jonathan S. Katz wrote:
>>>>> # Feature Highlights
>>>>
>>>> The ability to create case-insensitive and accent-insensitive collations
>>>> is probably of interest to many users.
>>>
>>> +1
>> +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?
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
> < [hidden email]> 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 < [hidden email]> http://momjian.us EnterpriseDB http://enterprisedb.com+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
|
123
|
Locked
signature.asc (849 bytes)