外观
PostgreSQL 性能优化扩展
概述
PostgreSQL 提供了丰富的内置性能优化功能，但在实际生产环境中，有时需要更强大的工具来满足复杂的性能优化需求。PostgreSQL 的扩展机制允许用户安装和使用各种性能优化扩展，以增强数据库的性能优化能力。
本文将介绍 PostgreSQL 中常用的性能优化扩展，包括它们的安装、配置、使用方法和最佳实践，帮助 DBA 和开发人员在实际工作中合理选择和使用这些扩展，提高数据库性能优化的效率和质量。
常用性能优化扩展
pg_repack - 在线表重构
什么是 pg_repack
pg_repack 是一个用于在线表重构的扩展，可以在不阻塞读写操作的情况下，重建表和索引，解决表膨胀问题，提高查询性能。
版本支持
- 兼容 PostgreSQL 9.4+ 版本
- PostgreSQL 14+ 支持并行重构
- PostgreSQL 15+ 支持更多表类型
安装方法
bash
# Ubuntu/Debian
apt-get install postgresql-14-repack
# CentOS/RHEL 8+
dnf install postgresql14-repack
# 源码编译（适用于所有版本）
git clone https://github.com/reorg/pg_repack.git
cd pg_repack
make && make installsql
-- 安装扩展
CREATE EXTENSION IF NOT EXISTS pg_repack;生产环境核心配置
在 postgresql.conf 中添加以下配置（可选，用于优化 pg_repack 性能）：
ini
# 并行工作进程数，建议设置为 CPU 核心数的 1/4 到 1/2
max_parallel_workers_maintenance = 4生产环境最佳实践
表膨胀评估
sql
-- 安装 pgstattuple 扩展评估表膨胀
CREATE EXTENSION IF NOT EXISTS pgstattuple;
-- 检查表膨胀情况
SELECT
table_len,
tuple_len,
tuple_count,
dead_tuple_len,
dead_tuple_count,
round((dead_tuple_len::numeric / table_len) * 100, 2) AS dead_tuple_percent
FROM pgstattuple('your_table_name');
-- 当死元组比例超过 20% 或表大小增长过快时，考虑使用 pg_repack在线重构操作
bash
# 重建表（解决表膨胀）
pg_repack -d your_database -t your_table --jobs 4
# 重建表和索引
pg_repack -d your_database -t your_table -x --jobs 4
# 只重建索引
pg_repack -d your_database -t your_table -o --jobs 4
# 重建指定索引
pg_repack -d your_database -i your_index --jobs 4
# 使用连接串
pg_repack "host=localhost port=5432 dbname=your_database user=postgres" -t your_table --jobs 4自动化重构脚本
bash
#!/bin/bash
# 自动检测并重构膨胀率超过 20% 的表
DB_NAME="your_database"
THRESHOLD=20
LOG_FILE="/var/log/pg_repack.log"
# 获取膨胀率超过阈值的表
INFLATED_TABLES=$(psql -d $DB_NAME -t -c "
SELECT schemaname || '.' || relname
FROM pg_stat_user_tables
WHERE pg_total_relation_size(relid) > 104857600 -- 只处理大于 100MB 的表
ORDER BY pg_total_relation_size(relid) DESC
")
# 对每个膨胀表执行重构
for TABLE in $INFLATED_TABLES; do
TABLE=$(echo $TABLE | xargs) # 去除空格
if [ -n "$TABLE" ]; then
echo "$(date): 开始重构表 $TABLE" >> $LOG_FILE
pg_repack -d $DB_NAME -t "$TABLE" --jobs 4 >> $LOG_FILE 2>&1
if [ $? -eq 0 ]; then
echo "$(date): 表 $TABLE 重构成功" >> $LOG_FILE
else
echo "$(date): 表 $TABLE 重构失败" >> $LOG_FILE
fi
fi
done注意事项
- 确保有足够的磁盘空间（至少需要表大小的 1.5 倍）
- 在业务低峰期执行，避免影响生产业务
- PostgreSQL 14+ 支持并行重构，使用
--jobs参数指定并行度 - 不支持临时表、外部表和某些特殊表类型
pg_prewarm - 数据预加载
什么是 pg_prewarm
pg_prewarm 是 PostgreSQL 提供的一个数据预加载扩展，可以将表或索引的数据预加载到 PostgreSQL 的缓冲区缓存中，减少后续查询的磁盘 I/O，提高查询性能。
版本支持
- PostgreSQL 9.4+ 内置支持
- PostgreSQL 13+ 新增
prefetch模式
安装方法
sql
-- 安装扩展
CREATE EXTENSION IF NOT EXISTS pg_prewarm;生产环境应用场景
数据库启动后预加载热点数据
sql
-- 数据库重启后，预加载核心业务表
SELECT pg_prewarm('users');
SELECT pg_prewarm('orders');
SELECT pg_prewarm('products');
-- 预加载索引
SELECT pg_prewarm('idx_orders_user_id', mode => 'index');
SELECT pg_prewarm('idx_users_email', mode => 'index');表维护后预加载
sql
-- VACUUM FULL 或 pg_repack 后预加载
VACUUM FULL VERBOSE your_table;
SELECT pg_prewarm('your_table');
-- 重建索引后预加载
REINDEX INDEX your_index;
SELECT pg_prewarm('your_index', mode => 'index');智能预加载策略
sql
-- 根据查询频率预加载表的热点区域
-- 1. 先查看表的块数量
SELECT relname, relpages FROM pg_class WHERE relname = 'your_table';
-- 2. 预加载表的前 1000 个块（通常是热点数据）
SELECT pg_prewarm('your_table', first_block => 0, last_block => 999, mode => 'buffer');
-- 3. PostgreSQL 13+ 使用预取模式，异步加载
SELECT pg_prewarm('your_table', mode => 'prefetch');预加载效果验证
sql
-- 预加载前查看缓存命中率
SELECT
sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS cache_hit_rate
FROM pg_statio_user_tables
WHERE relname = 'your_table';
-- 执行预加载
SELECT pg_prewarm('your_table');
-- 预加载后再次查看缓存命中率
SELECT
sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS cache_hit_rate
FROM pg_statio_user_tables
WHERE relname = 'your_table';最佳实践
- 只预加载频繁访问的热点表和索引
- 在业务低峰期或数据库重启后执行
- 结合
pg_buffercache扩展监控缓存使用情况 - PostgreSQL 13+ 优先使用
prefetch模式，减少对系统的影响 - 对于非常大的表，只预加载热点数据区域
pg_partman - 分区表管理
什么是 pg_partman
pg_partman 是一个用于分区表管理的扩展，可以自动创建和管理分区表，支持按时间、数值等多种方式分区，提高大表的查询性能。
版本支持
- 兼容 PostgreSQL 9.6+ 版本
- PostgreSQL 10+ 支持原生分区表
- PostgreSQL 13+ 支持并行分区维护
安装方法
bash
# Ubuntu/Debian
apt-get install postgresql-14-partman
# CentOS/RHEL 8+
dnf install postgresql14-partmansql
-- 安装扩展
CREATE EXTENSION IF NOT EXISTS pg_partman;生产环境配置
在 postgresql.conf 中添加以下配置：
ini
# 启用 pg_partman 自动维护
shared_preload_libraries = 'pg_partman_bgw'
pg_partman_bgw.interval = 3600 # 自动维护间隔（秒）
pg_partman_bgw.role = 'postgres' # 执行维护的角色生产环境最佳实践
按时间分区表创建
sql
-- 创建父表
CREATE TABLE logs (
id SERIAL,
event_time TIMESTAMP NOT NULL,
event_type VARCHAR(50),
event_data JSONB
);
-- 初始化分区（PostgreSQL 10+ 原生分区）
SELECT partman.create_parent(
p_parent_table => 'public.logs',
p_control => 'event_time',
p_type => 'native',
p_interval => 'daily',
p_premake => 7, -- 预创建 7 天的分区
p_start_partition => '2023-01-01', -- 开始分区日期
p_inherit_fk => true -- 继承外键约束
);
-- 查看分区情况
SELECT * FROM partman.show_partitions('public.logs');分区自动维护
sql
-- 手动触发分区维护
SELECT partman.run_maintenance_proc();
-- 结合 pg_cron 定期执行
SELECT cron.schedule(
'hourly-partman-maintenance',
'0 * * * *',
'CALL partman.run_maintenance_proc()'
);
-- 配置分区保留策略（保留 30 天数据）
UPDATE partman.part_config
SET retention = '30 days',
retention_keep_table = false, -- 自动删除过期分区
retention_keep_index = false
WHERE parent_table = 'public.logs';分区表性能优化
sql
-- 确保查询包含分区键
EXPLAIN ANALYZE
SELECT * FROM logs
WHERE event_time BETWEEN '2023-01-01' AND '2023-01-31'; -- 只扫描相关分区
-- 避免全部分区扫描
EXPLAIN ANALYZE
SELECT * FROM logs
WHERE event_type = 'error'; -- 可能扫描所有分区
-- 为分区表添加合适的索引
CREATE INDEX idx_logs_event_type ON logs(event_type) INCLUDE (event_data);注意事项
- 选择合适的分区间隔，避免分区过多或过少
- 确保查询包含分区键，充分利用分区裁剪
- 定期监控分区大小和数量，调整保留策略
- PostgreSQL 13+ 支持并行分区维护，提高维护效率
pg_hint_plan - 查询计划控制
什么是 pg_hint_plan
pg_hint_plan 是一个用于控制 PostgreSQL 查询计划的扩展，可以通过注释的方式向查询优化器提供提示，强制使用特定的索引或连接方式，提高查询性能。
版本支持
- 兼容 PostgreSQL 9.5+ 版本
- PostgreSQL 13+ 支持更多提示类型
- PostgreSQL 15+ 支持 JSON 格式提示
安装方法
bash
# Ubuntu/Debian（来自 pgapt 仓库）
apt-get install postgresql-14-pg-hint-plan
# 源码编译
wget https://github.com/ossc-db/pg_hint_plan/archive/refs/tags/REL14_1_4_0.tar.gz
tar -xzf REL14_1_4_0.tar.gz
cd pg_hint_plan-REL14_1_4_0
make && make installsql
-- 安装扩展
CREATE EXTENSION IF NOT EXISTS pg_hint_plan;配置参数
在 postgresql.conf 中添加以下配置：
ini
# 启用 pg_hint_plan 扩展
shared_preload_libraries = 'pg_hint_plan'
# 配置参数
pg_hint_plan.enable_hint = on
pg_hint_plan.debug_print = off # 生产环境关闭调试输出
pg_hint_plan.parse_messages = warning生产环境最佳实践
基本索引提示
sql
-- 强制使用指定索引
/*+ IndexScan(users idx_users_email) */
SELECT * FROM users WHERE email = 'user@example.com';
-- 强制使用位图索引扫描
/*+ BitmapScan(orders idx_orders_status) */
SELECT * FROM orders WHERE status = 'completed';连接顺序和方法提示
sql
-- 强制连接顺序和连接方法
/*+
Leading(users orders)
HashJoin(users orders)
IndexScan(users idx_users_id)
IndexScan(orders idx_orders_user_id)
*/
SELECT u.*, o.*
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active';并行查询提示
sql
-- 强制使用并行查询
/*+ Parallel(orders 4) */
SELECT count(*) FROM orders WHERE order_date > '2023-01-01';
-- 禁用并行查询（某些情况下并行查询可能更慢）
/*+ NoParallel(complex_query_view) */
SELECT * FROM complex_query_view;慎用场景和注意事项
- 只在查询计划明显错误时使用
- 定期验证提示的有效性，特别是在版本升级后
- 避免过度依赖提示，优先优化 schema 和统计信息
- 记录使用提示的原因和测试结果
- 结合
pg_stat_statements监控使用提示的查询性能
hypopg - 虚拟索引
什么是 hypopg
hypopg 是一个用于创建虚拟索引的扩展，可以在不实际创建索引的情况下，测试索引对查询性能的影响，帮助 DBA 选择合适的索引策略。
版本支持
- 兼容 PostgreSQL 9.5+ 版本
- PostgreSQL 14+ 支持并行查询测试
安装方法
sql
-- 安装扩展
CREATE EXTENSION IF NOT EXISTS hypopg;生产环境应用
测试索引效果
sql
-- 1. 先查看当前查询计划
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'user@example.com';
-- 2. 创建虚拟索引
SELECT hypopg_create_index('CREATE INDEX ON users (email)');
-- 3. 再次查看查询计划，检查是否使用虚拟索引
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'user@example.com';
-- 4. 查看虚拟索引列表
SELECT * FROM hypopg_list_indexes();
-- 5. 删除虚拟索引
SELECT hypopg_drop_index(indexrelid) FROM hypopg_list_indexes() WHERE indexname LIKE '%users_email%';测试多个索引组合
sql
-- 测试复合索引效果
SELECT hypopg_create_index('CREATE INDEX ON orders (user_id, order_date DESC)');
-- 测试部分索引效果
SELECT hypopg_create_index('CREATE INDEX ON orders (order_date) WHERE status = ''completed''');
-- 比较不同索引的查询成本
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 123 AND order_date > '2023-01-01';
-- 清理所有虚拟索引
SELECT hypopg_reset();索引策略评估
sql
-- 结合 pg_stat_statements 识别需要优化的查询
SELECT queryid, query, calls, total_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;
-- 对慢查询创建虚拟索引并测试
-- 例如，对以下查询测试索引效果
/*
SELECT * FROM orders WHERE user_id = $1 AND status = $2 ORDER BY order_date DESC;
*/
-- 测试不同索引方案
SELECT hypopg_create_index('CREATE INDEX ON orders (user_id, status, order_date DESC)');
SELECT hypopg_create_index('CREATE INDEX ON orders (status, user_id, order_date DESC)');
-- 比较查询计划成本
EXPLAIN
SELECT * FROM orders WHERE user_id = 123 AND status = 'completed' ORDER BY order_date DESC;最佳实践
- 只在测试环境或生产环境低峰期使用
- 避免创建过多虚拟索引，影响查询计划生成
- 结合
EXPLAIN ANALYZE进行真实性能测试 - 虚拟索引只对当前会话有效，会话结束后自动清理
- 用于辅助决策，最终以实际创建索引后的性能为准
pg_squeeze - 表压缩工具
什么是 pg_squeeze
pg_squeeze 是一个用于表压缩的扩展，可以在不阻塞读写操作的情况下，压缩表和索引，减少存储空间占用，提高查询性能。
版本支持
- 兼容 PostgreSQL 10+ 版本
- PostgreSQL 12+ 支持并行压缩
- PostgreSQL 14+ 支持更多压缩算法
安装方法
bash
# 源码编译安装
git clone https://github.com/cybertec-postgresql/pg_squeeze.git
cd pg_squeeze
make && make installsql
-- 安装扩展
CREATE EXTENSION IF NOT EXISTS pg_squeeze;配置参数
在 postgresql.conf 中添加以下配置：
ini
# 启用 pg_squeeze 扩展
shared_preload_libraries = 'pg_squeeze'
# 压缩任务工作进程数
pg_squeeze.max_worker_processes = 4
# 自动压缩开关
pg_squeeze.auto = on生产环境使用
手动压缩表
sql
-- 压缩表，设置填充因子为 80
SELECT squeeze.squeeze_table(
p_table := 'public.your_table',
p_index := 'your_table_pkey',
p_fillfactor := 80
);
-- 查看压缩任务状态
SELECT * FROM squeeze.job;
-- 查看压缩历史
SELECT * FROM squeeze.history ORDER BY started_at DESC;自动压缩配置
sql
-- 配置表自动压缩策略
INSERT INTO squeeze.tables (
tableoid,
enabled,
fillfactor,
autocompression_enabled
) VALUES (
'public.your_table'::regclass,
true,
80,
true
);
-- 设置自动压缩阈值（当表膨胀超过 30% 时自动压缩）
UPDATE squeeze.tables
SET autocompression_pct = 30
WHERE tableoid = 'public.your_table'::regclass;压缩效果验证
sql
-- 压缩前查看表大小
SELECT pg_size_pretty(pg_total_relation_size('your_table')) AS before_compression;
-- 执行压缩
SELECT squeeze.squeeze_table(p_table := 'public.your_table');
-- 压缩后查看表大小
SELECT pg_size_pretty(pg_total_relation_size('your_table')) AS after_compression;性能优化扩展选择指南
按场景选择扩展
| 优化场景 | 推荐扩展 | 版本要求 | 性能影响 |
|---|---|---|---|
| 表膨胀处理 | pg_repack | 9.4+ | 低（在线操作） |
| 缓存优化 | pg_prewarm | 9.4+ | 极低 |
| 大表性能 | pg_partman | 9.6+ | 极低 |
| 查询计划调优 | pg_hint_plan | 9.5+ | 无 |
| 索引策略测试 | hypopg | 9.5+ | 极低 |
| 存储空间优化 | pg_squeeze | 10+ | 低 |
生产环境部署建议
基础优化组合：
- pg_repack：定期处理表膨胀
- pg_prewarm：数据库重启后预加载热点数据
- hypopg：测试索引策略
中大型数据库：
- 增加 pg_partman：管理分区表
- 增加 pg_squeeze：压缩表空间
复杂查询优化：
- 增加 pg_hint_plan：调优特定查询计划
性能影响评估
| 扩展名称 | CPU 影响 | I/O 影响 | 内存影响 | 锁影响 |
|---|---|---|---|---|
| pg_repack | 中 | 中 | 低 | 无（在线操作） |
| pg_prewarm | 低 | 中 | 高 | 无 |
| pg_partman | 极低 | 极低 | 极低 | 无 |
| pg_hint_plan | 无 | 无 | 无 | 无 |
| hypopg | 极低 | 无 | 极低 | 无 |
| pg_squeeze | 中 | 中 | 低 | 无（在线操作） |
扩展维护最佳实践
定期更新扩展
sql
-- 查看已安装扩展版本
SELECT extname, extversion FROM pg_extension;
-- 更新扩展到最新版本
ALTER EXTENSION pg_repack UPDATE;
ALTER EXTENSION pg_partman UPDATE;监控扩展性能
sql
-- 监控 pg_repack 进程
SELECT * FROM pg_stat_activity WHERE query LIKE '%pg_repack%';
-- 监控 pg_partman 维护作业
SELECT * FROM pg_stat_activity WHERE query LIKE '%partman%';
-- 查看扩展占用的资源
SELECT * FROM pg_stat_user_functions WHERE funcname LIKE '%pg_repack%' OR funcname LIKE '%partman%';版本升级注意事项
升级前：
- 检查所有扩展的版本兼容性
- 备份数据库和扩展相关元数据
- 在测试环境验证升级
升级后：
- 重新编译或安装扩展
- 更新扩展版本
- 测试扩展功能正常
- 验证依赖关系
总结
PostgreSQL 的性能优化扩展提供了丰富的功能，可以帮助 DBA 解决各种复杂的性能问题。选择合适的扩展组合，结合最佳实践进行配置和使用，可以显著提高数据库的性能和稳定性。
在实际生产环境中，建议：
- 从基础扩展开始，逐步引入高级扩展
- 定期评估扩展的使用效果和性能影响
- 结合监控工具，持续优化扩展配置
- 关注扩展的版本兼容性，及时更新
- 建立完善的扩展变更管理流程
通过合理使用这些性能优化扩展，可以有效解决 PostgreSQL 在实际生产环境中遇到的各种性能挑战，提高数据库的整体性能和可靠性。