Re: Proposal: "query_work_mem" GUC, to distribute working memory to the query's individual operators

On Tue, Mar 4, 2025 at 5:47 PM James Hunter <james(dot)hunter(dot)pg(at)gmail(dot)com> wrote:
>
> Attaching a new revision, which substantially reworks the previous revision --
>

Attaching a rebased revision, with some minor changes.

Also, some context for why this change is especially useful for cloud
variants of PostgreSQL -- if you compare PostgreSQL guidance for
buffer pool size [1] to Amazon Aurora's [2], PostgreSQL recommends the
buffer pool to be sized to 25% of system memory, while Aurora
recommends it to be sized to ~ 70%. PostgreSQL explicitly relies on
the OS filesystem cache, effectively to extend the buffer pool; while
Aurora docs don't mention this at all.

Accordingly, Aurora PostgreSQL queries have less memory to work with
than ordinary PostgreSQL queries, making per-Node memory limits more
important.

Questions, comments?

Thanks,
James

[1] https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-SHARED-BUFFERS
[2] https://docs.aws.amazon.com/prescriptive-guidance/latest/tuning-postgresql-parameters/shared-buffers.html