Как понять хватает ли памяти серверу PostgreSQL

Это очень интересный вопрос, так как на него нет ни простого ни точного ответа…

Для начала нужно разделить потребляемую память на 2 вида:
▫️ Память на соединение
▫️ Память на весь сервер

Сначала давайте попробуем понять, как мониторить хватает ли нам памяти на соединение?

Для ограничения потребления памяти в сеансе используется 3 параметра temp_buffers, work_mem и maintenance_work_mem.

Для отслеживания достаточности у нас есть на данный момент только один инструмент – логирование имён и размеров временных файлов, которое включается параметром log_temp_files.
Итак, допустим у нас temp_buffers = 128 MB, work_mem = 256 MB, а maintenance_work_mem = 512 MB тогда параметр log_temp_files лучше всего задать равным меньшему из этих значений, т.е. log_temp_files = 128 MB.

Тогда, в логах будет может появиться запись вида:

СООБЩЕНИЕ:  временный файл: путь "base/pgsql_tmp/pgsql_tmp33513.6.sharedfileset/0.0", размер 317964288

Как видно, был создан временный файл размером 317 964 288 байт.
А ниже этой строчки будет строка с текстом запроса, который вызвал создание временного файла такого размера:

ОПЕРАТОР:  CREATE UNIQUE INDEX _inforg2483_2 ON public._inforg2483 USING btree (_fld12588, _fld4134rref, _fld4133_type, _fld4133_rtref, _fld4133_rrref);

И вот тут придётся соотносить смысл запроса с параметрами, ограничивающими потребление оперативной памяти на сеанс, в данном примере этот временный файл был сформирован в оперативной памяти, так как параметр maintenance_work_mem больше чем размер файла.
А дальше делать вывод – повышать ограничение, если это не разовая, а массовая операция и при этом оперативки ещё много, а диски уже взывают о помощи.
Или пренебречь разовыми выплесками и оставить настройки как есть.
С другой стороны, если за релевантный для вашей системы период в логах нет таких записей, то стоит понизить значение параметра log_temp_files, например в 2 раза и собрать информацию.
Затем, возможно принять решение об уменьшении параметров, так как оперативки на сервере уже не хватает.

Теперь давайте посмотрим что у нас с памятью на сервер?
За объём выделенной памяти отвечает параметр shared_buffers, который по умолчанию рекомендуется ставить в 25% от всей оперативной памяти сервера.

Тут одновременно и сложнее и интереснее…

В анализе нам очень поможет расширение pg_buffercache.
Оно работает на таблицу, поэтому перед использованием необходимо выполнить команду создания расширения: CREATE EXTENSION IF NOT EXISTS pg_buffercache;
После этого мы можем узнать общее состояние буфера командой pg_buffercache_summary();
Ну и тут будет сразу видно, если у вас число неиспользуемых буферов buffers_unused достаточно большое относительно числа используемых buffers_used, то видимо переборщили с количеством кэша и его можно уменьшить.
❗️Важное замечание – параметр shared_buffers задаётся в байтах, а значения полей buffers_unused и buffers_used выдаётся в количестве буферов, один буфер = 8КБ.

❗️Ну и наконец, с помощью этого расширения мы можем узнать какие именно таблицы базы и сколько именно буферов в общем кэше занимают.
Это можно получить следующим запросом на каждую базу, который выдаст 20 таблиц-лидеров потребления кэша:

SELECT current_database() AS database_name, CASE WHEN d.relname IS NULL THEN c.relname ELSE d.relname END AS table_name,
count(*) AS buffers_size, cast (100*count(*)/cast ((SELECT buffers_used + buffers_unused FROM pg_buffercache_summary()) as numeric) as numeric(5,2)) AS percent_buffers_used
             FROM pg_buffercache b JOIN pg_class c
             ON b.relfilenode = pg_relation_filenode(c.oid) 
    LEFT JOIN pg_class d
    ON c.oid = d.reltoastrelid 
    AND b.reldatabase IN (0, (SELECT oid FROM pg_database WHERE datname = current_database()))
             GROUP BY CASE WHEN d.relname IS NULL THEN c.relname ELSE d.relname END
             ORDER BY 3 DESC
             LIMIT 20;

Напоминаю про канал в MAX, мало ли...