specsfy-specialist-postgres

PostgreSQL

适用场景

• 用于设计 schema、编写或审查 SQL、选择索引、分析执行计划（EXPLAIN）、诊断锁/死锁、规划 migration，或确定 Postgres 备份/恢复方案。
• 当 ORM（Eloquent、Prisma、Drizzle）生成的 SQL 效率低下，且根本原因是建模或索引，而非 ORM API 时，也应使用。
• 不用于 Supabase 专属决策（RLS、Auth、Realtime、Edge Functions）——应使用 $specsfy-specialist-supabase，它在底层使用 Postgres，并提供这些额外层。
• 当入口是 Eloquent 时，与 $specsfy-specialist-laravel 结合使用；当瓶颈不仅涉及数据库，还涉及应用、网络或缓存时，与 $specsfy-specialist-performance-engineering 结合使用。

流程

1. 提出建议前，先了解 Postgres 版本、已安装的扩展、当前数据量、预期增长、工作负载（OLTP、分析型、混合型），以及数据所有者。
2. 根据使用场景，使用精确类型、NOT NULL、CHECK、UNIQUE、外键和适当的规范化来建模不变量。
3. 编写能表达规则的最简单查询，并使用有代表性的数据，通过 EXPLAIN (ANALYZE, BUFFERS) 测量实际执行计划；绝不要在空表或开发环境中测量。
4. 根据观察到的工作负载（WHERE、ORDER BY、JOIN 谓词）选择索引——绝不能仅仅因为“会查询这一列”就创建索引。
5. 根据实际负载，分析隔离级别、事务时长、锁获取顺序和预期并发情况。
6. 规划 migration，确保部署期间应用的新旧版本兼容，并提供可测试的回滚路径。
7. 将执行标记为 [MIGRATION]，指出已版本控制的文件，在测试数据库中应用，并在完成前检查 migration 工具的历史记录。
8. 在目标环境中验证备份、恢复、监控和容量，再宣布变更已准备好投入生产。

规范

• 对所有必须始终成立的不变量，优先使用数据库约束（NOT NULL、CHECK、UNIQUE、FK、EXCLUDE）——只在应用中验证，可能导致其他写入路径产生不一致数据。
• 避免在生产代码中使用 SELECT *、不精确类型（用 text 表示封闭枚举、用 float 表示金额），以及与现有索引具有相同前缀、造成重复的索引。
• 添加索引前，务必查看写入模式、表大小、谓词选择性，以及添加前后的执行计划——索引选择不当会拖慢写入，却无法加快读取。
• 保持事务简短（避免在事务中执行外部 I/O、等待用户响应或调用网络），并确保所有代码路径的锁获取顺序一致，以避免死锁。
• 仅在实际执行查询安全的环境中运行 EXPLAIN (ANALYZE, BUFFERS)（不要在生产环境运行，除非使用 ROLLBACK/副本）。
• 对不兼容的 schema 变更或高数据量表采用 expand/contract：绝不要在同一步中重命名/删除生产环境正在读取的列并新增列。
• 授予最低必要权限，并分离 migration（DDL）、应用（DML）和只读（仅 SELECT）角色——应用绝不能使用拥有 DROP TABLE 权限的角色连接。

反模式

• 因为“该列出现在 WHERE 中”就创建索引，而忽略选择性 — 对于低基数列（布尔值、取值很少的状态列），规划器通常更倾向于顺序扫描，而索引只会增加写入开销。
• 在旧版 Postgres（< 11）中使用 ALTER TABLE ... ADD COLUMN ... NOT NULL DEFAULT，会在锁定期间重写整张表 — 当前版本会针对常量 DEFAULT 进行优化，但使用易变函数的 DEFAULT 仍会触发重写。
• 长事务在等待网络响应或用户确认期间一直持有锁 — 这会阻止 autovacuum 清理死元组，并增加膨胀。
• 在不断增长的表中使用较大的 OFFSET 分页 — 开销会随 offset 线性增长；优先使用基于 keyset 的分页（WHERE id > :cursor ORDER BY id LIMIT :n）。
• 自动备份从未恢复过 — “我们有备份”，但没有经过完整恢复测试，只是未经验证的假设，并非保证。

验证

• 测试完整性（违反约束时会产生预期错误）、并发（两个 writer 同时操作不会破坏不变量）以及热路径上的关键查询。
• 使用符合实际情况的基数比较变更前后的 EXPLAIN (ANALYZE, BUFFERS)，不要使用开发环境中的空表。
• 在安排部署窗口前，根据生产环境中的实际表大小，估算任何 DDL 的锁定情况和重写耗时。
• 通过文件、应用命令以及迁移工具的历史查询核对每项 [MIGRATION] 任务，并确认每项记录的退出码均为零。
• 定期使用真实备份验证恢复，并记录恢复过程耗时（RTO）— 未经恢复测试的备份不能保证可恢复。
• 未比较执行计划时，不要声称变更“不会影响性能”；未通过上述约束和并发测试时，不要声称 schema“完整”。

相关 Skills

• $specsfy-specialist-data-modeling 用于在考虑 Postgres 特定细节之前，定义实体、关系和生命周期。
• $specsfy-specialist-application-security 用于定义威胁、授权和隔离，并通过约束、角色和 RLS 在数据库中落实。
• 当 Postgres 由 Supabase 管理时，使用 $specsfy-specialist-supabase（RLS、Auth、Realtime、特定的连接池）。
• 当入口为 Eloquent，且修复需要体现在 migration/model 中时，使用 $specsfy-specialist-laravel。
• 当瓶颈无法仅通过索引/执行计划解决（网络、缓存、应用）时，使用 $specsfy-specialist-performance-engineering。
• 使用 $specsfy-specialist-observability 获取生产环境数据库指标和告警（连接、锁、复制、延迟）。
• 当部分查询状态位于数据库外的缓存中时，使用 $specsfy-specialist-redis — Postgres 仍然是真实数据源，Redis 永远无法取代它。
• 使用 $specsfy-specialist-docker 将 Postgres 服务打包到容器中并进行运维（官方镜像、数据卷、健康检查）— schema、索引和执行计划的决策仍由此处负责。

请阅读 references/standards.md，了解类型、索引、并发、安全、migrations、运维和官方来源。