EXTENSIONS 合集

1. 可见性地图 - pg_visibility

CREATE EXTENSION pg_visibility;

select * from pg_visibility_map_summary('test_vm'::regclass);
 all_visible | all_frozen 
-------------+------------
         541 |          0
         
select * from pg_visibility_map('test_vm'::regclass);
 blkno | all_visible | all_frozen 
-------+-------------+------------
     0 | t           | f
     1 | t           | f

 

2. 空闲空间地图- pg_freespacemap

CREATE EXTENSION pg_freespacemap;

# 查看整个表的可用性地图
select *, round(100 * avail/8192, 2) as "freespace ratio" from pg_freespace('test_vm');
 blkno | avail | freespace ratio 
-------+-------+-----------------
     0 |    64 |            0.00
     1 |     0 |            0.00
     2 |     0 |            0.00

# 查看指定的page
SELECT pg_freespace('public.test_vm', 0);
 pg_freespace 
--------------
           64
(1 row)

 

3. Page和元组结构 - pageinspect

CREATE EXTENSION IF NOT EXISTS pageinspect;

# 查看page header
SELECT
    h.*,
    h.upper - h.lower AS contiguous_free_space
FROM page_header(
    get_raw_page('public.test_vm', 0)
) AS h;
    lsn     | checksum | flags | lower | upper | special | pagesize | version | prune_xid | contiguous_free_space 
------------+----------+-------+-------+-------+---------+----------+---------+-----------+-----------------------
 1/CAEFBB18 |        0 |     5 |   764 |   832 |    8192 |     8192 |       4 |         0 |                    68
(1 row)

# 查看page 0里面的元组
SELECT
    lp,
    lp_off,
    lp_flags,
    lp_len,
    t_xmin,
    t_xmax,
    t_ctid,
    t_oid,
    t_data
FROM heap_page_items(
    get_raw_page('public.test_vm', 'main', 0)
);
 lp  | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_ctid  | t_oid |             t_data             
-----+--------+----------+--------+--------+--------+---------+-------+--------------------------------
   1 |      0 |        0 |      0 |        |        |         |       | 
   2 |   8152 |        1 |     36 |    878 |      0 | (0,2)   |       | \x0200000011676176696e5f32
   3 |   8112 |        1 |     36 |    878 |      0 | (0,3)   |       | \x0300000011676176696e5f33

 

4. 查看死元组 - pgstattuple

CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- 查询指定表的精确膨胀信息
mydb=# SELECT
    table_len,                                                        -- 表物理大小（字节）
    tuple_count,                                                      -- 活元组数
    tuple_percent,                                                                                                        -- 活元组占比
    dead_tuple_count,                                                 -- 死元组数
    round(dead_tuple_len * 100.0 / table_len, 2) AS dead_space_pct,   -- 死空间占比
    free_space,                                                       -- 可复用空间
    round(free_space * 100.0 / table_len, 2)     AS free_space_pct    -- 可复用空间占比
FROM pgstattuple('test_auto_vacuum');
 table_len  | tuple_count | tuple_percent | dead_tuple_count | dead_space_pct | free_space | free_space_pct 
------------+-------------+---------------+------------------+----------------+------------+----------------
 2984763392 |      100000 |          0.11 |                0 |           0.00 | 2970960520 |          99.54
(1 row)

 

5. 批量导入数据 - pg_bulkload

典型场景包括：

• 数据仓库、报表库批量装载。
• 从 CSV 或其他文本文件导入海量数据。
• 数据迁移、历史数据初始化。
• ETL 流程中的批量加载。
• 定时导入日志、账单、流水等离线数据。
• 测试环境快速生成或恢复大量测试数据。
• 对错误行进行过滤和单独记录。

# 安装 github：https://github.com/ossc-db/pg_bulkload
make PG_CONFIG=/usr/local/postgresql/bin/pg_config
make install

mydb=# create extension pg_bulkload;
CREATE EXTENSION

# 使用控制文件来加载数据
vim test2.ctl

INPUT = /home/postgres/test/bulktest.txt
PARSE_BADFILE = /home/postgres/test/bulktest-parse.log
LOGFILE = /home/postgres/test/bulktest.log
LIMIT = INFINITE
PARSE_ERRORS = 0
CHECK_CONSTRAINTS = NO
TYPE = CSV
SKIP = 0
DELIMITER = |
QUOTE = "\""
ESCAPE = "\""
OUTPUT = public.test_2
MULTI_PROCESS = NO
VERBOSE = YES
WRITER = DIRECT
DUPLICATE_BADFILE = /home/postgres/test/20260907052115_mydb_public_test_2.dup.csv
DUPLICATE_ERRORS = 0
ON_DUPLICATE_KEEP = NEW
TRUNCATE = NO 

# 执行控制文件
pg_bulkload test.ctl -d mydb