PostgreSQL 架构与核心特性

概述

PostgreSQL 是功能最丰富的关系型开源数据库，以 ACID 强一致、扩展性强（FDW/PG/PL）、MVCC 实现优雅著称，在 OLTP + OLAP 混合场景和地理信息系统中优势明显。

MySQL vs PostgreSQL 关键差异

维度	MySQL	PostgreSQL
MVCC 实现	Undo Log 版本链	元组多版本（旧版本留在堆中）
VACUUM	不需要	✅ 必须做，回收死元组
索引类型	B+Tree, 全文, 空间(R-Tree)	B-Tree, Hash, GiST, GIN, SP-GiST, BRIN
并发查询	单进程多线程	多进程（每个连接一个进程）
DDL 事务	❌（隐式提交，8.0 支持原子 DDL）	✅ 原生支持
JSON	JSON 类型 + 函数	JSONB（二进制）+ GIN 索引，查询极快
扩展性	插件有限	FDW（外部数据包装器）、自定义类型/操作符/索引
复制	主从复制	流复制 + 逻辑复制 + 级联复制
存储引擎	可插拔（InnoDB/MyISAM 等）	统一引擎（不可插拔）
连接池需求	可忽略	强烈需要（PgBouncer/Pgpool-II）

并发模型：多进程架构

         Client
           ↓
    ┌──────────────┐
    │  Postmaster   │ (主进程，fork + 管理)
    └──┬───┬───┬───┘
       ↓   ↓   ↓
   Backend  Backend  Backend   (每个连接一个进程)
       ↓       ↓       ↓
    ┌──────────────────────────┐
    │    Shared Buffer Pool    │ (共享内存)
    ├──────────────────────────┤
    │    WAL Buffer            │
    ├──────────────────────────┤
    │    Lock Table            │
    └──────────────────────────┘
              ↕
    ┌──────────────────────────┐
    │       数据文件             │
    └──────────────────────────┘

连接池是必须的： 每个 PG 后端进程约 10MB。1000 个连接 = 10GB 内存。

# 推荐 PgBouncer 生产配置
pool_mode = transaction       # 事务结束后归还连接
default_pool_size = 80        # 每个数据库/用户的连接池大小
max_client_conn = 2000        # 最大客户端连接

MVCC 实现：元组多版本（关键差异）

PG 的 MVCC 特点

MySQL MVCC：
  Undo Log 保存旧版本 → 通过回滚指针串联 → Purge 清理

PG MVCC：
  旧元组直接保留在堆中（不移动）→ 通过可见性规则判断 → VACUUM 回收

核心概念：每个元组有 4 个系统列

列名	含义
`xmin`	插入此元组的事务 ID
`xmax`	删除/更新此元组的事务 ID（0 表示未删除）
`cmin`	插入时的事务内命令 ID
`cmax`	删除时的事务内命令 ID

UPDATE 在 PG 中的实际行为

-- UPDATE users SET name='new' WHERE id=1;
-- 不是原地修改，而是：
-- 1. 新元组：INSERT 新版数据（xmin=当前 txid）
-- 2. 旧元组：标记 xmax=当前 txid（逻辑删除）
-- 3. 更新索引指向新元组
-- → 旧元组等待 VACUUM 回收

从这可以看出 PG 的 MVCC 设计哲学：不覆盖数据，版本自然堆叠。好处是实现简单、回滚几乎零成本。代价是必须靠 VACUUM 清理，且更新频繁时表膨胀严重。

VACUUM —— PG 运维核心

为什么 VACUUM 如此重要

Dead Tuples（死元组）越来越多
    → 表膨胀（磁盘/内存浪费）
    → 扫描效率下降（需要检查更多元组）
    → 事务 ID 回卷风险（xid 用尽 → 数据库 shutdown）

AUTO VACUUM 自动清理

-- 查看 AUTO VACUUM 状态
SHOW autovacuum;                  -- 强烈建议 ON（默认）
SHOW autovacuum_vacuum_scale_factor;   -- 0.2（死元组 20% 触发）
SHOW autovacuum_vacuum_threshold;      -- 50 行

-- 大表调优（降低阈值让 VACUUM 更频繁）
ALTER TABLE large_table SET (
    autovacuum_vacuum_scale_factor = 0.05,    -- 5% 触发
    autovacuum_vacuum_threshold = 10000
);

-- 查看 VACUUM 进度
SELECT * FROM pg_stat_progress_vacuum;

VACUUM vs VACUUM FULL

操作	回收空间	锁级别	是否会阻塞	建议频率
VACUUM	标记可重用，不归还磁盘	弱锁（可并发读写）	❌ 不阻塞	自动/每天
VACUUM FULL	完全重写表 → 归还磁盘	ACCESS EXCLUSIVE（排他）	✅ 阻塞所有操作	极少用，业务低峰期

大表 VACUUM FULL 是核武器——必须确认停机窗口。更安全的方式是用 pg_repack（在线重组，不锁表）。

事务 ID 回卷（XID Wraparound）

-- PG 用 32 位事务 ID，42 亿上限 → 需要循环使用
-- 风险查询：
SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
WHERE age(datfrozenxid) > 1000000000   -- 超过 10 亿 → 告警
ORDER BY xid_age DESC;

-- 手动执行 FREEZE（需停机或低负载）
VACUUM FREEZE;

-- 设置告警阈值（建议监控）
-- age > 2^31(约 20 亿) → PG 拒绝新事务 → 灾难

索引类型速查

索引	适用场景	示例
B-Tree	等值/范围查询（默认）	`WHERE id = 1`, `WHERE age BETWEEN 20 AND 30`
Hash	仅等值查询	`WHERE email = 'x@y.com'`
GiST	全文搜索/几何数据	`WHERE document @@ to_tsquery('search')`
GIN	数组/JSONB/全文索引	`WHERE tags @> ARRAY['tag1']`, `WHERE data->>'key' = 'value'`
BRIN	顺序极大的大表（如日志）	`WHERE created_at > '2026-01-01'` — 块级索引
SP-GiST	非平衡数据结构（IP/点）	`WHERE ip <<= '192.168.1.0/24'`

JSONB 索引（PG 杀手级特性）

-- GIN 索引 JSONB 列
CREATE INDEX idx_data ON orders USING GIN (data jsonb_path_ops);

-- 查询示例（走索引）
SELECT * FROM orders WHERE data @> '{"status": "paid"}';
SELECT * FROM orders WHERE data->>'user_id' = '100';

关键参数调优

# 内存
shared_buffers = 4GB               # 物理内存的 25%（PG 依赖 OS 缓存）
effective_cache_size = 12GB        # 物理内存的 75%（优化器估算用）
work_mem = 64MB                    # 单个操作的排序/Hash 内存（需基于并发量调整）
maintenance_work_mem = 1GB         # VACUUM/REINDEX 内存

# WAL
wal_level = replica                # 流复制所需级别
max_wal_size = 8GB                 # WAL 最大大小（自动 Checkpoint）
checkpoint_timeout = 15min         # Checkpoint 间隔
wal_compression = on               # WAL 压缩（IO 密集型建议开启）

# 查询计划
random_page_cost = 1.1             # SSD 环境设为 1.0-1.5（默认 4 针对 HDD）
effective_io_concurrency = 200     # SSD 并发 IO（HDD 设为 2）

常用运维命令

-- 查看连接数
SELECT count(*), state FROM pg_stat_activity GROUP BY state;

-- KILL 特定连接
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE state = 'idle in transaction' AND now() - state_change > interval '10 minutes';

-- 查看表大小
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;

-- 查看慢查询（需先安装 pg_stat_statements）
CREATE EXTENSION pg_stat_statements;
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

-- 查看死元组比例
SELECT relname,
       n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 0 ORDER BY dead_pct DESC LIMIT 10;

常见问题 / 坑点

问题	原因	解决方案
连接数打满	多进程模型 + 无连接池	部署 PgBouncer（必须）
表膨胀，查询越来越慢	VACUUM 跟不上 / 长事务阻止 VACUUM	检查长事务 + 调整 autovacuum 参数
事务 ID 回卷告警	VACUUM FREEZE 未执行	紧急执行 `VACUUM FREEZE` + 监控 age
UPDATE 性能差	MVCC 实现：每次 UPDATE = INSERT + DELETE	批量更新分批提交 + 考虑分区表
`COUNT(*)` 慢	需扫描所有元组（不像 MySQL InnoDB 有优化）	用估算 `reltuples` 或用 Redis 计数
索引膨胀	频繁 UPDATE 导致旧索引项残留	`REINDEX CONCURRENTLY`

关联知识

MySQL 事务与锁机制 — MVCC 实现对比（Undo Log vs 堆内多版本）
MySQL 索引原理与优化 — 索引类型对比（PG 更丰富）
../linux/Linux 内核调优总览 — shared_buffers 大页优化

参考资源

PG 官方文档：https://www.postgresql.org/docs/16/
PgBouncer：https://www.pgbouncer.org/
pg_repack：https://reorg.github.io/pg_repack/

学习时间

阶段	时间	备注
初次学习	2026-07-14	MVCC + VACUUM + 索引 + 连接池

状态: 📖 已掌握 下次复习日期: 2026-08-14