PostgreSQL процент попадания в кэш

postgres=# SELECT round(100 * sum(blks_hit) / sum(blks_hit + blks_read), 3) as cache_hit_ratio
FROM pg_stat_database;
 cache_hit_ratio
-----------------
          94.662
(1 строка)

Использование буферного кеша:

postgres=# CREATE EXTENSION pg_buffercache;
CREATE EXTENSION
postgres=#

SELECT 'total', pg_size_pretty(count(*) * (SELECT current_setting('block_size')::int))
FROM pg_buffercache
UNION SELECT 'dirty', pg_size_pretty(count(*) * (SELECT current_setting('block_size')::int))
FROM pg_buffercache
WHERE isdirty
UNION SELECT 'clear', pg_size_pretty(count(*) * (SELECT current_setting('block_size')::int))
FROM pg_buffercache
WHERE NOT isdirty
UNION SELECT 'used', pg_size_pretty(count(*) * (SELECT current_setting('block_size')::int))
FROM pg_buffercache
WHERE reldatabase IS NOT NULL
UNION SELECT 'free', pg_size_pretty(count(*) * (SELECT current_setting('block_size')::int))
FROM pg_buffercache
WHERE reldatabase IS NULL;
SELECT
d.datname,
pg_size_pretty(pg_database_size(d.datname)) AS database_size,
pg_size_pretty(count(b.bufferid) * (SELECT current_setting('block_size')::int)) AS size_in_shared_buffers,
round((100 * count(b.bufferid) / (SELECT setting FROM pg_settings WHERE name = 'shared_buffers')::decimal),2)
AS pct_of_shared_buffers
FROM pg_buffercache b
JOIN pg_database d ON b.reldatabase = d.oid
WHERE b.reldatabase IS NOT NULL
GROUP BY 1 ORDER BY 4 DESC LIMIT 10;

   datname  | database_size | size_in_shared_buffers | pct_of_shared_buffers
-----------+---------------+------------------------+-----------------------
 demo1     | 5342 MB       | 120 MB                 |                 93.98
 demo      | 1960 MB       | 5896 kB                |                  4.50
 postgres  | 7653 kB       | 1200 kB                |                  0.92
 template1 | 7621 kB       | 640 kB                 |                  0.49
(4 строки)

# postgresql.conf
shared_preload_libraries = 'online_analyze, plantuner, pg_stat_statements' # (change requires restart)

pg_stat_statements.max = 10000
pg_stat_statements.track = all

Сохранить.

postgres=# CREATE EXTENSION pg_stat_statements;