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

本文以 PostgreSQL 18 和一个订单系统为例，建立一套适合后端开发者的优化流程：先找出真正消耗数据库时间的查询，再通过执行计划判断瓶颈，最后在 SQL、索引、表结构、预计算和数据生命周期之间选择代价合适的方案。

文中的测试数据只用于观察规划器和访问路径，不代表真实生产负载。性能结论应在与生产数据分布、参数和并发量接近的环境中重新验证。

优化之前：先定义问题

“接口很慢”不是一个足够具体的数据库问题。开始优化前，至少需要回答下面几个问题：

  • 慢的是平均响应时间，还是 p95、p99 长尾延迟？
  • 查询本身很慢，还是等待锁、连接池或网络？
  • 单次执行很慢，还是单次不慢但调用频率极高？
  • 问题只影响一个租户、一段时间或某种参数，还是所有请求？
  • 当前目标是降低延迟、提高吞吐量、减少 CPU，还是减少磁盘读取？

一个单次耗时 500 毫秒、每天执行 10 次的后台查询，未必比单次耗时 8 毫秒、每秒执行 2000 次的查询更值得优先处理。优化顺序应综合业务影响、调用次数和累计数据库时间，而不是只看最慢的一次执行。

可以把完整流程概括为：

发现慢请求
  -> 确认数据库是否为主要耗时
  -> 找到高总耗时或高频 SQL
  -> 使用执行计划定位具体节点
  -> 选择 SQL、索引、表结构或预计算方案
  -> 用相同条件重新验证

顺序扫描并不天然等于性能问题。读取一张很小的表，或者查询需要返回表中大部分数据时，顺序扫描通常比大量随机回表更合理。反过来，执行计划出现 Index Scan 也不代表查询一定高效：低选择性的索引可能读取大量索引项和数据页，成本反而更高。

准备订单系统案例

后续示例使用用户、订单、订单明细和支付记录四张表。内部关联键使用 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 是否需要索引，要根据连接、删除父记录和更新父键的实际访问模式决定。

下面的测试数据使用 setseed() 固定当前会话中 random() 的伪随机序列，便于重复生成相近的数据分布。行数可以根据本机资源调小。

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;

生成数据后执行 ANALYZE 很重要。规划器依赖统计信息估算条件选择性和连接结果行数；如果测试表刚写入大量数据却没有统计信息，后续执行计划可能主要反映统计信息缺失，而不是索引设计的差异。

找到真正需要优化的 SQL

数据库监控应该和应用链路追踪一起看。应用侧先确认请求时间主要消耗在数据库调用，而不是连接池排队、HTTP 请求、序列化或下游服务；数据库侧再确认查询是在运行、等待锁，还是等待 I/O。只根据应用日志中的单次耗时，很难判断数据库内部发生了什么。

慢查询日志适合发现单次异常

log_min_duration_statement 可以记录超过指定时长的语句。例如下面的配置会记录执行时间达到 500 毫秒的 SQL：

log_min_duration_statement = 500ms

慢查询阈值应根据业务延迟目标和日志量设置。阈值过高会漏掉高频但累计成本很大的查询，过低则可能产生大量日志，并增加日志写入和分析成本。生产环境通常还需要结合参数采样、链路 ID 和数据库会话信息，才能把 SQL 与具体接口对应起来。

pg_stat_statements 适合确定累计成本

pg_stat_statements 会聚合规范化后的语句统计，可以回答“哪些查询累计占用了最多数据库时间”。PostgreSQL 18 中该模块需要通过 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判断单次执行是否昂贵
rows识别返回或处理行数异常
shared_blks_read观察需要从存储读取的数据块
temp_blks_written发现排序、哈希等操作写入临时文件

统计值从上次重置或服务器启动后持续累计，比较优化前后数据时需要记录统计窗口。不要在没有确认影响的情况下随意调用 pg_stat_statements_reset()，否则会清除其他人正在使用的基线。

先排除锁等待

一条 SQL 在应用中耗时很长，不代表它一直在消耗 CPU。下面的查询可以查看正在执行或等待的会话：

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 增加索引可能缩短它获得锁后的执行时间，却不能解决长事务、错误的锁顺序或应用忘记提交事务的问题。

读懂 EXPLAIN ANALYZE

找到目标 SQL 后，再分析具体访问路径。以按用户倒序查询订单为例：

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT
  id,
  public_id,
  status,
  total_amount,
  created_at
FROM orders
WHERE user_id = 42000
ORDER BY created_at DESC, id DESC
LIMIT 50;

EXPLAIN 只展示规划器估算，EXPLAIN ANALYZE 会真正执行查询并记录实际数据。阅读时建议从最深层节点向上看，因为上层节点的耗时通常来自下层累计。

先看估算行数和实际行数

计划中的 rows 是规划器估算，actual rows 是实际返回行数。如果两者相差几个数量级，规划器可能选择错误的连接方式、扫描方式或内存策略。常见原因包括：

  • 刚批量写入数据但没有执行 ANALYZE。
  • 某列数据高度倾斜，默认统计样本无法描述热点值。
  • 两个条件存在相关性，规划器却按相互独立估算。
  • 表达式、类型转换或复杂谓词让选择性难以估算。

对明显倾斜的重要列，可以提高单列统计目标，而不是全局无限提高统计采样成本：

ALTER TABLE orders
ALTER COLUMN status SET STATISTICS 500;

ANALYZE orders (status);

多列相关性问题可以评估扩展统计信息，但它不是缺失索引的替代品，也不会自动修复所有连接估算。

actual time 必须结合 loops

actual time=a..b rows=n loops=m 中，时间通常是每次循环的平均值。Nested Loop 内层节点如果单次只耗时很短，但循环几十万次，总成本仍然可能很高。分析时不要只找数值最大的单个 actual time，还要结合 loops、返回行数和父节点行为。

BUFFERS 告诉你读取了多少数据

常见缓冲区指标包括：

  • shared hit：数据块已经在 PostgreSQL 共享缓冲区中。
  • shared read：数据块需要从存储层读取。
  • shared dirtied：执行过程中把数据页修改为脏页。
  • temp read / temp written：排序或哈希操作使用了临时文件。

缓存命中不意味着查询已经足够高效。一个查询如果每次都命中缓存，却读取几十万个无关数据块，仍然会消耗 CPU、内存带宽并挤占其他热点数据。

关注排序、过滤和回表

下面几类信号通常值得继续调查：

  • Rows Removed by Filter 很大：扫描了大量数据后才过滤。
  • Sort Method 出现 external merge：排序数据超过可用内存并写入磁盘。
  • Bitmap Heap Scan 读取很多 heap block：索引筛选后仍需访问大量表页。
  • Index Scan 返回大量行：索引选择性可能不足。
  • Heap Fetches 很大：Index Only Scan 仍然频繁访问表，可能与可见性映射或更新频率有关。

EXPLAIN ANALYZE 会执行语句

对 INSERT、UPDATE、DELETE 和会产生副作用的函数，EXPLAIN ANALYZE 会真正执行。可以在确认所有副作用都受事务控制时使用：

BEGIN;

EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders
SET status = 'cancelled',
    updated_at = now()
WHERE id = 123
  AND status = 'pending';

ROLLBACK;

这并不是绝对安全的沙箱。序列取值不会因回滚恢复，触发器可能调用外部系统，某些函数也可能产生事务外副作用。生产写语句应优先在隔离环境、只读副本或可控数据上验证。

对于执行时间极短、调用次数很多的查询，逐节点计时本身可能带来可见开销，可以比较 EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)，但关闭计时后只能看到整体执行时间和行数，不能看到每个节点的实际时间。

从表结构减少无效成本

表结构优化的目标不是尽可能缩短每一列，而是让约束、访问方式和数据生命周期与业务一致。错误的数据模型会迫使每条查询做重复转换、读取无关大字段，或者维护昂贵索引。

text 和 varchar(n) 主要是约束差异

在 PostgreSQL 中，text、没有长度限制的 varchar 和 varchar(n) 不应以“哪个查询更快”作为主要选型依据。只有当业务确实要求最大长度时，才使用长度约束。比如外部支付渠道编号最多 64 个字符，可以通过 varchar(64) 或 CHECK 明确表达；用户备注没有稳定长度规则时，使用 text 更自然。

不要使用过大的固定长度 char(n) 期待获得性能优势。它会用空格填充，并带来容易忽略的比较与展示语义。

金额表示必须先定义精度规则

示例使用 numeric(14, 2)，优点是十进制计算精确且表达直观，代价是存储和计算成本高于整数。另一种方案是用 bigint 保存最小货币单位，例如分：

total_amount_minor bigint NOT NULL CHECK (total_amount_minor >= 0)

整数方案计算高效，但必须统一币种、精度和舍入规则。涉及多币种、不同小数位或财务计算时，不能只为追求速度就把所有金额强行压成同一种整数语义。真正的选择依据是业务精度模型，而不是一条脱离场景的性能结论。

JSONB 不应吞掉稳定关系字段

JSONB 适合保存结构经常变化、整体读取或只在少数场景检索的数据。订单状态、用户 ID、创建时间和金额属于高频过滤、排序、关联和约束字段，应保留为类型明确的列。

把这些字段全部放入 metadata 会带来几个问题：

  • 类型和必填约束更难表达。
  • 查询需要重复提取和转换。
  • 统计信息与选择性估算更困难。
  • GIN 索引体积和写入维护成本可能很高。
  • 字段重命名与兼容逻辑容易扩散到大量 SQL。

如果确实需要按 JSON 属性检索，应根据操作符选择 GIN operator class。例如 jsonb_path_ops 适合以包含和 JSONPath 为主的查询，但不支持所有默认 jsonb_ops 能支持的操作符，不能仅因为索引通常更小就统一替换。

宽行会降低缓存密度

订单如果同时保存大型原始报文、审计快照和业务字段，即使列表接口只读取 5 列，宽行仍可能降低每个数据页容纳的元组数量，并增加 TOAST、Vacuum 和缓存压力。

极少读取的大字段可以纵向拆分：

CREATE TABLE order_payloads (
  order_id bigint PRIMARY KEY REFERENCES orders (id),
  request_payload jsonb,
  response_payload jsonb,
  audit_snapshot text
);

拆表会增加需要完整读取订单时的连接成本，因此只适合访问频率差异明显的冷热字段，不应该为了“范式化”机械拆分每个字段组。

时间点优先使用 timestamptz

订单创建时间、支付时间表示现实世界中的时间点，通常应使用 timestamptz。PostgreSQL 内部保存的是绝对时间点，显示时根据会话时区转换。业务日期、账期等不带具体时刻的概念，则可以使用 date。

不要通过去掉时区信息来规避跨时区问题。更稳妥的做法是统一保存时间点，并在 API 边界、报表时区或用户界面中明确转换规则。

主键选择影响索引局部性

常见选择可以这样比较：

类型主要优点主要代价
bigint identity8 字节、递增、索引紧凑、连接高效容易暴露业务规模，跨系统生成需要额外机制
UUIDv4可在多个节点独立生成，不暴露递增数量16 字节且随机写入，索引局部性较差
UUIDv7可分布式生成，并按时间大致有序仍为 16 字节，时间有序不等于严格业务顺序

PostgreSQL 18 内置 uuidv7()。它由毫秒级 UNIX 时间戳、亚毫秒信息和随机部分组成，通常比 UUIDv4 更有利于 B-tree 插入局部性。但高并发下生成顺序不应被当作业务事务的严格先后关系，更不能替代独立的版本号或单调序列。

订单系统常见的折中是：内部关联继续使用紧凑的 bigint，对外暴露不可枚举的 UUID。代价是多维护一个唯一索引，需要结合安全需求和写入压力评估。

更新频繁的表可以评估 fillfactor 和 HOT

PostgreSQL 更新行时会产生新元组版本。更新没有出现在任何索引中的列，并且原数据页有足够空间时，可能使用 HOT（Heap-Only Tuple）减少索引更新。

对于频繁更新状态、但很少新增索引字段的表，可以评估较低的 fillfactor：

ALTER TABLE orders SET (fillfactor = 85);

这会在数据页中预留空间，提高新版本留在同一页的机会，但也会降低页密度、增加表大小和扫描块数。修改 fillfactor 只影响后续写入或被重写的页面，不能把它当成立即消除表膨胀的命令。

为查询模式设计索引

索引设计应从具体查询的过滤、排序、连接和返回列出发。先为“按用户倒序查询最近订单”建立联合索引：

CREATE INDEX idx_orders_user_created_id
ON orders (user_id, created_at DESC, id DESC)
INCLUDE (status, total_amount);

对应查询为：

SELECT
  id,
  status,
  total_amount,
  created_at
FROM orders
WHERE user_id = $1
ORDER BY created_at DESC, id DESC
LIMIT 50;

这个索引的设计依据是：

  • user_id 使用等值条件，放在前面可以快速定位用户范围。
  • created_at DESC, id DESC 与排序顺序一致，id 作为唯一且稳定的平局处理键。
  • status、total_amount 只需要返回，不参与搜索和排序，因此放入 INCLUDE。

INCLUDE 可能让查询使用 Index Only Scan，但它不是“保证不回表”。PostgreSQL 仍需要通过可见性映射确认页面上的元组对当前快照可见；更新频繁、Vacuum 尚未处理的数据页可能出现大量 Heap Fetches。另外，被包含列会扩大索引，降低缓存密度并增加写入成本。

联合索引顺序不能只背口诀

对 B-tree 来说，前导列的等值条件通常最有利；第一个没有等值约束的列可以通过范围条件限制扫描区域。PostgreSQL 18 还能在部分场景使用 skip scan：即使查询没有前导列条件，优化器也可能为前导列的不同值重复执行索引查找。

例如索引 (status, created_at) 的第一列只有少量状态值，只按最近时间查询时，skip scan 可能比扫描整个索引更便宜。但如果前导列是拥有十万种取值的 user_id，重复查找通常没有意义，规划器更可能选择其他路径。

因此，skip scan 扩大了多列索引的可用场景，却没有消除字段顺序、基数和查询形态之间的权衡。是否使用仍要看实际执行计划。

部分索引只保存真正关心的子集

如果系统频繁扫描待支付订单，而绝大部分历史订单已经完成，可以只索引待支付数据：

CREATE INDEX idx_orders_pending_created
ON orders (created_at)
WHERE status = 'pending';

下面的查询可以与索引谓词匹配：

SELECT id, user_id, created_at
FROM orders
WHERE status = 'pending'
  AND created_at < now() - interval '30 minutes'
ORDER BY created_at
LIMIT 500;

部分索引更小，完成订单也不需要维护该索引。但 PostgreSQL 必须在规划阶段证明查询条件蕴含索引谓词。像 WHERE status = $1 这样的参数化条件无法保证参数永远为 pending，可能无法使用这个部分索引。设计前要检查应用实际发送的预备语句，而不是只在 SQL 客户端中测试字面量版本。

表达式索引适合稳定的规范化规则

如果登录查询始终忽略邮箱大小写，可以创建表达式唯一索引：

CREATE UNIQUE INDEX idx_users_lower_email
ON users (lower(email));

查询必须使用与索引表达式匹配的形式：

SELECT id, email, display_name
FROM users
WHERE lower(email) = lower($1);

表达式索引把计算成本转移到写入阶段，并增加索引维护。如果规范化规则涉及区域、排序规则或业务语义，还需要先明确这些规则是否长期稳定。

BRIN 适合超大且物理相关的数据

BRIN 不为每行保存完整索引项，而是记录一段物理块范围的摘要。对于按时间持续追加、物理顺序与 created_at 高度相关的超大历史表，它通常非常小：

CREATE INDEX idx_orders_created_brin
ON orders USING brin (created_at);

BRIN 会找出可能包含目标值的数据块范围，再进行检查，因此不适合高选择性的单行查询，也不适合物理顺序与索引列无关的表。删除、更新和乱序导入会降低相关性，需要通过实际块读取量验证效果。

B-tree、GIN、GiST 和 BRIN 怎么选

索引类型常见场景主要边界
B-tree等值、范围、排序、唯一约束多列顺序和选择性很重要
GINJSONB、数组、全文检索中的成员或词项写入和更新成本较高，索引可能很大
GiST范围、几何、距离和可扩展操作符类行为取决于具体 operator class
BRIN超大、追加写且与物理顺序相关的表返回的是候选块范围，需要二次检查

索引类型必须和查询操作符匹配。看到列类型是 JSONB 就创建 GIN，或者看到时间列就创建 BRIN，都属于缺少查询证据的设计。

生产环境并发创建索引

普通 CREATE INDEX 会阻止并发写入目标表。生产大表通常评估：

CREATE INDEX CONCURRENTLY idx_payments_order_id
ON payments (order_id);

CONCURRENTLY 允许正常写入继续，但需要更多工作、通常耗时更长，并且不能放在事务块中执行。如果执行失败，可能留下 INVALID 索引；它不会用于查询，却仍可能产生更新维护成本，需要检查后删除或重建。

SELECT
  c.relname AS index_name,
  i.indisvalid,
  i.indisready
FROM pg_index AS i
JOIN pg_class AS c ON c.oid = i.indexrelid
WHERE i.indrelid = 'payments'::regclass;

索引创建还可能等待旧事务结束。上线方案需要包含执行窗口、锁等待监控、取消条件和失败清理，而不是只把 SQL 丢给迁移工具。

删除索引前先看读取与写入价值

可以从统计视图观察索引使用情况：

SELECT
  relname AS table_name,
  indexrelname AS index_name,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan, pg_relation_size(indexrelid) DESC;

idx_scan = 0 不能直接证明索引无用：统计信息可能刚重置，索引可能只服务月末任务、唯一约束或外键删除检查。删除前还要确认约束、低频关键查询和观察窗口。

SQL 写法与访问路径

索引存在不代表 SQL 会产生合适的访问路径。参数类型、函数位置、返回列和分页方式都会影响规划器能否利用索引。

避免在索引列上做不必要转换

下面的写法把每个 user_id 转换为文本，普通的 user_id B-tree 索引无法直接支持：

-- 不推荐
SELECT *
FROM orders
WHERE user_id::text = '42000';

参数应使用与列一致的类型：

SELECT id, public_id, status, total_amount, created_at
FROM orders
WHERE user_id = $1::bigint;

应用驱动通常能够发送正确的参数类型。不要为了方便把所有参数都当作字符串，再让数据库在每条查询中猜测或转换。

日期筛选也应优先写成范围：

-- 容易阻碍 created_at 普通索引
WHERE created_at::date = DATE '2026-07-20'

-- 明确半开区间
WHERE created_at >= TIMESTAMPTZ '2026-07-20 00:00:00+08'
  AND created_at <  TIMESTAMPTZ '2026-07-21 00:00:00+08'

半开区间还能明确时区和边界语义。如果业务必须按固定表达式检索，可以评估表达式索引，但要同时承担写入计算和索引空间成本。

不要用 SELECT * 扩大读取面

SELECT * 会把表新增列自动带入查询，增加网络、序列化和可能的 TOAST 读取成本，也可能让原本能使用覆盖索引的查询重新回表。列表接口应该明确返回字段：

SELECT id, public_id, status, total_amount, created_at
FROM orders
WHERE user_id = $1
ORDER BY created_at DESC, id DESC
LIMIT 50;

这不是要求所有查询都手写最少列。后台导出确实需要完整记录时，显式列出的主要价值是稳定接口契约，而不是追求字面上的最短 SQL。

避免应用层 N+1 查询

先查询 50 个订单，再为每个订单分别查询支付记录，会把一次页面请求放大为 51 次数据库往返。可以通过一次连接查询或批量查询完成：

SELECT
  o.id,
  o.public_id,
  o.status,
  o.total_amount,
  p.status AS payment_status,
  p.paid_at
FROM orders AS o
LEFT JOIN payments AS p ON p.order_id = o.id
WHERE o.user_id = $1
ORDER BY o.created_at DESC, o.id DESC
LIMIT 50;

如果一张订单可能有多条支付记录，直接连接会让订单重复。此时需要先定义“最新支付”或“成功支付”的业务规则，再使用 LATERAL、窗口函数或预聚合挑选目标行。减少往返不能以返回错误行数为代价。

只判断存在时使用存在语义

如果业务只关心是否至少有一条成功支付记录，不需要统计全部行：

SELECT EXISTS (
  SELECT 1
  FROM payments
  WHERE order_id = $1
    AND status = 'succeeded'
);

EXISTS 允许找到匹配行后停止，但它也不是无条件比 count(*) 快。数据分布、索引和查询改写仍应通过执行计划验证。

批量写入减少往返和事务成本

逐行执行数万次 INSERT 会产生大量网络往返和语句执行开销。中小批量可以使用多值插入，批量导入优先评估 COPY。批次过大也会扩大事务、WAL、锁持有时间和失败重试成本，因此要根据数据量和恢复策略分批。

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES
  ($1, $2, $3, $4),
  ($5, $6, $7, $8),
  ($9, $10, $11, $12);

批量更新同样应尽量使用集合操作，而不是在应用循环中执行单行更新。但集合更新可能一次锁住大量行，必须限制批次大小并保持稳定锁顺序。

深分页使用稳定的 Keyset Pagination

OFFSET 100000 仍然需要找到并跳过前面的记录。页码越深，需要处理的数据通常越多：

SELECT id, public_id, status, total_amount, created_at
FROM orders
WHERE user_id = $1
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 100000;

如果产品交互允许“加载更多”或顺序翻页，可以把上一页最后一条记录的 (created_at, id) 作为游标：

SELECT id, public_id, status, total_amount, created_at
FROM orders
WHERE user_id = $1
  AND (created_at, id) < ($2::timestamptz, $3::bigint)
ORDER BY created_at DESC, id DESC
LIMIT 50;

created_at 可能重复，所以必须增加唯一且排序方向一致的 id 作为平局处理键。Keyset Pagination 的代价是难以自然跳转到任意页，也要设计不可伪造或能够校验的游标格式。

先减少行数，再进行昂贵连接

报表查询容易把订单、明细和支付直接连接后再聚合。如果一张订单有多条明细和多条支付记录，会形成行数乘积，既可能算错金额，也会放大排序和哈希成本。

更稳妥的方式是先按业务主键分别聚合，再连接聚合结果：

WITH item_totals AS (
  SELECT
    order_id,
    sum(quantity * unit_price) AS item_amount
  FROM order_items
  GROUP BY order_id
), payment_totals AS (
  SELECT
    order_id,
    sum(CASE WHEN status = 'succeeded' THEN 1 ELSE 0 END) AS success_count
  FROM payments
  GROUP BY order_id
)
SELECT
  o.id,
  i.item_amount,
  p.success_count
FROM orders AS o
JOIN item_totals AS i ON i.order_id = o.id
LEFT JOIN payment_totals AS p ON p.order_id = o.id
WHERE o.created_at >= $1
  AND o.created_at < $2;

CTE 是否物化、连接顺序和聚合方式由 PostgreSQL 18 规划器结合查询决定。这里的关键不是“CTE 一定更快”，而是先消除多对多连接带来的重复行语义。

用物化视图承接重复聚合

当相同的大范围聚合被报表、定时任务和接口反复计算，而且业务允许一定延迟时，可以考虑物化视图。它把查询结果保存为实际数据，读取时不需要每次重新扫描和聚合源表。

下面按上海时区生成每日已支付订单汇总。显式声明报表时区可以避免 timestamptz 转日期时依赖会话时区：

CREATE MATERIALIZED VIEW daily_order_summary AS
SELECT
  (created_at AT TIME ZONE 'Asia/Shanghai')::date AS order_date,
  count(*) AS order_count,
  sum(total_amount) AS total_amount
FROM orders
WHERE status = 'paid'
GROUP BY (created_at AT TIME ZONE 'Asia/Shanghai')::date
WITH NO DATA;

CREATE UNIQUE INDEX idx_daily_order_summary_date
ON daily_order_summary (order_date);

-- 第一次填充不能使用 CONCURRENTLY。
REFRESH MATERIALIZED VIEW daily_order_summary;

物化视图填充后，后续可以并发刷新：

REFRESH MATERIALIZED VIEW CONCURRENTLY daily_order_summary;

PostgreSQL 18 要求并发刷新时至少存在一个覆盖所有行、只由普通列组成的唯一索引，不能使用部分唯一索引或表达式唯一索引。物化视图还必须已经填充。即使使用 CONCURRENTLY，同一物化视图同一时间也只能运行一个刷新任务。

普通视图、物化视图和汇总表的区别

方案数据更新方式适合场景主要代价
普通视图每次读取时执行底层查询封装 SQL、保持实时结果不减少底层计算成本
物化视图显式完整刷新允许延迟的重复聚合刷新消耗资源，结果存在时效性
业务汇总表应用、CDC 或任务增量维护更低延迟或需要增量更新一致性、补偿和回放逻辑复杂

PostgreSQL 原生 REFRESH MATERIALIZED VIEW 会替换完整内容，并不是通用的增量刷新机制。源表规模很大而刷新间隔很短时，即使读取变快，刷新本身也可能成为新的 I/O 和 CPU 高峰。

生产使用物化视图至少要设计：

  • 可接受的数据延迟，例如 5 分钟、1 小时或次日。
  • 初次填充和后续刷新方式。
  • 刷新失败后的告警、重试和旧数据标识。
  • 刷新与业务高峰错开的调度策略。
  • 刷新耗时超过调度间隔时的互斥机制。
  • 源数据修正后如何重新计算历史区间。

如果接口要求事务提交后立即看到聚合结果，或者聚合只扫描少量已正确索引的数据，物化视图可能增加的复杂度大于收益。

什么时候应该使用分区

分区把一张逻辑表的数据拆到多个物理子表中。它最有价值的两个场景是：查询经常只访问少量分区，以及历史数据需要按时间快速归档或删除。

订单主表已经存在主键和大量外键关系，直接改造成分区表可能涉及复杂迁移。下面使用持续追加的订单事件表展示按月范围分区：

CREATE TABLE order_events (
  id bigint GENERATED ALWAYS AS IDENTITY,
  order_id bigint NOT NULL REFERENCES orders (id),
  event_type text NOT NULL,
  payload jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL,
  PRIMARY KEY (created_at, id)
) PARTITION BY RANGE (created_at);

CREATE TABLE order_events_2026_07
PARTITION OF order_events
FOR VALUES FROM ('2026-07-01 00:00:00+00')
         TO   ('2026-08-01 00:00:00+00');

CREATE TABLE order_events_2026_08
PARTITION OF order_events
FOR VALUES FROM ('2026-08-01 00:00:00+00')
         TO   ('2026-09-01 00:00:00+00');

CREATE INDEX idx_order_events_order_created
ON order_events (order_id, created_at DESC);

在分区父表上创建索引，会为现有分区创建匹配的子索引，并让后续新建或挂载的分区遵循该索引结构。

查询条件与分区键匹配时，规划器或执行器可以裁剪无关分区：

SELECT order_id, event_type, created_at
FROM order_events
WHERE created_at >= TIMESTAMPTZ '2026-07-10 00:00:00+00'
  AND created_at <  TIMESTAMPTZ '2026-07-11 00:00:00+00';

分区裁剪依据分区边界，而不是子表中是否存在普通索引。子表索引负责在已经选中的分区内部继续缩小访问范围。

分区适合数据生命周期管理

如果事件数据只保留 12 个月，删除旧分区通常比在超大表中执行大批量 DELETE 更直接：

ALTER TABLE order_events
DETACH PARTITION order_events_2026_07;

DROP TABLE order_events_2026_07;

先分离再处理，可以把导出、校验和最终删除拆开。具体操作仍会涉及锁和依赖关系，需要在测试环境验证，并为长事务和正在使用该分区的查询设置处理策略。

分区不是普通表的性能开关

下面几种情况不应仅为了“查询更快”就引入分区：

  • 表规模不大，索引已经能快速定位数据。
  • 常用查询不包含分区键，导致访问大量分区。
  • 分区数量过多，增加规划、元数据和维护成本。
  • 唯一性要求无法自然包含分区键。
  • 应用和运维流程无法持续提前创建、校验和清理分区。

分区表上的唯一约束或主键必须包含所有分区键列，因为单个子索引只能保证一个分区内部唯一。这也是示例使用 (created_at, id) 作为主键的原因。如果业务必须让 id 在所有分区全局唯一，可以继续使用序列生成，但数据库不能仅凭各分区局部索引形成一个不包含分区键的全局唯一约束。

分区方案上线前要用真实参数确认 EXPLAIN 中实际访问的分区数量，并检查 enable_partition_pruning 没有被关闭。

MVCC、Vacuum 与表膨胀

PostgreSQL 使用 MVCC 让读写事务看到各自的一致性快照。UPDATE 通常不会原地覆盖旧版本，DELETE 也不会立即把文件中的空间归还给操作系统，而是留下后续可以回收或复用的死元组。

这意味着性能优化不能只关注 SQL 和索引。写入频繁的表如果 Vacuum 跟不上，可能出现：

  • 表和索引读取的无效版本越来越多。
  • 可见性映射无法及时更新，Index Only Scan 频繁回表。
  • 统计信息过期，规划器估算偏差扩大。
  • 表文件和索引持续膨胀。
  • 事务 ID 冻结不及时，最终带来 wraparound 风险。

先观察日常维护状态

SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  n_mod_since_analyze,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze,
  vacuum_count,
  autovacuum_count,
  analyze_count,
  autoanalyze_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

这些数字是统计估算，适合发现趋势和异常表，不是精确膨胀率。还要结合表大小、更新速率、事务年龄和 autovacuum 日志判断。

普通维护可以显式执行：

VACUUM (ANALYZE, VERBOSE) orders;

普通 VACUUM 主要让死元组空间可以被后续写入复用，并更新可见性映射，通常不会把表文件明显缩小。VACUUM FULL 会重写整张表并获取强锁，可以归还更多空间，但会阻塞正常访问并需要额外磁盘空间，不应作为定期保养命令。

autovacuum 参数应按热点表调整

默认 autovacuum 触发条件包含固定阈值和表行数比例。对于数亿行的大表，即使只更新了一个很小但绝对数量很大的活跃集合，默认比例也可能让触发时间太晚；对于很小但更新极频繁的表，则可能需要更积极地分析统计信息。

可以针对单表设置参数，而不是直接把全局配置改得非常激进：

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01
);

上面的值只是展示配置方式，不是生产推荐值。实际设置要根据表大小、每秒更新量、单次 autovacuum 耗时、I/O 余量和死元组增长速度计算，并持续观察调整后的运行结果。

长事务会阻止旧版本回收

只读但长期不结束的事务，同样可能持有很旧的快照，让 Vacuum 无法清理对它仍可能可见的元组。应用连接池中的 idle in transaction 会话尤其值得关注：

SELECT
  pid,
  usename,
  application_name,
  state,
  xact_start,
  now() - xact_start AS transaction_age,
  age(backend_xmin) AS xmin_age,
  wait_event_type,
  wait_event,
  left(query, 160) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

修复方向通常是缩短事务边界、确保异常路径回滚、为事务和空闲事务设置合理超时，并避免在事务中执行缓慢的外部网络调用。直接终止会话可能中断业务，必须先确认事务所有者和影响。

索引也会膨胀

更新索引列、删除大量数据和随机插入都会增加索引维护压力。可以先观察索引大小和使用趋势：

SELECT
  s.relname AS table_name,
  s.indexrelname AS index_name,
  s.idx_scan,
  pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
  pg_size_pretty(pg_relation_size(s.relid)) AS table_size
FROM pg_stat_user_indexes AS s
ORDER BY pg_relation_size(s.indexrelid) DESC;

需要重建索引时，可以评估 REINDEX CONCURRENTLY，但它仍会消耗 I/O、CPU 和额外磁盘空间。是否重建应基于膨胀证据和业务收益，而不是看到索引很大就定期执行。

连接和内存参数的边界

数据库参数调优不能脱离并发量和查询形态。把某个测试环境中的参数值复制到生产，可能让单条查询变快，却在高并发时耗尽内存。

连接不是越多越好

每个 PostgreSQL 后端连接都是独立进程，并持有会话和事务状态。max_connections 设置很高会增加内存、调度和锁管理压力，也会让数据库在流量突增时同时接收过多工作。

应用应使用有上限的连接池，并让池大小与实例容量和数据库吞吐匹配。大量短连接或服务实例很多时，可以评估 PgBouncer。使用事务池模式前要确认应用是否依赖临时表、会话级 SET、会话锁或其他跨事务状态。

shared_buffers 是 PostgreSQL 自身缓存

shared_buffers 控制 PostgreSQL 共享缓冲区规模。设置过小可能增加缓存淘汰，设置过大也不是免费收益，因为操作系统仍需要文件缓存，检查点和脏页管理也会受到影响。

调整时应观察工作集、缓存命中、真实块读取、检查点行为和操作系统内存，而不是使用固定百分比作为不可变规则。

work_mem 是每个执行操作的基础上限

work_mem 用于排序、哈希表等操作在写入临时文件前可以使用的基础内存。它不是“每个连接只分配一次”，一条复杂查询可能同时或依次出现多个排序和哈希节点，并行执行还会引入多个 worker。

因此，简单使用：

work_mem x 最大连接数

仍可能低估最坏情况内存。更稳妥的流程是从 pg_stat_statements 的临时块、执行计划中的 Sort Method 和并发报表数量出发，只对受控任务使用会话级或事务级调整，并限制并发。

effective_cache_size 不会分配缓存

effective_cache_size 是规划器对单个查询可利用缓存规模的估算，其中可以包含 PostgreSQL 共享缓冲区和操作系统文件缓存。它不会预留或分配这部分内存，只会影响索引扫描与顺序扫描的成本判断。

把它设置得很高不会让数据自动进入缓存；错误估算反而可能让规划器偏向不合适的访问路径。

maintenance_work_mem 服务维护操作

maintenance_work_mem 主要用于 VACUUM、CREATE INDEX 等维护操作。提高它可能加快单次维护，但 autovacuum worker、并行维护和人工任务可能同时运行，仍需计算总内存峰值。

任何参数调整都应记录：修改前的执行计划和资源指标、参数作用范围、预计并发、回滚值以及观察周期。参数不能替代正确的 SQL、索引和数据模型。

PostgreSQL 18 带来了什么

PostgreSQL 18 的性能能力值得利用，但升级版本本身不是完整优化方案。下面几项与本文案例最相关。

异步 I/O 扩大存储并行能力

PostgreSQL 18 引入异步 I/O 子系统，可改善顺序扫描、Bitmap Heap Scan、Vacuum 等操作的 I/O 行为。具体收益取决于操作系统、存储设备、io_method、缓存状态和工作负载。

异步 I/O 能降低部分等待成本，却不会减少查询必须读取的数据量。一个缺少过滤条件、每次扫描数亿行的接口，即使底层读取更高效，仍然可能抢占大量 I/O 和缓存。执行计划中的扫描行数与块读取依然是第一判断依据。

B-tree skip scan 让部分联合索引更灵活

PostgreSQL 18 可以在合适的多列 B-tree 上使用 skip scan，通过为缺少等值条件的前导列生成重复查找来缩小扫描范围。当前导列不同值很少、后续列条件选择性较高时，它可能避免完整扫描索引。

skip scan 是否出现完全由成本估算决定。它不能证明一个为其他查询设计的联合索引已经足够，也不应该成为忽略核心查询字段顺序的理由。上线前仍要使用真实数据执行 EXPLAIN (ANALYZE, BUFFERS)。

uuidv7() 提供时间有序 UUID

PostgreSQL 18 可以直接生成 UUIDv7：

SELECT uuidv7();

还可以从 UUIDv7 提取其时间信息：

SELECT
  public_id,
  uuid_extract_timestamp(public_id)
FROM orders
LIMIT 10;

这也意味着 UUIDv7 会暴露大致生成时间，不应把它理解为完全随机、完全隐藏时间信息的标识符。需要严格保密创建时间时，应单独评估对外 ID 方案。

虚拟生成列成为默认类型

PostgreSQL 18 的生成列默认是虚拟生成列：读取时计算，不占用普通列存储。也可以显式写出 VIRTUAL：

ALTER TABLE users
ADD COLUMN email_domain text
GENERATED ALWAYS AS (
  split_part(lower(email), '@', 2)
) VIRTUAL;

虚拟生成列可以集中稳定的派生规则，提高查询可读性，但计算仍发生在读取阶段，不应仅为了“更快”而添加。生成表达式只能使用不可变表达式，并且虚拟生成列对用户自定义函数和类型还有额外限制。若主要目标是加速固定表达式过滤，表达式索引可能更直接；若需要避免重复计算并接受写入与存储成本，则评估 stored generated column。

PostgreSQL 18 提供了更多可选路径，但优化顺序没有改变：先测量，再解释计划，最后选择最小且可验证的改动。

上线前后的验证清单

数据库优化的完成条件不是“SQL 已经修改”，而是在相同业务语义下，用可比较的数据证明收益，并确认没有把成本转移到写入、内存或其他查询。

修改前

  • 保存实际 SQL、参数类型和具有代表性的参数值。
  • 记录调用频率、平均延迟、p95/p99 和累计数据库时间。
  • 保存 EXPLAIN (ANALYZE, BUFFERS)，重点记录实际行数、循环次数和块读取。
  • 记录测试数据量、数据分布、缓存状态和 PostgreSQL 配置。
  • 确认是否存在锁等待、连接池排队或长事务。
  • 记录目标表与索引大小、死元组、Vacuum 和 Analyze 状态。
  • 明确业务正确性要求，例如排序稳定性、数据实时性和分页语义。
  • 设计变更的锁级别、执行窗口、超时、取消条件和回滚方案。

修改后

  • 使用相同参数和数据规模重新获取执行计划。
  • 对比处理行数、块读取、临时文件、排序方式和执行时间，而不只比较一次最低耗时。
  • 检查新增索引的大小、实际使用次数和写入延迟。
  • 对 INSERT、UPDATE、DELETE 和 Vacuum 负载进行回归测试。
  • 检查执行计划是否只对部分参数改善，却让其他参数明显退化。
  • 验证物化视图的数据延迟、刷新耗时和失败恢复。
  • 验证分区裁剪、分区创建和历史数据清理流程。
  • 在真实并发下观察连接数、内存、CPU、I/O、WAL 和锁等待。
  • 保留足够观察窗口，再决定是否删除旧索引或扩大变更范围。

常见症状与优先检查方向

症状优先证据常见方向
估算行数与实际行数差距巨大执行计划、pg_stats、Analyze 时间更新统计信息，提高列统计目标，评估扩展统计
扫描大量行后只返回少量结果Rows Removed by Filter、块读取改写条件，设计匹配查询的索引
深分页越来越慢OFFSET 大小、扫描行数稳定排序键和 Keyset Pagination
排序或哈希写临时文件temp blocks、Sort Method减少输入行数，匹配排序索引，谨慎调整 work_mem
Index Only Scan 仍大量回表Heap Fetches、可见性与更新频率检查 Vacuum，不要过度依赖覆盖索引
查询时间主要在等待wait_event_type、阻塞链缩短事务，统一锁顺序，设置合理超时
表和索引持续增长死元组、更新量、对象大小调整 autovacuum，检查长事务和索引写放大
重复报表聚合消耗很高调用频率、扫描块数、刷新容忍度物化视图或增量汇总表
历史数据删除成本很高删除批次、WAL、Vacuum、保留规则按生命周期设计分区

这张表只用于确定调查方向，不是自动处方。同一个症状可能同时受到数据分布、并发、存储和应用访问模式影响，最终仍要回到执行计划和业务约束。

结论

PostgreSQL 18 性能优化可以归纳为四个层次：

  1. 测量：通过链路追踪、慢查询日志、pg_stat_statements、等待事件和执行计划确定真正的瓶颈。
  2. 减少工作量：减少无关行、无关列、重复往返、重复聚合和不必要排序。
  3. 选择合适访问路径：让索引、表结构、分区和预计算服务于明确的查询模式。
  4. 保持系统可维护：控制连接和内存，处理死元组、长事务、统计信息和数据生命周期。

索引不是越多越好，分区不是大表的默认答案，物化视图也不是实时查询的替代品。每种优化都在读取性能、写入成本、空间、数据时效和运维复杂度之间做交换。

PostgreSQL 18 的异步 I/O、B-tree skip scan、uuidv7() 和虚拟生成列提供了更多工具，但可靠的优化方法没有变化：先用证据解释问题，再做最小改动，最后用相同口径验证。

参考资料