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 |
In reply to this post by Jonathan S. Katz-3
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 |
In reply to this post by Jonathan S. Katz-3
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.com The Russian Postgres Company |
In reply to this post by Amit Langote-2
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 |
In reply to this post by Jonathan S. Katz-3
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 |
In reply to this post by Darren Duncan
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 |
In reply to this post by Peter Eisentraut-6
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 |
In reply to this post by Jonathan S. Katz-3
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 Rgds, Gilberto Castillo |
In reply to this post by Jonathan S. Katz-3
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 |
In reply to this post by gilberto.castillo
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 missed it on my pass in the release notes. Where I can find more information on it? Thanks, Jonathan |
In reply to this post by Andres Freund
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. 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 |
In reply to this post by Jonathan S. Katz-3
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? 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 |
In reply to this post by David Rowley-3
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 + |
Free forum by Nabble | Edit this page |