PostgreSQL 总览

PostgreSQL 不是“开源版 Oracle”，也不是“换了语法的 MySQL”。它的核心价值在于标准 SQL、复杂查询、强事务、丰富类型、扩展能力，以及和 MySQL 很不一样的 MVCC/VACUUM/WAL 机制。

学习 PostgreSQL 要抓住一条主线：

业务建模 -> SQL 与类型 -> B-tree/GIN/BRIN 索引 -> Planner 执行计划 -> heap tuple 多版本 -> VACUUM 清理 -> WAL 恢复复制 -> 锁和长事务排查。

你应该按什么顺序学

mermaid

flowchart TD
    A["1. SQL、Schema、Role"] --> B["2. 表设计和约束"]
    B --> C["3. B-tree、GIN、BRIN 索引"]
    C --> D["4. EXPLAIN ANALYZE"]
    D --> E["5. MVCC: xmin/xmax"]
    E --> F["6. VACUUM 和膨胀"]
    F --> G["7. WAL、复制、备份恢复"]
    G --> H["8. 锁、长事务、生产排查"]
    H --> I["9. 面试标准回答"]

不要把 PostgreSQL 只当 CRUD 数据库。它的很多生产问题都来自 MVCC 版本、VACUUM、统计信息和复杂计划。

专栏学习地图

页面	学什么	学完要会什么
基础 SQL	`schema`、`bigserial`、`jsonb`、数组、`upsert`、`returning`	能写 PostgreSQL 常见业务 SQL
从零到生产级掌握	PostgreSQL 完整主线	能把 SQL、索引、MVCC、VACUUM、WAL 和排查串起来
商业场景训练营	医疗资产、抢任务、JSONB、长事务、BRIN、复制	能把原理跑成项目能力
架构与存储	database、schema、role、page、tuple、WAL	能解释 PostgreSQL 数据怎么存
核心全过程原理	Parser、Planner、Executor、MVCC、WAL、VACUUM	能讲一条 SQL 和 UPDATE 怎么执行
索引与优化器	B-tree、GIN、GiST、BRIN、统计信息、计划	能判断为什么 Seq Scan/Index Scan/Bitmap Scan
事务与锁	隔离级别、写写冲突、死锁、锁等待	能排查阻塞和长事务
MVCC、VACUUM 与 WAL	tuple 版本、快照、dead tuple、WAL 恢复	能解释表膨胀、Index Only Scan、复制恢复
备份与高可用	`pg_dump`、基础备份、WAL、PITR、流复制	能理解备份恢复边界
性能排查	`EXPLAIN ANALYZE BUFFERS`、锁、膨胀、VACUUM	能按证据排查慢 SQL
PostgreSQL 面试	标准回答和追问	能短答面试题，并跳回原理页

PostgreSQL 和 MySQL 最核心区别

维度	PostgreSQL	MySQL InnoDB
旧版本位置	heap 中保留多个 tuple 版本	undo log 版本链
清理机制	VACUUM 清 dead tuple	purge 清 undo
执行计划验证	`EXPLAIN ANALYZE BUFFERS` 看真实执行	`EXPLAIN` + 慢日志 + optimizer trace
覆盖索引理解	Index Only Scan 依赖 Visibility Map	覆盖索引列齐即可减少回表
JSON 能力	`jsonb` + GIN 很常用	JSON 支持存在，但模式不同
运维重点	VACUUM、膨胀、长事务、WAL	Buffer Pool、redo/binlog、主从延迟

如果你用 MySQL 思维套 PostgreSQL，最容易踩这几个坑：

以为更新会原地覆盖，不知道会产生新 tuple。
以为有索引就能完全不访问表，不知道可见性判断可能回 heap。
忽略长事务，导致 VACUUM 清不了 dead tuple。
用一个超大 JSONB 字段承载所有业务字段，导致约束、统计、索引都变差。
不看 actual rows 和 buffers，只凭感觉调索引。

最小商业 Demo：医疗资产表

sql

create schema if not exists biz;

create table biz.medical_asset (
  id bigserial primary key,
  asset_no varchar(64) not null,
  hospital_id bigint not null,
  department_id bigint,
  status varchar(20) not null,
  asset_name varchar(128) not null,
  attrs jsonb not null default '{}',
  created_at timestamp not null default now(),
  updated_at timestamp not null default now(),
  constraint uk_medical_asset_no unique (asset_no),
  constraint ck_medical_asset_status
    check (status in ('IDLE', 'USED', 'REPAIR', 'SCRAPPED'))
);

create index idx_asset_hospital_status_created
on biz.medical_asset(hospital_id, status, created_at desc);

create index idx_asset_attrs_gin
on biz.medical_asset using gin(attrs);

查询计划：

sql

explain (analyze, buffers)
select id, asset_no, asset_name, status
from biz.medical_asset
where hospital_id = 1001
  and status = 'USED'
order by created_at desc
limit 20;

JSONB 查询：

sql

explain (analyze, buffers)
select id, asset_no
from biz.medical_asset
where attrs @> '{"deviceType":"CT"}';

状态更新：

sql

begin;

update biz.medical_asset
set status = 'USED',
    updated_at = now()
where asset_no = 'A001'
  and status = 'IDLE';

commit;

这个 Demo 背后的原理：

unique 和 check 是数据库最后防线。
B-tree 联合索引支撑列表查询。
GIN 索引支撑 JSONB 包含查询。
update 会生成新 tuple，旧 tuple 之后由 VACUUM 清理。
EXPLAIN (ANALYZE, BUFFERS) 能看到真实行数和页访问。

PostgreSQL 最容易被问的原理

问题	你应该跳到哪里
PostgreSQL 为什么需要 VACUUM	MVCC、VACUUM 与 WAL
`xmin`、`xmax` 是什么	MVCC、VACUUM 与 WAL
Index Only Scan 为什么还可能访问 heap	索引与优化器
WAL 做什么	MVCC、VACUUM 与 WAL
`EXPLAIN ANALYZE BUFFERS` 看什么	性能排查
`jsonb` 为什么用 GIN	索引与优化器
长事务为什么危险	事务与锁
表膨胀怎么排查	性能排查

商业项目中 PostgreSQL 怎么用

适合：

场景	为什么适合
复杂 SQL 和报表	窗口函数、CTE、复杂 Join 能力强
GIS	PostGIS 生态成熟
半结构化属性	`jsonb` + GIN 适合灵活字段查询
数据治理平台	类型、约束、扩展能力强
审计和查询平台	SQL 表达能力强

不建议：

把 PostgreSQL 当 Redis 缓存。
把无限日志写入都塞进普通表，不做分区和归档。
所有字段都放 JSONB，放弃关系约束。
长事务导出大报表，阻止 VACUUM。
不维护统计信息和 autovacuum，只靠加索引解决慢。

学完 PostgreSQL 专栏的验收标准

你要能讲清：

database、schema、role、table 是什么关系。
heap page 和 tuple 是什么。
更新为什么生成新 tuple，而不是简单覆盖。
xmin、xmax 和 snapshot 怎么判断可见性。
VACUUM 清什么，为什么长事务会阻止清理。
WAL 为什么能支持崩溃恢复、复制和 PITR。
B-tree、GIN、BRIN 分别适合什么查询。
EXPLAIN ANALYZE BUFFERS 中估算行数、实际行数、Buffers 怎么看。
Index Only Scan 为什么还依赖 Visibility Map。
表膨胀、锁等待、从库延迟、WAL 暴涨怎么排查。

面试标准回答

text

PostgreSQL 是功能完整、SQL 标准和扩展能力很强的开源关系型数据库，适合复杂查询、JSONB、GIS、报表和强约束业务。它和 MySQL 最大区别之一是 MVCC 实现：PostgreSQL 更新通常生成新 tuple，旧 tuple 通过 xmin/xmax 和快照判断可见性，之后由 VACUUM 清理；MySQL InnoDB 更多依赖 undo log 版本链。PostgreSQL 使用 WAL 支持崩溃恢复、流复制和时间点恢复。生产排查要重点看 EXPLAIN ANALYZE BUFFERS、统计信息、长事务、锁、dead tuple、VACUUM、表膨胀和 WAL。

JDK 8/11/17/21 LTS演进

线程池

PostgreSQL 总览 ​

你应该按什么顺序学 ​

专栏学习地图 ​

PostgreSQL 和 MySQL 最核心区别 ​

最小商业 Demo：医疗资产表 ​

PostgreSQL 最容易被问的原理 ​

商业项目中 PostgreSQL 怎么用 ​

学完 PostgreSQL 专栏的验收标准 ​

面试标准回答 ​