ia-postgresql

核心用法

本技能卡为PostgreSQL数据库工程提供全方位指导，覆盖从基础schema设计到高级查询优化的完整生命周期。

Schema设计层面：强制推荐现代数据类型——BIGINT GENERATED ALWAYS AS IDENTITY替代遗留的SERIAL，TIMESTAMPTZ处理时区敏感时间戳，JSONB替代无索引能力的JSON，gen_random_uuid()(PG13+)替代uuid-ossp。每张外键列必须显式建索引（PostgreSQL不会自动创建），所有布尔列强制NOT NULL DEFAULT，杜绝三值逻辑隐患。

迁移安全策略：采用"扩展-迁移-收缩"零停机模式，禁止生产环境直接重命名或删除列。危险操作清单包括：NOT NULL无默认值会锁表重写、CREATE INDEX非并发版本阻塞写入、ALTER TYPE大表原地修改。CREATE INDEX CONCURRENTLY必须独立事务执行；大数据回填需用FOR UPDATE SKIP LOCKED分批处理，避免全表锁。

索引工程：B-tree处理等值/范围查询，GIN优化JSONB containment与全文检索，GiST支持几何与范围数据，BRIN适配时序大数据。复合索引遵循"高选择性列优先"原则（≤3-4列），部分索引（WHERE status='active'）与覆盖索引（INCLUDE）减少I/O，表达式索引支持函数式查询。写密集表设置fillfactor=70-90预留HOT更新空间。

JSONB深度操作：GIN索引加速@> containment查询，jsonb_path_ops操作类可将索引体积缩减2-3倍。关键区分：-操作符删除键或数组元素，#-按路径递归删除；jsonb_set(path, 'null')设置JSON null而非删除，误用jsonb_set(path, NULL)会因STRICT语义导致整列覆灭为SQL NULL。

行级安全(RLS)：强制FORCE ROW LEVEL SECURITY使表所有者受策略约束。策略表达式按行求值，须将函数调用包装为标量子查询(SELECT func())实现单例缓存。务必为策略引用列建索引。

向量搜索(pgvector)：支持HNSW（高召回、高内存）与IVFFlat（低内存、大数据集）两种索引。关键规则：过滤条件必须在向量搜索之前执行，利用CTE预过滤或部分索引。距离运算符包括<=>余弦相似、<->欧氏距离、<#>内积。

并发与事务：长事务阻塞VACUUM导致表膨胀，应控制事务粒度。SELECT ... FOR UPDATE不阻塞幻读插入，需用 advisory lock 或INSERT ON CONFLICT处理get-or-create竞争。嵌套BEGIN实际为SAVEPOINT，错误会中止外层事务；需独立提交时应使用顶层事务。

显著优点

• 工程实践权威性：内容源自生产环境血泪教训（如JSONB delete操作符的微妙行为、NULLS NOT DISTINCT的语义陷阱），非教科书式罗列
• 零停机迁移框架：Expand-Contract模式与危险操作清单，为S级可用性系统提供可执行路径
• 性能优化深度：从EXPLAIN (ANALYZE, BUFFERS)解读到fillfactor调优、HOT更新机制，覆盖PostgreSQL特有优化点
• 现代特性覆盖：PG15+的NULLS NOT DISTINCT、PG13+的gen_random_uuid()、pgvector向量检索等前沿能力
• 防御性编程导向：大量"常见错误"与"检测查询"段落，预判工程师易踩陷阱

潜在缺点与局限性

• 版本碎片化：部分特性（NULLS NOT DISTINCT需PG15+，gen_random_uuid()需PG13+）在旧版PostgreSQL不可用，文档未显式标注最低版本矩阵
• 扩展依赖：pgvector、pg_stat_statements等扩展需额外安装运维，云托管PostgreSQL可能受限或版本滞后
• 工作负载假设：BRIN索引推荐依赖"自然有序"数据假设，乱序写入场景性能崩塌；HNSW索引内存消耗对成本敏感型部署构成挑战
• ORM兼容性：Prepared statement与PgBouncer transaction模式的交互警告、嵌套事务SAVEPOINT行为，对抽象层较厚的ORM用户调试困难
• 缺少定量阈值：如"大表"定义（100M行）、"慢查询"阈值（100ms）虽有提及，但未建立与硬件规格关联的性能基线

适合人群

• 数据库工程师/SRE：负责PostgreSQL集群schema演进、索引调优、扩容分片的技术负责人
• 后端服务开发者：编写高并发事务逻辑、处理JSONB半结构化数据、实现多租户RLS策略的应用工程师
• 数据平台工程师：构建pgvector向量检索功能、设计物化视图刷新策略的AI/搜索基础设施团队
• 技术架构师：评估PostgreSQL适用场景、制定零停机迁移方案、选型连接池部署模式的决策者

常规风险

| 风险类别 | 具体表现 | 缓解措施 |

|---------|---------|---------|

| 迁移事故 | `NOT NULL`无默认值导致锁表重写、`ALTER TYPE`大表阻塞 | 严格遵循Expand-Contract，危险操作强制Code Review checklist |

| 索引失效 | `LIKE '%suffix'`前模糊查询、函数包装列导致SeqScan | `EXPLAIN`验证执行计划，必要时建表达式索引 |

| 并发竞争 | `FOR UPDATE`不防幻读、读改写丢失更新 | 关键路径改用advisory lock或`INSERT ON CONFLICT` |

| JSONB数据损坏 | `-`操作符误删子树、`jsonb_set(NULL)`整列覆灭 | 迁移前单条验证，复杂操作先用`SELECT` dry-run |

| 连接池故障 | PgBouncer statement模式破坏advisory lock/session特性 | 按功能特性选择模式，prepared statement使用unnamed协议 |

| RLS性能崩塌 | 策略函数每行求值、未索引策略列 | 标量子查询缓存+强制索引策略 |

| pgvector召回不足 | HNSW/IVFFlat参数不当、过滤后置导致结果集截断 | 预过滤+距离阈值调优+recall测试 |

建议建立pg_stat_statements与pg_stat_user_tables监控基线，结合检测查询定期巡检慢查询、表膨胀、未使用索引。

核心用途

ia-postgresql 是一份面向生产环境的 PostgreSQL 数据库设计与运维专业指南，涵盖 Schema 设计、查询优化、索引策略、并发控制、行级安全（RLS）、零停机迁移、分区、全文搜索及向量检索（pgvector）等核心主题。

显著优点

1. 生产级安全实践：强制使用 GENERATED ALWAYS AS IDENTITY 替代 SERIAL，TIMESTAMPTZ 替代 TIMESTAMP，JSONB 替代 JSON，从源头规避常见数据陷阱。

2. 零停机迁移策略：详细阐述 expand-contract 模式、CREATE INDEX CONCURRENTLY、批量回填 FOR UPDATE SKIP LOCKED 等操作，确保大表schema变更不锁表。

3. 深度索引优化：覆盖 B-tree、GIN、GiST、BRIN 选型场景，提供复合索引、部分索引、覆盖索引、表达式索引的实战规则，以及未索引外键检测脚本。

4. 并发与一致性：涵盖 RLS 性能优化（标量子查询缓存）、事务边界陷阱（嵌套 BEGIN 实为 SAVEPOINT）、幻影插入防护（partial unique index + ON CONFLICT）、以及 compare-and-swap 模式。

5. 现代PostgreSQL特性：NULLS NOT DISTINCT（PG15+）、gen_random_uuid()（PG13+）、HNSW/IVFFlat 向量索引、物化视图并发刷新等。

潜在局限

• 版本依赖：部分建议（如 NULLS NOT DISTINCT、gen_random_uuid()）需 PostgreSQL 13+ 或 15+，旧版本需回退方案。
• T3来源：个人开发者维护，虽内容专业但无企业背书，生产环境建议交叉验证。
• 无交互式验证：纯文档型 skill，不自动检查用户 SQL 语法或执行计划，需手动运行 EXPLAIN (ANALYZE, BUFFERS)。
• 场景聚焦：专注 PostgreSQL 特有功能，如需 MySQL/MariaDB 兼容方案需额外参考。

适合人群

• 后端开发工程师设计高并发数据库Schema
• DBA 执行大表迁移与索引优化
• 架构师评估分区、RLS、pgvector 等高级特性
• 运维人员排查锁竞争、死锁、表膨胀问题

常规风险

• 迁移误操作风险：未遵循 "先 nullable 再 backfill 再加约束" 流程可能导致大表锁死。
• RLS 性能陷阱：策略表达式未优化可能引发全表扫描。
• JSONB 误用：过度使用 JSONB 代替结构化列会降低查询性能与类型安全。
• read-modify-write 竞争：批量迁移时未用原子更新或 compare-and-swap 可能丢失并发写入。