PostgreSQL 技能综合评估
核心用法
本技能是一套 PostgreSQL 深度实践指南，覆盖数据库全生命周期管理：
1. Schema 设计规范
- 数据类型推荐：使用
BIGINT GENERATED ALWAYS AS IDENTITY替代SERIAL，TIMESTAMPTZ存储时区，JSONB替代JSON，NUMERIC处理货币 - 索引策略：B-tree 用于等值/范围查询，GIN 用于 JSONB 和全文检索，GiST 用于几何数据，BRIN 用于大表时序数据
- 复合索引设计原则：高选择性列在前，最多 3-4 列
2. 高级特性应用
- JSONB 优化：
jsonb_path_ops索引类可将索引体积缩减 2-3 倍 - 行级安全（RLS）：通过
SET app.current_user_id实现无扩展的会话上下文，强制策略对表主人生效 - 分区策略：RANGE 分区处理时序数据（>1亿行时启用），LIST 按租户/地区分片
- 向量搜索：pgvector 扩展支持 HNSW（高召回）和 IVFFlat（低内存）两种索引算法
3. 零停机迁移（Expand-Contract 模式）
三步完成字段重命名/删除：扩展期双写 → 迁移期切读 → 收缩期删旧。禁止单步迁移导致代码与 schema 版本不一致。
4. 查询优化体系
- 诊断工具链：
EXPLAIN (ANALYZE, BUFFERS)+pg_stat_statements+pg_stat_user_tables - 分页优化：游标分页
WHERE id > $last ORDER BY id LIMIT n替代OFFSET - 近似计数：
reltuples元数据替代COUNT(*)
显著优点
- 生产级安全：详列危险操作（无 DEFAULT 的 NOT NULL 锁表、非 CONCURRENTLY 索引阻塞写入）及规避方案
- 性能深度：HOT 更新优化（fillfactor）、覆盖索引（INCLUDE）、部分索引（WHERE 过滤）完整覆盖
- 并发控制：
SKIP LOCKED实现无竞争队列，pg_advisory_xact_lock应用级互斥 - 连接池指导：PgBouncer transaction/statement 模式选型，解决 prepared statement 陷阱
潜在局限
- 版本依赖：部分特性要求 PG13+（
gen_random_uuid）、PG15+（NULLS NOT DISTINCT），需确认环境版本 - 向量扩展外部依赖：pgvector 非核心扩展，需额外安装维护
- RLS 性能陷阱：未优化的策略函数每行调用，需手动改写为标量子查询缓存
- 迁移复杂度：Expand-Contract 需要多部署周期，不适合紧急 hotfix 场景
适合人群
- 数据库工程师：负责 schema 设计、索引调优、容量规划
- 后端架构师：需要设计高并发、零停机演进的系统
- SRE/运维：关注连接池配置、监控查询、表膨胀检测
- AI 应用开发者：集成 pgvector 实现向量检索
常规风险
- 锁竞争：
CREATE INDEX忘加CONCURRENTLY导致写阻塞；长事务阻塞 autovacuum 引发表膨胀 - RLS 绕过：未启用
FORCE ROW LEVEL SECURITY时表主人可无视策略 - 连接泄露：未池化生产环境直接连接耗内存（~10MB/连接）
- JSONB 误用：频繁查询的结构化数据未提取为独立列，导致 GIN 索引效率低下
认证说明
内容源自 PostgreSQL 官方文档最佳实践及成熟生产经验总结，技术建议与官方推荐一致。