Back

现代 Web 应用的 Postgres 最佳实践

OpenReplay Team OpenReplay Team

Apr 6, 2026 · 2 min read

现代 Web 应用的 Postgres 最佳实践

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

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

核心要点

  • 对需要过滤、排序或关联的字段使用带约束的类型化列,而将 JSONB 保留给不断演变或非结构化的数据。
  • 有针对性地选择索引——B-tree 用于等值和范围查询,GIN 用于 JSONB 和全文搜索,部分索引用于范围过滤。
  • 使用像 PgBouncer 这样的连接池工具(事务模式)来防止连接耗尽,特别是在无服务器环境中。
  • 使模式迁移具有增量性和非锁定性,并在生产环境运行之前始终验证迁移工具生成的内容。
  • 启用 pg_stat_statements 以在慢查询成为生产事故之前识别它们。

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

良好的 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 字段提升为独立列。专用列允许您添加 NOT NULL 约束、外键和标准 B-tree 索引,与查询嵌套 JSONB 路径相比,所有这些都能提高数据完整性和查询性能。

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

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

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

Gain control over your UX

See how users are using your site as if you were sitting next to them, learn and iterate faster with OpenReplay. — the open-source session replay tool for developers. Self-host it in minutes, and have complete control over your customer data. Check our GitHub repo and join the thousands of developers in our community.

OpenReplay

More articles from OpenReplay Blog