PosrgreSQL表元组清理

清理查看表膨胀情况（膨胀率高建议FULL）

SELECT
schemaname,
relname AS table_name,
n_live_tup AS live_rows,
n_dead_tup AS dead_rows,
ROUND(n_dead_tup::NUMERIC / (n_live_tup + 1) * 100, 2) AS dead_tuple_percent,
'vacuum full ' || schemaname || '.' || relname AS vacuum_full_command
FROM pg_stat_user_tables
WHERE schemaname = 'xxxxxxxxxx' AND relname = 'xxxxxxxxx';
ORDER BY ROUND(n_dead_tup::NUMERIC / (n_live_tup + 1) * 100, 2) DESC

检查表的 autovacuum 状态

SELECT
relname,
(reloptions IS NULL OR reloptions::text NOT LIKE '%autovacuum_enabled=false%') AS autovacuum_is_enabled
FROM
pg_class

延迟pg autovacuum

SELECT
c.relname,
n.nspname AS schema_name,
(
c.reloptions IS NULL OR
c.reloptions::text NOT LIKE '%autovacuum_enabled=false%'
) AS autovacuum_is_enabled,
-- 拼接出关闭 autovacuum 的命令
'ALTER TABLE "' || n.nspname || '"."' || c.relname || '" SET (autovacuum_enabled=false);' AS disable_autovacuum_cmd
FROM
pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE
c.relkind = 'r' -- 只查普通表
AND n.nspname = 'xxxxxxxx';