Subject: Re: Auto-tune shared_buffers to use available huge pages

From: Anthonin Bonnefoy <[email-masked]>
Date: Mon, 26 Jan 2026 14:16:45 +0100

On Fri, Jan 23, 2026 at 4:50 PM Tom Lane <[email-masked]> wrote:
> Not expressing an opinion on whether we should do this, but
> there is a comment on GetHugePageSize() that you seem to have
> falsified without bothering to correct:

Thanks for the review, I've updated the comment.

I've also revised the approach a bit. Using the free huge pages for
auto-tuning was too restrictive, it prevents setup like running
multiple Postgres on the same instance, which would require splitting
the available huge pages.

I've replaced it with a shared_buffers_autotune_target GUC, which
controls the amount of shared memory to target. If the requested
shared memory is below the target size, shared_buffers will be
increased to (approximately) reach this target.

By setting shared_buffers_autotune_target to the amount of available
huge pages, shared_buffers will be auto-tuned to use the leftover
space.

Regards,
Anthonin Bonnefoy

---

From: Ashutosh Bapat <[email-masked]>
Date: Thu, 29 Jan 2026 17:12:59 +0530

> huge pages, shared_buffers will be auto-tuned to use the leftover

The user could achieve the same effect by setting shared_buffers to
the same value as shared_buffers_autotune_target. What's the
difference?

Further they can use -C and -c options to postgres to decide which
setting of shared_buffers will consume the desired amount of available
memory. That is quite quick and easy.

I may be missing some usecase where such runtime autotuning is useful.

-- 
Best Wishes,
Ashutosh Bapat

---

From: Anthonin Bonnefoy <[email-masked]>
Date: Thu, 29 Jan 2026 15:10:57 +0100

> I may be missing some usecase where such runtime autotuning is useful.

To give a concrete example, I have a pod with 30GB of huge pages. If I
start postgres with "postgres -cshared_buffers=30GB -chuge_pages=on",
it fails with:

FATAL:  could not map anonymous shared memory: Cannot allocate memory
HINT:  This error usually means that PostgreSQL's request for a shared
memory segment exceeded available memory, swap space, or huge pages.
To reduce the request size (currently 32988200960 bytes), reduce
PostgreSQL's shared memory usage, perhaps by reducing "shared_buffers"
or "max_connections".

With all the additional memory requests, ~30.7GB is requested. I need
to lower the shared_buffers to 29.2GB to be able to start, but this is
imprecise and with this value, I'm off by ~60 huge pages in the final
allocation. The huge pages that were not part of the mmap are
completely wasted since it can't even be used by Linux for the page
cache.

So the idea behind the autotune logic is to be able to provide a
specific amount of shared memory to target and adjust shared_buffers
accordingly. The huge pages I have reserved for the pod are only
useful if they are used in the shared memory mmap, so the autotune
makes sure that nothing is wasted.

shared_buffers_autotune_target is probably confusing,
shared_memory_autotune_target might be a more fitting name.

---

From: Ashutosh Bapat <[email-masked]>
Date: Thu, 29 Jan 2026 20:16:06 +0530

> completely wasted since it can't even be used by Linux for the page

With -C you can quickly find out how much shared memory and huge pages
are being used without starting the server. E.g.
$ postgres -D $DataDir -c shared_buffers="128kB" -C "shared_memory_size"
9
$ postgres -D $DataDir -c shared_buffers="150MB" -C "shared_memory_size"
173
$ postgres -D $DataDir -c shared_buffers="150MB" -C
"shared_memory_size_in_huge_pages"
87

You could write a script to find the optimal value of shared_buffers,
that will consume memory optimally; auto-tuning it without starting
the server.

Isn't that sufficient?
--
Best Wishes,
Ashutosh Bapat

---

From: Anthonin Bonnefoy <[email-masked]>
Date: Thu, 29 Jan 2026 17:52:39 +0100

> that will consume memory optimally; auto-tuning it without starting

That was my initial approach. I have such a script and built a mapping
between memory available and shared_buffers to request, one for each
PG version. But that breaks as soon as a user does something that
increases memory request, like increasing max_connections or adding an
extension.

Thus, I need to run this script before every PG starts to account for
those possible changes. With Kubernetes, this means:
- You use a PG Docker image, you need to clone it and override the
entry point to run the script before starting PG.
- The cluster may be managed by systems like patroni[1], so patroni
would need to be modified to do the auto-tuning.
- You may also use a Kubernetes operator to manage the cluster. AFAIK,
they don't provide a way to override how PG is started, the operators
would need to implement the auto-tuning logic.

If this auto-tune logic is directly implemented in PG, all kubernetes
users would be able to benefit from this, without having to hijack how
PG is started.

[1]: https://github.com/patroni/patroni