How much shared memory is PostgreSQL using? (2) – details

In the last post in this two post series we’ve briefly looked at configuring huge pages on Linux for PostgreSQL. It became clear, that calculating the number of huge pages purely based on the setting of shared_buffers will not work. PostgreSQL requires more shared memory, but what exactly is it? Well, this is the goal of this post and I’ll introduce a new feature of PostgreSQL 15 which helps in identifying those bits without digging into the operating systems details about memory management.

Let’s quickly review the current situation. We have shared_buffers set to 128MB:

postgres=# show shared_buffers;
 shared_buffers 
----------------
 128MB
(1 row)

Using the official way of calculating the huge pages we see that PostgreSQL currently requires this amount of shared memory (huge pages in PostgreSQL need to be disabled for this):

postgres@debian11pg:/home/postgres/ [pgdev] head -1 $PGDATA/postmaster.pid
826
postgres@debian11pg:/home/postgres/ [pgdev] pmap 826 | awk '/rw-s/ && /zero/ {print $2}'
145360K

As we already have seen in the last post this is more than the 128MB configured for shared_buffers. What else could change the requirement for shared memory. What about wal_buffers? In the default configuration (-1) this consumes 128/32 MB:

postgres=# show wal_buffers;
 wal_buffers 
-------------
 4MB
(1 row)

What happens if we change it?

postgres@debian11pg:/home/postgres/ [pgdev] psql -c "alter system set wal_buffers='16MB'"
ALTER SYSTEM
postgres@debian11pg:/home/postgres/ [pgdev] pg_ctl restart
postgres@debian11pg:/home/postgres/ [pgdev] head -1 $PGDATA/postmaster.pid
858
postgres@debian11pg:/home/postgres/ [pgdev] pmap 858 | awk '/rw-s/ && /zero/ {print $2}'
157664K

This increases shared memory usage from 145360K to 157664K. So, yes, wal_buffers definitely impact the usage of shared memory. What about extensions, could they have an impact as well?

postgres@debian11pg:/home/postgres/ [pgdev] psql -c "alter system set shared_preload_libraries=pg_stat_statements"
ALTER SYSTEM
postgres@debian11pg:/home/postgres/ [pgdev] pg_ctl restart
postgres@debian11pg:/home/postgres/ [pgdev] head -1 $PGDATA/postmaster.pid
882
postgres@debian11pg:/home/postgres/ [pgdev] pmap 882 | awk '/rw-s/ && /zero/ {print $2}'
159176K

We’ve again increased shared memory usage from 157664K to 159176K. Extensions also might consume shared memory. What about max_connections?

postgres@debian11pg:/home/postgres/ [pgdev] psql -c "show max_connections"
 max_connections 
-----------------
 100
(1 row)

postgres@debian11pg:/home/postgres/ [pgdev] psql -c "alter system set max_connections=1000"
ALTER SYSTEM
postgres@debian11pg:/home/postgres/ [pgdev] pg_ctl restart
postgres@debian11pg:/home/postgres/ [pgdev] head -1 $PGDATA/postmaster.pid
900
postgres@debian11pg:/home/postgres/ [pgdev] pmap 900 | awk '/rw-s/ && /zero/ {print $2}'
201288K

Next increase, this time from 159176K to 201288K. Without going any further: There was no way to see the real shared memory consumption from inside PostgreSQL and this is why this discussion started. The result is this:

postgres=# show shared_buffers;
 shared_buffers 
----------------
 128MB
(1 row)

postgres=# show shared_memory_size;
 shared_memory_size 
--------------------
 197MB
(1 row)

… which is pretty close to 201288K. Really nice.