7 个 PostgreSQL 性能调优实战技巧：索引策略、查询优化、主从复制全解析

作者


阿里云推广

7 个 PostgreSQL 性能调优实战技巧：索引策略、查询优化、主从复制全解析

从索引设计到查询优化，从配置调优到主从复制，手把手提升 PostgreSQL 性能。

为什么 PostgreSQL 性能会变差？

随着数据量增长，常见的性能瓶颈：

  • 缺少索引或索引设计不合理 → 全表扫描
  • 查询语句写得差 → 大量无用 IO
  • 配置参数沿用默认值 → 内存/并发配置不匹配
  • 没有主从分离 → 读写互相影响

    • 技巧1：索引策略——不是建得越多越好

    1.1 查看缺失索引

    -- 找出执行最慢的查询
SELECT 
    query,
    calls,
    total_time,
    mean_time,
    rows
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

-- 查看表上的索引使用情况
SELECT 
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS index_scans,
    seq_scan AS sequential_scans
FROM pg_stat_user_tables t
JOIN pg_stat_user_indexes i ON t.relid = i.relid
WHERE seq_scan > idx_scan * 10  -- 顺序扫描远多于索引扫描
ORDER BY seq_scan DESC;

    1.2 创建合适的索引

    -- ❌ 错误：对低区分度字段建索引（如性别）
CREATE INDEX idx_gender ON users(gender);  -- 只有2个值，索引效果不佳

-- ✅ 正确：对高区分度字段建索引
CREATE INDEX idx_email ON users(email);  -- email 唯一，区分度极高

-- ✅ 复合索引（注意字段顺序：区分度高的放前面）
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);

-- ✅ 部分索引（只索引部分数据，节省空间）
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

-- ✅ GIN 索引（用于 JSONB / 数组字段）
CREATE INDEX idx_metadata ON products USING GIN(metadata);

    1.3 索引使用原则

    场景 推荐索引类型
    —— ————-
    精确匹配（=） B-tree（默认）
    范围查询（>, <, BETWEEN） B-tree
    模糊查询（LIKE ‘%xxx%’） 不支持索引，考虑全文搜索
    模糊查询（LIKE ‘xxx%’） B-tree
    包含查询（JSONB） GIN
    地理位置 GiST / SP-GiST

    技巧2：查询优化——EXPLAIN 是你的好朋友

    2.1 读懂执行计划

    -- 开启 BUFFERS 可以看到 IO 情况
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM orders WHERE user_id = 12345;

/*
Seq Scan on orders  (cost=0.00..153.00 rows=50 width=88) (actual time=0.020..1.200 rows=48 loops=1)
  Filter: (user_id = 12345)
  Rows Removed by Filter: 99952
  Buffers: shared hit=128
*/
-- ↑ 问题：Seq Scan（全表扫描），扫描了99952行才找到48行
-- 解决：给 user_id 建索引

    2.2 常见慢查询优化

    -- ❌ 慢查询：使用函数导致索引失效
SELECT * FROM users WHERE DATE(created_at) = '2024-01-01';
-- DATE() 函数让索引失效，全表扫描

-- ✅ 优化：用范围查询
SELECT * FROM users 
WHERE created_at >= '2024-01-01' 
  AND created_at < '2024-01-02';

-- ❌ 慢查询：SELECT * 返回所有字段
SELECT * FROM orders WHERE user_id = 123;

-- ✅ 优化：只查需要的字段
SELECT id, status, total_amount FROM orders WHERE user_id = 123;

-- ❌ 慢查询：NOT IN 子查询
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM banned_users);

-- ✅ 优化：用 NOT EXISTS 或 LEFT JOIN
SELECT u.* FROM users u
LEFT JOIN banned_users b ON u.id = b.user_id
WHERE b.user_id IS NULL;

    技巧3：配置调优——不要只用默认值

    3.1 关键参数（postgresql.conf）

    # 内存配置（64GB 服务器示例）
shared_buffers = 16GB          # 物理内存的 25%
effective_cache_size = 48GB     # 物理内存的 75%
work_mem = 64MB                 # 每个操作的内存，调大可加速排序/哈希
maintenance_work_mem = 2GB      # VACUUM / CREATE INDEX 时的内存

# WAL 配置（影响写入性能）
wal_buffers = 64MB              # WAL 缓冲区
checkpoint_timeout = 30min      # 检查点间隔，调大减少 IO
checkpoint_completion_target = 0.9  # 分散 IO 压力

# 并发配置
max_connections = 200           # 根据连接池调整，不要设太大

    3.2 用 pgTune 自动计算参数

    # 在线工具：https://pgtune.leopard.in.ua/
# 或本地运行：
docker run --rm pgtuned \
  -c "db_type=web" \
  -c "total_memory=64GB" \
  -c "cpus=16" \
  -c "connections=200"

    技巧4：VACUUM 和 ANALYZE——维护表健康

    -- 查看表膨胀情况
SELECT 
    schemaname,
    relname AS table_name,
    n_dead_tup,
    n_live_tup,
    round(n_dead_tup * 100.0 / (n_live_tup + n_dead_tup), 2) AS dead_percent
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY dead_percent DESC;

-- 手动 VACUUM（回收死元组空间）
VACUUM (VERBOSE, ANALYZE) your_table;

-- 设置自动 VACUUM（在 postgresql.conf）
autovacuum_max_workers = 6       # 同时运行的 autovacuum 进程数
autovacuum_vacuum_threshold = 100  # 触发 VACUUM 的最小更新行数

    技巧5：分区表——应对亿级数据

    -- 创建分区主表（按时间分区）
CREATE TABLE orders (
    id BIGSERIAL,
    user_id BIGINT NOT NULL,
    amount DECIMAL(10,2),
    created_at TIMESTAMP NOT NULL
) PARTITION BY RANGE (created_at);

-- 创建分区（每月一个分区）
CREATE TABLE orders_2024_01 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE orders_2024_02 PARTITION OF orders
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- 查询时只扫描相关分区（分区裁剪）
SELECT * FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31';
-- ↑ 只扫描 orders_2024_01 分区，其他分区不扫描

    技巧6：主从复制——读写分离

    6.1 配置主库（Primary）

    # postgresql.conf（主库）
wal_level = replica              # 开启 WAL 复制
max_wal_senders = 10           # 最大复制连接数
wal_keep_size = 1GB            # 保留 WAL 文件大小
    -- 创建复制用户
CREATE USER replicator REPLICATION LOGIN PASSWORD 'your_password';

-- pg_hba.conf 允许从库连接
# TYPE  DATABASE  USER         ADDRESS         METHOD
  host   replication  replicator  192.168.1.0/24   md5

    6.2 配置从库（Replica）

    # 从主库基础备份
pg_basebackup -h primary-host -D /var/lib/postgresql/data \
  -U replicator -P -v -R

# 启动从库（会自动以只读模式运行）
pg_ctl start -D /var/lib/postgresql/data
    -- 验证复制状态（在主库执行）
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn 
FROM pg_stat_replication;

-- 在应用中实现读写分离
-- 写操作 → 主库
-- 读操作 → 从库（用连接池配置）

    技巧7：连接池——减少连接开销

    PostgreSQL 每个连接消耗约 10MB 内存，连接数过多会严重影响性能。

    # 使用 PgBouncer 或 SQLAlchemy 连接池
from sqlalchemy import create_engine
from sqlalchemy.pool import QueuePool

engine = create_engine(
    "postgresql://user:pass@localhost/mydb",
    poolclass=QueuePool,
    pool_size=20,          # 连接池大小
    max_overflow=10,       # 超出 pool_size 后最多创建的连接数
    pool_recycle=3600,     # 连接回收时间（秒）
)

    性能调优检查清单

    □ 是否存在全表扫描的大表？（用 pg_stat_statements 查）
□ 索引是否合理？（高区分度字段有索引，低区分度字段无冗余索引）
□ 是否启用了 autovacuum？
□ shared_buffers / effective_cache_size 是否按服务器内存调整？
□ 是否有分区表应对大表？
□ 是否配置了主从复制实现读写分离？
□ 应用是否使用了连接池？

    总结

    优化层次 具体措施 预期效果
    ———- ———- ———-
    SQL 层 优化慢查询、避免 SELECT * 减少 50-90% 响应时间
    索引层 合理建索引、删除冗余索引 查询提速 10-100 倍
    配置层 调整 postgresql.conf 提升并发处理能力
    架构层 分区表 + 主从复制 支撑亿级数据

    记住：先测瓶颈，再优化。用 EXPLAIN ANALYZE 和 pg_stat_statements 找到真问题。

    👤 作者简介

    一枚在大中原腹地（河南）卖公有云的从业者，主营腾讯云/阿里云/火山云，曾踩坑无数，现专注AI大模型应用落地。关注公众号「公有云cloud」，围观AI前沿动态~

    博客：yunduancloud.icu

    发表评论