PostgreSQL 性能调优完全指南

数据库

为什么 2026 年选择 PostgreSQL？

PostgreSQL 在 2026 年已经稳居全球最受欢迎的开源关系型数据库之首。凭借其强大的扩展性、丰富的数据类型和卓越的 SQL 标准兼容性，它已成为从初创公司到大型企业的首选数据库。

PostgreSQL 核心优势

维度 PostgreSQL MySQL SQL Server Oracle
扩展性 极强（扩展框架） 中等 中等 强
数据类型 极丰富（JSONB、GIS、数组） 中等 丰富 丰富
SQL 标准兼容 最接近标准 中等 高 高
开源 完全开源（PostgreSQL License） GPL 部分开源 闭源
并发控制 MVCC（优秀） MVCC MVCC MVCC
分区能力 原生声明式分区 原生分区 原生分区 原生分区

2026 年 PostgreSQL 的关键进展

  1. PostgreSQL 18：并行查询进一步增强，逻辑复制性能大幅提升
  2. 增量排序（Incremental Sort）优化：多列排序场景性能提升 3-5 倍
  3. JSON 路径表达式增强：更强大的 JSONB 查询与索引支持
  4. 异步 I/O 支持：底层 I/O 架构改进，大幅提升高并发场景吞吐
  5. 逻辑复制改进：支持 DDL 复制，大幅简化数据同步架构

💡 使用 SQL 格式化 工具美化 PostgreSQL 查询语句，便于分析执行计划。

EXPLAIN ANALYZE 深度解析

EXPLAIN ANALYZE 是 PostgreSQL 性能调优最核心的工具，它不仅展示查询计划，还执行查询并返回实际耗时统计。

基础用法

-- 查看执行计划（不执行）
EXPLAIN SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2026-01-01'
  AND o.status = 'shipped';

-- 执行查询并返回实际统计
EXPLAIN ANALYZE SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2026-01-01'
  AND o.status = 'shipped';

-- 查看详细输出（含缓冲区统计）
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE status = 'shipped';

扫描类型详解

顺序扫描（Seq Scan）

EXPLAIN ANALYZE SELECT * FROM orders WHERE amount > 1000;
-- Seq Scan on orders  (cost=0.00..15432.00 rows=100000 width=88)
--   Filter: (amount > 1000)
  • 适用场景：表数据量小、查询返回大部分行、无合适索引
  • 优化方向：若返回行数 < 总行数 5%，考虑添加索引

索引扫描（Index Scan）

EXPLAIN ANALYZE SELECT * FROM orders WHERE order_id = 12345;
-- Index Scan using orders_pkey on orders  (cost=0.42..8.44 rows=1 width=88)
--   Index Cond: (order_id = 12345)
  • 适用场景：等值查询、高选择性条件、返回少量行
  • 优化方向：确保索引列顺序与查询条件匹配

位图扫描（Bitmap Scan）

EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'shipped';
-- Bitmap Heap Scan on orders  (cost=523.12..8234.56 rows=15000 width=88)
--   Recheck Cond: (status = 'shipped')
--   -> Bitmap Index Scan on idx_orders_status  (cost=0.00..519.37 rows=15000)
  • 适用场景：返回中等数量行（表的 5%-15%）、多条件组合查询
  • 优化方向：Bitmap Index Scan + Bitmap Heap Scan 组合，适合中等选择性

连接类型详解

嵌套循环连接（Nested Loop）

EXPLAIN ANALYZE
SELECT * FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.customer_id = 100;
-- Nested Loop  (cost=0.85..234.56 rows=50 width=200)
--   -> Index Scan using idx_orders_customer on orders o
--   -> Index Scan using idx_items_order on order_items oi
  • 适用场景：小表驱动大表、内表有索引、返回少量行
  • 优化方向：确保内表连接列有索引

哈希连接（Hash Join）

EXPLAIN ANALYZE
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;
-- Hash Join  (cost=345.67..12345.89 rows=100000 width=200)
--   Hash Cond: (o.customer_id = c.customer_id)
--   -> Seq Scan on orders o
--   -> Hash
--       -> Seq Scan on customers c
  • 适用场景：大表连接大表、等值连接、无排序需求
  • 优化方向：确保 Build 侧（Hash 表）能放入 work_mem

归并连接（Merge Join）

EXPLAIN ANALYZE
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
ORDER BY o.customer_id;
-- Merge Join  (cost=0.86..15678.90 rows=100000 width=200)
--   Merge Cond: (o.customer_id = c.customer_id)
--   -> Index Scan using idx_orders_customer on orders o
--   -> Index Scan using customers_pkey on customers c
  • 适用场景：两侧数据已排序、等值连接、大数据量
  • 优化方向：确保两侧连接列有索引（已排序）

EXPLAIN 输出关键字段

字段 含义 关注点
cost 估算代价（启动..总代价） 总代价过高需优化
rows 估算返回行数 与实际行数偏差大需 ANALYZE
width 平均行宽度（字节） 过宽考虑减少 SELECT 列
actual time 实际耗时（毫秒） 关注慢节点
actual rows 实际返回行数 与估算偏差大需更新统计
loops 执行次数 嵌套循环中 loops × time = 总耗时
Buffers 缓冲区命中统计 shared hit 高表示缓存友好

💡 使用 JSON 格式化 工具解析 FORMAT JSON 输出的 EXPLAIN 结果。

索引类型与选型实战

PostgreSQL 提供了丰富的索引类型，选择合适的索引类型是性能调优的关键。

B-tree 索引（默认）

-- 创建 B-tree 索引
CREATE INDEX idx_orders_date ON orders (order_date);

-- 复合 B-tree 索引
CREATE INDEX idx_orders_status_date ON orders (status, order_date);

-- 适用场景：等值查询、范围查询、排序、LIKE 'prefix%'
-- 最常用索引类型，覆盖 90%+ 场景

GIN 索引（倒排索引）

-- JSONB 字段索引
CREATE INDEX idx_products_attrs ON products USING GIN (attributes);

-- 数组字段索引
CREATE INDEX idx_articles_tags ON articles USING GIN (tags);

-- 全文搜索索引
CREATE INDEX idx_docs_content ON documents USING GIN (to_tsvector('english', content));

-- 查询示例
SELECT * FROM products WHERE attributes @> '{"color": "red"}';
SELECT * FROM articles WHERE tags @> ARRAY['postgresql', 'performance'];
SELECT * FROM documents WHERE to_tsvector('english', content) @@ to_tsquery('postgresql & tuning');
  • 适用场景：JSONB、数组、全文搜索
  • 注意：GIN 索引构建慢，查询快；考虑 fastupdate = on 加速写入

GiST 索引（通用搜索树）

-- 地理空间索引（PostGIS）
CREATE INDEX idx_stores_location ON stores USING GiST (location);

-- 范围类型索引
CREATE INDEX idx_events_period ON events USING GiST (time_range);

-- 查询示例
SELECT * FROM stores WHERE ST_DWithin(location, ST_Point(116.4, 39.9), 5000);
SELECT * FROM events WHERE time_range && '[2026-01-01, 2026-06-30)'::daterange;
  • 适用场景：地理空间、范围类型、树结构
  • 注意：GiST 支持自定义操作符类

BRIN 索引（块范围索引）

-- 时序数据索引
CREATE INDEX idx_logs_created ON logs USING BRIN (created_at);

-- 自定义块大小
CREATE INDEX idx_logs_created ON logs USING BRIN (created_at) WITH (pages_per_range = 32);

-- 查询示例
SELECT * FROM logs WHERE created_at >= '2026-06-01' ORDER BY created_at DESC LIMIT 100;
  • 适用场景：大表中自然排序的列（时序数据、自增 ID）
  • 优势：索引体积极小（通常为 B-tree 的 1/100）
  • 注意：仅适合数据物理有序的列，选择性查询不如 B-tree

Hash 索引

-- 创建 Hash 索引
CREATE INDEX idx_users_email_hash ON users USING HASH (email);

-- 适用场景：纯等值查询
-- 注意：PostgreSQL 10+ 后 Hash 索引已支持 WAL，可安全使用
-- 但通常 B-tree 已足够，Hash 索引使用场景有限

索引类型选型决策表

场景 推荐索引 原因
等值/范围查询 B-tree 最通用，性能稳定
JSONB 包含查询 GIN 原生支持 @> 操作符
全文搜索 GIN 配合 tsvector 高效检索
地理空间查询 GiST PostGIS 标准搭配
范围重叠查询 GiST 原生支持 && 操作符
时序数据范围查询 BRIN 索引极小，适合物理有序数据
纯等值查询（超大表） Hash 等值查询略快于 B-tree

部分索引与表达式索引

部分索引（Partial Index）

-- 仅索引未完成的订单（减少索引体积）
CREATE INDEX idx_orders_pending ON orders (customer_id, order_date)
WHERE status = 'pending';

-- 仅索引活跃用户
CREATE INDEX idx_users_active ON users (last_login_at)
WHERE is_active = true;

-- 查询自动使用部分索引
SELECT * FROM orders WHERE status = 'pending' AND customer_id = 100;
  • 优势：索引体积大幅减小，写入开销降低
  • 注意：查询条件必须满足 WHERE 子句才能使用索引

表达式索引

-- 不区分大小写查询
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

-- 日期截取查询
CREATE INDEX idx_orders_date_trunc ON orders (DATE(order_date));

-- JSONB 特定键索引
CREATE INDEX idx_products_color ON products ((attributes->>'color'));

-- 查询示例
SELECT * FROM users WHERE LOWER(email) = 'admin@example.com';
SELECT * FROM orders WHERE DATE(order_date) = '2026-06-11';
SELECT * FROM products WHERE attributes->>'color' = 'red';
  • 优势：避免查询时计算表达式，直接走索引
  • 注意：查询条件必须与索引表达式完全一致

复合索引列顺序原则

-- 遵循：等值条件列 → 范围条件列 → 排序列
CREATE INDEX idx_orders_optimal ON orders (status, customer_id, order_date DESC);

-- 覆盖索引：包含所有查询列，避免回表
CREATE INDEX idx_orders_covering ON orders (status, order_date)
INCLUDE (customer_id, amount);

查询优化模式

避免 SELECT *

-- 反模式
SELECT * FROM orders WHERE customer_id = 100;

-- 优化：只查需要的列
SELECT order_id, order_date, amount
FROM orders WHERE customer_id = 100;

分页优化

-- 反模式：OFFSET 在大偏移量时性能极差
SELECT * FROM orders ORDER BY order_id OFFSET 100000 LIMIT 20;

-- 优化 1：Keyset 分页（游标分页）
SELECT * FROM orders WHERE order_id > 100000 ORDER BY order_id LIMIT 20;

-- 优化 2：覆盖索引 + JOIN
SELECT o.* FROM orders o
JOIN (SELECT order_id FROM orders ORDER BY order_id OFFSET 100000 LIMIT 20) t
ON o.order_id = t.order_id;

子查询优化

-- 反模式：相关子查询
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM order_items oi WHERE oi.order_id = o.order_id AND oi.price > 100);

-- 优化：改写为 JOIN
SELECT DISTINCT o.* FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE oi.price > 100;

CTE 优化

-- PostgreSQL 12+：CTE 默认可内联优化
WITH recent_orders AS (
  SELECT * FROM orders WHERE order_date >= '2026-01-01'
)
SELECT ro.*, c.customer_name
FROM recent_orders ro
JOIN customers c ON ro.customer_id = c.customer_id;

-- 需要物化 CTE 时显式指定
WITH MATERIALIZED expensive_calc AS (
  SELECT customer_id, SUM(amount) AS total
  FROM orders GROUP BY customer_id
)
SELECT * FROM expensive_calc WHERE total > 10000;

批量操作优化

-- 批量插入（单条 INSERT 多值）
INSERT INTO orders (customer_id, amount, status)
VALUES
  (1, 100.00, 'pending'),
  (2, 200.00, 'shipped'),
  (3, 150.00, 'pending');

-- 批量更新使用 CASE
UPDATE orders SET status = CASE
  WHEN order_id = 1 THEN 'shipped'
  WHEN order_id = 2 THEN 'delivered'
  WHEN order_id = 3 THEN 'cancelled'
  ELSE status
END
WHERE order_id IN (1, 2, 3);

-- 使用 COPY 快速导入
COPY orders FROM '/data/orders.csv' WITH (FORMAT csv, HEADER true);

连接池配置（PgBouncer）

PgBouncer 是 PostgreSQL 最流行的连接池，能有效解决连接数过多导致的性能问题。

为什么需要连接池？

每个 PostgreSQL 连接会 fork 一个进程，消耗约 10MB 内存。1000 个连接 = 10GB 内存开销，且进程切换成本高。

PgBouncer 安装与配置

# 安装 PgBouncer
sudo apt install pgbouncer

# 启动 PgBouncer
sudo systemctl start pgbouncer
sudo systemctl enable pgbouncer

; /etc/pgbouncer/pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 10000
default_pool_size = 50
min_pool_size = 10
reserve_pool_size = 10
reserve_pool_timeout = 3
server_idle_timeout = 300
server_lifetime = 3600
log_connections = 0
log_disconnections = 0
stats_period = 60

连接池模式

模式 特点 适用场景
session 会话级复用，连接绑定到客户端 需要会话变量、临时表
transaction 事务级复用，事务结束后连接归还 大多数 Web 应用（推荐）
statement 语句级复用，每条语句后归还 短查询、无事务场景

连接数规划

-- 查看当前连接数
SELECT count(*) FROM pg_stat_activity;

-- 按状态分组
SELECT state, count(*) FROM pg_stat_activity GROUP BY state ORDER BY count DESC;

-- 查看 PostgreSQL 最大连接数
SHOW max_connections;

-- 推荐公式
-- PgBouncer default_pool_size = CPU 核心数 × 2 + 磁盘数
-- PostgreSQL max_connections = 所有 PgBouncer pool_size 之和 + 预留

核心参数调优

shared_buffers

-- 共享缓冲区大小，PostgreSQL 最核心参数
-- 推荐值：系统总内存的 25%，但不超过 8GB（Linux）
ALTER SYSTEM SET shared_buffers = '4GB';

work_mem

-- 排序和哈希操作的内存上限
-- 推荐值：总内存 / (max_connections × 3)，通常 16MB-256MB
ALTER SYSTEM SET work_mem = '64MB';

-- 查看当前 work_mem 导致的磁盘排序
SELECT query, calls, temp_blks_written
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC LIMIT 10;

effective_cache_size

-- 查询优化器对系统缓存的估算值（不实际分配内存）
-- 推荐值：系统总内存的 50%-75%
ALTER SYSTEM SET effective_cache_size = '12GB';

WAL 相关参数

-- WAL 写入方式：open_datasync（Linux 默认）、fdatasync、fsync 等
ALTER SYSTEM SET wal_sync_method = 'open_datasync';

-- WAL 缓冲区大小
ALTER SYSTEM SET wal_buffers = '64MB';

-- 检查点间隔
ALTER SYSTEM SET checkpoint_timeout = '10min';
ALTER SYSTEM SET max_wal_size = '2GB';
ALTER SYSTEM SET min_wal_size = '512MB';

-- 完整性保证（生产环境务必开启）
ALTER SYSTEM SET fsync = 'on';
ALTER SYSTEM SET synchronous_commit = 'on';

关键参数速查表

参数 推荐值（16GB 内存） 说明
shared_buffers 4GB 共享缓冲区
work_mem 64MB 排序/哈希内存
effective_cache_size 12GB 优化器缓存估算
maintenance_work_mem 1GB 维护操作内存
wal_buffers 64MB WAL 缓冲区
max_wal_size 2GB WAL 最大大小
checkpoint_timeout 10min 检查点间隔
random_page_cost 1.1（SSD） 随机页代价
effective_io_concurrency 200（SSD） 并发 I/O 数
max_connections 100-200 最大连接数 
# 使用 pgtune 自动生成推荐配置
pgtune -i postgresql.conf -M 16GB -T web -c 200

分区策略

PostgreSQL 10+ 支持原生声明式分区，是处理大表的核心手段。

范围分区（Range）

-- 按月分区订单表
CREATE TABLE orders (
  order_id BIGINT,
  customer_id INT,
  order_date DATE,
  amount DECIMAL(10,2),
  status TEXT
) PARTITION BY RANGE (order_date);

CREATE TABLE orders_2026_q1 PARTITION OF orders
  FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');
CREATE TABLE orders_2026_q2 PARTITION OF orders
  FOR VALUES FROM ('2026-04-01') TO ('2026-07-01');
CREATE TABLE orders_2026_q3 PARTITION OF orders
  FOR VALUES FROM ('2026-07-01') TO ('2026-10-01');
CREATE TABLE orders_2026_q4 PARTITION OF orders
  FOR VALUES FROM ('2026-10-01') TO ('2027-01-01');
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

列表分区（List）

-- 按地区分区
CREATE TABLE customers (
  customer_id BIGINT,
  name TEXT,
  region TEXT,
  created_at TIMESTAMP
) PARTITION BY LIST (region);

CREATE TABLE customers_north PARTITION OF customers
  FOR VALUES IN ('北京', '天津', '河北');
CREATE TABLE customers_south PARTITION OF customers
  FOR VALUES IN ('广东', '福建', '海南');
CREATE TABLE customers_default PARTITION OF customers DEFAULT;

哈希分区（Hash）

-- 哈希分区（均匀分布）
CREATE TABLE access_logs (
  log_id BIGINT,
  user_id INT,
  path TEXT,
  created_at TIMESTAMP
) PARTITION BY HASH (user_id);

CREATE TABLE access_logs_p0 PARTITION OF access_logs
  FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE access_logs_p1 PARTITION OF access_logs
  FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE access_logs_p2 PARTITION OF access_logs
  FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE access_logs_p3 PARTITION OF access_logs
  FOR VALUES WITH (MODULUS 4, REMAINDER 3);

分区维护

-- 自动创建未来分区（使用 pg_partman 扩展）
CREATE EXTENSION pg_partman;
SELECT partman.create_parent(
  p_parent_table := 'public.orders',
  p_control := 'order_date',
  p_type := 'range',
  p_interval := '1 month',
  p_premake := 6
);

-- 分区裁剪验证
EXPLAIN SELECT * FROM orders WHERE order_date = '2026-06-11';
-- 应只扫描 orders_2026_q2 分区

-- 删除旧分区（归档后删除）
DROP TABLE orders_2024_q1;

并行查询

PostgreSQL 9.6+ 引入并行查询，18 版本已大幅增强。

启用并行查询

-- 开启并行查询
ALTER SYSTEM SET max_parallel_workers_per_gather = 4;
ALTER SYSTEM SET max_parallel_workers = 8;
ALTER SYSTEM SET parallel_tuple_cost = 0.01;
ALTER SYSTEM SET parallel_setup_cost = 100;

-- 最小表大小触发并行（默认 8MB）
ALTER SYSTEM SET min_parallel_table_scan_size = '8MB';

-- 并行工作者数随表大小增长
ALTER SYSTEM SET min_parallel_index_scan_size = '512kB';

并行查询验证

EXPLAIN ANALYZE SELECT COUNT(*), SUM(amount)
FROM orders WHERE order_date >= '2026-01-01';
-- Gather Merge
--   -> Sort
--       -> Partial Aggregate
--           -> Parallel Seq Scan on orders

并行查询适用场景

场景 并行效果 说明
大表全表扫描 极好 并行 Seq Scan
大表聚合 极好 并行 Aggregation
大表 JOIN 好 并行 Hash Join
索引扫描 一般 并行 Bitmap Scan
小表查询 无效果 低于 min_parallel_table_scan_size
CTE（物化） 不支持 MATERIALIZED CTE 不并行

VACUUM 与 autovacuum 调优

PostgreSQL 的 MVCC 机制会产生死元组（dead tuples），必须通过 VACUUM 回收空间。

手动 VACUUM

-- 普通 VACUUM（不回收磁盘空间，只标记可复用）
VACUUM orders;

-- VACUUM FULL（回收磁盘空间，锁表！）
VACUUM FULL orders;

-- 并行 VACUUM（PostgreSQL 12+，不锁表）
VACUUM (PARALLEL 4) orders;

-- 分析统计信息
ANALYZE orders;

-- 同时 VACUUM + ANALYZE
VACUUM ANALYZE orders;

autovacuum 调优

-- 开启 autovacuum（默认开启）
ALTER SYSTEM SET autovacuum = 'on';

-- autovacuum 工作者数量
ALTER SYSTEM SET autovacuum_max_workers = 4;

-- 触发阈值（当死元组数超过此值时触发）
-- 公式：autovacuum_vacuum_scale_factor × 表行数 + autovacuum_vacuum_threshold
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.1;
ALTER SYSTEM SET autovacuum_vacuum_threshold = 50;

-- 分析触发阈值
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.05;
ALTER SYSTEM SET autovacuum_analyze_threshold = 50;

-- autovacuum 单次处理成本限制
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 2000;
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '2ms';

单表 autovacuum 调优

-- 对频繁更新的表降低触发阈值
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_vacuum_cost_limit = 5000
);

-- 查看表的 VACUUM 统计
SELECT relname, n_live_tup, n_dead_tup,
  last_vacuum, last_autovacuum,
  last_analyze, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;

监控 VACUUM 进度

-- PostgreSQL 12+ 查看 VACUUM 进度
SELECT pid, datname, relid::regclass, phase,
  heap_blks_total, heap_blks_scanned, heap_blks_vacuumed,
  index_vacuum_count, num_dead_item_ids
FROM pg_stat_progress_vacuum;

pg_stat_statements 慢查询分析

pg_stat_statements 是 PostgreSQL 最强大的慢查询分析工具。

安装与启用

-- 加载扩展
CREATE EXTENSION pg_stat_statements;

-- 修改 postgresql.conf
-- shared_preload_libraries = 'pg_stat_statements'
-- pg_stat_statements.track = all
-- pg_stat_statements.max = 10000

Top N 慢查询

-- 按总耗时排序
SELECT query, calls, total_exec_time, mean_exec_time,
  min_exec_time, max_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;

-- 按平均耗时排序
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
WHERE calls > 10
ORDER BY mean_exec_time DESC LIMIT 10;

-- 按磁盘 I/O 排序
SELECT query, calls, shared_blks_read, shared_blks_hit,
  ROUND(shared_blks_hit::numeric / NULLIF(shared_blks_hit + shared_blks_read, 0), 4) AS cache_hit_ratio
FROM pg_stat_statements
WHERE shared_blks_read > 0
ORDER BY shared_blks_read DESC LIMIT 10;

-- 重置统计
SELECT pg_stat_statements_reset();

缓存命中率分析

-- 整体缓存命中率（应 > 99%）
SELECT
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS cache_hit_ratio
FROM pg_statio_user_tables;

-- 单表缓存命中率
SELECT relname,
  heap_blks_hit, heap_blks_read,
  ROUND(heap_blks_hit::numeric / NULLIF(heap_blks_hit + heap_blks_read, 0), 4) AS cache_hit_ratio
FROM pg_statio_user_tables
WHERE heap_blks_read > 0
ORDER BY heap_blks_read DESC;

常见性能反模式

反模式 1：过度索引

-- 问题：每个索引都会增加写入开销
-- 检查未使用的索引
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- 删除未使用索引
DROP INDEX CONCURRENTLY idx_orders_unused;

反模式 2：大事务

-- 问题：长事务阻止 VACUUM 回收死元组，导致表膨胀
-- 检查长事务
SELECT pid, now() - xact_start AS duration, query, state
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'active')
  AND xact_start IS NOT NULL
ORDER BY duration DESC;

-- 设置事务超时（PostgreSQL 17+）
ALTER SYSTEM SET idle_in_transaction_session_timeout = '60000';

反模式 3：类型不匹配

-- 问题：隐式类型转换导致无法使用索引
-- 反模式
SELECT * FROM orders WHERE order_id = '12345';  -- VARCHAR 与 BIGINT 不匹配

-- 优化：确保类型一致
SELECT * FROM orders WHERE order_id = 12345;

反模式 4：OR 条件滥用

-- 反模式
SELECT * FROM orders WHERE customer_id = 100 OR amount > 5000;

-- 优化：使用 UNION ALL
SELECT * FROM orders WHERE customer_id = 100
UNION ALL
SELECT * FROM orders WHERE amount > 5000 AND customer_id != 100;

反模式 5：函数导致索引失效

-- 反模式：对索引列使用函数
SELECT * FROM orders WHERE DATE(created_at) = '2026-06-11';

-- 优化：使用范围查询
SELECT * FROM orders WHERE created_at >= '2026-06-11' AND created_at < '2026-06-12';

-- 或使用表达式索引
CREATE INDEX idx_orders_created_date ON orders (DATE(created_at));

Prometheus + Grafana 监控

postgres_exporter 部署

# 安装 postgres_exporter
wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.15.0/postgres_exporter-0.15.0.linux-amd64.tar.gz
tar xzf postgres_exporter-0.15.0.linux-amd64.tar.gz

# 配置数据源
export DATA_SOURCE_NAME="postgresql://monitor:password@localhost:5432/mydb?sslmode=disable"

# 启动
./postgres_exporter --web.listen-address=:9187

Prometheus 配置

scrape_configs:
  - job_name: 'postgresql'
    static_configs:
      - targets: ['localhost:9187']
    metric_relabel_configs:
      - source_labels: [__name__]
        regex: 'pg_.*'
        action: keep

关键监控指标

指标 含义 告警阈值
pg_stat_activity_count 当前连接数 > max_connections × 80%
pg_stat_database_deadlocks 死锁数 > 0
pg_stat_user_tables_n_dead_tup 死元组数 > 总行数 × 10%
pg_stat_statements_mean_exec_time 平均查询耗时 P99 > 500ms
pg_replication_lag 复制延迟 > 30s
pg_database_size_bytes 数据库大小 > 磁盘 85%

告警规则示例

groups:
  - name: postgresql_alerts
    rules:
      - alert: PostgreSQLHighConnections
        expr: pg_stat_activity_count > 160
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "PostgreSQL 连接数过高"
          description: "当前连接数超过 160，持续 5 分钟"

      - alert: PostgreSQLReplicationLag
        expr: pg_replication_lag > 30
        for: 2m
        labels:
          severity: critical
        annotations:
          summary: "PostgreSQL 复制延迟过高"
          description: "复制延迟超过 30 秒"

      - alert: PostgreSQLDeadTuplesHigh
        expr: pg_stat_user_tables_n_dead_tup / pg_stat_user_tables_n_live_tup > 0.1
        for: 10m
        labels:
          severity: warning
        annotations:
          summary: "PostgreSQL 死元组比例过高"
          description: "死元组占比超过 10%，需要 VACUUM"

💡 使用 哈希加密 工具生成监控告警 webhook 的签名密钥。

常见问题 FAQ

Q1：shared_buffers 设置多大合适？

推荐设置为系统总内存的 25%，但不超过 8GB（Linux）。因为 PostgreSQL 依赖操作系统的页面缓存，过大的 shared_buffers 会与 OS 缓存争抢内存。例如 16GB 内存的服务器，设置 shared_buffers = 4GB。

Q2：什么时候应该使用 BRIN 索引而非 B-tree？

当表数据量极大（> 1 亿行）且列值与物理存储顺序相关时（如时间戳、自增 ID），BRIN 索引体积仅为 B-tree 的 1/100，但查询性能接近。典型场景：日志表的时间列、时序数据的采集时间。

Q3：如何判断是否需要分区？

当单表数据量超过 1000 万行，且查询通常只涉及部分数据（如按时间范围查询），就应该考虑分区。分区裁剪可以大幅减少扫描数据量。

Q4：VACUUM FULL 什么时候使用？

VACUUM FULL 会锁表并重建整张表，回收所有碎片空间。仅在表膨胀严重（死元组 > 50%）且无法通过普通 VACUUM 回收时使用。推荐使用 pg_repack 扩展作为在线替代方案。

Q5：PgBouncer 的 transaction 模式有什么限制？

transaction 模式下，事务结束后连接会被回收，因此：不能使用临时表（跨事务）、不能使用 SET 设置会话级参数（跨事务）、不能使用 LISTEN/NOTIFY（跨事务）、不能使用预备语句（跨事务）。如需这些功能，使用 session 模式。

Q6：如何快速定位慢查询？

  1. 启用 pg_stat_statements 扩展
  2. 查询 pg_stat_statements 按 total_exec_time 排序
  3. 对慢查询使用 EXPLAIN ANALYZE 分析执行计划
  4. 检查是否有 Seq Scan、高 cost 节点
  5. 针对性添加索引或改写查询

Q7：random_page_cost 应该设置多少？

  • SSD/NVMe：设置为 1.1（接近顺序 I/O 代价）
  • HDD：保持默认 4.0
  • 这个参数直接影响优化器选择索引扫描还是顺序扫描的决策

Q8：PostgreSQL 的连接数上限怎么定？

每个连接约消耗 10MB 内存。推荐公式：max_connections = 可用内存(GB) × 100，但实际连接数应通过 PgBouncer 池化控制。PostgreSQL 的 max_connections 设置为 100-200 即可，外部连接由 PgBouncer 管理。

总结

PostgreSQL 在 2026 年依然是功能最强大的开源关系型数据库，其性能调优需要系统性地从查询分析、索引设计、参数配置、架构优化四个维度入手。关键要点回顾：

  1. EXPLAIN ANALYZE 是性能调优的起点，理解每种扫描和连接类型的适用场景
  2. 索引选型 至关重要：B-tree 通用、GIN 处理 JSONB/数组、BRIN 适合时序数据
  3. 参数调优 四大核心：shared_buffers、work_mem、effective_cache_size、WAL 配置
  4. 分区策略 是大表治理的基石：范围分区最常用、哈希分区保均匀
  5. VACUUM 调优 防止表膨胀：autovacuum 必须开启并合理配置
  6. 监控体系 是保障：pg_stat_statements + postgres_exporter + Grafana 三件套
#PostgreSQL#性能调优#数据库#SQL优化#教程