PostgreSQL的扩展pg_visibility

PostgreSQL的扩展pg_visibility

pg_visibility 是 PostgreSQL 提供的一个高级扩展，用于检查表的可见性映射(Visibility Map, VM)和页面级可见性信息。这个扩展主要帮助数据库管理员诊断和解决与MVCC(多版本并发控制)相关的性能问题。

一、pg_visibility 扩展概述

核心功能

• 检查可见性映射：查看哪些数据块被标记为"全部可见"
• 诊断页面问题：识别可能包含死元组(dead tuples)的页面
• 维护辅助：帮助优化VACUUM操作

适用场景

• 分析表膨胀(table bloat)问题
• 诊断VACUUM操作效果不佳的情况
• 高级性能调优

二、安装与启用

1. 安装扩展

-- 使用超级用户或具有CREATEEXTENSION权限的用户执行
CREATE EXTENSION pg_visibility;

-- 验证安装
SELECT * FROM pg_extension WHERE extname = 'pg_visibility';

2. 查看提供的函数

\df pg_visibility.*

三、主要功能详解

1. 基本功能函数

检查表的可见性映射

-- 检查表的可见性映射状态
SELECT * FROM pg_visibility('表名');

-- 示例：检查public.sample表的可见性
SELECT * FROM pg_visibility('public.sample');

检查特定页面的可见性

-- 检查特定页面的详细可见性信息
SELECT * FROM pg_visibility_map('表名', 页号);

-- 示例：检查public.orders表的第5页
SELECT * FROM pg_visibility_map('public.orders', 5);

2. 高级诊断功能

检查全部可见的页面

-- 找出表中所有被标记为"全部可见"的页面
SELECT blkno FROM pg_visibility('表名') WHERE all_visible;

检查包含死元组的页面

-- 找出表中包含死元组的页面
SELECT blkno FROM pg_visibility('表名') WHERE all_visible IS FALSE;

四、实际应用场景

1. 表膨胀分析

-- 结合pg_class分析表膨胀情况
SELECT 
    c.relname,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
    (SELECT count(*) FROM pg_visibility(c.oid) WHERE all_visible) AS visible_blocks,
    (SELECT count(*) FROM pg_visibility(c.oid) WHERE all_visible IS FALSE) AS non_visible_blocks
FROM 
    pg_class c
WHERE 
    c.relkind = 'r' 
    AND c.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')
ORDER BY 
    non_visible_blocks DESC;

2. 优化VACUUM策略

-- 识别需要重点VACUUM的表
WITH vm_stats AS (
    SELECT 
        c.oid,
        c.relname,
        (SELECT count(*) FROM pg_visibility(c.oid) WHERE all_visible IS FALSE) AS bad_blocks
    FROM 
        pg_class c
    WHERE 
        c.relkind = 'r'
        AND c.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')
)
SELECT 
    relname,
    bad_blocks,
    pg_size_pretty(pg_total_relation_size(oid)) AS size,
    CASE 
        WHEN bad_blocks > 100 THEN '需要紧急VACUUM'
        WHEN bad_blocks > 20 THEN '建议近期VACUUM'
        ELSE '状态良好'
    END AS recommendation
FROM 
    vm_stats
WHERE 
    bad_blocks > 0
ORDER BY 
    bad_blocks DESC;

3. 验证VACUUM效果

-- 在VACUUM前后比较可见性映射变化
-- VACUUM前
CREATE TEMP TABLE pre_vacuum_visibility AS
SELECT * FROM pg_visibility('large_table');

-- 执行VACUUM
VACUUM (VERBOSE, ANALYZE) large_table;

-- VACUUM后
SELECT 
    p.blkno,
    p.all_visible AS before_vacuum,
    n.all_visible AS after_vacuum
FROM 
    pre_vacuum_visibility p
    JOIN pg_visibility('large_table') n ON p.blkno = n.blkno
WHERE 
    p.all_visible IS DISTINCT FROM n.all_visible;

五、高级用法

1. 批量检查所有表的可见性状态

-- 生成检查所有用户表的SQL
SELECT 
    format('SELECT %L AS table_name, (SELECT count(*) FROM pg_visibility(%L) WHERE all_visible IS FALSE) AS bad_blocks', 
           n.nspname || '.' || c.relname, 
           n.nspname || '.' || c.relname)
FROM 
    pg_class c
JOIN 
    pg_namespace n ON c.relnamespace = n.oid
WHERE 
    c.relkind = 'r'
    AND n.nspname NOT LIKE 'pg_%'
    AND n.nspname != 'information_schema'
\gexec

2. 与pageinspect扩展结合使用

-- 首先安装pageinspect扩展
CREATE EXTENSION IF NOT EXISTS pageinspect;

-- 检查特定页面的详细内容
SELECT 
    v.blkno,
    v.all_visible,
    (SELECT count(*) FROM heap_page_items(get_raw_page('public.orders', v.blkno)) WHERE t_infomask & 256 = 0) AS dead_tuples
FROM 
    pg_visibility('public.orders') v
WHERE 
    v.all_visible IS FALSE
LIMIT 10;

六、注意事项

1. 性能影响：

   • 扫描大型表的可见性映射可能消耗大量I/O资源
   • 建议在低峰期执行相关诊断操作

2. 权限要求：

   • 需要超级用户或对目标表有SELECT权限
   • 某些函数可能需要额外的权限

3. 版本兼容性：

   • 不同PostgreSQL版本中函数可能略有不同
   • 建议查看对应版本的文档

七、最佳实践

1. 定期监控：

   -- 创建定期监控视图
   CREATE VIEW visibility_monitor AS
   SELECT 
       n.nspname || '.' || c.relname AS table_name,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS size,
       (SELECT count(*) FROM pg_visibility(c.oid) WHERE all_visible IS FALSE) AS non_visible_blocks,
       (SELECT count(*) FROM pg_visibility(c.oid)) AS total_blocks,
       round((SELECT count(*) FROM pg_visibility(c.oid) WHERE all_visible IS FALSE)::numeric / 
             (SELECT count(*) FROM pg_visibility(c.oid)) * 100, 2) AS pct_non_visible
   FROM 
       pg_class c
   JOIN 
       pg_namespace n ON c.relnamespace = n.oid
   WHERE 
       c.relkind = 'r'
       AND n.nspname NOT LIKE 'pg_%'
       AND n.nspname != 'information_schema';
   

2. 自动化报告：

   -- 生成需要关注的表列表
   SELECT * FROM visibility_monitor
   WHERE non_visible_blocks > 10
   ORDER BY pct_non_visible DESC;
   

3. 与autovacuum配置结合：

   -- 根据可见性状态调整autovacuum参数
   ALTER TABLE high_churn_table SET (
       autovacuum_vacuum_scale_factor = 0.05,
       autovacuum_vacuum_threshold = 1000,
       autovacuum_analyze_scale_factor = 0.02
   );
   

pg_visibility扩展为PostgreSQL管理员提供了深入了解MVCC机制内部工作状态的能力，是诊断和解决表膨胀问题的重要工具。合理使用这些功能可以显著提高数据库维护效率和查询性能。