 

这些年跑过几个千万级、亿级数据量的生产系统后，我意识到一件事：**大部分 PostgreSQL 性能问题，加 CPU 和内存是解决不了的。** 真正管用的，是理解 PostgreSQL 的“思考方式”——它怎么做查询规划、怎么利用索引、怎么处理并发。

下面这些经验，是在多次凌晨三点的故障排查中反复验证过的。如果一开始有人能告诉我这些，很多坑根本不用踩。

## 1. `SELECT *` 是默认要戒掉的坏习惯

看着无害，其实代价很大：

```sql
-- 坏习惯
SELECT * FROM orders WHERE user_id = 123;

-- 更好
SELECT id, amount, created_at FROM orders WHERE user_id = 123;
```

每多取一列，PostgreSQL 就要多读一页数据、多传一些字节到网络。更重要的是，`SELECT *` 常常让 **Index Only Scan** 无法生效——这是查询规划器里最快的访问路径之一。字段越少，越有可能只扫索引不碰表。

## 2. 别猜，看 `EXPLAIN`

我见过太多人凭直觉调优查询。正确做法是：

```sql
EXPLAIN (ANALYZE, BUFFERS) SELECT ...
```

然后认真看输出：有没有 Seq Scan？Nested Loop 的驱动表是不是太小？`Rows Removed by Filter` 是不是很大？执行计划会直接告诉你时间花在哪了——问题是很多人不看，或者看了也当没看见。

## 3. 索引不是越多越好

一个常见误解是“索引越多，查询越快”。实际上，**每个索引在写入时都需要维护**。一个从未被查询使用的索引，纯粹是写操作的额外负担，只增加开销而不产生收益。

加索引之前，先问一个问题：**这个索引到底有没有查询会用？** 可以用 `pg_stat_user_indexes` 查看现有索引的命中情况。

## 4. 联合索引的列顺序不是随便排的

如果查询条件是：

```sql
WHERE org_id = ? AND status = ?
```

索引应该建在 `(org_id, status)`，而不是 `(status, org_id)`——除非你的查询主要只按 `status` 过滤。**最左列决定了索引是否可用**，这个细节踩坑率极高。

## 5. 部分索引被严重低估

不用索引所有行，只索引真正需要查询的那些：

```sql
CREATE INDEX idx_pending_orders ON orders(created_at)
WHERE status = 'pending';
```

如果只有 5% 的订单是 `pending`，索引大小就只有原来的 5%——维护成本更低，扫描速度更快。

## 6. 覆盖索引：直接从索引拿数据，不碰表

```sql
CREATE INDEX idx_user_email ON users(email) INCLUDE (name);
```

当查询只需要 `email` 和 `name` 时，PostgreSQL 可以直接从索引返回结果，完全不需要访问表数据。

## 7. 用 Keyset 分页，别用 `OFFSET`

`OFFSET 100000 LIMIT 20` 会让 PostgreSQL 扫描并丢弃前 10 万行。更好的做法是：

```sql
WHERE id > :last_seen_id ORDER BY id LIMIT 20
```

第一页和第五万页的查询成本基本一样。

## 8. 随机 UUID 会拖垮写入性能

UUIDv4 的写入位置在索引中完全随机，会导致索引页频繁分裂、缓存命中率下降。如果用 UUID，优先考虑 **UUIDv7、ULID 或 KSUID**——它们保持时间顺序，写入集中在索引“热端”，对写入性能更友好。

## 9. JSONB 是工具，不是 schema 的替代品

JSONB 适合存元数据、用户偏好、功能开关这类真正的无模式数据。但如果你的查询主要围绕 JSONB 字段做过滤，建议加上合适的 GIN 索引，并做好基准测试——因为 JSONB 的查询性能经常会出乎意料。

## 10. 重视 VACUUM

MVCC 意味着每次更新和删除都会留下“死元组”。不清理，索引会膨胀、表会变大、查询会越来越慢。autovacuum 会自动处理大部分场景，但需要关注它是否跟得上写入节奏——尤其是在大规模批量操作之后，可能需要手动触发 `VACUUM`。

## 11. 连接池是性价比最高的基础设施

PostgreSQL 每个连接都是一个进程，建立和维护开销不小。用 PgBouncer 或应用层的连接池（如 `pgxpool`）将连接数控制在合理范围内，能省下大量资源。

## 12. 批量插入，别逐条插入

1 万条单独的 `INSERT` 意味着 1 万次网络往返。合并成多行插入：

```sql
INSERT INTO events (a, b) VALUES (...), (...), (...);
```

差别往往是一个数量级。

## 13. `COPY` 是批量导入之王

当数据量达到百万级时，`COPY` 的优势非常明显——几分钟能完成普通 `INSERT` 几个小时才能处理完的工作。

## 14. 分区表 

表大到一定程度后，按月或按租户分区会带来明显收益：维护更方便，删除旧数据只需 `DROP TABLE`，查询只扫描相关分区。不要过早使用，但也不必畏惧。

## 15. 顺序扫描有时就是最优解

看到 Seq Scan 不用恐慌。如果 PostgreSQL 预期要读取表的大部分数据，顺序扫描比逐行走索引更快。规划器通常知道自己在做什么——除非基准测试证明它错了，否则先信任它。

## 16. 别让 N+1 问题悄悄积累

10 次独立执行的快查询，往往输给 1 个写得好的联合查询——**网络往返的开销本身就可能成为瓶颈**。`JOIN`、CTE 和窗口函数就是为了避免在应用代码里循环查询而设计的。

## 17. 用窗口函数替代应用层逻辑

排名、去重、累计求和、比较相邻行——这些在应用层做既慢又费代码：

```sql
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC)
```

PostgreSQL 在数据存储的位置完成这些计算，通常比应用层循环快得多。

## 18. CTE 物化行为会影响性能

在旧版本中，每个 CTE 都会被物化——完整计算、存储、再读取。现代版本（12+）通常会将它们内联到主查询中，并且可以用 `MATERIALIZED / NOT MATERIALIZED` 控制行为。这个细节可能让同一个查询的 performance 天差地别。

## 19. 过期的统计信息会生成糟糕的执行计划

当 PostgreSQL 选择了奇怪的执行计划时，问题往往不在于查询本身，而在于统计信息已经过时。数据量发生大的变化后，执行一下 `ANALYZE` 往往就能恢复正常。

## 20. 开启 `pg_stat_statements`

看不见的瓶颈没法优化。这个扩展会记录每条查询的总耗时、调用次数和平均延迟。真正消耗数据库资源的查询，往往不是你以为的那几条。

## 21. `FOR UPDATE SKIP LOCKED` 是任务队列的利器

用 PostgreSQL 做任务队列时：

```sql
SELECT * FROM jobs WHERE status = 'pending'
ORDER BY created_at
FOR UPDATE SKIP LOCKED LIMIT 10;
```

多个 worker 可以并行拉取任务，不会互相阻塞，也不会抢到同一行。

## 22. 选择正确的数据类型

- `timestamptz` 优先于 `timestamp` —— 否则时区问题迟早会找上门
- `text` 优先于 `varchar(n)` —— 长度限制在 PostgreSQL 里弊大于利
- 计数类字段用 `bigint` —— `serial` 溢出那天会很不好过
- 金额用 `numeric`，**不要用 float**

## 23. 提前设置超时，别等出问题再补

这三个配置帮我避免了很多次生产事故：

```sql
SET statement_timeout = '30s';
SET lock_timeout = '5s';
SET idle_in_transaction_session_timeout = '60s';
```

## 24. `LATERAL` JOIN 解决“每组 Top N”问题

每个客户的最新订单、每篇文章的前三条评论——这类问题用 `LATERAL` 处理非常方便：

```sql
SELECT c.name, o.*
FROM customers c
CROSS JOIN LATERAL (
  SELECT * FROM orders
  WHERE customer_id = c.id
  ORDER BY created_at DESC
  LIMIT 3
) o;
```

## 25. 先测量，再改，改完再测

直觉会骗人，基准测试不会。每次优化都应该遵循相同的流程：`EXPLAIN ANALYZE` 记录当前状态，改动一处，重新测量。**没有测量过的优化，只能算“碰运气”。**

---

## 我最想告诉新人的一条原则

“PostgreSQL 调优最重要的是什么？”

不是索引，不是分区，也不是硬件。**是会读执行计划，并且理解 PostgreSQL 为什么这么选。**

当执行计划不再像天书的时候，数据库调优就不再有“魔法”的感觉，而是一步步可验证的工程实践。

这些年跑过几个千万级、亿级数据量的生产系统后，我意识到一件事：大部分 PostgreSQL 性能问题，加 CPU 和内存是解决不了的。 真正管用的，是理解 PostgreSQL 的“思考方式”——它怎么做查询规划、怎么利用索引、怎么处理并发。

优化 PostgreSQL 的 25 条实战经验：从能跑到跑的稳

数据库

后端

职业发展

PostgreSQL性能调优实战：从SELECT*戒除到执行计划分析，覆盖索引设计、Keyset分页、JSONB应用、VACUUM维护、批量导入等25个核心技巧。通过真实生产环境验证，帮助开发者理解查询规划器决策逻辑，避免常见性能陷阱，实现可验证的数据库优化。

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

优化 PostgreSQL 的 25 条实战经验：从能跑到跑的稳-腾讯云开发者社区-腾讯云

优化 PostgreSQL 的 25 条实战经验：从能跑到跑的稳

优化 PostgreSQL 的 25 条实战经验：从能跑到跑的稳

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐