PostgreSQL 的扩展pg_freespacemap

PostgreSQL 的扩展pg_freespacemap

pg_freespacemap 是 PostgreSQL 提供的一个内置扩展，用于查看表的空闲空间映射（Free Space Map, FSM）信息。这个扩展对于数据库性能调优和空间管理非常有用。

一 扩展概述

功能：提供对表的空闲空间映射的直接访问
用途：

• 分析表的空间利用率
• 识别空间浪费严重的表
• 优化VACUUM和空间回收策略
• 诊断膨胀问题

版本支持：PostgreSQL 8.4+（不同版本功能可能略有差异）

二、安装与启用

-- 创建扩展
CREATE EXTENSION pg_freespacemap;-- 验证是否安装成功
SELECT * FROM pg_available_extensions WHERE name = 'pg_freespacemap';

输出示例：

white=# CREATE EXTENSION pg_freespacemap;
CREATE EXTENSION
white=# SELECT * FROM pg_available_extensions WHERE name = 'pg_freespacemap';name       | default_version | installed_version |             comment              
-----------------+-----------------+-------------------+----------------------------------pg_freespacemap | 1.2             | 1.2               | examine the free space map (FSM)
(1 row)white=# 

三 主要功能函数

3.1 pg_freespace(relation regclass, blkno bigint)

返回特定表块的空闲空间字节数

-- 获取表'yewu1.t1'的第0块的空闲空间
SELECT pg_freespace('yewu1.t1'::regclass, 0);

输出示例：

white=# SELECT pg_freespace('yewu1.t1'::regclass, 0);pg_freespace 
--------------0
(1 row)white=# 

3.2 pg_freespace(relation regclass)

返回表所有块的空闲空间信息

-- 获取表'test'的所有块空闲空间
SELECT * FROM pg_freespace('yewu1.t1'::regclass);

输出示例：

white=# SELECT * FROM pg_freespace('yewu1.t1'::regclass);blkno | avail 
-------+-------0 |     0
(1 row)white=#

四 使用示例

示例1：分析表的空间利用率

-- 创建测试表
CREATE TABLE test_table (id serial, data text);
INSERT INTO test_table (data) SELECT generate_series(1,10000)::text;-- 查看空闲空间分布
SELECT blkno, avail 
FROM pg_freespace('test_table'::regclass)
ORDER BY avail DESC
LIMIT 10;

输出示例：

white=# CREATE TABLE yewu1.test_table (id serial, data text);
CREATE TABLE
white=# INSERT INTO yewu1.test_table (data) SELECT generate_series(1,10000)::text;
INSERT 0 10000
white=# 
white=# -- 查看空闲空间分布
white=# SELECT blkno, avail 
white-# FROM pg_freespace('yewu1.test_table'::regclass)
white-# ORDER BY avail DESC
white-# LIMIT 10;blkno | avail 
-------+-------4 |    322 |     07 |     09 |     05 |     06 |     03 |     08 |     01 |     00 |     0
(10 rows)white=# 

示例2：识别空间浪费严重的表

-- 找出数据库中平均空闲空间最多的表
SELECTn.nspname AS schema,c.relname AS table,pg_size_pretty(pg_relation_size(c.oid)) AS size,(SELECT avg(avail) FROM pg_freespace(c.oid)) AS avg_free_space
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
ORDER BY avg_free_space DESC
LIMIT 10;

输出示例：

white=# SELECT
white-#     n.nspname AS schema,
white-#     c.relname AS table,
white-#     pg_size_pretty(pg_relation_size(c.oid)) AS size,
white-#     (SELECT avg(avail) FROM pg_freespace(c.oid)) AS avg_free_space
white-# FROM pg_class c
white-# JOIN pg_namespace n ON n.oid = c.relnamespace
white-# WHERE c.relkind = 'r'
white-# ORDER BY avg_free_space DESC
white-# LIMIT 10;schema   |         table         |  size   | avg_free_space 
------------+-----------------------+---------+----------------pg_catalog | pg_user_mapping       | 0 bytes |               pg_catalog | pg_subscription       | 0 bytes |               pg_catalog | pg_statistic_ext_data | 0 bytes |               yewu1      | test5                 | 0 bytes |               pg_catalog | pg_foreign_table      | 0 bytes |               yewu1      | test6                 | 0 bytes |               yewu1      | test2                 | 0 bytes |               yewu1      | test4                 | 0 bytes |               yewu1      | test3                 | 0 bytes |               pg_catalog | pg_inherits           | 0 bytes |               
(10 rows)white=# 

五 输出解释

pg_freespace 函数输出

列名	类型	描述
blkno	bigint	块号（从0开始）
avail	int	该块中可用空间字节数

六 实际应用场景

场景1：定期空间监控

-- 创建监控视图
CREATE VIEW table_space_monitor AS
SELECTn.nspname AS schema,c.relname AS table,pg_size_pretty(pg_relation_size(c.oid)) AS size,(SELECT avg(avail) FROM pg_freespace(c.oid)) AS avg_free_bytes,round((SELECT sum(avail) FROM pg_freespace(c.oid)) * 100.0 / NULLIF(pg_relation_size(c.oid), 0), 2) AS free_percent
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
AND pg_relation_size(c.oid) > 0;

输出示例：

white=# CREATE VIEW table_space_monitor AS
white-# SELECT
white-#     n.nspname AS schema,
white-#     c.relname AS table,
white-#     pg_size_pretty(pg_relation_size(c.oid)) AS size,
white-#     (SELECT avg(avail) FROM pg_freespace(c.oid)) AS avg_free_bytes,
white-#     round((SELECT sum(avail) FROM pg_freespace(c.oid)) * 100.0 / 
white(#           NULLIF(pg_relation_size(c.oid), 0), 2) AS free_percent
white-# FROM pg_class c
white-# JOIN pg_namespace n ON n.oid = c.relnamespace
white-# WHERE c.relkind = 'r'
white-# AND pg_relation_size(c.oid) > 0;
CREATE VIEW
white=# 
white=# select * from table_space_monitor;schema       |          table          |    size    |     avg_free_bytes     | free_percent 
--------------------+-------------------------+------------+------------------------+--------------public             | pgbench_accounts        | 128 MB     |   107.0799220272904483 |         1.31public             | pgbench_branches        | 8192 bytes | 0.00000000000000000000 |         0.00public             | pgbench_history         | 13 MB      |     4.3969465648854962 |         0.05public             | pgbench_tellers         | 8192 bytes | 0.00000000000000000000 |         0.00pg_catalog         | pg_type                 | 128 kB     |    54.0000000000000000 |         0.66pg_catalog         | pg_statistic            | 208 kB     |    17.2307692307692308 |         0.21pg_catalog         | pg_authid               | 8192 bytes |  5984.0000000000000000 |        73.05yewu1              | t2                      | 440 kB     |   140.2181818181818182 |         1.71yewu1              | test10                  | 8192 bytes | 0.00000000000000000000 |         0.00pg_catalog         | pg_attribute            | 568 kB     |     1.3521126760563380 |         0.02pg_catalog         | pg_proc                 | 832 kB     |    74.1538461538461538 |         0.91pg_catalog         | pg_attrdef              | 8192 bytes | 0.00000000000000000000 |         0.00yewu1              | t1                      | 8192 bytes | 0.00000000000000000000 |         0.00pg_catalog         | pg_constraint           | 24 kB      |  1546.6666666666666667 |        18.88pg_catalog         | pg_index                | 40 kB      |    32.0000000000000000 |         0.39pg_catalog         | pg_operator             | 112 kB     |   162.2857142857142857 |         1.98pg_catalog         | pg_opfamily             | 16 kB      |   272.0000000000000000 |         3.32pg_catalog         | pg_opclass              | 24 kB      |   842.6666666666666667 |        10.29pg_catalog         | pg_am                   | 8192 bytes |  7392.0000000000000000 |        90.23pg_catalog         | pg_amop                 | 56 kB      |    59.4285714285714286 |         0.73pg_catalog         | pg_amproc               | 40 kB      |   921.6000000000000000 |        11.25pg_catalog         | pg_language             | 8192 bytes |  7648.0000000000000000 |        93.36pg_catalog         | pg_aggregate            | 16 kB      |   128.0000000000000000 |         1.56pg_catalog         | pg_rewrite              | 120 kB     |   157.8666666666666667 |         1.93pg_catalog         | pg_trigger              | 8192 bytes | 0.00000000000000000000 |         0.00pg_catalog         | pg_description          | 344 kB     |    97.4883720930232558 |         1.19pg_catalog         | pg_cast                 | 16 kB      |  2208.0000000000000000 |        26.95pg_catalog         | pg_namespace            | 8192 bytes |  7584.0000000000000000 |        92.58pg_catalog         | pg_conversion           | 16 kB      |   224.0000000000000000 |         2.73pg_catalog         | pg_depend               | 128 kB     | 0.00000000000000000000 |         0.00pg_catalog         | pg_database             | 8192 bytes |  7008.0000000000000000 |        85.55pg_catalog         | pg_tablespace           | 8192 bytes |  7936.0000000000000000 |        96.88pg_catalog         | pg_auth_members         | 8192 bytes |  8000.0000000000000000 |        97.66pg_catalog         | pg_shdescription        | 8192 bytes |  7936.0000000000000000 |        96.88pg_catalog         | pg_ts_config            | 8192 bytes |  5024.0000000000000000 |        61.33pg_catalog         | pg_ts_config_map        | 24 kB      |    64.0000000000000000 |         0.78pg_catalog         | pg_ts_dict              | 8192 bytes |  4000.0000000000000000 |        48.83pg_catalog         | pg_ts_parser            | 8192 bytes |  8032.0000000000000000 |        98.05pg_catalog         | pg_ts_template          | 8192 bytes |  7616.0000000000000000 |        92.97pg_catalog         | pg_extension            | 8192 bytes |  8032.0000000000000000 |        98.05pg_catalog         | pg_init_privs           | 32 kB      |    16.0000000000000000 |         0.20pg_catalog         | pg_collation            | 224 kB     |   265.1428571428571429 |         3.24pg_catalog         | pg_range                | 8192 bytes |  7776.0000000000000000 |        94.92pg_catalog         | pg_sequence             | 8192 bytes | 0.00000000000000000000 |         0.00information_schema | sql_features            | 64 kB      |   704.0000000000000000 |         8.59information_schema | sql_implementation_info | 8192 bytes |  7296.0000000000000000 |        89.06information_schema | sql_parts               | 8192 bytes |  7328.0000000000000000 |        89.45information_schema | sql_sizing              | 8192 bytes |  6176.0000000000000000 |        75.39

场景2：自动VACUUM决策

-- 找出需要VACUUM的表（空闲空间超过30%）
SELECT n.nspname AS schema,c.relname AS table,round((SELECT sum(avail) FROM pg_freespace(c.oid)) * 100.0 / NULLIF(pg_relation_size(c.oid), 0), 2) AS free_percent
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
AND (SELECT sum(avail) FROM pg_freespace(c.oid)) > 0.3 * pg_relation_size(c.oid)
ORDER BY free_percent DESC;

输出示例：

white=# SELECT 
white-#     n.nspname AS schema,
white-#     c.relname AS table,
white-#     round((SELECT sum(avail) FROM pg_freespace(c.oid)) * 100.0 / 
white(#           NULLIF(pg_relation_size(c.oid), 0), 2) AS free_percent
white-# FROM pg_class c
white-# JOIN pg_namespace n ON n.oid = c.relnamespace
white-# WHERE c.relkind = 'r'
white-# AND (SELECT sum(avail) FROM pg_freespace(c.oid)) > 
white-#     0.3 * pg_relation_size(c.oid)
white-# ORDER BY free_percent DESC;schema       |          table          | free_percent 
--------------------+-------------------------+--------------pg_catalog         | pg_ts_parser            |        98.05pg_catalog         | pg_extension            |        98.05pg_catalog         | pg_auth_members         |        97.66pg_catalog         | pg_shdescription        |        96.88pg_catalog         | pg_tablespace           |        96.88pg_catalog         | pg_range                |        94.92pg_catalog         | pg_language             |        93.36pg_catalog         | pg_ts_template          |        92.97pg_catalog         | pg_namespace            |        92.58pg_catalog         | pg_am                   |        90.23information_schema | sql_parts               |        89.45information_schema | sql_implementation_info |        89.06pg_catalog         | pg_database             |        85.55information_schema | sql_sizing              |        75.39pg_catalog         | pg_authid               |        73.05pg_catalog         | pg_ts_config            |        61.33pg_catalog         | pg_ts_dict              |        48.83
(17 rows)white=# 

七 注意事项

1. 性能影响：频繁查询FSM会对系统性能产生一定影响，建议在非高峰期使用
2. 权限要求：需要超级用户或表所有者权限
3. 实时性：FSM信息不是实时更新的，VACUUM操作后会更新
4. 外部表：不适用于外部表
5. TOAST表：需要单独检查TOAST表的空闲空间

八 与VACUUM的关系

1. VACUUM会更新FSM信息
2. FSM大小由参数max_fsm_pages和max_fsm_relations控制
3. 可以使用VACUUM VERBOSE查看FSM更新情况

-- 调整FSM参数（需要重启）
ALTER SYSTEM SET max_fsm_pages = 200000;
ALTER SYSTEM SET max_fsm_relations = 10000;

九 故障排查

问题1：扩展无法创建

解决方案：

-- 检查是否在正确数据库创建
SELECT current_database();-- 检查扩展是否已存在
SELECT * FROM pg_available_extensions WHERE name = 'pg_freespacemap';-- 以超级用户身份创建
\c - postgres
CREATE EXTENSION pg_freespacemap;

问题2：查询返回空结果

可能原因：

• 表太小（小于1个块）
• 没有空闲空间
• 权限不足

验证方法：

-- 检查表大小
SELECT pg_size_pretty(pg_relation_size('table_name'));-- 检查权限
\z table_name

通过合理使用pg_freespacemap扩展，数据库管理员可以有效监控和管理PostgreSQL表的空间使用情况，优化存储效率并减少不必要的空间浪费。