pg_freespacemap模块提供一种检查*空间映射（FSM）的手段。它提供一个名为pg_freespace的函数，或精确的说是两个重载函数。该函数在一个给定的页面或关系中的所有页面的*空间映射内显示记录的值。缺省的公共访问在该函数中取消了，只是因为潜藏的安全问题。
1，创建扩展
jinli=# create extension pg_freespacemap; CREATE EXTENSION jinli=# \dxS+ pg_freespacemap Objects in extension "pg_freespacemap" Object Description ---------------------------------------- function pg_freespace(regclass) function pg_freespace(regclass,bigint) (2 rows)
可以发现pg_freespacemap扩展模块被创建出来后，多了两个重载函数。
2，示例输出
jinli=# select * from pg_freespace(‘public.log_level_problem‘); blkno | avail
-------+-------
0 | 73921 | 66562 | 35523 | 3204 | 45765 | 49286 | 59527 | 80648 | 79369 | 793610 | 6944 (11 rows) select * from pg_freespace(‘public.log_level_problem‘,10); pg_freespace
--------------
6944 (1 row) jinli=# --查看表的平均空间空闲率 jinli=# select count(1) as "number of pages",pg_size_pretty(cast(avg(avail) as bigint)) as "freespace size",round(100 * avg(avail)/8192,2)||‘%‘ as "freespace tatio" from pg_freespace(‘public.log_level_problem‘); number of pages | freespace size | freespace tatio
-----------------+---------------------+----------------------
11 | 5841 bytes | 71.31%(1 row) jinli=# vacuum public.log_level_problem; VACUUM jinli=# select count(1) as "number of pages",pg_size_pretty(cast(avg(avail) as bigint)) as "freespace size",round(100 * avg(avail)/8192,2)||‘%‘ as "freespace tatio" from pg_freespace(‘public.log_level_problem‘); number of pages |freespace size |freespace tatio
-----------------+---------------------+----------------------
11 | 5690 bytes | 69.46% (1 row) vacuum full public.log_level_problem; VACUUM jinli=# select count(1) as "number of pages",pg_size_pretty(cast(avg(avail) as bigint)) as "freespace size",round(100 * avg(avail)/8192,2)||‘%‘ as "freespace tatio" from pg_freespace(‘public.log_level_problem‘); number of pages | freespace size | freespace tatio
-----------------+---------------------+----------------------
4 | 0 bytes | 0.00%
(1 row)
jinli=# select * from pg_freespace(‘public.log_level_problem‘);
blkno | avail
-------+-------
0 | 01 | 02 | 03 | 0 (4 rows)
j该示例可以看出来执行完vacuum并不能全部释放空间，需执行vacuum full之后才能彻底“压实”页面。