Fix postgresql shared_buffers size?

[imc67]

@girish Following up on this one for PostgreSQL, since you're in that code anyway: shared_buffers there is still at the built-in default of 128 MB, on a service that had a 4 GB limit here.

shared_buffers         16384 (8kB)  = 128 MB
effective_cache_size  524288 (8kB)  = 4 GB    (also the built-in default)
work_mem                4 MB

Postgres leans on the OS page cache far more than InnoDB does, so this is less dramatic than the MySQL case. But the usual guidance is somewhere around 25% of available memory, and 3% is a long way below that. effective_cache_size is a planner hint and is also left at its default, so it does not track the limit either.

Would the same clamp you just added for MySQL make sense here, with a smaller fraction?

[girish]

I forked this from https://forum.cloudron.io/topic/15767/mysql-service-still-runs-with-the-default-128m-innodb_buffer_pool_size/ to track it separately

[girish]

Not so sure about this. shared_buffers is a fixed reservation. The recommendation in the docs is for dedicated dbs - https://www.postgresql.org/docs/current/runtime-config-resource.html . On Cloudron, we have a shared environment. Postgres allocates it at startup and keeps it. It does not shrink when other apps or containers need RAM. It’s not like the OS page cache, which the kernel can reclaim under pressure.

IOW, if you have 6GB for postgres, it will hog 25% of this. It's not a soft memory where it will relinquish this if other apps need it.

[imc67]

That makes sense, I hadn't looked at it that way. The page cache gives the memory back and shared_buffers doesn't, which matters a lot more on a box with thirty containers than on a dedicated database server — and the 25% guidance assumes the latter.

What might still be worth it is effective_cache_size, which doesn't allocate anything and only tells the planner how much cache it can expect. On three of my servers with 8, 8 and 10 GB limits it reads 4GB everywhere, straight from boot_val — it doesn't track the limit at all. Deriving that one from the memory limit would improve plan choices without reserving any RAM.

No rush on this one, it's much smaller than the MySQL case. It just seemed inconsistent that the buffer pool now follows the memory limit while the planner hint doesn't.