PostgreSQL查询shared buffer使用情况和清理方式c.round(100.round(100.0 *

本文已参与「新人创作礼」活动，一起开启掘金创作之路

查询shared buffer使用情况：

SELECT
     c.relname,
     pg_size_pretty(count(*) * 8192) as buffered,
     round(100.0 * count(*) / (SELECT setting FROM pg_settings WHERE name='shared_buffers')::integer, 1) AS buffer_percent,
     round(100.0 * count(*) * 8192 / pg_table_size(c.oid), 1) AS percent_of_relation
 FROM pg_class c
 INNER JOIN pg_buffercache b ON b.relfilenode = c.relfilenode
 INNER JOIN pg_database d ON (b.reldatabase = d.oid AND d.datname = current_database())
 GROUP BY c.oid, c.relname
 ORDER BY 3 DESC
 LIMIT 10;

清空shared buffer：

pg_ctl stop
sync
echo 3 > /proc/sys/vm/drop_caches
pg_ctl start

可以使用pg_dropcache插件，但是仅支持pg13以下的版本：
github.com/zilder/pg_d…