PostgreSQL

核心用法

PostgreSQL技能专注于数据库性能优化与架构设计，提供从索引创建到连接池配置的完整实践指南。

索引策略

• 部分索引：WHERE active = true 可减少80%索引大小，适合状态列
• 表达式索引：ON lower(email) 必须精确匹配查询，否则触发全表扫描
• 覆盖索引：INCLUDE (name, email) 实现索引唯一扫描，减少Heap Fetches
• 复合索引顺序：(a, b) 仅支持WHERE a = ?，不支持WHERE b = ?
• 外键索引：PostgreSQL不自动创建，JOIN和级联删除必需

查询模式

• SELECT FOR UPDATE SKIP LOCKED：实现无外部工具的分布式任务队列
• pg_advisory_lock(key)：应用级互斥锁，无需建表
• IS NOT DISTINCT FROM：NULL安全比较，替代冗长条件
• DISTINCT ON (x) ORDER BY x, y：每组首行查询，PG特有高效语法

连接与资源管理

• PgBouncer：>50连接必需，事务级连接池，每连接约10MB内存
• 超时配置：statement_timeout防失控查询，idle_in_transaction_session_timeout清理僵尸事务
• max_connections：默认100过低，需按RAM调优

数据类型规范

• SERIAL已弃用，改用GENERATED ALWAYS AS IDENTITY
• TIMESTAMPTZ替代无时区TIMESTAMP，内部存UTC
• 金额用NUMERIC(12,2)或整数分，避免浮点精度问题
• TEXT与VARCHAR(n)性能无差异，无需强制长度限制

维护与监控

• VACUUM ANALYZE批量插入后必需，更新统计信息
• pg_repack无损清理膨胀表，无锁回收空间
• 监控事务回卷(xid耗尽)，autovacuum需调优大表

EXPLAIN分析

• 始终使用EXPLAIN (ANALYZE, BUFFERS)获取实际执行时间与I/O
• "Heap Fetches"高说明需覆盖索引，顺序扫描>10%数据时可能更优

显著优点

• 生产级深度：涵盖从开发到运维的全生命周期，非基础教程
• 性能导向：每个建议附带量化指标(80%更小、10MB/连接、>10-20%阈值)
• PG特性充分利用：DISTINCT ON、advisory lock等独特功能
• 反模式警示：明确标注废弃实践(SERIAL、无timezone时间戳、float金额)

潜在缺点与局限性

• 版本依赖：部分特性需较新PG版本(如GENERATED ALWAYS AS IDENTITY)
• 场景特定：PgBouncer建议基于">50连接"阈值，小型项目可能过度
• 无分布式覆盖：未涉及Patroni、Citus等高可用/扩展方案
• 调参需测试：autovacuum等参数需结合实际负载验证

适合人群

• 有SQL基础的中高级后端开发者
• 负责PG运维的DBA或SRE
• 从MySQL/MSSQL迁移至PG的工程师
• 需要优化现有慢查询的维护团队

常规风险

• 索引滥用：未使用的索引拖慢写入，需定期审查pg_stat_user_indexes
• 锁竞争：长事务阻塞vacuum，建议控制在秒级
• 连接泄漏：未配置idle超时导致资源耗尽
• 统计过期：大表未ANALYZE导致执行计划错误

PostgreSQL 高效开发完全指南

核心定位与价值

本 Skill 是一份深度聚焦于 PostgreSQL 生产环境实践的权威指南，由数据库专家 Ivan G. Davila 维护，涵盖从索引设计到连接管理的 9 大关键领域。其价值在于将容易遗忘的底层机制（如 VACUUM、事务隔离级别）转化为可直接落地的代码模式。

核心用法详解

索引策略：被忽视的性能杠杆

• 部分索引：WHERE active = true 可使索引体积缩小 80%，特别适合状态列
• 覆盖索引：INCLUDE (name, email) 实现纯索引扫描，需配合 EXPLAIN 检查 "Heap Fetches"
• 复合索引顺序：(a, b) 仅对 a 的等值查询有效，对 b 单独查询无效
• 外键陷阱：PostgreSQL 不会自动为外键列建索引，JOIN 和级联删除需手动处理

高级查询模式

• Skip Locked：SELECT FOR UPDATE SKIP LOCKED 实现无外部依赖的 Job Queue
• Advisory Lock：pg_advisory_lock(key) 提供应用级互斥，无需创建锁表
• NULL 安全比较：IS NOT DISTINCT FROM 替代冗长的 NULL 处理逻辑
• PG 专属语法：DISTINCT ON (x) 实现每组首行选取，避免子查询开销

运维关键配置

• 连接池化：PgBouncer 在连接数 >50 时必需，PG 连接占用约 10MB 内存
• 超时防护：statement_timeout 防慢查询，idle_in_transaction_session_timeout 杀僵尸事务
• 内存调优：默认 100 max_connections 需根据 RAM 重新计算

显著优点

1. 生产导向：所有建议均针对真实故障场景（如 xid 回卷导致数据库停止）
2. 反模式警示：明确标注常见错误（SERIAL 已弃用、float 存储货币、TIMESTAMP 无时区）
3. 可观测性：强调 EXPLAIN (ANALYZE, BUFFERS) 的实际执行分析，而非仅靠估算
4. 版本前瞻性：推荐使用 GENERATED ALWAYS AS IDENTITY 替代传统 SERIAL

局限性与注意事项

1. PG 专用性：DISTINCT ON 等语法无法迁移至其他数据库
2. 经验门槛：部分建议（如索引顺序决策）需要理解 B-tree 结构
3. 场景依赖：全文本搜索配置需根据语言（english/simple）调整，无通用最优解

适用人群

• 已有 SQL 基础，希望突破性能瓶颈的后端开发者
• 负责 PostgreSQL 运维的 DBA 和 SRE
• 从 MySQL/SQLite 迁移至 PG 的工程师

常规风险提示

• 索引过多会拖累写入性能，需定期清理 idx_scan = 0 的僵尸索引
• 长时间事务会阻塞 VACUUM 并持有锁，应尽量控制在秒级
• SERIALIZABLE 隔离级别需配合 40001 错误重试机制，否则可能引发应用异常