生产模式概述

在真实的后端项目中，PostgreSQL 的性能问题很少来自某一条慢查询，而是应用连接方式、查询方式以及 Schema 随时间演进三者共同作用的结果。本课涵盖四种直接影响端到端性能的生产模式：使用 PgBouncer 进行连接池、针对大数据集的分页策略、使用 sqitch 或 Flyway 等工具的 Schema 迁移工作流，以及一套从查询入手、深入到数据库配置的系统化性能调优方法。

目标是让系统从"在我机器上能跑"变成在实际负载下行为可预测。

使用 PgBouncer 进行连接池

每一条到 PostgreSQL 的连接都会 fork 一个后端进程，这会消耗内存和 CPU。当后端收到成百上千的并发请求时，连接数会很快耗尽。PgBouncer 位于应用和 PostgreSQL 之间，将大量的应用连接复用到一小撮真实的数据库连接上。

最常见的生产模式是事务级连接池（transaction pooling），即连接仅在一个事务执行期间分配给客户端：

# /etc/pgbouncer/pgbouncer.ini
[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

在你的应用中，将数据库主机指向6432 端口而不是 5432端口。事务级连接池有一个重要的注意事项：由于底层连接可能在事务之间发生变化，预编译语句（prepared statements）、`SET` 命令以及像 `LISTEN/NOTIFY` 这类 session 级特性都无法可靠工作。只有在 session 池模式下才可以使用服务端的预编译语句。

分页策略

使用 `LIMIT ... OFFSET ...` 的 OFFSET 分页虽然使用方便，但在大型表上性能较差，因为 PostgreSQL 仍然需要扫描并丢弃所有被跳过的行。在一个拥有数百万行的表上，第 1000 页明显比第 1 页慢。

Keyset 分页（也称为游标分页）通过使用索引列上最后看到的值，完全避免使用 offset：

-- Bad: offset pagination
SELECT id, created_at, title
FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 10000;

-- Good: keyset pagination
SELECT id, created_at, title
FROM posts
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;

第二个查询使用 `(created_at, id)` 上的复合索引，可以直接跳转到正确位置。即使在翻页之间插入了新行，它也能返回稳定的结果，这对于信息流和无限滚动至关重要。

Schema 迁移（Schema Migrations）

在生产环境中，schema 变更必须是可重复、可审查、可回滚的。Flyway、Liquibase 或 sqitch 等工具将迁移存储为按顺序运行的版本化 SQL 文件，并通过一张跟踪表记录已应用的迁移。

一个典型的迁移文件如下所示：

-- V002__add_user_email_index.sql
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

三条规则最为重要。第一，对现有表始终使用 `CREATE INDEX CONCURRENTLY`，避免在创建索引期间阻塞写入。第二，永远不要在同一次迁移中混合破坏性变更（删除列）与新功能，以便能够回滚。第三，使用生产数据的副本测试迁移，因为 PostgreSQL 的规划器统计信息可能暴露出小型开发数据库永远不会显示的问题。

端到端性能调优

调优遵循分层方法。首先，使用 `pg_stat_statements` 或应用层日志识别慢查询。其次，运行 `EXPLAIN (ANALYZE, BUFFERS)` 查看实际执行计划、行数以及缓冲命中与磁盘读取的对比。第三，修复根本原因，通常是缺少索引、错误的连接顺序，或是查询获取了超出需要的数据。

只有在查询层面清理干净之后，才应该调整数据库参数。常见的优化包括将 `shared_buffers` 提升到 RAM 的约 25%，将 `effective_cache_size` 设置为约 75%，并在出现大排序或哈希溢出到磁盘时按查询调优 `work_mem`。但请记住：配置无法拯救一条在每次请求时都全表扫描的查询。

-- Finding the top 10 slowest queries by total execution time
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

总结

生产环境的 PostgreSQL 性能是一个系统级属性，而非单一旋钮。通过 PgBouncer 进行连接池化以应对并发而不耗尽数据库资源；在大型表上将 offset 分页替换为 keyset 分页；通过版本化迁移工具管理 schema 变更；在调整服务器配置之前遵循查询优先的调优工作流。这些模式共同为你带来可预测的延迟，并在负载增加时提供清晰的优化路径。