Re: PostgreSQL 17 release announcement draft

Additional chunk:

Proposed:
PostgreSQL 17 improves the performance of queries with `IN` clauses that
use [B-tree](
https://www.postgresql.org/docs/17/indexes-types.html#INDEXES-TYPES-BTREE)
indexes, the default index method in PostgreSQL). Support for parallel
index builds were added for [BRIN](
https://www.postgresql.org/docs/17/brin.html) indexing.

PostgreSQL 17 leverages the constraints you've defined. The query planner
removes redundant `IS NOT NULL` statements when a column has a `NOT NULL`
constraint, and skips over `IS NULL` clauses for columns with an `IS NOT
NULL` constraint.

On Thu, Sep 5, 2024 at 11:58 AM Andrew Atkinson <andyatkinson(at)gmail(dot)com>
wrote:

> The write operations seem to be under-sold here! These seem very exciting
> and would be beneficial to all Postgres databases I've worked on.
>
> Is this a fair way to reduce the three items mentioned, as much as
> possible? I'm not proposing they change, but just sort of pushing on
> building down the items tied to their benefit to the end user as much as
> possible. Possibly shortening them and making them punchier will help.
>
> Faster Write throughput:
> - "Fewer WAL locks shorter lock lengths" (check this, I'm making it up,
> but I'm wondering if it's fewer WAL locks, or the locks are held for less
> time), thus better throughput
> - "Faster Sequential scans" - is the benefit that they're faster?
> - "Faster ANALYZE" - is it that ANALYZE runs faster on 17 vs. the same
> operation on 16?
>
> The last point "Allowing extensions to be integrated further." I didn't
> grab on to as much. I'm wondering if it's something like "new write
> operation APIs are now available for extension creators" or something like
> that?
>
> On Thu, Sep 5, 2024 at 11:49 AM Andrew Atkinson <andyatkinson(at)gmail(dot)com>
> wrote:
>
>> Hi Jonathan. Do you want change proposals here as text snippets in
>> emails? It seems the patch process isn't used here.
>>
>> If so, here's an attempted reduction that echoes what Robert said. I also
>> thought explaining Vacuum wouldn't be necessary for this audience, so less
>> lead-in could work. Is the benefit to end users that there is less memory
>> and CPU needed by vacuum, thus more CPU and memory is available to their
>> foreground workload?
>>
>> Original:
>> > A foundational feature of PostgreSQL is [vacuum](
>> https://www.postgresql.org/docs/17/routine-vacuuming.html) which is
>> used to reclaim storage from data that was marked as removed. Reducing
>> resources required for vacuuming directly helps other areas of PostgreSQL,
>> particularly on very busy systems. PostgreSQL 17 introduces a new internal
>> memory structure for vacuum that's shown up to a 20x reduction in memory
>> and improvements in overall vacuuming speed.
>>
>> Proposed:
>> The PostgreSQL [vacuum](
>> https://www.postgresql.org/docs/17/routine-vacuuming.html) process is
>> critical for healthy operations, requiring server instance resources to
>> operate. With PostgreSQL 17, a new internal memory structure for vacuum was
>> used that consumes up to 20x less memory. This improves vacuum speed and
>> also reduces the use of shared resources, making more available for your
>> workload.
>>
>>
>> Something along those lines, where the benefit to the user is they could
>> expect more CPU/mem etc. available for their SQL operations, right? This
>> could be something folks want to benchmark as well as a reason to upgrade,
>> at least for Vacuum-intensive workloads, high UPDATE and DELETE operations
>> etc.
>>
>>
>>
>>
>>
>> On Thu, Sep 5, 2024 at 11:04 AM Robert Haas <robertmhaas(at)gmail(dot)com>
>> wrote:
>>
>>> On Thu, Sep 5, 2024 at 5:22 AM Peter Eisentraut <peter(at)eisentraut(dot)org>
>>> wrote:
>>> > On 04.09.24 23:05, Jonathan S. Katz wrote:
>>> > > Attached is the draft of the PostgreSQL 17 release announcement.
>>> This is
>>> > > a draft of the text that will go into the press kit, with the key
>>> > > portions to review starting from the top of the document, up until
>>> the
>>> > > "About PostgreSQL" section.
>>> >
>>> > I noticed that we don't yet have a list of major features in the PG17
>>> > release notes. We should probably put that in soon, so that what we
>>> > list there and what is in the announcement are consistent.
>>>
>>> +1.
>>>
>>> > On the actual list, there will be lots of opinions to be had, but I'll
>>> > just offer one: I don't think the MERGE RETURNING clause deserves
>>> twice
>>> > as much space as incremental backup.
>>>
>>> I agree with that, although obviously I'm biased.
>>>
>>> I also feel like this whole thing could just be shorter. If it were
>>> half as long and mentioned fewer things and those more briefly, would
>>> we be worse off? I think we might be better off, because it just feels
>>> wordy to me right now. For example:
>>>
>>> A foundational feature of PostgreSQL is
>>> [vacuum](https://www.postgresql.org/docs/17/routine-vacuuming.html),
>>> which is used to reclaim storage from data that was marked as removed.
>>> Reducing resources required for vacuuming directly helps other areas
>>> of PostgreSQL, particularly on very busy systems. PostgreSQL 17
>>> introduces a new internal memory structure for vacuum that's shown up
>>> to a 20x reduction in memory and improvements in overall vacuuming
>>> speed. This release also removes the `1GB` limit on the memory it can
>>> use (controlled by
>>> [`maintenance_work_mem`](
>>> https://www.postgresql.org/docs/17/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM)
>>> ),
>>> letting users apply more resources to vacuuming, which is beneficial
>>> for systems with lots of changes.
>>>
>>> It seems to me that the first two sentences could just be completely
>>> nuked, and everything from "letting users" to the end could also be
>>> nuked. At least to me, all of that stuff reads as unnecessarily
>>> filler. I'm not at all sure that removing the 1GB limit on
>>> maintenance_work_mem is important enough that it needs to be in the
>>> release announcement -- I agree it's a good improvement, but to have
>>> it be one of the first things in the press release seems like an odd
>>> choice from my perspective. Nobody's going to look back on this
>>> release years from now and say "oh, that was the release where could
>>> finally set maintenance_work_mem=4GB, that was so much better". If
>>> they think about VACUUM, they'll think about the 20x memory reduction
>>> stuff which made the ability to configure values larger than 1GB
>>> irrelevant in the first place. So I'd probably delete the part about
>>> lifting the 1GB cap entirely. But even if you don't do that, the
>>> paragraph could be half as long without losing anything, from my
>>> perspective.
>>>
>>> --
>>> Robert Haas
>>> EDB: http://www.enterprisedb.com
>>>
>>>
>>>