PostgreSQL shared buffer cache or shared memory buffers

PostgreSQL shared buffer cache (shared_buffers) aka shared memory buffers is more or less equivalent to the MyISAM key buffer (key_buffer_size). PostgreSQL additionally uses the file system cache for caching. PostgreSQL does “double buffering” when usinge the shared buffer cache. Should be set to about 25% of RAM. This PostgreSQL variable is only changeable with a database (cluster) restart! The shared buffer cache uses Shared memory. So kernel parameters SHMMAX and SHMALL must be adjusted.

The shard buffer cache is used for:

• “Sets the amount of memory the database server uses for shared memory buffers”
• “which is used for connection control, active operations, and more”
• “it defines how much dedicated system memory PostgreSQL will use for cache”

Sources:

• https://aiven.io/docs/products/postgresql/concepts/pg-shared-buffers
• https://stormatics.tech/blogs/postgresql-shared-buffers
• https://pganalyze.com/blog/5mins-explain-analyze-buffers-nested-loops
• shared_buffers

Queries to view the shared buffer cache

Show the shared buffer cache settings:

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

16384 buffers à 8k!?!

To view the contents of the shared buffer cache one needs to install the extension pg_buffercache:

postgres=# CREATE EXTENSION pg_buffercache;
CREATE EXTENSION

postgres=# SELECT CASE WHEN c.reldatabase IS NULL THEN 'empty'
  WHEN c.reldatabase = 0 THEN 'unknown (0)'
  ELSE d.datname
  END AS database
  , COUNT(*) AS cached_blocks
  FROM pg_buffercache AS c
  LEFT JOIN pg_database AS d ON c.reldatabase = d.oid
 GROUP BY d.datname, c.reldatabase
 ORDER BY d.datname, c.reldatabase;
 database   | cached_blocks
-------------+---------------
 postgres    |           285
 template1   |            71
 unknown (0) |            14
 empty       |         16014
(4 rows)

postgres=# SELECT c.relname, count(*) AS buffers
FROM pg_buffercache b
INNER JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid) AND
b.reldatabase IN (0, (SELECT oid FROM pg_database
WHERE datname = CURRENT_DATABASE()))
GROUP BY c.relname
ORDER BY 2 DESC
LIMIT 10;
             relname             | buffers
---------------------------------+---------
 pg_attribute                    |      33
 pg_proc                         |      20
 pg_class                        |      18
 pg_operator                     |      14
 pg_type                         |       9
 pg_proc_oid_index               |       8
 pg_attribute_relid_attnum_index |       8
 pg_statistic                    |       8
 pg_proc_proname_args_nsp_index  |       8
 pg_depend                       |       6
(10 rows)

postgres=# SELECT n.nspname, c.relname, count(*) AS buffers
             FROM pg_buffercache b JOIN pg_class c
             ON b.relfilenode = pg_relation_filenode(c.oid) AND
                b.reldatabase IN (0, (SELECT oid FROM pg_database
                                      WHERE datname = current_database()))
             JOIN pg_namespace n ON n.oid = c.relnamespace
             GROUP BY n.nspname, c.relname
             ORDER BY 3 DESC
             LIMIT 10;

  nspname   |             relname             | buffers
------------+---------------------------------+---------
 pg_catalog | pg_attribute                    |      33
 pg_catalog | pg_proc                         |      20
 pg_catalog | pg_class                        |      18
 pg_catalog | pg_operator                     |      14
 pg_catalog | pg_type                         |       9
 pg_catalog | pg_attribute_relid_attnum_index |       8
 pg_catalog | pg_proc_oid_index               |       8
 pg_catalog | pg_proc_proname_args_nsp_index  |       8
 pg_catalog | pg_statistic                    |       8
 pg_catalog | pg_depend                       |       6
(10 rows)

postgres=# SELECT * FROM pg_buffercache_summary();
 buffers_used | buffers_unused | buffers_dirty | buffers_pinned |   usagecount_avg
--------------+----------------+---------------+----------------+--------------------
          365 |          16019 |            45 |              0 | 3.5123287671232877
(1 row)

postgres=# SELECT * FROM pg_buffercache_usage_counts();
 usage_count | buffers | dirty | pinned
-------------+---------+-------+--------
           0 |   16019 |     0 |      0
           1 |      96 |     9 |      0
           2 |      41 |     9 |      0
           3 |       9 |     2 |      0
           4 |      16 |     5 |      0
           5 |     203 |    20 |      0
(6 rows)

Source: pg_buffercache — inspect PostgreSQL buffer cache state