PostgreSQL 内存占用查看（Memory / Buffers / Connections）

SHOW shared_buffers;              -- 共享内存：数据页缓存
SHOW work_mem;                    -- 私有内存：单次排序/哈希
SHOW maintenance_work_mem;        -- 私有内存：VACUUM / CREATE INDEX 等
SHOW temp_buffers;                -- 会话私有：临时表缓存
SHOW max_connections;             -- 连接上限
SHOW superuser_reserved_connections; -- 预留给超级用户的连接数

SELECT name, setting, unit, short_desc
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'maintenance_work_mem',
               'temp_buffers', 'max_connections', 'effective_cache_size');

-- ① 会话级：只影响当前会话，立即生效（适合试参数、临时调整）
SET work_mem = '128MB';
SET LOCAL work_mem = '128MB';   -- 事务结束自动恢复

-- ② 实例级：ALTER SYSTEM 写入 postgresql.auto.conf（需超级用户；优先级高于 postgresql.conf）
ALTER SYSTEM SET work_mem = '64MB';
ALTER SYSTEM RESET work_mem;    -- 恢复默认

SELECT name, setting, unit, context, pending_restart
FROM pg_settings
WHERE name IN ('shared_buffers', 'max_connections',
               'superuser_reserved_connections', 'work_mem',
               'maintenance_work_mem', 'temp_buffers', 'effective_cache_size');

-- 例 1：work_mem 这类可热加载的参数
ALTER SYSTEM SET work_mem = '64MB';
SELECT pg_reload_conf();   -- 返回 true 表示已发出 reload 信号

-- 例 2：shared_buffers / max_connections 这类需要重启的
ALTER SYSTEM SET shared_buffers = '6GB';
ALTER SYSTEM SET max_connections = '300';
SELECT name, setting FROM pg_settings WHERE pending_restart;  -- 列出待重启才生效的参数（PG 9.5+）

-- 然后重启（方式因部署而异）
pg_ctl restart -D $PGDATA          -- 或 systemctl restart postgresql / 平台控制台

SHOW shared_buffers;
-- 或看来源：boot_val 默认值 / setting 当前值 / source 值从哪来
SELECT name, boot_val, setting, source, pending_restart
FROM pg_settings WHERE name IN ('shared_buffers', 'work_mem');

-- 按大小排序看每个共享内存模块
SELECT name, size, allocated_size
FROM pg_shmem_allocations
ORDER BY size DESC;

-- 共享内存总大小（所有行 size 之和，含 named / anonymous / 未用内存）
SELECT pg_size_pretty(sum(size)) AS total_shared
FROM pg_shmem_allocations;

SELECT datname,
       round(sum(blks_hit) * 100.0 / nullif(sum(blks_hit + blks_read), 0), 2) AS hit_rate_pct
FROM pg_stat_database
GROUP BY datname
ORDER BY hit_rate_pct;

-- 被全表扫描最多的表（seq_scan 高 = 频繁扫全表）
SELECT schemaname, relname, seq_scan, seq_tup_read
FROM pg_stat_user_tables
ORDER BY seq_scan DESC
LIMIT 10;

CREATE EXTENSION IF NOT EXISTS pg_buffercache;

-- 哪个表/索引占用了最多的缓存页
SELECT c.relname,
       count(*)             AS buffers,
       pg_size_pretty(count(*) * current_setting('block_size')::int) AS approx
FROM pg_buffercache b
JOIN pg_class c ON c.relfilenode = b.relfilenode
GROUP BY c.relname
ORDER BY buffers DESC
LIMIT 20;

SELECT count(*) AS dirty_buffers FROM pg_buffercache WHERE isdirty;

SELECT datname,
       temp_files,
       pg_size_pretty(temp_bytes) AS temp_bytes
FROM pg_stat_database
ORDER BY temp_bytes DESC;

SHOW log_temp_files;
-- 建议设 0：ALTER SYSTEM SET log_temp_files = 0; 之后日志里搜 "temporary file"

SELECT n.nspname,
       pg_size_pretty(sum(pg_total_relation_size(c.oid))) AS total
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname LIKE 'pg_temp%'
GROUP BY n.nspname;

-- 当前连接数 vs 上限
SELECT count(*) AS current_conns FROM pg_stat_activity;
SHOW max_connections;

-- 按数据库 + 状态聚合，快速定位谁在占坑
SELECT datname, state, count(*)
FROM pg_stat_activity
GROUP BY datname, state
ORDER BY 1, 2;

-- 仅客户端连接（不含 autovacuum / background worker 等；PG 14+）
SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'client backend';