Буферный кэш 

=> CREATE DATABASE wal_buffercache;

CREATE DATABASE

=> \c wal_buffercache

You are now connected to database "wal_buffercache" as user "postgres".

Создадим таблицу: 

=> CREATE TABLE test(t text)
WITH (autovacuum_enabled = off);

CREATE TABLE

=> INSERT INTO test
SELECT 'line '||s.id FROM generate_series(1,500) AS s(id);

INSERT 0 500

=> VACUUM ANALYZE test;

VACUUM

Уменьшим временно размер буферного кэша, чтобы упростить наблюдение за ним. 

=> ALTER SYSTEM SET shared_buffers = 512;

ALTER SYSTEM

Изменение требует перезапуска сервера. 

=> \q

postgres$ pg_ctl -w -l /home/postgres/logfile -D /usr/local/pgsql/data restart

waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started

Проверим размер кэша: 

student$ psql wal_buffercache

=> SELECT setting, unit FROM pg_settings WHERE name = 'shared_buffers';

 setting | unit 
---------+------
 512     | 8kB
(1 row)

Содержимое буферного кэша можно посмотреть с помощью расширения: 

=> CREATE EXTENSION pg_buffercache;

CREATE EXTENSION

Создадим для удобства представление: 

=> CREATE VIEW pg_buffercache_v AS
SELECT bufferid,
       (SELECT c.relname
        FROM   pg_class c
        WHERE  pg_relation_filenode(c.oid) = b.relfilenode
       ) relname,
       CASE relforknumber
         WHEN 0 THEN 'main'
         WHEN 1 THEN 'fsm'
         WHEN 2 THEN 'vm'
       END relfork,
       relblocknumber,
       isdirty,
       usagecount
FROM   pg_buffercache b
WHERE  b.reldatabase IN (
         0, (SELECT oid FROM pg_database WHERE datname = current_database())
       )
AND    b.usagecount is not null;

CREATE VIEW

Условие на базу данных необходимо, так как в буферном кэше содержатся данные всего кластера. Расшифровать мы можем только информацию из той БД, к которой подключены. Глобальные объекты считаются принадлежащими БД с нулевым OID. 

=> SELECT * FROM pg_buffercache_v LIMIT 20;

 bufferid |   relname    | relfork | relblocknumber | isdirty | usagecount 
----------+--------------+---------+----------------+---------+------------
        1 | pg_database  | main    |              0 | f       |          4
        2 | pg_authid    | main    |              0 | f       |          3
        3 | pg_class     | main    |              0 | t       |          5
        4 | pg_class     | main    |              1 | f       |          5
        5 | pg_class     | main    |              2 | f       |          5
        6 | pg_attribute | main    |              0 | f       |          5
        7 | pg_attribute | main    |              1 | f       |          5
        8 | pg_attribute | main    |              2 | f       |          5
        9 | pg_attribute | main    |              3 | f       |          5
       10 | pg_attribute | main    |              4 | f       |          5
       11 | pg_attribute | main    |              5 | f       |          5
       12 | pg_attribute | main    |              6 | f       |          5
       13 | pg_attribute | main    |              7 | f       |          5
       14 | pg_attribute | main    |              8 | f       |          5
       15 | pg_attribute | main    |              9 | f       |          5
       16 | pg_attribute | main    |             10 | f       |          5
       17 | pg_attribute | main    |             11 | f       |          5
       18 | pg_attribute | main    |             12 | f       |          5
       19 | pg_attribute | main    |             13 | f       |          5
       20 | pg_attribute | main    |             14 | f       |          5
(20 rows)

Используя это расширение, можно наблюдать за состоянием кэша с разных сторон.

Кроме информации о том, как представлена в кэше та или иная страница, можно, например, посмотреть распределение буферов по их "популярности": 

=> SELECT usagecount, count(*)
FROM pg_buffercache
GROUP BY usagecount
ORDER BY usagecount;

 usagecount | count 
------------+-------
          1 |   104
          2 |    31
          3 |    10
          4 |    13
          5 |    98
            |   256
(6 rows)

Можно посмотреть, какая доля каких таблиц закэширована (и насколько активно используются эти данные): 

=> SELECT c.relname,
  count(*) blocks,
  round( 100.0 * 8192 * count(*) / pg_table_size(c.oid) ) "% of rel",
  round( 100.0 * 8192 * count(*) FILTER (WHERE b.usagecount > 3) / pg_table_size(c.oid) ) "% hot"
FROM pg_buffercache b
  JOIN pg_class c ON pg_relation_filenode(c.oid) = b.relfilenode
WHERE  b.reldatabase IN (
         0, (SELECT oid FROM pg_database WHERE datname = current_database())
       )
AND    b.usagecount is not null
GROUP BY c.relname, c.oid
ORDER BY 2 DESC
LIMIT 10;

            relname             | blocks | % of rel | % hot 
--------------------------------+--------+----------+-------
 pg_attribute                   |     28 |       56 |    48
 pg_proc                        |     17 |       22 |     4
 pg_depend_reference_index      |     15 |       41 |    27
 pg_class                       |     14 |       93 |    73
 pg_depend                      |     13 |       24 |    11
 pg_operator                    |     10 |       56 |    44
 pg_type                        |     10 |       77 |     8
 pg_proc_oid_index              |      8 |       89 |    44
 pg_proc_proname_args_nsp_index |      7 |       23 |     6
 pg_rewrite                     |      7 |        9 |     1
(10 rows)

Подобные запросы могут подсказать, насколько активно используется буферный кэш и дать пищу для размышлений о том, стоит ли увеличивать или уменьшать его размер.

Надо учитывать, что такие запросы:

Прогрев кэша

После перезапуска сервера должно пройти некоторое время, чтобы кэш "прогрелся" - набрал актуальные активно использующиеся данные. Иногда может оказаться полезным сразу прочитать в кэш данные определенных таблиц.

Посмотрим на расширение для прогрева кэша: 

=> CREATE EXTENSION pg_prewarm;

CREATE EXTENSION

Например, сейчас в кэше нет таблицы test (мы перезагружали сервер): 

=> SELECT * FROM pg_buffercache_v WHERE relname = 'test';

 bufferid | relname | relfork | relblocknumber | isdirty | usagecount 
----------+---------+---------+----------------+---------+------------
(0 rows)

В простейшем виде вызов функции считывает указанную таблицу в буферный кэш. 

=> SELECT pg_prewarm('test');

 pg_prewarm 
------------
          3
(1 row)


=> SELECT * FROM pg_buffercache_v WHERE relname = 'test';

 bufferid | relname | relfork | relblocknumber | isdirty | usagecount 
----------+---------+---------+----------------+---------+------------
      278 | test    | main    |              0 | f       |          1
      279 | test    | main    |              1 | f       |          1
      280 | test    | main    |              2 | f       |          1
(3 rows)

Изменим одну строку таблицы: 

=> UPDATE test SET t = 'changed' WHERE t = 'line 1';

UPDATE 1

=> SELECT * FROM pg_buffercache_v WHERE relname = 'test';

 bufferid | relname | relfork | relblocknumber | isdirty | usagecount 
----------+---------+---------+----------------+---------+------------
      278 | test    | main    |              0 | t       |          2
      279 | test    | main    |              1 | f       |          2
      280 | test    | main    |              2 | t       |          3
      283 | test    | vm      |              0 | t       |          1
      284 | test    | fsm     |              0 | f       |          1
      285 | test    | fsm     |              1 | f       |          1
      286 | test    | fsm     |              2 | f       |          1
(7 rows)

Как объяснить такой результат?

Изменились две табличные страницы (на одной версия строки удалена, в другую - добавлена), в кэш прочитана карта свободного пространства и карта видимости (которая, к тому же, изменилась).

Следует учитывать, что расширение pg_buffercache накладывает блокировки на время доступа к кэшу, поэтому не следует использовать его слишком часто на производственном сервере.

Расширение pg_prewarm может использоваться и для прогрева кэша операционной системы. А в версии PostgreSQL 11 оно получит возможность сохранять актуальное состояние кэша на диск и восстанавливать его же после перезагрузки сервера.

Конец демонстрации.