PostgreSQL 性能优化完全指南:从一次"1.2 亿行 orders 表加 30 个索引反而更慢 CPU 95% 高峰打挂"看懂为什么加索引远远不够

2023 年我们接手一个老电商系统的性能优化任务,数据库是 PostgreSQL 14,主表 orders 1.2 亿行 products 800 万行 users 500 万行,前端反馈商品搜索 + 订单查询慢到无法忍受,P99 5 秒起步,高峰期数据库 CPU 95%+ 经常被打挂。前任 DBA 留下的字典是"加索引就行,看慢日志哪个表慢就加",我们照着做了一周,加了 30 多个索引,情况非但没好转反而更糟 — 写入变慢、磁盘空间膨胀 200GB、查询计划反而走错索引。我盯着 EXPLAIN 输出看了三天才彻底想明白第一种最让我傻眼的是有个查询 WHERE status='paid' AND created_at > '2023-01-01' 我加了 (status, created_at) 复合索引但 PG 走了 status 单列索引 + Filter 比之前还慢,因为 PG 的 cost-based optimizer 用 pg_statistic 估算,而 status 只有 5 个值的低基数列 + 数据倾斜 (paid 占 85%) 让规划器误判;第二种最难缠的是分页 LIMIT 100 OFFSET 1000000 这种深度分页 OFFSET 1 千万直接扫一千万行只为了丢弃,生产 SQL 全是 OFFSET 我们手忙脚乱改 keyset 分页;第三种最离谱的是 ORM 生成 SELECT * FROM orders WHERE user_id IN (?,?,...,1000个) PG 直接拒绝 plan 走顺序扫描 1.2 亿行 改成 = ANY(array) 立马走索引;第四种最致命的是我们见 IS NULL 走不了索引就给空字段建 partial index,但忘了加 WHERE 条件,索引体积膨胀到 50GB,VACUUM 一次锁表 20 分钟;第五种最莫名其妙是同样 SQL 同样数据,白天慢晚上快,排查半天发现 pg_stats 自动 ANALYZE 阈值是 10% 变更率,白天写入低统计陈旧 PG 估算错误走错索引,晚上批量任务跑完触发 ANALYZE 计划自动校正。真正能稳定撑住生产负载的 PostgreSQL 性能优化是一个统计信息维护 + EXPLAIN ANALYZE 深读 + 索引设计精细化 + 查询模式重构 + 连接池治理 + 分区与归档 + 持续监控的完整方法论,任何一环失守都可能让你的数据库从"丝滑"变成"间歇性瘫痪"。本文从头梳理 PostgreSQL 性能优化的要点,统计信息怎么管 执行计划怎么读 索引怎么设计 SQL 怎么改 连接池怎么配 分区怎么做 监控怎么搭,以及一些把 PG 撑过亿级数据要避开的工程坑。

问题背景:为什么"加索引就行"远远不够

很多团队的 DBA 文化停留在"哪慢加哪个索引"的层次,但 PostgreSQL 是 cost-based optimizer 系统,索引只是优化器候选之一,真正决定快慢的是:

• 统计信息(pg_statistic):决定优化器是否选你的索引,陈旧统计 = 错误计划。
• 执行计划(EXPLAIN ANALYZE):不读懂 nested loop / hash join / bitmap scan 区别就是瞎调。
• 索引设计:复合索引列顺序、partial index 条件、covering index INCLUDE 是性能 10x 差距。
• SQL 写法:OFFSET 大、IN 列表巨长、隐式类型转换、ORDER BY + LIMIT 都能让索引失效。
• 连接池:无连接池或配置错 PG 进程暴涨 内存爆炸 + idle 连接 holding lock。
• 分区与归档:亿级表不分区 VACUUM 烫手 索引膨胀 查询慢。

一 统计信息维护:让优化器看清世界

第一版我们以为加了索引 PG 就会用,结果优化器经常无视索引走顺序扫描。问题根源是 pg_statistic 信息陈旧或不准,优化器估算的行数与实际差 100x,自然选错计划。统计信息是性能的隐藏地基。

-- 1 查看统计信息状态
SELECT relname,
       n_live_tup,
       n_dead_tup,
       last_analyze,
       last_autoanalyze,
       n_mod_since_analyze
FROM pg_stat_user_tables
WHERE relname IN ('orders', 'products', 'users');

-- n_mod_since_analyze 远大于 n_live_tup * 0.1 说明统计陈旧

-- 2 手动 ANALYZE 重建统计
ANALYZE VERBOSE orders;
ANALYZE VERBOSE (SKIP_LOCKED) orders;  -- 跳过锁定行加速

-- 3 关键列单独高精度
-- 默认 default_statistics_target=100 高基数列调高
ALTER TABLE orders ALTER COLUMN user_id SET STATISTICS 1000;
ALTER TABLE orders ALTER COLUMN created_at SET STATISTICS 500;
ANALYZE orders;

-- 4 调整 autovacuum 阈值 让高频写表更早 ANALYZE
ALTER TABLE orders SET (
    autovacuum_analyze_scale_factor = 0.02,    -- 默认 0.1 太宽松
    autovacuum_analyze_threshold = 1000,
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_vacuum_threshold = 5000
);

-- 5 检查列基数与直方图
SELECT attname,
       n_distinct,
       most_common_vals,
       most_common_freqs,
       histogram_bounds
FROM pg_stats
WHERE tablename = 'orders' AND attname IN ('status', 'user_id');

-- status 字段 most_common_freqs={0.85,0.10,0.03,...} 说明数据倾斜严重
-- 优化器在 status='paid' 时知道大约扫 85% 数据不会走索引
-- 真正瓶颈是低基数列的查询要 partial index 或 BRIN

实战经验:autovacuum 默认 scale_factor 0.1 对大表太宽松,亿级表 1200 万行变更才触发,统计早就过时,必须按表调小;高基数关键列调大 STATISTICS 到 500-1000 让直方图更精细;每次大批量数据导入后必须立即 ANALYZE 不能等 autovacuum;n_dead_tup 占比超过 20% 该 VACUUM FULL 或 pg_repack 了。我们调完统计参数后, 仅靠这一项让 30% 的慢查询自动用对了索引。

二 EXPLAIN ANALYZE 深读:看穿执行计划

不会读 EXPLAIN 等于盲调。EXPLAIN ANALYZE 实际跑一遍 SQL 给你预估行数与真实行数的对比 是诊断"为啥这条 SQL 慢"的核心工具。下面是生产级 EXPLAIN 解读方法。

-- 1 标准诊断模板
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
SELECT o.id, o.total, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'paid'
  AND o.created_at > '2023-06-01'
ORDER BY o.created_at DESC
LIMIT 100;

-- 关键看以下几个指标
-- 1) rows= 预估行数 vs actual rows= 实际行数 差 10x 以上 = 统计错
-- 2) cost= 预估代价 vs actual time= 实际耗时
-- 3) loops= 循环次数 nested loop 内层大 loop 高警惕
-- 4) Buffers: shared hit=缓存命中 read=磁盘读 高 read 说明内存不够或扫描太多
-- 5) Heap Fetches 走 index only scan 还需回表说明 VACUUM 滞后

-- 2 常见执行计划类型与含义
-- Seq Scan        全表扫描 大表致命 检查为什么没走索引
-- Index Scan      索引扫描 + 回表 适合少量数据返回
-- Index Only Scan 全部从索引出 最快 配 covering index
-- Bitmap Heap Scan 中等数据量最优 先收集索引再批量回表
-- Nested Loop     小表 join 大表 内层有索引 OK
-- Hash Join       大表 join 大表 占 work_mem
-- Merge Join      两侧已排序的等值连接

-- 3 用 auto_explain 抓慢日志中的完整计划
-- postgresql.conf
-- shared_preload_libraries = 'auto_explain'
-- auto_explain.log_min_duration = '500ms'
-- auto_explain.log_analyze = on
-- auto_explain.log_buffers = on
-- auto_explain.log_nested_statements = on

-- 4 强制走某种计划(诊断用 不可生产固化)
SET enable_seqscan = off;
EXPLAIN ANALYZE SELECT ...;  -- 看走索引会快多少
RESET enable_seqscan;

-- 5 当 EXPLAIN 显示 rows 估算错误 用扩展统计
CREATE STATISTICS s_orders_status_created
ON status, created_at
FROM orders;
ANALYZE orders;
-- 让优化器知道 status 与 created_at 的相关性 估算更准

实战经验:看 EXPLAIN 第一步看 rows 预估 vs actual rows 差距,差 10x 以上必须先解决统计问题再调索引;Buffers 比 cost 更真实 关注 shared read 高的节点;auto_explain 是生产必开 抓出真实慢查询的完整计划;扩展统计 CREATE STATISTICS 解决多列相关性估算 是 PG10+ 神器。我们用 auto_explain 抓出一个 N+1 查询 nested loop 套了 5000 次 表面单条 SQL 慢的本质是 ORM 没批量化。

三 索引设计精细化:从单列到复合到 partial

很多人以为加索引就是 CREATE INDEX 列名 但 PG 索引类型多 设计空间大 选错索引比不加还糟。下面是生产中高频用到的索引设计模式。

-- 1 复合索引列顺序 决定性影响
-- 错误:order_by 字段放第一列
CREATE INDEX idx_orders_bad ON orders (created_at, user_id, status);
-- WHERE user_id=? AND status=? 这条查询用不了

-- 正确:等值匹配列放前面 范围查询列放后面 ORDER BY 列放最后
CREATE INDEX idx_orders_good ON orders (user_id, status, created_at);
-- 覆盖 WHERE user_id=? AND status=? ORDER BY created_at DESC

-- 2 Partial Index:针对热点子集
-- 80% 订单是 paid 状态 查 paid 没意义 查少数状态有意义
CREATE INDEX idx_orders_pending
ON orders (created_at)
WHERE status IN ('pending', 'processing', 'refunding');
-- 索引体积小 写入开销低 查这几个状态飞快

-- 3 Covering Index (INCLUDE) 实现 Index Only Scan
CREATE INDEX idx_orders_user_cover
ON orders (user_id, status)
INCLUDE (total, created_at);
-- 查 SELECT total, created_at WHERE user_id=? AND status=? 全程不回表

-- 4 Expression Index:函数计算列
CREATE INDEX idx_users_lower_email
ON users (lower(email));
-- 配合 WHERE lower(email) = lower(?) 走索引
-- 直接 WHERE email ILIKE 不走索引 必须改写法

-- 5 BRIN 索引:时间序列大表的低成本索引
CREATE INDEX idx_orders_created_brin
ON orders USING BRIN (created_at)
WITH (pages_per_range = 32);
-- 1.2 亿行表 B-tree 索引 8GB BRIN 只要 50MB
-- 范围查询 created_at BETWEEN ? AND ? 也快
-- 但点查不如 B-tree 选场景用

-- (B-tree 与 BRIN 解决了传统列与时序场景,但生产里还有大量 JSONB 与文本检索需求)

B-tree 和 BRIN 覆盖绝大多数关系型查询场景,但 PG 真正的 killer feature 是 GIN/GiST 索引族 — 能让 JSONB 子文档匹配和中文全文检索走索引飞快,这是 MySQL 用户最羡慕 PG 的地方之一。

-- 6 GIN 索引:JSONB 与全文检索
CREATE INDEX idx_orders_meta ON orders USING GIN (meta jsonb_path_ops);
-- meta @> '{"channel":"app"}' 直接走索引
CREATE INDEX idx_products_search ON products USING GIN (to_tsvector('chinese', name));

-- 7 检查未使用与重复索引(节省空间)
SELECT s.schemaname,
       s.relname,
       s.indexrelname,
       s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid))
FROM pg_stat_user_indexes s
WHERE s.idx_scan = 0
  AND s.indexrelname NOT LIKE 'pg_%'
ORDER BY pg_relation_size(s.indexrelid) DESC;
-- 长期 idx_scan=0 的索引可以删

实战经验:复合索引列顺序 等值列优先 范围列在后 ORDER BY 列最后是铁律;partial index 是高数据倾斜场景的银弹 体积小写入快;INCLUDE 列让 Index Only Scan 成为可能 配 vacuum 及时 整套链路最快;BRIN 索引 1/100 体积 适合按时间或自然有序插入的大表 别只盯 B-tree;每月跑一次 idx_scan=0 检查删冗余索引省空间提写入。

四 查询模式重构:让 SQL 配合优化器

哪怕索引完美 不会写的 SQL 一样把优化器逼疯。下面是生产中最常踩的 SQL 反模式与改写方案。

-- 1 深度分页 OFFSET 反模式
-- 慢:OFFSET 1000000 扫一千万丢弃
SELECT * FROM orders
ORDER BY created_at DESC
LIMIT 100 OFFSET 1000000;

-- 改 keyset pagination (cursor-based)
SELECT * FROM orders
WHERE created_at < $last_seen_created_at
   OR (created_at = $last_seen_created_at AND id < $last_seen_id)
ORDER BY created_at DESC, id DESC
LIMIT 100;
-- 无论第几页都常数级

-- 2 IN 列表过长 反模式
-- 慢:IN (?,?,...,1000个) PG 可能放弃索引
SELECT * FROM orders WHERE user_id IN ($1, $2, ..., $1000);

-- 改 = ANY(array)
SELECT * FROM orders WHERE user_id = ANY($1::int[]);
-- 走索引且 plan cache 命中率高

-- 3 隐式类型转换 反模式
-- user_id 是 bigint 但传字符串
SELECT * FROM orders WHERE user_id = '12345';   -- 走不了索引
SELECT * FROM orders WHERE user_id = 12345;     -- 走索引

-- 4 NOT IN 与 NULL 反模式
-- 慢且坑:user_id NOT IN (SELECT bad_user_id FROM blacklist)
-- 如果 blacklist.bad_user_id 有 NULL 整个查询返回 0 行
-- 改 LEFT JOIN ... WHERE rhs.id IS NULL 或 NOT EXISTS
SELECT o.*
FROM orders o
WHERE NOT EXISTS (
    SELECT 1 FROM blacklist b WHERE b.bad_user_id = o.user_id
);

-- 5 OR 反模式
-- 慢:OR 两侧索引不一致 PG 难选
SELECT * FROM orders WHERE id = $1 OR ref_no = $2;

-- 改 UNION ALL 分别走索引
SELECT * FROM orders WHERE id = $1
UNION ALL
SELECT * FROM orders WHERE ref_no = $2 AND id <> $1;

-- 6 ORDER BY + LIMIT 走错索引
-- WHERE 命中索引 A ORDER BY 命中索引 B 优化器纠结
-- 解决:建复合索引 WHERE 列 + ORDER BY 列在一个索引里
CREATE INDEX idx_orders_user_created
ON orders (user_id, created_at DESC);

SELECT * FROM orders
WHERE user_id = $1
ORDER BY created_at DESC
LIMIT 20;
-- 现在走单索引 sorted 直接 LIMIT 出

-- 7 COUNT(*) 大表反模式
-- 慢:COUNT(*) 全表扫
SELECT COUNT(*) FROM orders;

-- 估算值可接受用 pg_class
SELECT reltuples::bigint FROM pg_class WHERE relname = 'orders';

-- 或维护实时计数表
CREATE TABLE orders_count (n bigint);
-- 用 trigger 维护 +1/-1

实战经验:OFFSET 大于 1000 就该考虑 keyset 分页 移动端瀑布流场景必用;IN 长列表用 = ANY(array) PG 绑定参数更高效 plan 复用率高;NOT IN 永远改 NOT EXISTS 防 NULL 陷阱;COUNT(*) 大表场景下精确不重要的用 pg_class.reltuples 估算 重要的维护汇总表;ORDER BY + LIMIT 必须配套复合索引 否则优化器经常纠结走错。我们靠这几条改写让首页订单列表 P99 从 3 秒降到 50 毫秒。

五 连接池与并发治理

PG 每个连接是一个独立进程 1000 连接 = 几 GB 内存 + 大量上下文切换。生产环境必须有连接池 + 合理的并发控制,否则数据库会被自家应用打挂。

# 1 PgBouncer 配置(transaction pooling 模式)
# /etc/pgbouncer/pgbouncer.ini
"""
[databases]
proddb = host=127.0.0.1 port=5432 dbname=proddb pool_size=40

[pgbouncer]
listen_port = 6432
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 40
reserve_pool_size = 10
reserve_pool_timeout = 3
server_lifetime = 3600
server_idle_timeout = 600
query_wait_timeout = 30
"""

# transaction 模式:连接在事务结束后即归还
# 优点:1 个 PG 连接服务 N 个客户端事务
# 限制:不能用 prepared statement / SET / advisory lock 跨事务

# 2 应用层连接池(asyncpg + Python)
import asyncpg

pool = await asyncpg.create_pool(
    dsn="postgres://user:pass@pgbouncer:6432/proddb",
    min_size=5,
    max_size=20,             # 单实例上限 不超过 pgbouncer pool_size/实例数
    max_inactive_connection_lifetime=300,
    command_timeout=30,
    server_settings={
        "application_name": "order_service",
        "statement_timeout": "10000",     # 10s 查询超时
        "lock_timeout": "5000",            # 5s 锁等待超时
        "idle_in_transaction_session_timeout": "60000"  # 防长事务
    }
)

连接池配好了应用层不再爆炸,但 PG 侧还有一类隐形杀手 — 长事务。一个 hold 住事务半小时不提交的会话能让 vacuum 失效、行版本无限累积、其他事务排队等锁,必须有数据库侧的兜底机制。

# 3 长事务杀手 trigger
"""
-- 数据库侧定时查
SELECT pid, now() - xact_start AS duration, query, state
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND now() - xact_start > interval '5 minutes'
ORDER BY duration DESC;

-- 自动 kill 超 5min 的事务(谨慎!)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND now() - xact_start > interval '5 minutes'
  AND application_name NOT IN ('etl_job', 'admin');
"""

# 4 读写分离 PG 主从 + 应用路由
class PgRouter:
    def __init__(self, master_pool, replica_pools):
        self.master = master_pool
        self.replicas = replica_pools  # list of pools
        self._rr = 0

    async def execute(self, query, *args, read_only=False):
        if read_only:
            # 读走从库 轮询
            pool = self.replicas[self._rr % len(self.replicas)]
            self._rr += 1
        else:
            pool = self.master
        async with pool.acquire() as conn:
            return await conn.fetch(query, *args)

# 5 流量降级开关
class QueryGuard:
    def __init__(self):
        self.slow_query_threshold = 2.0   # 2s
        self.recent_slow = []
        self.degraded = False

    async def execute(self, query, *args):
        if self.degraded and self.is_non_critical(query):
            return []   # 降级返回空 不打数据库
        start = time.time()
        result = await pool.fetch(query, *args)
        elapsed = time.time() - start
        if elapsed > self.slow_query_threshold:
            self.recent_slow.append(time.time())
            # 1 分钟超 10 个慢查询 进入降级
            self.recent_slow = [t for t in self.recent_slow if time.time() - t < 60]
            if len(self.recent_slow) > 10:
                self.degraded = True
                schedule_recovery_check()
        return result

实战经验:PG 直连超过 200 必上 PgBouncer transaction pool 1 万应用连接 → 50 PG 后端连接稳如老狗;statement_timeout 必设 别让一条慢 SQL 占连接不归还;idle_in_transaction_session_timeout 是长事务最大杀手 防止 hold lock 阻塞 vacuum;长事务监控自动 kill 但必须排除 ETL 与管理任务白名单;读写分离让读流量打从库 主库专心写 整体 QPS 提升 3-5 倍。

[mermaid]
flowchart TD
A[应用请求] --> B[应用连接池]
B --> C[PgBouncer transaction 模式]
C --> D{读 or 写}
D -->|写| E[主库 PG]
D -->|读| F[从库 PG 轮询]
E --> G[WAL 流复制]
G --> F
E --> H[执行计划]
F --> H
H --> I{统计是否最新}
I -->|否| J[ANALYZE 触发]
J --> H
I -->|是| K[选索引或 seq scan]
K --> L[结果返回]
H --> M[auto_explain 抓慢]
M --> N[慢日志 + 告警]

六 分区与归档:亿级表的生存法则

orders 表到 1 亿行后,普通索引体积超过 20GB,VACUUM 一次锁表数分钟,查询计划越来越不稳。分区表 + 冷热数据归档是亿级 PG 的必经之路。

-- 1 PG11+ 声明式分区(按月分区)
CREATE TABLE orders (
    id bigserial,
    user_id bigint NOT NULL,
    status text NOT NULL,
    total numeric(10,2),
    created_at timestamptz NOT NULL,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- 2 自动建分区(用 pg_partman 或脚本)
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');
-- ... 提前 3 个月建好分区 不要等数据进来才发现没分区

-- 3 默认分区接收意外数据
CREATE TABLE orders_default PARTITION OF orders DEFAULT;
-- 定期检查 orders_default 不为空说明分区没建对

-- 4 分区裁剪生效检查
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE created_at >= '2024-03-01' AND created_at < '2024-04-01'
  AND status = 'paid';
-- 关键看:只扫 orders_2024_03 没扫其他分区

-- 5 老数据归档与 detach
-- 把 1 年前的分区 detach 出去 转冷存储
ALTER TABLE orders DETACH PARTITION orders_2023_01;
-- 此时 orders_2023_01 是独立表 可以
--   a) 转 ZFS 压缩冷盘
--   b) COPY 出来到 S3 后 DROP
--   c) 转 columnar 引擎做分析

-- 6 索引继承
-- PG11+ 主表 CREATE INDEX 会自动在每个分区建
CREATE INDEX ON orders (user_id, status, created_at);
-- 自动作用到 orders_2024_01 等子分区

-- 7 VACUUM 与 ANALYZE 分区级
-- 旧分区基本不变 跳过
-- 当前月活跃 高频 VACUUM
ALTER TABLE orders_2024_03 SET (
    autovacuum_vacuum_scale_factor = 0.02,
    autovacuum_analyze_scale_factor = 0.01
);
ALTER TABLE orders_2023_06 SET (
    autovacuum_enabled = off  -- 老分区不再变更 关 autovacuum
);

-- 8 监控分区健康
SELECT parent.relname AS parent_table,
       child.relname AS partition,
       pg_size_pretty(pg_relation_size(child.oid)) AS size,
       (SELECT n_live_tup FROM pg_stat_user_tables WHERE relname = child.relname) AS live_tup
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
WHERE parent.relname = 'orders'
ORDER BY child.relname DESC;

实战经验:亿级表分区是必选 不分区到 5 亿行 PG 就开始崩;按时间分区 + pg_partman 自动维护提前 3 个月建好分区;default 分区是逃生通道 但定期检查 不能让数据长期落 default;detach 老分区做归档比 DELETE 快 1000 倍且不产生 dead tuple;老分区关 autovacuum 释放 IO 给热分区 整体性能更稳。我们 orders 表分区改造后 VACUUM 时间从 20 分钟降到 30 秒 查询 P99 从 5s 降到 200ms。

关键概念速查

避坑清单

1. 不要默认 autovacuum 参数跑大表 必须按表调小 scale_factor 让统计及时。
2. 不要凭直觉加索引 必须先 EXPLAIN ANALYZE 看 rows 预估差距再决定。
3. 不要建复合索引把 ORDER BY 列放前面 等值列必须在前。
4. 不要省 partial index 高数据倾斜的查询用 partial 体积小 10 倍。
5. 不要用 OFFSET 做深度分页 必须 keyset (cursor) 分页。
6. 不要传字符串到 bigint 列 隐式类型转换让索引失效。
7. 不要用 NOT IN 配可能含 NULL 的子查询 改 NOT EXISTS。
8. 不要直连 PG 超过 200 个连接 必须 PgBouncer transaction pool。
9. 不要省 statement_timeout 一条慢 SQL 能拖垮整个连接池。
10. 不要等表到 5 亿行才分区 1 亿就该规划 RANGE 分区 + 提前建好未来 3 个月分区。

总结

把 PostgreSQL 性能优化这套从我们踩过的所有坑里反过来看 你会发现真正影响数据库稳定性的不是机器配置 而是工程化的全栈优化能力。同样一台 32 核 128GB 的 PG 默认配 + 瞎加索引 P99 5 秒 CPU 95% 经常被打挂;调好 autovacuum + EXPLAIN 驱动加索引 + keyset 分页 + PgBouncer + 分区表 同样的机器扛 10 倍流量 P99 50ms CPU 30% 稳如老狗。PostgreSQL 调优不是"哪慢加哪个索引"的活儿 它是一个统计信息 + 执行计划 + 索引设计 + SQL 重构 + 连接池 + 分区归档 + 监控的完整系统工程。

另一个常见的认知误区是把数据库当黑盒 觉得加索引调参数就能解决一切。但事实是 PG 优化器是一个 cost-based 概率系统 同样 SQL 在统计陈旧时和统计准确时执行计划截然不同 同一索引在数据倾斜时优化器可能拒用 同一连接池在长事务下可能资源全占。性能工程的核心是 假设优化器随时可能选错 用数据驱动决策 用监控发现退化 用工具化手段保证不退步。

打个比方 PostgreSQL 性能优化像马拉松运动员的训练体系。统计信息是身体数据(心率 血氧 乳酸)EXPLAIN ANALYZE 是赛后视频复盘(看每一步动作)索引设计是训练计划(哪些肌群重点练)SQL 改写是动作纠正(去掉无效消耗)连接池是配速控制(不上头冲爆)分区归档是身体维护(老伤定期处理)监控告警是教练实时跟队(发现状态异常立即调整)。哪一环没做 这个运动员可能短期出成绩 但长期一定崩盘 要么伤病不断 要么状态波动剧烈 要么大赛掉链。

所以下一次再有人跟你说"PG 慢加索引就行"你可以反问他 统计准吗 EXPLAIN 看了吗 索引顺序对吗 partial 与 covering 用了吗 SQL 改写做了吗 PgBouncer 上了吗 statement_timeout 设了吗 分区做了吗 慢日志监控开了吗 这些工作没做完 PG 调优只是一次性烟花 跑两天又退化。从踩坑到稳如老狗 中间隔着一整套数据库性能工程方法论 这条路没有捷径 但走完之后 你的 PG 会从间歇瘫痪变成 99.99% 可用 从每月几起 P1 故障变成半年零事故 从应用骂数据库慢变成数据库扛得住所有突发流量。

—— 别看了 · 2026