自己做的网站发布详细步骤,企业网络构建,怎么做网站跳转,中国建设银行网站的社保板块在哪PostgreSQL的扩展pg_visibility
pg_visibility 是 PostgreSQL 提供的一个高级扩展#xff0c;用于检查表的可见性映射(Visibility Map, VM)和页面级可见性信息。这个扩展主要帮助数据库管理员诊断和解决与MVCC(多版本并发控制)相关的性能问题。
一、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_blocksFROM pg_class cWHERE c.relkind rAND 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 需要紧急VACUUMWHEN bad_blocks 20 THEN 建议近期VACUUMELSE 状态良好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 pJOIN 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 rAND n.nspname NOT LIKE pg_%AND n.nspname ! information_schema
\gexec2. 与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;六、注意事项 性能影响 扫描大型表的可见性映射可能消耗大量I/O资源建议在低峰期执行相关诊断操作 权限要求 需要超级用户或对目标表有SELECT权限某些函数可能需要额外的权限 版本兼容性 不同PostgreSQL版本中函数可能略有不同建议查看对应版本的文档
七、最佳实践 定期监控 -- 创建定期监控视图
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 rAND n.nspname NOT LIKE pg_%AND n.nspname ! information_schema;自动化报告 -- 生成需要关注的表列表
SELECT * FROM visibility_monitor
WHERE non_visible_blocks 10
ORDER BY pct_non_visible DESC;与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机制内部工作状态的能力是诊断和解决表膨胀问题的重要工具。合理使用这些功能可以显著提高数据库维护效率和查询性能。
