核心用法
ia-postgresql 是一个面向 PostgreSQL 数据库全生命周期的综合性技能指南，覆盖从 schema 设计、查询优化到运维管理的各个方面。
Schema 设计与数据类型：
- 推荐使用
BIGINT GENERATED ALWAYS AS IDENTITY作为主键，避免SERIAL的序列所有权问题 - 强制使用
TIMESTAMPTZ存储时间戳，杜绝时区丢失风险 - 优先使用
TEXT而非VARCHAR(n)，除非有明确长度约束需求 - JSON 数据必须使用
JSONB以支持索引和高效查询 - 财务数据使用
NUMERIC(precision, scale)，绝对避免MONEY和FLOAT
索引策略：
- 外键列必须手动创建索引（PostgreSQL 不会自动创建）
- 复合索引：最多 3-4 列，选择性最高的列放最前
- 部分索引：使用
WHERE条件缩小索引范围 - 覆盖索引：使用
INCLUDE避免回表查询 - 表达式索引：支持函数式查询条件
- 写入密集型表设置
fillfactor = 70-90预留 HOT 更新空间
迁移安全（零停机核心）：
- 采用 Expand-Contract 模式：先扩展新结构→迁移读取→最后收缩旧结构
- 危险操作：
NOT NULL无默认值会锁表重写所有行CREATE INDEX不加CONCURRENTLY会阻塞写入- 大数据回填必须使用
FOR UPDATE SKIP LOCKED分批处理 - 生产环境只允许前向迁移，回滚通过新的前向迁移实现
查询优化：
- 强制使用
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)分析执行计划 - 使用
pg_stat_statements识别慢查询 - 优先使用
EXISTS而非IN处理关联子查询 - 游标分页（
WHERE id > $last）替代OFFSET避免大表性能灾难 - 大表估算行数使用
pg_class.reltuples替代count(*)
高级特性：
- Row-Level Security (RLS)：策略表达式按行求值，需将函数调用包装在标量子查询中以启用缓存
- 分区：超过 1 亿行或需 TTL 清理时使用，分区键必须包含在所有唯一约束中
- pgvector 向量搜索：推荐 HNSW 索引作为默认选择，必须在向量搜索前进行过滤
显著优点
1. 生产级可靠性：零停机迁移模式、并发索引创建、事务安全的数据回填等机制，确保大型应用的无缝演进
2. 性能导向设计：从 fillfactor 调优到覆盖索引、从 BRIN 到 HNSW，提供针对不同场景的精细优化方案
3. 安全与合规：RLS 强制策略、最小权限原则（REVOKE ALL ON SCHEMA public）、可审计的数据访问控制
4. 反模式主动防御：明确的禁用清单（如 SELECT *、OFFSET 分页、ORDER BY RANDOM()）配合检测查询，预防常见问题
5. 现代 PostgreSQL 特性：涵盖 PG13+ 的 gen_random_uuid()、PG15 的 NULLS NOT DISTINCT 等最新功能
潜在缺点与局限性
1. 学习曲线陡峭：大量高级特性（GIN/GiST/BRIN 索引选择、RLS 策略优化、分区键设计）需要深入理解 PostgreSQL 内部机制
2. 部分建议版本依赖：如 NULLS NOT DISTINCT 需 PG15+，gen_random_uuid() 需 PG13+，旧版本需替代方案
3. 迁移复杂度：Expand-Contract 模式虽安全，但显著增加迁移文件数量和部署复杂度
4. 工具链假设：提及 pg_stat_statements、PgBouncer 等扩展和工具，未涵盖基础安装配置
5. 向量搜索深度有限：pgvector 部分仅提供基础索引创建，未涉及召回率调优、量化压缩等进阶主题
适合人群
- 数据库架构师：设计高可用、可扩展的 PostgreSQL schema
- 后端工程师：编写高性能、可维护的 SQL 查询和迁移脚本
- DBA/运维工程师：监控、调优和维护生产 PostgreSQL 实例
- 技术负责人：制定团队数据库开发规范和安全策略
常规风险
| 风险类别 | 具体描述 | 缓解建议 |
|---------|---------|---------|
| 迁移事故 | `ALTER TABLE` 大表或创建非并发索引导致长时间锁表 | 严格使用 `CREATE INDEX CONCURRENTLY`，大表变更分多步执行 |
| RLS 性能灾难 | 策略函数每行求值导致查询性能崩溃 | 将函数包装在标量子查询中，确保策略列有索引 |
| 索引膨胀 | 高写入负载下索引膨胀未及时处理 | 监控 `pg_stat_user_indexes`，适时执行 `REINDEX` |
| 连接耗尽 | 未使用连接池直接连接数据库 | 强制使用 PgBouncer，`transaction` 模式为默认 |
| 数据类型陷阱 | `TIMESTAMP` 丢失时区、`FLOAT` 精度损失 | 代码审查强制类型白名单 |
| 死锁 | 事务顺序不一致导致应用死锁 | 统一表访问顺序，使用 `pg_advisory_xact_lock` 协调 |