现代 Web 应用的 Postgres 最佳实践

大多数开发者选择 PostgreSQL 是因为它免费、可靠且久经考验。但选择它只是简单的部分。真正的挑战在于如何用好它——设计能够应对不断变化需求的模式(schema),编写随着数据增长仍能保持快速的查询,以及以不会悄悄拖垮应用性能的方式管理连接。

本文涵盖了在生产环境中最重要的 PostgreSQL Web 应用最佳实践。

模式设计:从结构化开始,保持灵活性

良好的 PostgreSQL 模式设计始于清晰地区分已知重要的内容和可能在未来重要的内容。

对于需要过滤、排序或关联的字段——用户 ID、时间戳、状态标志、外键——使用带约束的适当类型化列。对于其他所有内容,JSONB 列可以为您提供文档存储的灵活性,而不会放弃关系完整性。

CREATE TABLE users (
  id         BIGSERIAL PRIMARY KEY,
  email      TEXT UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now(),
  metadata   JSONB DEFAULT '{}'::jsonb
);

这种混合方法是最实用的现代 PostgreSQL 特性之一。您可以在重要的地方获得模式强制执行,在需求仍在演变的地方获得灵活性。当 JSONB 字段开始经常出现在 WHERE 子句中时,将其提升为真正的列并添加约束。这种迁移很简单,值得去做。

需要避免的一点是:从一开始就将所有内容存储在 JSONB 中。在您需要强制执行 NOT NULL 约束或添加外键之前,它感觉很灵活——然后您就需要重写一半的模式了。

真正有帮助的 PostgreSQL 索引策略

索引是大多数 PostgreSQL 性能技巧汇聚的地方。默认设置适合入门,但生产工作负载需要深思熟虑的选择。

B-tree 索引处理大多数等值和范围查询。在最新的 PostgreSQL 版本(18+)中,规划器可以在多列 B-tree 索引上执行跳跃扫描,这意味着列顺序不像以前那么重要——但以选择性最强的列作为开头仍然是更安全的默认选择。

GIN 索引是 JSONB 列和全文搜索的正确选择:

CREATE INDEX idx_user_metadata ON users USING GIN (metadata);

这使得像 WHERE metadata @> '{"plan": "premium"}'::jsonb 这样的查询即使在大表上也很快。

部分索引使用不足,但通常是过滤活跃或最近记录的最佳工具:

CREATE INDEX idx_active_users ON users (email) WHERE deleted_at IS NULL;

避免为每一列都建索引。每个索引都会增加写入开销并消耗内存。只为查询实际过滤的内容建立索引,使用 EXPLAIN ANALYZE 进行验证,并删除未使用的索引。

Web 工作负载的连接管理

PostgreSQL 的连接模型是有状态的,建立连接的成本相对较高。每个连接都会在服务器上生成一个专用进程。在规模化时,来自 Web 应用的数百个短期连接会明显降低性能。

解决方案是在应用和数据库之间放置一个连接池。PgBouncer 是标准选择。在事务模式下,它通过少量真实数据库连接复用许多应用连接。

这在无服务器环境(Lambda、Vercel、Cloudflare Workers)中尤其重要,其中每次函数调用都可能尝试打开新连接。如果没有连接池,在中等流量下您就会达到 PostgreSQL 的 max_connections 限制。有关详细信息,请参阅 PostgreSQL 文档中的连接设置。

生产环境中的安全迁移

模式变更是最容易出问题的地方。最安全的模式是首先进行增量变更:添加带默认值的新列,回填数据,然后在后续部署中删除旧列。

避免在负载下锁定表的 ALTER TABLE 操作。例如,在较旧的 PostgreSQL 版本中,添加没有默认值的 NOT NULL 列会重写整个表。在 PostgreSQL 11+ 中,添加带非易失性默认值的列不再需要表重写——但在生产环境运行之前,仍然值得检查迁移工具实际生成的内容。

在可能的情况下,始终在事务内运行迁移,并在部署前测试回滚路径。

查询性能:首先找到慢查询

不要猜测性能问题。启用 pg_stat_statements 来跟踪工作负载中的查询执行统计信息:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

然后查询它以找到最慢或调用最多的查询:

SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

一旦有了目标,使用 EXPLAIN (ANALYZE, BUFFERS) 来了解规划器在做什么以及索引是否有帮助。

结论

PostgreSQL 会奖励那些从一开始就认真对待模式设计、索引和连接管理的开发者。这些不是高级主题——它们是在生产环境中运行 Web 应用而不出意外的基准线。从重要的地方开始使用类型化列和约束,仅在真正需要灵活性的地方添加 JSONB,并基于实际查询模式而非假设来建立索引。将其与连接池和严格的迁移策略相结合,您将避免困扰生产应用的最常见陷阱。

常见问题

何时应该将 JSONB 字段提升为专用列?

当您经常对其进行过滤、排序或关联时,应将 JSONB 字段提升为独立列。专用列允许您添加 NOT NULL 约束、外键和标准 B-tree 索引,与查询嵌套 JSONB 路径相比,所有这些都能提高数据完整性和查询性能。

如何在 PgBouncer 事务模式和会话模式之间选择?

事务模式是大多数 Web 应用的正确默认选择,因为它在每个事务后将连接返回到池中,从而最大化吞吐量。仅当您的应用依赖会话级特性(如咨询锁、LISTEN/NOTIFY、会话配置更改或必须在多个事务中持久化的临时表)时,才使用会话模式。

在生产环境中向大表添加新列是否安全?

在 PostgreSQL 11 及更高版本中,添加带非易失性默认值的列是仅元数据操作,不会重写表。但是,在较旧版本上添加没有默认值的 NOT NULL 列会锁定并重写整个表。在对生产环境运行之前,始终检查迁移工具生成的 SQL。

典型的 PostgreSQL 表应该有多少个索引?

没有固定数字。仅为查询实际过滤、排序或关联的列建立索引。每个额外的索引都会增加写入开销和内存使用。使用 pg_stat_user_indexes 识别未使用的索引并删除它们。从最少开始,使用 EXPLAIN ANALYZE 监控,并根据观察到的查询模式添加索引。