Re: maintenance_work_mem is it per instance or per database??

On Fri, Nov 2, 2018 at 12:28 AM pavan95 <pavan(dot)postgresdba(at)gmail(dot)com> wrote:

> Hello all,
>
> Is the parameter maintenance_work_mem is per database or per instance??
>
> And while exploring the configuration parameters I'm assuming that the sum
> of values(default values) for the parameters maintenance_work_mem(64MB),
> work_mem(4MB),temp_buffers(8MB), wal_buffers(4MB) will be equal to the
> value
> of shared buffers(128MB).
>
> But the total corresponds to 80MB where is the remaining part of shared
> buffers(48MB) used?? I have read like clog buffers don't have any explicit
> parameter to be set, it is tuned automatically by database engine
>
> So will that remaining will be used by the client requests??
>
> Please clarify me. Thanks in advance!!
>
> Regards,
> Pavan
>
>
>
> --
> Sent from:
> http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html

I highly recommend reading over the Tuning section of the PG wiki for a
pretty good, concise description of how the memory settings work and are
related.

https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

As a quick answer, shared_buffers is completely independent from all the
other memory settings you mentioned. shared_buffers is allocated to PG when
the database starts and that memory is always reserved for PG while it is
running. The rest of the ones you mentioned (work_mem,
maintenance_work_mem, etc) are all memory allocations that can be used in
addition to shared_buffers and are allocated on an as-needed basis, some
multiple times each.

I've also written some other blog posts on investigating more detail in how
PG is actually using shared_buffers if you're interested
https://www.keithf4.com/a-large-database-does-not-mean-large-shared_buffers/
https://www.keithf4.com/a-small-database-does-not-mean-small-shared_buffers/

Keith