PostgreSQL 的扩展pg_prewarm - 详解

PostgreSQL 的扩展pg_prewarm

pg_prewarm 是 PostgreSQL 提供的一个实用扩展，用于将数据预先加载到共享缓冲区或操作系统缓存中，从而提升查询性能。

一、扩展概述

核心功能

• 手动预热：将指定的表或索引数据加载到内存
• 自动预热：重启后恢复之前的缓存状态
• 两级缓存：支持加载到 PostgreSQL 缓冲区或操作系统缓存

适用场景

• 数据库重启后的性能恢复
• 关键业务查询的预热
• 性能测试前的环境准备
• 定期维护后的缓存重建

二、安装与基本操作

1. 安装扩展

CREATE EXTENSION pg_prewarm;
-- 验证安装
SELECT *
FROM pg_available_extensions WHERE name = 'pg_prewarm'
;

2. 基本函数

-- 预热整个表（包括索引）
SELECT pg_prewarm('table_name'
)
;
-- 预热特定模式下的表
SELECT pg_prewarm('schema_name.table_name'
)
;
-- 预热特定索引
SELECT pg_prewarm('index_name'::regclass)
;

三、函数参数详解

函数签名

pg_prewarm(regclass,
mode
text
default 'buffer'
, fork text
default 'main'
,
first_block int8 default null
, last_block int8 default null
)

参数说明

参数	类型	说明
regclass	表/索引OID	要预热的关系对象
mode	text	'buffer'(PG缓冲区), 'read'(OS缓存), 'prefetch'(异步预取)
fork	text	'main'(主数据), 'fsm'(空闲空间图), 'vm'(可见性图)
first_block	int8	起始块号
last_block	int8	结束块号

四、实用场景示例

1. 预热关键业务表

-- 预热完整表到PG缓冲区
SELECT pg_prewarm('orders'
, 'buffer'
)
;
-- 预热到操作系统缓存
SELECT pg_prewarm('order_items'
, 'read'
)
;
-- 只预热前1000个块
SELECT pg_prewarm('customers'
, 'buffer'
, 'main'
, 0
, 999
)
;

2. 自动重启预热

-- 创建预热函数
CREATE OR
REPLACE
FUNCTION auto_prewarm(
)
RETURNS void AS $$
DECLARE
r RECORD;
BEGIN
FOR r IN
SELECT relname FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE c.relkind IN ('r'
,'i'
)
AND n.nspname NOT IN ('pg_catalog'
, 'information_schema'
)
LOOP
EXECUTE format('SELECT pg_prewarm(%L, ''buffer'')'
, r.relname)
;
END
LOOP
;
END
;
$$ LANGUAGE plpgsql;
-- 设置数据库启动时执行
ALTER SYSTEM SET shared_preload_libraries = 'pg_prewarm'
;
ALTER SYSTEM SET pg_prewarm.autoprewarm =
on
;
SELECT pg_reload_conf(
)
;

3. 定期维护脚本

#!/bin/bash
# 每周预热热门表
PGPASSWORD=$DB_PASS psql -h $DB_HOST -U $DB_USER -d $DB_NAME <<
EOF
SELECT pg_prewarm('hot_table1', 'buffer');
SELECT pg_prewarm('hot_table2', 'read');
EOF

五、高级用法

1. 结合pg_stat_user_tables

-- 预热最常访问的表
SELECT pg_prewarm(schemaname||'.'||relname, 'buffer'
)
FROM pg_stat_user_tables
ORDER
BY seq_scan + idx_scan DESC
LIMIT 10
;

2. 块级精确预热

-- 只预热特定范围的块
SELECT pg_prewarm(
'large_table'
,
'buffer'
,
'main'
,
(
SELECT min(ctid::text::point
)[0]::int8 FROM large_table WHERE create_date >
'2023-01-01'
)
,
(
SELECT max(ctid::text::point
)[0]::int8 FROM large_table WHERE create_date >
'2023-01-01'
)
)
;

3. 并行预热大表

-- 使用dblink并行预热
SELECT dblink_connect('conn1'
, 'dbname=mydb'
)
;
SELECT dblink_connect('conn2'
, 'dbname=mydb'
)
;
-- 分片预热
SELECT dblink_send_query('conn1'
,
'SELECT pg_prewarm(''big_table'', ''buffer'', ''main'', 0, 100000)'
)
;
SELECT dblink_send_query('conn2'
,
'SELECT pg_prewarm(''big_table'', ''buffer'', ''main'', 100001, 200000)'
)
;

六、性能注意事项

1. 资源消耗：

   • 预热过程会消耗大量I/O和内存带宽
   • 建议在低峰期执行大规模预热

2. 模式选择：

   -- 三种模式对比
   /*
   buffer: 加载到PG共享缓冲区（最快但容量有限）
   read: 加载到OS页面缓存（不挤占PG缓冲区）
   prefetch: 异步预取（不等待完成）
   */

3. 监控方法：

   -- 查看预热效果
   SELECT c.relname, COUNT(*
   )
   AS buffers
   FROM pg_buffercache b
   JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
   WHERE c.relname = 'prewarmed_table'
   GROUP
   BY c.relname;

七、与类似工具对比

特性	pg_prewarm	pgfincore	os预热工具
内置PG支持	✔	✖ (第三方扩展)	✖
精确控制预热范围	✔	✔	✖
支持操作系统缓存	✔	✔	✔
自动重启恢复	✔	✖	✖

八、生产环境建议

1. 关键表预热：

   -- 每日任务预热TOP10表
   SELECT pg_prewarm(relid::regclass, 'buffer'
   )
   FROM pg_stat_user_tables
   ORDER
   BY idx_scan + seq_scan DESC
   LIMIT 10
   ;

2. 配置优化：

   # postgresql.conf
   shared_buffers = 8GB                # 足够容纳预热数据
   pg_prewarm.autoprewarm = on         # 启用自动预热
   pg_prewarm.autoprewarm_interval = 300  # 每5分钟保存状态

3. 避免过度使用：

   • 不要预热很少访问的表
   • 监控缓冲区命中率调整策略

   SELECT sum(heap_blks_hit) /
   nullif(sum(heap_blks_hit + heap_blks_read)
   , 0
   )
   FROM pg_statio_user_tables;

pg_prewarm 是PostgreSQL性能调优工具箱中的重要组件，合理使用可以显著减少冷启动后的性能波动，但需要根据实际业务负载设计预热策略。