PostgreSQL 性能优化（一）：建立基线并定位真正瓶颈

数据库优化最容易犯的错误，是看到一条慢 SQL 就立即加索引，或者直接调整 shared_buffers、work_mem。这些操作有时有效，但如果没有先确认瓶颈来自哪里，很可能只是把问题暂时藏起来，同时增加写入、内存或运维成本。

这套手册从诊断开始。本篇先把业务延迟对应到具体数据库活动，并保存可复测的基线；后续优化才有明确对象，也能用同一组证据判断改动是否真的有效。示例数据只用于观察定位过程，不代表真实生产负载。

优化前先定义问题

“接口很慢”不是足够具体的问题。开始优化前，先确认慢的是平均响应时间还是 p95、p99 长尾延迟，时间消耗在执行、锁等待、连接池排队还是网络，以及目标是降低延迟、提高吞吐量、减少 CPU 还是减少磁盘读取。

优化优先级也不能只看单次耗时。一条偶尔执行的慢查询，业务影响可能小于单次不慢但高频调用、累计占用大量数据库时间的查询。应同时比较业务影响、调用频率、累计数据库时间与资源消耗。

发现慢请求
  -> 确认数据库是否为主要耗时
  -> 找到高总耗时或高频 SQL
  -> 排除锁、连接与外部等待
  -> 保存执行计划和数据条件
  -> 用相同条件重新验证

准备订单系统案例

示例使用用户、订单、订单明细和支付记录四张表。内部关联键使用 bigint identity，对外订单编号使用 PostgreSQL 18 的 uuidv7()。这里只建立主键、唯一约束和外键，不预先添加业务索引，以免在没有查询证据前假定访问模式。

CREATE TABLE users (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL,
  display_name text NOT NULL,
  profile jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  public_id uuid NOT NULL DEFAULT uuidv7(),
  user_id bigint NOT NULL REFERENCES users (id),
  status text NOT NULL CHECK (
    status IN ('pending', 'paid', 'cancelled', 'refunded')
  ),
  total_amount numeric(14, 2) NOT NULL CHECK (total_amount >= 0),
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (public_id)
);

CREATE TABLE order_items (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id bigint NOT NULL REFERENCES orders (id),
  product_id bigint NOT NULL,
  quantity integer NOT NULL CHECK (quantity > 0),
  unit_price numeric(14, 2) NOT NULL CHECK (unit_price >= 0)
);

CREATE TABLE payments (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id bigint NOT NULL REFERENCES orders (id),
  provider text NOT NULL,
  provider_trade_no text,
  status text NOT NULL CHECK (
    status IN ('created', 'succeeded', 'failed', 'refunded')
  ),
  paid_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now()
);

PostgreSQL 不会因为创建外键就自动为引用列创建索引。是否为 orders.user_id、order_items.order_id 和 payments.order_id 建索引，应由后续观察到的连接、父记录删除与父键更新模式决定。

SELECT setseed(0.42);

INSERT INTO users (email, display_name, created_at)
SELECT
  'user-' || n || '@example.com',
  'User ' || n,
  now() - random() * interval '1000 days'
FROM generate_series(1, 100000) AS n;

INSERT INTO orders (
  user_id,
  status,
  total_amount,
  created_at,
  updated_at
)
SELECT
  1 + floor(random() * 100000)::bigint,
  CASE
    WHEN n % 20 = 0 THEN 'pending'
    WHEN n % 20 IN (1, 2, 3) THEN 'cancelled'
    WHEN n % 20 = 4 THEN 'refunded'
    ELSE 'paid'
  END,
  round((10 + random() * 990)::numeric, 2),
  now() - random() * interval '730 days',
  now()
FROM generate_series(1, 500000) AS n;

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT
  o.id,
  1 + floor(random() * 50000)::bigint,
  1 + floor(random() * 4)::integer,
  round((5 + random() * 495)::numeric, 2)
FROM orders AS o
CROSS JOIN LATERAL generate_series(
  1,
  1 + (o.id % 3)::integer
);

INSERT INTO payments (
  order_id,
  provider,
  provider_trade_no,
  status,
  paid_at,
  created_at
)
SELECT
  o.id,
  (ARRAY['stripe', 'wechat', 'alipay'])[
    1 + floor(random() * 3)::integer
  ],
  'trade-' || o.public_id,
  CASE WHEN o.status IN ('paid', 'refunded')
    THEN 'succeeded'
    ELSE 'created'
  END,
  CASE WHEN o.status IN ('paid', 'refunded')
    THEN o.created_at + random() * interval '30 minutes'
    ELSE NULL
  END,
  o.created_at
FROM orders AS o
WHERE o.status <> 'cancelled';

ANALYZE users;
ANALYZE orders;
ANALYZE order_items;
ANALYZE payments;

setseed() 让当前会话的伪随机序列可重复；生成后执行 ANALYZE，让规划器基于当前数据分布估算选择性。行数可以按本机资源调小，但每轮对比应保持规模与分布一致。

从应用链路定位数据库耗时

先从接口的 trace 或分段计时确认数据库调用确实占主要耗时。连接池排队、DNS、网络、序列化和下游服务延迟都可能被笼统地记在一次请求里，不能看到“SQL 调用”就认定数据库执行慢。

把请求的 Trace ID、接口名称、数据库实例、会话标识与规范化 SQL 关联起来，并同时记录调用频率和 p95/p99。应用侧的长尾分布能够补足数据库聚合视图只有平均执行时间、没有请求级分位数的限制。若时间主要花在取连接之前，应先检查连接池容量、泄漏和事务边界。

慢查询日志发现单次异常

log_min_duration_statement 会记录执行时间达到阈值的语句，适合捕获偶发的单次异常：

log_min_duration_statement = 500ms

500ms 只是示例，不是通用推荐值。阈值应来自业务延迟目标与可接受日志量；设得过高会漏掉高频但累计成本大的查询，过低则会放大日志写入、存储与分析开销。生产环境还要评估参数值是否包含敏感数据，并通过 Trace ID 和会话信息把日志对应回具体请求。

pg_stat_statements 定位累计成本

pg_stat_statements 聚合规范化语句的规划与执行统计，适合回答“哪些查询累计占用了最多数据库时间”。PostgreSQL 18 需要先在 postgresql.conf 中预加载模块并启用查询标识；修改 shared_preload_libraries 后需要重启实例。

shared_preload_libraries = 'pg_stat_statements'
compute_query_id = auto

然后在需要读取该视图的数据库中创建扩展：

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

优先按累计执行时间筛选，再结合频率、单次均值、处理行数与块读写判断成本来源：

SELECT
  queryid,
  calls,
  round(total_exec_time::numeric, 2) AS total_exec_ms,
  round(mean_exec_time::numeric, 2) AS mean_exec_ms,
  rows,
  shared_blks_hit,
  shared_blks_read,
  temp_blks_written,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

calls 表示调用频率，total_exec_time 表示累计执行时间，mean_exec_time 表示单次均值；shared_blks_read 和 temp_blks_written 可提示存储读取或临时文件压力。统计会跨观察窗口累计，比较优化前后必须记录 stats_since 或统一采集区间。不要为方便对比就直接全局重置统计，否则会破坏其他人的诊断基线。

先排除锁与连接等待

应用看到的 SQL 耗时不等于 CPU 执行时间。pg_stat_activity 的 state、wait_event_type 和 wait_event 可以帮助区分正在执行与等待中的会话：

SELECT
  pid,
  usename,
  state,
  wait_event_type,
  wait_event,
  now() - query_start AS query_age,
  now() - xact_start AS transaction_age,
  left(query, 160) AS query
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
  AND state <> 'idle'
ORDER BY query_start;

若 wait_event_type 为 Lock，应优先定位阻塞者、持锁语句和事务边界。索引可能缩短获得锁后的执行时间，却不能修复长事务、错误的加锁顺序或应用忘记提交。若大量请求卡在获取连接且数据库中没有对应会话，则问题更可能在连接池或连接上限，而不是 SQL 访问路径。

建立可复测基线

进入索引、SQL 或表结构优化前，为每个候选查询保存同一份基线：

• SQL 与参数：保留规范化 SQL，也保存能复现选择性的数据类型与代表性参数。
• 调用频率：记录固定观察窗口内的调用次数，并注明接口、任务或租户来源。
• 平均与 p95/p99：数据库均值与应用链路分位数分开记录，避免把两个统计口径混为一谈。
• 累计数据库时间：记录查询在同一窗口内消耗的总执行时间，用于排序优化优先级。
• 执行计划：保存计划文本及采集命令；对写语句使用 EXPLAIN ANALYZE 会真实执行，必须先控制事务与副作用。
• 数据规模与分布：记录表行数、关键条件占比、时间范围以及统计信息是否新鲜。
• 缓存状态：注明冷缓存、预热后缓存或生产自然缓存，避免把缓存差异误认为方案收益。
• 等待事件：记录会话是否等待锁、I/O、客户端或其他资源，并保留阻塞链证据。

优化后使用相同 SQL、参数、数据快照、并发模型和采集窗口复测。只有业务目标改善、累计成本下降且写入与维护代价可接受，才说明改动值得上线。

参考资料

• PostgreSQL 18：pg_stat_statements
• PostgreSQL 18：Logging
• PostgreSQL 18：Monitoring Database Activity