PostgreSQL 最佳实践：OpenAI 黄金知识库，揭秘大规模数据库管理与性能优化策略

postgresql-best-practices by ahmed6ww/ax-agents

1 周安装量

1 GitHub Stars

GitHub

安装命令

npx skills add https://github.com/ahmed6ww/ax-agents --skill postgresql-best-practices

数据库开发运维性能优化

🇨🇳中文介绍

PostgreSQL 最佳实践（OpenAI 黄金知识库）

规范来源

首先阅读 {baseDir}/references/openai-scaling-postgresql.md。
来源 URL：https://openai.com/index/scaling-postgresql/
发布日期：January 22, 2026

不可协商的规则

保留来源中所有命名的技术、数字和日期，确保与原文完全一致。
完全保留章节顺序和意图：
- Use Postgres for everything
- Partitioning and indexing strategy
- Query optimization
- Schema management

广告位招租

在这里展示您的产品或服务

触达数万 AI 开发者，精准高效

联系我们

必须涵盖的内容

在总结或操作化时，请涵盖以下所有来源事实：

规模基线和增长指标：
- 每天超过 4 billion queries
- 超过 2,000 个数据库实例
- 从 2022 年的一个 Postgres 实例开始增长
- 超过 1 billion 月活跃用户和超过 800 million 周活跃用户
- 在一年多的时间里流量增长了约 2.5x
平台策略：
- 保持 PostgreSQL 作为核心数据存储
- 使用 Citus 和 Citus columnar 处理分布式以及分析/高吞吐量用例
分区/索引决策：
- 对大型事件/日志表使用哈希分区
- 对仅追加数据集使用时间范围分区
- 复合索引策略
- 自定义索引迁移工具以避免阻塞写入
- 瓶颈：表膨胀、自动清理效率低下、索引选择性差
- 峰值驱动因素：全表扫描、巨大的 B 树索引、热行导致的写入放大
查询优化操作：
- 查询指纹和执行统计信息
- 锁争用和长事务识别
- 重写昂贵的连接/子查询
- 语句级超时和重试策略
- 查询预算警报和基于 EXPLAIN 的审查工作流
- 报告的影响：数据库负载减少 43%，p95 延迟减少 38%
模式管理工作流：
- 扩展 -> 迁移 -> 收缩
- 带有节流和检查点的异步回填
- 采用渐进式推出的金丝雀迁移
- 迁移健康检查失败时自动回滚
- 开源工具：Haste 和 pgroll
复制和负载均衡：
- 区域感知的只读副本
- 副本延迟监控和查询分流
- 专用的分析副本
- 使用 Patroni 和自定义路由逻辑进行故障转移编排
- 深度集成 PgBouncer 和 PgAnalyze
结果和未来方向：
- 在指数级增长下保持低延迟
- 在不牺牲正常运行时间的情况下实现高迁移速度
- 用精简的基础设施团队运营数千个实例
- 持续迭代和开源贡献，包括 haste、pgroll、pgai

生成完整答案时，请严格按照以下顺序使用这些确切的标题：

Use Postgres for everything
Partitioning and indexing strategy
Query optimization
Schema management
Replication and load balancing
Results

当要求注明出处时，请始终包含来源 URL。

🇺🇸English

PostgreSQL Best Practices (OpenAI Golden Knowledgebase)

Canonical Source

Read {baseDir}/references/openai-scaling-postgresql.md first.
Source URL: https://openai.com/index/scaling-postgresql/
Published: January 22, 2026

Non-Negotiable Rules

Preserve all named technologies, numbers, and dates exactly as written in the source.
Preserve section order and intent exactly:
- Use Postgres for everything
- Partitioning and indexing strategy
- Query optimization
- Schema management
- Replication and load balancing
- Results
Keep technology and project names unchanged: Citus, Citus columnar, PgBouncer, PgAnalyze, Patroni, Haste, pgroll, pgai.
If a detail is not in the source file, explicitly state: Not stated in the source.
Do not add outside recommendations, interpretations, or alternative patterns that are not present in the source.

Required Coverage

When summarizing or operationalizing, cover all of the following source facts:

Scale baseline and growth metrics:
- Over 4 billion queries every day
- Over 2,000 database instances
- Growth from one Postgres instance in 2022
- Over 1 billion monthly users and over 800 million weekly users
- Traffic growth of about 2.5x in just over a year
Platform strategy:
- Keep PostgreSQL as the core datastore
- Use Citus and Citus columnar for distributed and analytical/high-throughput use cases
Partitioning/indexing decisions:

Output Format

When producing a complete answer, use these exact headings in this order:

Use Postgres for everything
Partitioning and indexing strategy
Query optimization
Schema management
Replication and load balancing
Results

Always include the source URL when attribution is requested.

Weekly Installs

Repository

ahmed6ww/ax-agents

GitHub Stars

First Seen

1 day ago

Security Audits

Gen Agent Trust HubPass SocketFail SnykPass

Installed on

zencoder1

amp1

cline1

openclaw1

opencode1

cursor1

Azure 升级评估与自动化工具 - 轻松迁移 Functions 计划、托管层级和 SKU

79,900 周安装

Hash partitioning for large event/log tables

Time-range partitioning for append-only datasets

Composite index strategy

Custom index migration tooling to avoid blocking writes

Bottlenecks: table bloat, autovacuum inefficiencies, poor index selectivity

Spike drivers: full table scans, huge B-tree indexes, write amplification from hot rows

Query optimization operations:

Query fingerprints and execution stats
Lock contention and long-running transaction identification
Rewriting expensive joins/subqueries
Statement-level timeout and retry strategy
Query budget alerts and EXPLAIN-based review workflows
Reported impact: 43% DB load reduction, 38% p95 latency reduction

Schema management workflows:

Expand -> migrate -> contract
Async backfills with throttling and checkpointing
Canary migrations with progressive rollout
Automatic rollback on failing migration health checks
Open-sourced tools: Haste and pgroll

Replication and load balancing:

Region-aware read replicas
Replica lag monitoring and query shedding
Dedicated analytical replicas
Failover orchestration with Patroni and custom routing logic
Deep integration of PgBouncer and PgAnalyze

Results and forward direction:

Low latency under exponential growth
High migration velocity without sacrificing uptime
Operating thousands of instances with lean infrastructure teams
Ongoing iteration and open-source contributions including haste, pgroll, pgai