PostgreSQL 从零到生产级掌握

这一页不是“PostgreSQL 简介”，而是给零基础到生产开发准备的一条完整学习线。你要把 PostgreSQL 学到能上项目，必须把 SQL、表设计、索引、执行计划、MVCC、WAL、VACUUM、事务锁、备份恢复和排查串成一条链。

先记住一句话：

PostgreSQL 是一个以 SQL 能力、事务一致性、扩展能力和多版本并发控制见长的关系型数据库。它不是 MySQL 的语法替代品，真正的差异在 MVCC、WAL、VACUUM、索引类型、执行计划和扩展生态。

学习目标

学完这一页，你应该能做到：

从零创建库、表、约束、索引，并写出常见业务查询。
解释 PostgreSQL 一条查询为什么可能走 Seq Scan、Index Scan、Bitmap Heap Scan 或 Index Only Scan。
解释更新一行为什么会产生新 tuple，为什么旧 tuple 不能马上删除。
解释 xmin、xmax、快照、VACUUM、Visibility Map 之间的关系。
解释 WAL 为什么先写日志再刷数据页，以及它如何支持崩溃恢复和复制。
使用 EXPLAIN (ANALYZE, BUFFERS) 判断慢 SQL 是索引、统计信息、Join、排序、膨胀、锁还是 IO 问题。
在商业项目中设计医疗资产、订单、事件日志这类表结构，并知道什么时候用 jsonb、GIN、分区、归档或 ES。

如果你已经理解本页主线，但还不知道能不能落到项目里，继续做：PostgreSQL 商业场景训练营。它把 jsonb、GIN、for update skip locked、Index Only Scan、长事务、VACUUM、BRIN、WAL 复制和排查串成可运行训练。

为什么要单独学 PostgreSQL

很多人学 PostgreSQL 容易犯一个错误：拿 MySQL 的经验直接套过来。语法层面确实有相似之处，但核心机制不同。

问题	MySQL InnoDB 常见思路	PostgreSQL 常见思路
MVCC 旧版本在哪里	undo log 版本链	heap 中多个 tuple 版本
更新旧行怎么处理	修改记录并写 undo	写新 tuple，旧 tuple 标记失效
旧版本谁清理	purge 清理 undo	VACUUM 清理 dead tuple
查询是否只读索引	覆盖索引可避免回表	Index Only Scan 还依赖 Visibility Map
JSON 查询	JSON 支持存在，但不是最强项	`jsonb` + GIN 很常用
全文和扩展	依赖生态或外部组件	内置全文、扩展机制强
性能排查重点	Buffer Pool、redo/binlog、锁	执行计划、统计信息、VACUUM、膨胀、WAL

所以 PostgreSQL 的学习关键不是“多背几个函数”，而是理解它为什么用 tuple 多版本、为什么必须 VACUUM、为什么执行计划受统计信息影响很大。

从零开始的学习路线

mermaid

flowchart TD
    A["安装与连接<br/>psql、连接串、角色"] --> B["SQL 基础<br/>DDL、DML、查询"]
    B --> C["表设计<br/>类型、约束、主键、唯一"]
    C --> D["索引<br/>B-tree、GIN、BRIN"]
    D --> E["执行计划<br/>EXPLAIN ANALYZE"]
    E --> F["事务与 MVCC<br/>xmin、xmax、快照"]
    F --> G["VACUUM 与膨胀<br/>dead tuple、可见性"]
    G --> H["WAL 与备份恢复<br/>崩溃恢复、复制、PITR"]
    H --> I["生产排查<br/>慢 SQL、锁、长事务、IO"]

学习顺序不能反过来。比如你还不会表设计就学 VACUUM，会觉得抽象；你还不会执行计划就讲索引，会误以为“建了索引就一定快”；你还不懂 MVCC 就学锁，会分不清普通读、当前读和更新冲突。

第一步：连接、库、Schema 和角色

PostgreSQL 里要区分几个对象：

概念	作用	类比理解
Cluster	一个 PostgreSQL 实例管理的数据集合	一套数据库服务
Database	数据库	一个业务或环境的数据容器
Schema	数据库里的命名空间	类似 Java 包名，避免表名冲突
Role/User	登录和权限主体	用户、角色都属于 role 体系
Table	表	真正存业务数据

最小操作：

sql

create database demo_app;

create user app_user with password 'ChangeMe_123';

grant connect on database demo_app to app_user;

进入库后：

sql

create schema biz;

grant usage on schema biz to app_user;

为什么 PostgreSQL 有 Schema：

一个数据库里可以按业务模块拆命名空间，例如 biz.asset、audit.asset_change_log。
可以让多个系统共用一个数据库但不混表名。
权限可以按 schema 控制。
不理解 schema，常见错误是表明明存在但 SQL 报找不到，因为 search_path 不包含对应 schema。

查看当前搜索路径：

sql

show search_path;

第二步：表设计从业务问题开始

表设计不要从“字段越多越好”开始，而要先问业务问题。

以医疗资产平台为例：

业务问题	表设计反映
每台设备要唯一识别	`asset_no` 唯一约束
资产属于某医院	`hospital_id` 必填并参与查询索引
资产状态经常过滤	`status` 单独成列
不同设备属性不固定	低频灵活字段放 `jsonb`
要按创建时间分页	`created_at` 参与联合索引
要追踪变更过程	单独建变更流水表

建表 Demo：

sql

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'))
);

为什么要用约束：

约束	保护什么	如果只靠 Java 代码会怎样
primary key	行唯一身份	数据定位困难
unique	业务唯一性	并发请求可能插入重复资产
not null	必填字段	产生语义不完整数据
check	状态值合法	脏状态进入库，后续统计异常

数据库约束不是“多余校验”。它是最后一道防线。商业系统里，接口、脚本、补偿任务、导入程序都可能写库，不能只相信某一个应用层校验。

第三步：索引不是越多越好

索引的本质是：为某些查询额外维护一份更容易定位的数据结构。它能加速读，但会拖慢写并占空间。

医疗资产常见查询：

sql

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;

合适的 B-tree 联合索引：

sql

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

为什么这个顺序合理：

hospital_id 是强过滤条件，先缩小到某医院。
status 是等值条件，可以继续缩小范围。
created_at desc 服务排序和分页。
如果反过来把 created_at 放第一，数据库很难直接按医院和状态定位。

JSONB 查询：

sql

select id, asset_no
from biz.medical_asset
where attrs @> '{"vendor": "GE"}';

GIN 索引：

sql

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

为什么不用普通 B-tree：

查询类型	合适索引
`hospital_id = ? order by created_at`	B-tree
`asset_no = ?`	unique B-tree
`attrs @> '{"vendor":"GE"}'`	GIN
超大历史表按时间近似过滤	BRIN
GIS 空间查询	GiST / SP-GiST

如果所有字段都建索引，会出现：

每次插入都要维护很多索引，写入变慢。
更新字段时索引也要更新，膨胀更明显。
索引占用缓存，挤占真正热点数据。
优化器面对过多候选计划，维护成本变高。

第四步：一条查询到底怎么执行

查询不是“看到 where 就直接找数据”。PostgreSQL 会先生成计划，再按计划执行。

mermaid

flowchart TD
    A["SQL 文本"] --> B["Parser 解析"]
    B --> C["Rewriter 重写视图和规则"]
    C --> D["Planner 生成候选计划"]
    D --> E["统计信息估算行数和成本"]
    E --> F["选择成本较低计划"]
    F --> G["Executor 执行计划树"]
    G --> H["访问索引或 Heap"]
    H --> I["检查 MVCC 可见性"]
    I --> J["排序、聚合、Limit"]
    J --> K["返回结果"]

看执行计划：

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;

你不是只看“有没有 Index Scan”，而是要按顺序看：

观察点	为什么重要
扫描节点	是 `Seq Scan`、`Index Scan`、`Bitmap Heap Scan` 还是 `Index Only Scan`
估算 rows 与实际 rows	差距大说明统计信息或数据分布有问题
`actual time`	真实耗时在哪个节点
`Buffers`	是缓存命中还是大量磁盘读取
Sort	是否发生大排序，是否需要 work_mem
Join	Nested Loop 外层行数是否过大
Heap Fetches	Index Only Scan 是否仍访问 heap

为什么统计信息很重要：

sql

analyze biz.medical_asset;

PostgreSQL 优化器不会每次都把整张表真实扫描一遍来决定计划，它依赖统计信息估算。如果统计信息过期，就可能出现“有索引但不用”“Join 顺序错误”“估算几行实际几十万行”。

第五步：MVCC 为什么这样设计

MVCC 的目标是让读写尽量不互相阻塞，同时保证每个事务看到一致的数据版本。

PostgreSQL 的更新过程：

mermaid

flowchart TD
    A["旧 tuple<br/>status=IDLE"] --> B["事务更新"]
    B --> C["旧 tuple 写入 xmax"]
    B --> D["生成新 tuple<br/>status=USED"]
    D --> E["新 tuple 写入 xmin"]
    E --> F["提交后新事务可见"]
    C --> G["旧版本等待 VACUUM 清理"]

简化版本：

text

更新前:
tuple(status=IDLE, xmin=10, xmax=空)

事务 20 更新后:
旧 tuple(status=IDLE, xmin=10, xmax=20)
新 tuple(status=USED, xmin=20, xmax=空)

查询时会根据快照判断哪个 tuple 可见：

mermaid

flowchart TD
    A["查询生成 Snapshot"] --> B["扫描 tuple"]
    B --> C["判断 xmin 创建事务是否可见"]
    C --> D["判断 xmax 删除/更新事务是否可见"]
    D --> E{"版本对当前快照可见吗"}
    E -- "是" --> F["返回该版本"]
    E -- "否" --> G["跳过"]

为什么不直接覆盖旧值：

如果直接覆盖	会破坏什么
老事务正在读旧值	一致性读被破坏
更新后回滚	找不到原值
读写互相等待	并发性能下降
复制恢复	缺少明确变更记录

这就是 PostgreSQL 必须配合 VACUUM 的原因：旧版本为了并发读写而保留，等没有事务需要时再清理。

第六步：VACUUM 不是可选项

更新和删除会产生 dead tuple。dead tuple 留在表文件中，会让扫描变慢。

mermaid

flowchart TD
    A["UPDATE / DELETE"] --> B["产生 dead tuple"]
    B --> C{"是否还有长事务需要旧版本"}
    C -- "有" --> D["不能清理"]
    C -- "没有" --> E["VACUUM 标记空间可复用"]
    E --> F["更新 Visibility Map"]
    F --> G["提升 Index Only Scan 效果"]

排查表膨胀和 dead tuple：

sql

select relname,
       n_live_tup,
       n_dead_tup,
       last_vacuum,
       last_autovacuum,
       last_analyze,
       last_autoanalyze
from pg_stat_user_tables
order by n_dead_tup desc;

排查长事务：

sql

select pid,
       usename,
       state,
       now() - xact_start as xact_age,
       query
from pg_stat_activity
where xact_start is not null
order by xact_age desc;

为什么长事务危险：

长事务可能还需要旧 tuple。
VACUUM 不敢清理这些旧版本。
dead tuple 越积越多。
查询扫描更多无效数据。
索引和表都膨胀。
autovacuum 压力越来越大。

商业系统里，最常见的长事务来源是大报表、导出任务、批处理脚本、连接池事务未关闭。

第七步：WAL、提交和崩溃恢复

PostgreSQL 不会要求每次提交都把所有数据页写到磁盘。它使用 WAL 保证恢复。

mermaid

flowchart TD
    A["事务修改 Shared Buffers"] --> B["生成 WAL 记录"]
    B --> C["提交时 WAL 按策略刷盘"]
    C --> D["返回提交成功"]
    D --> E["数据页稍后由后台刷盘"]
    E --> F["宕机后重放 WAL 恢复"]

WAL 的原则：

数据页落盘前，描述该修改的 WAL 必须先落盘。

这样设计的好处：

目标	WAL 如何满足
提交可靠	提交时确保 WAL 可恢复
写入性能	WAL 主要顺序写，比随机刷数据页便宜
崩溃恢复	重启后从 checkpoint 后重放 WAL
复制	从库接收 WAL 并重放
时间点恢复	基础备份 + WAL 归档恢复到指定时间

如果没有 WAL：

事务提交后数据页还在内存，宕机就可能丢失。
每次提交强制刷所有相关数据页，性能会很差。
从库不知道主库发生了哪些变化。
不能可靠做 PITR 时间点恢复。

第八步：事务与锁怎么理解

PostgreSQL 普通读依赖 MVCC，不会像传统锁读那样阻塞写。但写写冲突仍然需要锁。

常见现象：

场景	结果
普通 `select` 读一行，另一个事务 `update`	通常不阻塞
两个事务同时更新同一行	后来的事务等待
事务 A 更新不提交，事务 B 更新同一行	B 等待 A
DDL 改表	可能需要较强锁，阻塞业务 SQL
外键检查	可能产生关联锁等待

模拟写写冲突：

会话 A：

sql

begin;
update biz.medical_asset
set status = 'REPAIR'
where asset_no = 'A001';

会话 B：

sql

begin;
update biz.medical_asset
set status = 'USED'
where asset_no = 'A001';

会话 B 会等待，因为同一行不能被两个事务同时改成两个结果。

查看等待：

sql

select pid, wait_event_type, wait_event, state, query
from pg_stat_activity
where wait_event is not null;

第九步：生产建模不要把 PostgreSQL 用错

PostgreSQL 很强，但不是所有场景都该塞进一个表。

场景	推荐做法
核心资产主数据	关系表 + 约束 + B-tree 索引
灵活但需要查询的属性	`jsonb` + GIN，控制字段大小
高频变化的大字段	拆表，避免每次更新大 tuple
超大历史流水	分区表、归档、冷热分离
全文搜索复杂排序	同步到 Elasticsearch
热点读缓存	Redis 缓存，PostgreSQL 作为事实源
跨系统事件	outbox 表 + MQ，保证可补偿

错误设计示例：

sql

create table bad_asset (
  id bigserial primary key,
  payload jsonb not null
);

为什么不好：

必填字段没有约束。
资产编号无法自然唯一约束。
医院、状态、创建时间这些高频过滤字段难优化。
统计信息和执行计划更难准确。
更新任意属性都可能重写大 JSONB。

更合理的折中是：核心字段列化，低频扩展属性 JSONB 化。

第十步：完整 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

insert into biz.medical_asset(
  asset_no, hospital_id, department_id, status, asset_name, attrs
) values
  ('A001', 1001, 10, 'IDLE', 'CT-001', '{"vendor":"GE","deviceType":"CT"}'),
  ('A002', 1001, 11, 'USED', 'MRI-001', '{"vendor":"Siemens","deviceType":"MRI"}'),
  ('A003', 1002, 12, 'REPAIR', 'DR-001', '{"vendor":"Philips","deviceType":"DR"}');

分页查询：

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, asset_name
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 要观察四件事：

唯一约束能防止重复资产编号。
B-tree 联合索引服务列表查询。
GIN 索引服务 JSONB 属性查询。
更新状态会产生新 tuple，旧版本后续由 VACUUM 清理。

线上排查总流程

mermaid

flowchart TD
    A["PostgreSQL 线上问题"] --> B{"具体表现"}
    B -- "SQL 慢" --> C["EXPLAIN ANALYZE BUFFERS"]
    C --> D["看扫描、Join、排序、估算偏差"]
    B -- "表越来越大" --> E["查 dead tuple 和 autovacuum"]
    E --> F["查长事务、更新频率、膨胀"]
    B -- "接口卡住" --> G["查锁等待和连接"]
    G --> H["pg_stat_activity / pg_locks"]
    B -- "提交慢或复制延迟" --> I["查 WAL、checkpoint、磁盘 IO"]
    I --> J["看大事务、归档、从库回放"]

常用排查 SQL：

sql

select pid, usename, state, wait_event_type, wait_event, query
from pg_stat_activity
order by state, pid;

sql

select relation::regclass,
       mode,
       granted,
       pid
from pg_locks
where relation is not null;

sql

select relname, n_live_tup, n_dead_tup, last_autovacuum
from pg_stat_user_tables
order by n_dead_tup desc;

常见问题与处理

问题	原因	处理
有索引仍然慢	返回行太多、Heap 访问多、统计信息不准	看计划、更新统计、调整索引
Index Only Scan 仍慢	Visibility Map 不充分、Heap Fetches 多	检查 VACUUM、膨胀和长事务
表越来越大	update/delete 产生 dead tuple	autovacuum、清理长事务、必要时重建
JSONB 查询慢	没有 GIN 或查询不匹配索引	建合适 GIN，控制 JSON 字段
批量导入慢	单条插入、索引维护多	批量写、COPY、分批事务
锁等待	长事务、DDL、写写冲突	查 `pg_stat_activity` 和 `pg_locks`
从库延迟	WAL 产生快，从库回放慢	拆大事务、提升从库、限制重查询

面试标准回答

PostgreSQL 怎么从零学到生产可用

text

PostgreSQL 要按 SQL、表设计、索引、执行计划、事务 MVCC、VACUUM、WAL、备份恢复和线上排查这条线学习。先掌握建表、约束和查询，再理解 B-tree、GIN、BRIN 等索引适合什么条件，然后用 EXPLAIN ANALYZE 看真实执行计划。深入部分要理解 PostgreSQL 更新通常生成新 tuple，旧版本通过 xmin/xmax 和快照控制可见性，后续由 VACUUM 清理；WAL 用于崩溃恢复、复制和时间点恢复。生产排查要结合 pg_stat_activity、pg_locks、pg_stat_user_tables、执行计划和 Buffers 判断问题发生在哪一层。

PostgreSQL 和 MySQL 学习重点有什么不同

text

MySQL InnoDB 重点是 B+Tree、聚簇索引、undo log、redo log、binlog、Buffer Pool 和主从复制；PostgreSQL 重点是 tuple 多版本、xmin/xmax、WAL、VACUUM、Visibility Map、GIN/BRIN 等索引、执行计划和统计信息。PostgreSQL 更新会留下旧 tuple，必须关注 VACUUM 和表膨胀；Index Only Scan 也可能因为 MVCC 可见性访问 heap，这和 MySQL 覆盖索引的理解不完全一样。

关联知识点

知识点	说明
PostgreSQL 总览	专栏入口和学习顺序
核心全过程原理	SQL、MVCC、WAL、VACUUM 全链路
MVCC、VACUUM 与 WAL 原理	tuple 版本、快照、清理、恢复
索引与优化器	B-tree、GIN、BRIN、执行计划
事务与锁	隔离级别、写写冲突、锁等待
性能排查	慢 SQL、膨胀、统计信息和等待
PostgreSQL 面试	标准回答和追问

本章小结

PostgreSQL 从零到生产级掌握，不是背几个命令，而是能把业务建模、索引选择、执行计划、MVCC 版本、VACUUM 清理、WAL 恢复和线上排查串起来。你要能解释“为什么这样设计”，也要能说明“不这样会怎样”：不用约束会有脏数据，乱建索引会拖慢写入，不看执行计划会乱优化，长事务会阻止 VACUUM，不理解 WAL 就讲不清提交和恢复，不理解 Visibility Map 就会误判 Index Only Scan。

JDK 8/11/17/21 LTS演进

线程池

PostgreSQL 从零到生产级掌握 ​

学习目标 ​

为什么要单独学 PostgreSQL ​

从零开始的学习路线 ​

第一步：连接、库、Schema 和角色 ​

第二步：表设计从业务问题开始 ​

第三步：索引不是越多越好 ​

第四步：一条查询到底怎么执行 ​

第五步：MVCC 为什么这样设计 ​

第六步：VACUUM 不是可选项 ​

第七步：WAL、提交和崩溃恢复 ​

第八步：事务与锁怎么理解 ​

第九步：生产建模不要把 PostgreSQL 用错 ​

第十步：完整 Demo ​

线上排查总流程 ​

常见问题与处理 ​

面试标准回答 ​

PostgreSQL 怎么从零学到生产可用 ​

PostgreSQL 和 MySQL 学习重点有什么不同 ​

关联知识点 ​

本章小结 ​

PostgreSQL 从零到生产级掌握

学习目标

为什么要单独学 PostgreSQL

从零开始的学习路线

第一步：连接、库、Schema 和角色

第二步：表设计从业务问题开始

第三步：索引不是越多越好

第四步：一条查询到底怎么执行

第五步：MVCC 为什么这样设计

第六步：VACUUM 不是可选项

第七步：WAL、提交和崩溃恢复

第八步：事务与锁怎么理解

第九步：生产建模不要把 PostgreSQL 用错

第十步：完整 Demo

线上排查总流程

常见问题与处理

面试标准回答

PostgreSQL 怎么从零学到生产可用

PostgreSQL 和 MySQL 学习重点有什么不同

关联知识点

本章小结