PostgreSQL — 高级关系型数据库系统

Workflow — 使用流程

遇到 PostgreSQL 需求时，按以下顺序决策:

1. 明确需求类型
   ├── DDL (建表/改表)        → 见 SQL 语法速查
   ├── DML (查询/插入/更新)    → 见 SQL 语法速查
   ├── 函数/数据处理            → 见 函数速查
   ├── 查询性能优化             → 见 references/06-index-types.md + examples/03-performance-tuning.md
   └── 高可用/备份/复制         → 见 references/08-replication-backup.md + examples/04-streaming-replication.md

2. 确定模型: 关系型 → 标准表+B-Tree | JSON文档 → JSONB+GIN | 全文搜索 → tsvector+GIN | 地理 → PostGIS+GiST

3. 索引策略: 等值→B-Tree | 范围→B-Tree | 全文→GIN | JSON→GIN | 向量→IVFFlat/HNSW | 大表时序→BRIN

4. 数据量评估: <100GB→单实例 | 100GB-1TB→分区 | 1TB-10TB→分区+只读副本 | >10TB→Citus/逻辑复制

5. 运维策略: autovacuum + pg_stat_statements + WAL归档 + PgBouncer

When to Use (and When NOT to)

核心原则：PostgreSQL 是全能型关系型数据库，但不是所有场景的最佳选择。

Boundary — 能力边界

SQL 语法速查

深度 SQL 内容见 references/ 各文件，此处为索引。

• DDL: CREATE TABLE (含分区、继承), ALTER TABLE, 数据类型 (JSONB/TSVECTOR/CITEXT/数组等), 约束 (CHECK/EXCLUDE/UNIQUE/FOREIGN KEY)
• DML: INSERT ... ON CONFLICT (UPSERT), UPDATE ... FROM, DELETE ... USING, TRUNCATE, RETURNING 子句
• CTE: 公用表表达式 (WITH), 递归 CTE (WITH RECURSIVE) — 见 examples/02-cte-recursive.md
• 连接: INNER/LEFT/RIGHT/FULL/CROSS JOIN, LATERAL 子查询
• 事务: READ COMMITTED (默认), REPEATABLE READ, SERIALIZABLE, SAVEPOINT, FOR UPDATE/SHARE/NOWAIT/SKIP LOCKED, 咨询锁

函数速查

深度内容见 references/:

高级特性索引

Gotchas — 常见陷阱与反模式

FAQ

Q1: PostgreSQL vs MySQL 主要区别？ PostgreSQL: 完全 ACID, JSONB 可索引, 6 种索引类型, 递归 CTE, 流复制+逻辑复制, 丰富 EXTENSION。MySQL: Web 应用为主, 简单查询, InnoDB 事务, 间隙锁并发控制。

Q2: JSONB vs JSON？ 始终选 JSONB。二进制格式, 支持 GIN 索引, 查询更快。JSON 仅在你需要保留空格和键顺序时使用。

Q3: UUID 为什么不适合做主键？ UUID v4 随机值导致 B-Tree 页频繁分裂, 比 BIGSERIAL 慢 2-3 倍。方案: UUID v7 (时间排序), BIGSERIAL, 或 ULID/Snowflake。

Q4: 如何在线迁移 PostgreSQL？ 逻辑复制 (PG 10+, 推荐) > pglogical 扩展 > pg_dump+pg_restore (需停机)。逻辑复制支持跨大版本、选择性复制。

Q5: work_mem 怎么设？ 每个排序操作分配, 最大内存 = work_mem × (连接数 × 并发排序数)。64GB 机器建议 64-128MB。监控 temp_files 指标, 有磁盘排序则调大。

Q6: pg_repack vs VACUUM FULL？ VACUUM FULL 锁表 (ACCESS EXCLUSIVE)。pg_repack 不锁写, 适合在线环境, 优先选择。

Q7: 死锁怎么处理？ PG 自动检测并回滚一个事务。预防: 保持锁顺序一致、缩短事务、用 NOWAIT/SKIP LOCKED, 监控 pg_stat_database.deadlocks。

Q8: 如何选择分区键？ 条件: 查询频繁出现 (分区裁剪)、数据均匀分布、稳定不变。常见: 时间 (RANGE)、地区 (LIST)、ID 哈希 (HASH)。分区数建议 10-200。

Q9: 连接数设多少？ 每个连接 5-10MB, 一般 200-500 够用。超过 500 必须用 PgBouncer。(max_connections × work_mem × 0.5) + shared_buffers + 系统开销 < 内存 80%。

Q10: 何时用 SERIALIZABLE？ 金融转账、库存扣减、强一致性报表。注意: 失败率随冲突上升, 应用层需重试逻辑。

Q11: 查询没走索引的原因？ 统计信息过旧→ANALYZE | 类型不匹配→隐式转换 | 选择性低→规划器认为全表更优 | 函数包裹索引列→避免 WHERE DATE(col) = 写法。

Q12: 怎么判断要不要分区？ 表 > 100GB | 存在明显按时间/地区查询模式 | 旧数据定期归档 | VACUUM 跟不上更新。不满足则分区复杂度 > 收益。

Q13: 如何大版本升级？ pg_upgrade 最推荐: pg_upgrade -b old_bin -B new_bin -d old_data -D new_data, --link 模式最快。升级后执行 ANALYZE。

Q14: 逻辑复制 vs 流复制？ 流复制: 物理块级, 全库, 大版本必须一致, 用于 HA。逻辑复制: SQL 变更, 选表/行, 跨大版本, 用于数据同步/迁移。

Q15: VACUUM 后表大小没变小？ VACUUM (不带 FULL) 只标记空间可重用, 不还给 OS。真正缩小需 VACUUM FULL 或 pg_repack。

Keywords

postgresql, postgres, psql, SQL, DDL, DML, ACID, MVCC, 事务, 索引, B-Tree, GIN, GiST, BRIN, JSONB, hstore, 数组, 全文搜索, tsvector, tsquery, 窗口函数, CTE, 递归CTE, LATERAL, PL/pgSQL, 存储过程, 触发器, 物化视图, 分区表, PostGIS, pgvector, pg_stat_statements, pgcrypto, citext, pg_trgm, FDW, postgres_fdw, EXPLAIN, VACUUM, autovacuum, pg_dump, pg_restore, pg_basebackup, WAL归档, PITR, 流复制, 逻辑复制, PUBLICATION, SUBSCRIPTION, Patroni, repmgr, PgBouncer, RLS, 行级安全, 性能优化, 备份恢复, 高可用, 死锁, 连接池

References

• PostgreSQL 官方文档
• PostgreSQL 中文文档
• PostgreSQL 性能调优 — pgtune
• Patroni 文档
• PgBouncer 官方文档
• pgvector 文档
• PostGIS 文档

内部参考

• references/01-functions-string.md — 字符串/正则函数详解
• references/02-functions-datetime.md — 日期/时间函数详解
• references/03-functions-aggregate-window.md — 聚合/窗口函数详解
• references/04-functions-jsonb.md — JSONB 函数与操作详解
• references/05-fulltext-search.md — 全文搜索详解
• references/06-index-types.md — 索引类型与视图详解
• references/07-partition-fdw.md — 分区表与 FDW 详解
• references/08-replication-backup.md — 复制/备份/权限详解
• examples/01-jsonb-query.md — JSONB 查询示例
• examples/02-cte-recursive.md — 递归 CTE 示例
• examples/03-performance-tuning.md — 性能调优示例
• examples/04-streaming-replication.md — 流复制搭建示例