2026年6月25日

PostgreSQL 面试题精讲：从基础对象到高可用

将 PostgreSQL 高频面试题按关联关系合并，并按基础对象、索引、MVCC、事务锁、WAL、查询优化、连接池、备份恢复、复制与高可用的难度路径递进梳理。

第 24 章：PostgreSQL 面试题精讲

本章不再按截图题目逐题罗列，而是把重复和强关联的问题合并成 16 个题组。阅读顺序从“会用 PostgreSQL”逐步升级到“能解释内核机制、能处理事务/锁/连接池语义、能排查性能问题、能设计备份恢复和高可用方案”。

0. 合并后的面试路线

原始问题里有几类明显重叠：

VACUUM、autovacuum、MVCC、表膨胀本质上是一条链；
EXPLAIN、Index Only Scan、CTE、并行查询、自定义函数都属于执行计划和查询优化；
pg_stat_statements、pg_stat_activity、auto_explain、pgBadger 都属于性能观测；
WAL、WAL 归档、PITR、物理备份、流复制共同组成恢复链路；
Patroni、repmgr、BDR、Streaming Replication、Logical Replication 都属于复制与高可用，但目标不同。

另外，原题更偏 DBA 知识点。为了让回答更像资深后端，而不是只会背数据库组件，本章额外补入四组“后端语义题”：事务隔离与完整重试、行锁与任务队列、PgBouncer 连接池语义、幂等键与 Outbox。

合并后按难度递进如下。右侧链接可以直接跳回原理章节的对应小节，适合面试前按题组反查。

难度	题组	覆盖原题	对应主讲章节
基础	PostgreSQL 架构与 MySQL 差异	4	第 1 章：整体架构第 1 章：SQL 处理链
基础	表设计、约束、DDL 与生命周期	1	第 2 章：生产建模原则第 2 章：最小生产示例
基础	索引体系与生产级索引管理	2、17	第 4 章：B-tree 面试题第 5 章：索引生命周期 Runbook 第 8 章：索引类型选型
基础	导入导出与逻辑备份边界	3、23	第 22 章：大版本切流实验第 23 章：Backup/PITR 与 HA 边界
进阶	MVCC、VACUUM、autovacuum 与表膨胀	7、12、16	第 9 章：MVCC 版本生命周期第 12 章：VACUUM 心智模型第 3 章：Tuple 生命周期
进阶	事务隔离、并发异常与完整事务重试	新增资深后端题组	第 10 章：隔离级别心智模型第 10 章：SQLSTATE 分类边界第 10 章：Lost Update 实验
进阶	行锁、死锁、`SKIP LOCKED` 与任务队列	新增资深后端题组	第 11 章：四种行锁第 11 章：NOWAIT/SKIP LOCKED 第 11 章：任务领取器
进阶	WAL、Checkpoint、Crash Recovery 与归档	5、22	第 13 章：WAL 心智模型第 13 章：Crash Recovery 第 13 章：WAL 与复制/PITR
进阶	分区、继承、TOAST 与大表生命周期	6、8、9	第 14 章：分区心智模型第 3 章：TOAST 原理第 2 章：宽表与 TOAST
高阶	执行计划、SQL 改写与查询优化	17、18、19、20、21	第 6 章：EXPLAIN 证据链第 7 章：估算偏差排障第 4 章：Index Only Scan
高阶	性能观测与慢 SQL 证据链	10、11、13、14、15	第 18 章：pg_stat_statements 第 18 章：等待与阻塞链第 18 章：生产 Runbook
高阶	PgBouncer、连接池语义与故障切换连接行为	新增资深后端题组	第 16 章：连接池心智模型第 16 章：Session State 第 18 章：PgBouncer 配置与观测
高阶	业务一致性、幂等键、`ON CONFLICT` 与 Outbox	新增资深后端题组	第 17 章：幂等与 Outbox 心智模型第 17 章：安全边界第 23 章：幂等表
高阶	备份恢复、PITR、pg_basebackup 与 Barman	22、23、24、25、26、27	第 23 章：Backup/PITR 与 HA 边界第 23 章：备份恢复演练清单第 13 章：WAL 与复制/PITR
架构	Streaming Replication、Logical Replication 与 BDR	27、28、30	第 21 章：Streaming Replication 第 22 章：Logical Replication 第 22 章：物理复制 vs 逻辑复制
架构	Patroni、repmgr、Fencing 与生产级高可用	29、31、32	第 23 章：高可用心智模型第 23 章：Fencing 与 pre_promote 第 23 章：Backup/PITR 与 HA 边界

一、基础篇：先把对象、表和索引讲清楚

1. PostgreSQL 的整体架构是什么？和 MySQL 最大差异在哪里？

合并来源：原题 4。

对应主讲章节：第 1 章：整体架构、第 1 章：SQL 处理链、第 1 章：连接模型与连接池。

面试短答：

PostgreSQL 是多进程架构。客户端连接通常对应一个 Backend Process，多个 Backend 通过共享内存、WAL、Buffer Manager、Lock Manager、后台进程协作完成 SQL 执行和事务持久化。它和 MySQL/InnoDB 最大的存储差异是：PostgreSQL 默认是 heap table + 独立索引，MVCC 旧版本存放在 heap 中；InnoDB 是聚簇索引组织表，二级索引指向主键，旧版本主要依赖 undo log。

深入原理：

PostgreSQL 的核心组件可以按请求路径理解：

Client
-> Backend Process
-> Parser / Analyzer / Rewriter
-> Planner
-> Executor
-> Buffer Manager / Lock Manager / WAL
-> Storage

后台进程负责长期维护：

组件	主要职责
Postmaster	监听连接、管理子进程
Backend Process	执行客户端 SQL
Checkpointer	周期性触发 checkpoint
Background Writer	提前刷脏页，减轻 checkpoint 抖动
WAL Writer	写 WAL 缓冲
Autovacuum Worker	清理 dead tuple、更新统计信息、freeze
WAL Sender / Receiver	流复制传输和接收 WAL

PostgreSQL 与 MySQL 的面试对比不要只停留在“开源数据库不同”，应该落到存储和并发模型：

维度	PostgreSQL	MySQL/InnoDB
表组织	heap table	聚簇索引组织表
主键作用	普通唯一索引，行数据不按主键聚簇存储	主键就是聚簇索引
二级索引	指向 heap tuple 位置	指向主键
MVCC 旧版本	heap 中保留旧 tuple	undo log 中保留旧版本
清理机制	VACUUM / autovacuum	purge 线程清理 undo
扩展能力	类型、索引、函数、扩展机制很强	工程生态和兼容性强

工程实践/坑点：

PostgreSQL 连接是比较重的，生产环境通常要用连接池和 PgBouncer 控制 Backend 数量；
不要把 shared_buffers 命中率当成唯一性能指标，还要看 I/O、WAL、锁等待、执行计划；
不要照搬 MySQL 的“主键即物理聚簇顺序”思维来理解 PostgreSQL；
PostgreSQL 的 UPDATE 会生成新 tuple，索引和 VACUUM 成本要在表设计阶段考虑。

常见追问：

PostgreSQL 为什么需要 VACUUM，而 MySQL 面试里较少这么强调？

因为 PostgreSQL 的 MVCC 旧版本在 heap 中，UPDATE / DELETE 后旧版本不会立即消失，需要 VACUUM 清理 dead tuple、推进 freeze，并更新 visibility map。否则表和索引会膨胀，Index Only Scan 也可能退化。

2. PostgreSQL 中如何设计和管理生产级表？

合并来源：原题 1。

对应主讲章节：第 2 章：生产建模原则、第 2 章：最小生产示例、第 2 章：面试题。

面试短答：

生产级表设计不只是 CREATE TABLE。需要同时考虑字段类型、主键、唯一约束、外键、CHECK、默认值、NULL 语义、索引成本、DDL 锁、分区扩展、VACUUM 压力和后续迁移路径。

示例：

CREATE TABLE orders (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    tenant_id bigint NOT NULL,
    user_id bigint NOT NULL,
    status text NOT NULL CHECK (status IN ('created', 'paid', 'cancelled')),
    amount numeric(12, 2) NOT NULL CHECK (amount >= 0),
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX CONCURRENTLY idx_orders_tenant_user_created
ON orders (tenant_id, user_id, created_at DESC);

这里的 updated_at DEFAULT now() 只在插入时提供默认值，后续 UPDATE 不会自动刷新。生产里通常由应用在更新语句中显式写 updated_at = now()，或使用 BEFORE UPDATE 触发器统一维护。

深入原理：

表设计要回答四个问题：

数据语义是否被数据库约束保护：例如金额非负、状态枚举、租户字段非空；
写入路径是否稳定：主键生成、唯一约束、热点字段更新频率；
查询路径是否可预期：常用过滤条件、排序条件、分页方式是否能被索引支持；
未来演进是否可控：大表字段新增、回填、约束校验、分区拆分是否可在线完成。

资深面试要主动补两个边界：Identity 底层仍依赖 sequence，不保证无空洞，也不能脱离唯一/主键约束单独保证业务唯一；PostgreSQL 默认认为多个 NULL 互不相同，如果业务要求“最多一个 NULL”或“NULL 也参与唯一性”，要考虑 NULLS NOT DISTINCT 或部分唯一索引。

常见 DDL 需要关注锁：

ALTER TABLE orders ADD COLUMN remark text;

ALTER TABLE orders ADD CONSTRAINT orders_amount_positive
CHECK (amount >= 0) NOT VALID;

ALTER TABLE orders VALIDATE CONSTRAINT orders_amount_positive;

NOT VALID 可以让新增约束先保护新写入，再分阶段校验历史数据，适合大表在线迁移。

工程实践/坑点：

大表上直接执行高风险 DDL 前，要先确认是否会重写整表或长时间持有 ACCESS EXCLUSIVE 锁；
高频更新字段不要放进过多索引，否则每次更新都可能放大索引维护成本；
约束优先于应用层“先查后写”，并发下用唯一约束、CHECK、外键和 INSERT ... ON CONFLICT 消除竞态；
表设计要为查询和维护服务，不要为了“看起来范式正确”制造过多 JOIN；
numeric 精确但成本更高，金额适合它，普通计数或状态不一定适合；
宽字段、JSONB、大文本会牵涉 TOAST，不能只按逻辑字段数量判断行大小。
now() 是事务开始时间；如果长事务里要区分语句时间或真实墙钟时间，需要知道 statement_timestamp() 和 clock_timestamp() 的边界。

面试加分回答：

我会先从业务不变量、查询模式和演进方式设计表，再用约束保护数据，用索引服务核心查询，用在线 DDL 策略降低迁移风险，最后用 VACUUM/ANALYZE 和统计视图观察表的长期健康。

3. PostgreSQL 索引怎么设计、创建和治理？

合并来源：原题 2、17。

对应主讲章节：第 4 章：B-tree 索引面试题、第 4 章：Index Only Scan、第 5 章：索引生命周期 Runbook、第 8 章：索引类型选型。

面试短答：

PostgreSQL 支持 B-tree、Hash、GIN、GiST、SP-GiST、BRIN 等索引，也支持联合索引、部分索引、表达式索引、覆盖索引。生产中索引设计要从查询条件、排序、选择性、写入成本和维护成本出发，而不是“字段常查就建索引”。

常用命令：

CREATE INDEX CONCURRENTLY idx_orders_user_created
ON orders (user_id, created_at DESC);

CREATE INDEX CONCURRENTLY idx_orders_paid_recent
ON orders (created_at DESC)
WHERE status = 'paid';

CREATE INDEX CONCURRENTLY idx_orders_user_cover
ON orders (user_id, created_at DESC)
INCLUDE (status, amount);

DROP INDEX CONCURRENTLY idx_orders_user_created;

REINDEX INDEX CONCURRENTLY idx_orders_user_created;

深入原理：

索引设计的核心是让执行器少读、少排序、少回表：

索引能力	典型场景	面试要点
B-tree	等值、范围、排序、唯一约束	最常用，联合索引顺序很关键
GIN	JSONB、数组、全文检索	写入成本高，适合倒排类查询
GiST	范围、地理、相似度	更偏通用搜索树框架
BRIN	时间序列、大表顺序写入	很小，但依赖物理相关性
部分索引	只查某类状态	降低索引体积和维护成本
表达式索引	`lower(email)` 等	查询表达式要匹配
INCLUDE	Index Only Scan	非 key 列只用于覆盖返回字段

Index Only Scan 不是“不访问表”的绝对承诺。它需要 visibility map 证明 heap page 上的 tuple 对所有事务可见，否则仍然要访问 heap 判断可见性。

工程实践/坑点：

生产环境建索引用 CREATE INDEX CONCURRENTLY，避免长时间阻塞写入；
CREATE INDEX CONCURRENTLY 不能放在普通事务块里，失败后可能留下 invalid index；如果是唯一索引，失败状态下也可能继续带来唯一性检查成本，必须清理；
索引越多，写入越慢，VACUUM 和 autovacuum 压力也越大；
联合索引要服务具体查询，例如 (tenant_id, status, created_at DESC) 和 (created_at, tenant_id) 不是一回事；
部分索引要求优化器能证明查询谓词蕴含索引谓词，否则建了也可能不用；
INCLUDE 能改善覆盖读取，但会增大索引体积，降低写入和维护效率；
高频 UPDATE 要考虑 HOT update：只有未修改索引相关列且原 page 有空间时，才更容易避免为每个索引新增条目；fillfactor 是空间利用率和后续更新成本之间的权衡；
OFFSET 深分页即使用索引也可能扫描大量无用行，优先考虑 keyset pagination。

面试加分回答：

我不会只看有没有索引，而是会用 EXPLAIN (ANALYZE, BUFFERS) 验证扫描行数、回表次数、排序方式和缓存命中，再结合 pg_stat_user_indexes 清理低价值索引。

4. 数据导入导出、逻辑备份和迁移怎么选？

合并来源：原题 3、23。

对应主讲章节：第 22 章：大版本切流实验、第 22 章：逻辑复制不能替代 Backup/PITR、第 23 章：Backup/PITR 与 HA 边界。

面试短答：

临时导入导出优先用 COPY 或 \copy；逻辑备份和跨版本迁移常用 pg_dump / pg_restore；大规模初始化或完整恢复不能只依赖逻辑导出，还要结合物理备份和 WAL。

常用方式：

COPY orders TO '/tmp/orders.csv' WITH (FORMAT csv, HEADER true);

COPY orders FROM '/tmp/orders.csv' WITH (FORMAT csv, HEADER true);

客户端侧：

psql -d app -c "\copy orders TO 'orders.csv' WITH csv header"

pg_dump -Fc -d app -f app.dump

pg_restore -d app_restore --clean --if-exists --jobs=4 app.dump

深入原理：

几种工具解决的问题不同：

工具	适合场景	不适合场景
`COPY`	单表高速导入导出	完整库备份、对象依赖恢复
`pg_dump`	逻辑备份、跨版本迁移、对象级恢复	大库低 RTO 恢复
`pg_restore`	自定义格式并行恢复	直接恢复 WAL 时间点
`pg_basebackup`	物理基础备份、搭建从库	单表选择性恢复
WAL 归档	PITR、误操作恢复	单独使用不能恢复完整库

逻辑备份是“把对象和数据重新生成一遍”，物理备份是“复制数据库文件并通过 WAL 保证一致性”。面试要能说清边界。

工程实践/坑点：

COPY 服务端文件路径要求数据库服务端有文件权限，\copy 是客户端读写文件；
大批量导入前要考虑索引、外键、触发器、WAL 放大和 autovacuum；
pg_dump 不是高可用方案，也不是 PITR 的替代品；
恢复演练比备份成功更重要，没演练过的备份只能算“疑似备份”。

二、机制篇：从 MVCC、WAL 到大表生命周期

5. MVCC、VACUUM、autovacuum 和表膨胀是什么关系？

合并来源：原题 7、12、16。

对应主讲章节：第 9 章：MVCC 版本生命周期、第 12 章：VACUUM 心智模型、第 3 章：Tuple 生命周期、第 3 章：VM 与 Index Only Scan。

面试短答：

PostgreSQL 通过 MVCC 让读写并发，更新和删除不会立刻覆盖旧行，而是产生新的 tuple version，并在旧版本上记录状态。旧版本在旧 Snapshot 下仍可能可见，只有当全局可见性边界确认没有事务还需要它时，VACUUM 才能回收。autovacuum 是后台自动维护机制；长事务、idle in transaction、复制槽、standby 长查询、prepared transaction、写入热点和清理能力不足，都会让表膨胀。

深入原理：

一条 UPDATE 的生命周期可以这样讲：

UPDATE
-> 生成一个新的 tuple version
-> 旧版本记录 xmax 等状态
-> 对旧 Snapshot 来说，旧版本仍可能可见
-> 索引可能指向多个历史版本
-> 等所有可能看见旧版本的事务结束后
-> 旧版本才成为 VACUUM 可回收版本
-> visibility map 标记全可见页面
-> Index Only Scan 更容易生效

MVCC 依赖事务 ID 和 Snapshot 判断可见性：

概念	作用
`xmin`	创建 tuple 的事务
`xmax`	删除、更新或行锁相关的事务信息，可能是普通事务 ID，也可能是 MultiXactId
Snapshot	当前事务可见的事务边界
Dead tuple	对任何活跃事务都不可见的旧版本
Freeze	防止事务 ID wraparound
Visibility Map	标记页面是否全可见、全冻结

所以不能只根据 xmax != 0 判断一行“已经删除”。PostgreSQL 还要结合 tuple infomask、事务状态、MultiXact 和 Snapshot 判断可见性。

autovacuum 会根据表变化量触发，典型参数包括：

ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.02,
    autovacuum_analyze_scale_factor = 0.01
);

它的触发思想可以粗略理解为：

vacuum threshold
≈ autovacuum_vacuum_threshold
  + autovacuum_vacuum_scale_factor × 表规模

大表只调低 scale_factor 还不够，还要看绝对阈值、更新速率、worker 是否饱和、cost limit / delay、每个 worker 的内存、dead tuple 产生速度是否超过清理速度，以及是否被长事务或复制相关机制卡住。即使普通 autovacuum 被关闭，PostgreSQL 也仍会为了防止 XID/MultiXact wraparound 启动强制 vacuum。

工程实践/坑点：

长事务、idle in transaction、standby 长查询、prepared transaction、复制槽和 hot_standby_feedback 都可能延长回收 horizon；
VACUUM 通常不归还磁盘空间给操作系统，只是让空间可复用；
VACUUM FULL 会重写表并拿重锁，生产要谨慎；
高频更新表要降低 autovacuum scale factor，不能只依赖默认值；
表膨胀不能简单归因于“autovacuum 没运行”，还要判断清理是否被阻塞，以及清理速度是否追得上产生速度；
Index Only Scan 失效时，要检查 visibility map 和 VACUUM 情况。

面试加分回答：

我会把 MVCC 讲成“并发收益 + 维护成本”的组合：它让读写互不阻塞，但代价是需要 VACUUM 持续清理旧版本，并通过 autovacuum、长事务治理、复制槽治理、膨胀监控和恢复演练来保持表健康。

6. 事务隔离、并发异常和完整事务重试怎么讲？

合并来源：新增资深后端题组。

对应主讲章节：第 10 章：隔离级别心智模型、第 10 章：SQLSTATE 分类边界、第 10 章：Lost Update 实验。

面试短答：

PostgreSQL 的隔离级别要和 Snapshot、锁、错误重试一起讲。Read Committed 是每条语句获取新 Snapshot；Repeatable Read 使用事务级 Snapshot，能避免不可重复读，但仍可能在写冲突时失败；Serializable 基于 SSI 检测危险依赖，不是简单给所有读取加锁。遇到 40001 serialization failure 或 40P01 deadlock detected，通常应该有上限、退避和抖动地重试完整事务，而不是只重发最后一条 SQL。

深入原理：

面试里可以按“异常 -> 控制手段 -> 失败语义”讲：

问题	常见方案	边界
Lost Update	原子 `UPDATE`、`SELECT ... FOR UPDATE`、乐观锁版本号、Serializable	不要先查后写后无条件覆盖
写冲突	行锁或唯一约束	锁顺序不一致可能死锁
读写依赖异常	Serializable / SSI	可能返回 `40001`，应用要完整重试
死锁	固定加锁顺序、缩短事务	PostgreSQL 会中止其中一个事务并返回 `40P01`
提交阶段断连	幂等键和状态查询	不能武断判断事务没有提交

完整重试的意思是：重新执行从读取业务状态、计算决策、写入数据库到提交的整个事务函数。只重发最后一条 UPDATE 可能把已经变化的业务前提忽略掉，制造重复扣款、重复发货或库存错误。

工程实践/坑点：

40001 和 40P01 可以重试，但必须有最大次数、退避、Jitter 和原始请求超时；
约束错误、语法错误、权限错误、磁盘满等通常不应该进入普通重试；
事务里不要调用慢外部服务，外部副作用应放到事务外，必要时用 Outbox；
连接错误要结合阶段判断：执行前失败、执行中失败和 COMMIT 阶段失败语义不同；
在 Go/pgx 中要基于 SQLSTATE 分类，而不是只匹配错误字符串。

面试加分回答：

我会把事务重试做成业务级事务函数，而不是 SQL 级重放。数据库告诉我 40001 或 40P01 时，只能说明这次事务被回滚，可以在幂等和预算范围内重来；如果错误发生在 Commit 附近，则进入“提交结果未知”，要按幂等键查询事实后再决定是否补偿或重试。

7. 行锁、死锁、`SKIP LOCKED` 和任务队列怎么讲？

合并来源：新增资深后端题组。

对应主讲章节：第 11 章：四种行锁、第 11 章：NOWAIT/SKIP LOCKED、第 11 章：任务领取器。

面试短答：

PostgreSQL 行锁不是只有 FOR UPDATE。常见行锁包括 FOR UPDATE、FOR NO KEY UPDATE、FOR SHARE、FOR KEY SHARE，它们保护的更新范围和外键语义不同。死锁通常来自多个事务以不同顺序获取资源；NOWAIT 用于快速失败，SKIP LOCKED 适合竞争式任务队列，但它会跳过被锁住的行，得到的是不一致视图，不适合普通业务查询。

深入原理：

一个任务队列的典型领取 SQL 是：

WITH picked AS (
    SELECT id
    FROM jobs
    WHERE status = 'ready'
    ORDER BY priority DESC, id
    FOR UPDATE SKIP LOCKED
    LIMIT 10
)
UPDATE jobs j
SET status = 'running',
    locked_at = now()
FROM picked
WHERE j.id = picked.id
RETURNING j.*;

这里的关键不是“跳过锁就快”，而是把“选中任务”和“标记任务为 running”放进同一条原子语句，避免多个 worker 领取同一批任务。

工程实践/坑点：

固定加锁顺序比事后处理死锁更重要；
lock_timeout、statement_timeout 和应用超时要形成清晰层级；
SKIP LOCKED 可能造成饥饿，要有重试、租约过期、可观测性和补偿逻辑；
数据库队列适合中小规模、强事务耦合任务，不应替代专业消息队列的所有能力；
看到锁等待时，用 pg_blocking_pids()、xact_start、backend_xmin 和 SQL 文本一起判断阻塞链。

面试加分回答：

我会先说明锁解决的是并发正确性，不是吞吐魔法。任务队列可以用 FOR UPDATE SKIP LOCKED 做竞争领取，但必须接受不一致视图、饥饿、租约恢复和幂等处理成本；普通业务查询不能为了“不卡”随便加 SKIP LOCKED。

8. WAL、Checkpoint、Crash Recovery 和 WAL 归档分别解决什么问题？

合并来源：原题 5、22。

对应主讲章节：第 13 章：WAL 心智模型、第 13 章：Commit 路径、第 13 章：Checkpoint 与 Redo Point、第 13 章：Crash Recovery、第 13 章：WAL 与复制/PITR。

面试短答：

WAL 是 PostgreSQL 保证崩溃恢复能力的核心机制。数据页可以延迟刷盘，但在 fsync=on 且 synchronous_commit 不是 off 的通常可靠配置下，PostgreSQL 会等待提交记录对应的 WAL 在本地持久化后再向客户端返回成功。Checkpoint 会把要求范围内的脏页推进到数据文件，写入 checkpoint record，并形成 crash recovery 所依赖的 redo point。WAL 归档把已完成 WAL 段保存到外部存储，用于 PITR 和灾备。

深入原理：

PostgreSQL 的提交路径可以简化为：

修改共享缓冲区中的数据页
-> 生成 WAL record
-> 生成 commit record
-> 按 synchronous_commit / fsync 语义等待 WAL flush
-> 返回提交成功或先异步返回
-> 数据页稍后由后台进程刷盘

这就是 Write-Ahead Logging：先写日志，再写数据页。

概念	作用
WAL Record	描述数据变化
LSN	WAL 位置，复制和恢复都依赖它
Checkpoint	推进脏页刷盘，记录恢复所需的 redo point
Full Page Write	防止 torn page
WAL Archive	把已完成 WAL 段保存到外部存储
Crash Recovery	启动后重放 WAL，恢复到一致点

9. 分区、继承、TOAST 如何处理大表和大对象？

合并来源：原题 6、8、9。

对应主讲章节：第 14 章：分区心智模型、第 14 章：分区生产 Runbook、第 3 章：TOAST 原理、第 2 章：宽表与 TOAST。

面试短答：

分区表用于把大表按时间、范围、列表或哈希拆成多个物理子表，便于裁剪扫描范围和管理生命周期；继承是 PostgreSQL 早期实现分区和表复用的机制，现在生产分区优先用 declarative partitioning；TOAST 用于存储超大字段，例如大文本、JSONB、bytea。

深入原理：

分区的核心收益不是“天然更快”，而是：

查询条件命中分区键
-> partition pruning
-> 扫描更少分区
-> 索引和 VACUUM 维护粒度变小
-> 历史数据可按分区 detach/drop/archive

常见分区方式：

CREATE TABLE events (
    id bigint GENERATED BY DEFAULT AS IDENTITY,
    tenant_id bigint NOT NULL,
    event_time timestamptz NOT NULL,
    payload jsonb NOT NULL
) PARTITION BY RANGE (event_time);

CREATE TABLE events_2026_06
PARTITION OF events
FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

TOAST 的触发点是行太大无法舒适放进 heap page。PostgreSQL 会对大字段压缩或拆到 TOAST 表里，主表保留指针。

继承现在更多作为理解历史和特殊建模能力：

CREATE TABLE animals (id bigint, name text);
CREATE TABLE cats (lives int) INHERITS (animals);

工程实践/坑点：

分区表不一定更快，只有查询能利用分区键裁剪才明显收益；
分区过多会增加规划、维护和元数据成本；
PostgreSQL 没有跨所有分区的全局唯一索引；要在分区父表上声明原生 UNIQUE 或 PRIMARY KEY，约束列必须覆盖全部分区键列；
分区父表索引本质上是分区索引集合；大表常见在线方案是在各分区分别 CREATE INDEX CONCURRENTLY，再把分区索引 attach 到父索引；
TOAST 让大字段可存储，但频繁读取大 JSONB 或大文本仍然会带来 I/O 和 CPU 成本；
新项目不要用继承模拟分区，优先使用声明式分区。

三、性能篇：从执行计划到证据链

10. 如何阅读执行计划并优化 SQL？

合并来源：原题 17、18、19、20、21。

对应主讲章节：第 6 章：EXPLAIN 证据链、第 7 章：估算偏差排障、第 4 章：Index Only Scan。

面试短答：

优化 SQL 先看执行计划，再看真实执行数据。用 EXPLAIN (ANALYZE, BUFFERS) 判断扫描方式、连接方式、排序、聚合、并行度、估算偏差和 I/O 情况。Index Only Scan、CTE、并行查询和自定义函数都要放回执行计划里判断，而不是孤立判断“快或慢”。

常用命令：

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT id, status, amount
FROM orders
WHERE tenant_id = 1001
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

ANALYZE 选项会真正执行 SQL。对 INSERT、UPDATE、DELETE、MERGE 这类写语句，应该在可控环境执行，或使用 BEGIN; EXPLAIN ANALYZE ...; ROLLBACK; 包住，同时注意触发器、序列、通知、外部函数等副作用未必都能被“简单回滚”覆盖。

深入原理：

读计划时按这个顺序：

实际耗时在哪里：看节点的 actual time 和 loops；
估算是否偏离：比较 rows estimate 和 actual rows；
扫描方式是否合理：Seq Scan、Index Scan、Bitmap Scan、Index Only Scan；
连接方式是否合理：Nested Loop、Hash Join、Merge Join；
排序和聚合是否溢出：看 Sort Method、Disk、work_mem；
I/O 是否重：看 shared hit/read/dirtied/written；
并行是否真的收益：看 Workers Planned/Launched 和 Gather 成本。

几个判断不要绝对化：Seq Scan 不是坏计划，小表、低选择性或返回大量行时可能最优；Nested Loop 也不是慢的同义词，外表很小且内表有合适索引时通常很好；work_mem 是每个排序/哈希节点、每个并行 worker 都可能使用的预算，不是整条 SQL 的总上限；shared read 表示 PostgreSQL 把页读入 shared buffers，不等于一定发生了物理磁盘读取，数据可能来自操作系统页缓存。要判断真实 I/O 延迟，还要结合 track_io_timing、pg_stat_io 和主机指标。

几个常见追问可以合并回答：

追问	回答要点
Index Only Scan 为什么还可能回表	visibility map 不全可见时仍需访问 heap 判断可见性
CTE 一定优化吗	不一定，旧版本常是优化屏障，新版本可能内联，但仍要看 materialize
并行查询一定更快吗	不一定，有启动成本、Gather 成本和 worker 资源竞争
自定义函数会影响性能吗	会，函数成本、volatile/stable/immutable、是否阻止索引或并行都会影响计划

工程实践/坑点：

优化前先拿真实慢 SQL 和参数，不要只看模板 SQL；
不要只看 cost，要看 ANALYZE 后的真实耗时；
行数估算严重偏差时，先考虑 ANALYZE、统计目标和 extended statistics；
参数分布倾斜时，要考虑 generic plan 与 custom plan 的差异；
多列相关性可能需要 extended statistics，不能只盯单列统计；
CTE 是否物化取决于 PostgreSQL 版本、引用次数以及 MATERIALIZED / NOT MATERIALIZED 写法；
LIMIT 不代表一定快，如果排序或过滤前要扫描大量行仍然慢；
SQL 改写、索引、统计信息、参数化计划要一起验证。

面试加分回答：

我会用 EXPLAIN (ANALYZE, BUFFERS) 找到真实耗时节点，再判断是索引缺失、统计信息错误、JOIN 顺序问题、排序溢出、函数阻碍优化、CTE 物化，还是并行度和资源竞争导致的问题。

11. 如何建立 PostgreSQL 性能观测和慢 SQL 证据链？

合并来源：原题 10、11、13、14、15。

对应主讲章节：第 18 章：pg_stat_statements、第 18 章：等待与阻塞链、第 18 章：auto_explain、第 18 章：生产 Runbook。

面试短答：

性能分析要把全局统计、实时会话、慢 SQL 计划和日志聚合串起来：pg_stat_statements 看长期 SQL 聚合，pg_stat_activity 看当前连接和等待，auto_explain 捕获慢 SQL 执行计划，pgBadger 分析日志趋势。

工具分工：

工具	解决的问题	典型用法
`pg_stat_statements`	哪类 SQL 总耗时高、调用多、均值慢	找 Top SQL
`pg_stat_activity`	当前谁在跑、谁在等、谁阻塞	实时排障
`auto_explain`	慢 SQL 当时的真实计划	捕获线上偶发慢计划
pgBadger	从日志聚合趋势和报表	周期性分析

pg_stat_statements 示例：

SELECT
    calls,
    total_exec_time,
    mean_exec_time,
    rows,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

pg_stat_activity 示例：

SELECT
    pid,
    state,
    wait_event_type,
    wait_event,
    now() - query_start AS query_age,
    query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_age DESC;

auto_explain 常见配置：

shared_preload_libraries = 'pg_stat_statements,auto_explain'
auto_explain.log_min_duration = '500ms'
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_nested_statements = on

工程实践/坑点：

pg_stat_statements 是归一化累计统计，适合找“哪类 SQL”而不是某一次现场；分析时要看时间窗口增量和 stats_reset；
pg_stat_activity.state = active 不代表一定在消耗 CPU，也可能在等锁、I/O、客户端；
阻塞链用 pg_blocking_pids()，同时观察 xact_start、backend_xmin、等待事件和 SQL 文本；
I/O 看 pg_stat_io，WAL 看 pg_stat_wal，归档看 pg_stat_archiver；
auto_explain.log_analyze = on 会增加执行期开销，log_timing 的开销也要评估，阈值不要太低；
pgBadger 依赖日志配置，log_line_prefix、慢 SQL 阈值、锁等待日志要提前设计；
性能证据链要包含 SQL、参数、执行计划、等待事件、资源指标和业务时间线。

面试加分回答：

我会先用 pg_stat_statements 找累计伤害最大的 SQL，再用 pg_stat_activity 看当前等待，用 auto_explain 补齐慢 SQL 当时的计划，最后用日志和监控确认是不是锁、I/O、WAL、连接池或计划抖动。

四、后端工程篇：连接池、幂等和业务语义

12. PgBouncer 和连接池语义怎么讲？

合并来源：新增资深后端题组。

对应主讲章节：第 16 章：连接池心智模型、第 16 章：Session State、第 18 章：PgBouncer 配置与观测。

面试短答：

PostgreSQL 连接比较重，所以生产常用应用连接池和 PgBouncer 控制 Backend 数量。但 PgBouncer 不只是“加一层代理”：session pooling 保留完整会话语义，transaction pooling 会在事务之间复用后端连接，因此会改变 session-level 特性的可用性。资深回答要能说清连接数预算、排队、反压、事务池兼容性和 failover 后旧连接处理。

深入原理：

模式	语义	典型边界
Session pooling	客户端连接长期绑定一个服务端连接	会话状态完整，但后端复用率较低
Transaction pooling	只在事务期间占用服务端连接	复用率高，但会话状态不能随便依赖
Statement pooling	语句级复用	事务语义受限，业务服务较少使用

Transaction pooling 下要特别小心：SET / RESET、session advisory lock、LISTEN、保留型临时表、SQL 级 PREPARE、search_path 这类会话状态，不能按“我拿到的是同一个 PostgreSQL 后端”理解。协议级 prepared statement 是否可用，还要看 PgBouncer 版本和配置。

工程实践/坑点：

应用实例数 × MaxConns、PgBouncer server pool 和 PostgreSQL max_connections 必须闭合；
池耗尽时不要第一反应加大池，先看慢 SQL、锁等待、事务时间和数据库最佳并发；
failover 后要丢弃或重建旧连接，处理只读错误、连接 reset 和 server role 变化；
连接池要提供背压和超时，避免把数据库打满后再无限排队；
PgBouncer 参与故障切换时，要设计 PAUSE / RESUME / RECONNECT 或等价操作窗口。

面试加分回答：

我会把连接池当成准入控制层，而不是性能加速器。PgBouncer transaction pooling 能压缩后端连接，但也改变会话语义；因此应用要避免把业务状态绑在会话上，并在故障切换时主动重建连接和按幂等语义恢复请求。

13. 如何设计业务一致性、幂等键、`ON CONFLICT` 和 Outbox？

合并来源：新增资深后端题组。

对应主讲章节：第 17 章：幂等与 Outbox 心智模型、第 17 章：安全边界、第 23 章：幂等表。

面试短答：

资深后端不能只会调数据库参数，还要能把数据库约束转化成业务一致性方案。唯一约束比“先 SELECT 再 INSERT”可靠；INSERT ... ON CONFLICT 能原子实现 UPSERT；幂等键用来处理重试和提交结果未知；Transactional Outbox 把业务状态和待发送事件放进同一个本地事务，避免“数据库提交成功但消息没发出”的双写窗口。

深入原理：

一个支付请求可以这样设计：

收到 request_id
-> 在 idempotency 表占位，唯一约束防重复
-> 同一事务写 payment 状态
-> 同一事务写 outbox event
-> 提交后由 relay/CDC 投递外部副作用
-> 外部结果回调再用幂等事务推进状态

ON CONFLICT 适合把“检查是否存在 + 插入/更新”合并成数据库原子动作：

INSERT INTO idempotency_keys (key, request_hash, status)
VALUES ($1, $2, 'processing')
ON CONFLICT (key) DO NOTHING;

如果冲突了，不应该直接当作成功或失败，而要读取既有记录，校验请求参数是否一致，再返回已有结果或进入冲突处理。

工程实践/坑点：

唯一约束和幂等键要保存请求摘要，防止同一个 key 被不同参数复用；
ON CONFLICT DO UPDATE 也会产生写入和锁竞争，不是免费缓存；
Outbox 解决的是本地事务与事件发布的原子性，不提供瞬时跨系统强一致；
Relay 通常是 At-Least-Once，目标系统必须用事件 ID、业务键或去重表保证幂等；
Commit 阶段断连时，应用不能换新幂等键无脑重试，要先按原幂等键查询事实。

面试加分回答：

我会明确区分“数据库 Exactly Once 不存在”和“业务上实现一次效果”。真正可靠的方案通常是唯一约束、幂等键、状态查询、Outbox、目标端去重和对账一起工作，而不是靠应用层先查后写或失败后无脑重试。

五、恢复篇：从备份到 PITR

14. 如何设计 PostgreSQL 备份恢复体系？

合并来源：原题 22、23、24、25、26、27。

对应主讲章节：第 23 章：Backup/PITR 与 HA 边界、第 23 章：备份恢复演练清单、第 13 章：WAL 与复制/PITR、第 13 章：复制槽与归档检查。

面试短答：

可靠备份体系通常由 base backup + WAL archive + 定期恢复演练组成。pg_dump 适合逻辑备份和对象级恢复，pg_basebackup 适合物理基础备份，WAL 归档用于 PITR，Barman、pgBackRest、WAL-G 负责自动化备份、归档、校验和保留策略。

深入原理：

PITR 的恢复链路：

base backup
-> restore data directory
-> 创建 recovery.signal
-> restore_command 拉取 WAL
-> replay WAL
-> 到达 recovery_target_time / xid / lsn
-> promote

典型配置：

restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-06-25 10:30:00+08'
recovery_target_action = 'promote'

PostgreSQL 12 以后，目标恢复需要在恢复后的数据目录里创建 recovery.signal，否则实例不会因为这些参数自动进入 archive recovery：

touch recovery.signal

几类恢复方案的边界：

方案	能解决	不能解决
`pg_dump`	表级、库级逻辑恢复，跨版本迁移	快速恢复大库到任意时间点
`pg_basebackup`	集群级物理基础备份、搭建从库；默认或 `-X stream` 可带上让备份达到一致状态所需的 WAL	单表选择性恢复；没有后续连续 WAL 时不能恢复到备份结束之后的时间点
WAL Archive + PITR	误删、误更新、时间点恢复	没有 base backup 时不可用
Streaming Replication	主库机器故障、读扩展	误删会复制到从库
Barman	备份管理、WAL 归档、恢复编排	不能替代恢复演练

PITR 的成立前提是：有一份可用的物理基础备份，并且从基础备份开始到目标时间点所需的 WAL 链连续。PITR 恢复的是整个 database cluster，不是单表；failover 或多次恢复还会产生 timeline，恢复时要确认目标 timeline。pg_verifybackup 能依据 backup_manifest 校验备份文件完整性，但校验成功不能替代实际恢复演练。

工程实践/坑点：

Streaming Replication 不能防误删，误删会同步到从库；
PITR 能防误操作，但 RTO 取决于备份体积、WAL 数量和恢复速度；
备份要有保留策略，也要有校验和恢复演练；
归档目录写满会影响主库，需要监控和清理策略；
大库恢复要提前压测，不要事故时第一次测 RTO。
恢复到目标点后不要立刻对外开放写入，先校验关键业务数据、timeline、复制槽、归档和应用连接策略。

面试加分回答：

我会把备份恢复分成三层：逻辑备份解决对象级恢复和迁移，物理备份解决整库恢复，WAL 归档解决时间点恢复；真正上线前必须做恢复演练，用实测 RPO/RTO 而不是配置文件证明可靠性。

六、架构篇：复制、高可用与故障切换

15. Streaming Replication、Logical Replication 和 BDR 有什么区别？

合并来源：原题 27、28、30。

对应主讲章节：第 21 章：Streaming Replication、第 22 章：Logical Replication、第 22 章：物理复制 vs 逻辑复制、第 22 章：Logical Slot Failover。

面试短答：

Streaming Replication 是物理复制，复制 WAL 物理变化，适合 HA、读副本和灾备；Logical Replication 是逻辑复制，按表和变更流复制，适合迁移、CDC、部分数据同步和跨版本升级；BDR 更准确地说是 EDB Postgres Distributed / BDR 这类扩展产品能力，不是 PostgreSQL 内置 native replication 模式。它适合特殊多活场景，但冲突处理和一致性复杂度很高。

深入原理：

维度	Streaming Replication	Logical Replication	BDR
复制层级	物理 WAL	逻辑变更	双向逻辑变更
粒度	整个实例/集群数据目录	publication 中的表	多节点表级变更
适合	HA、读副本、灾备	CDC、迁移、部分同步	多主写入
跨版本	限制较多	更适合跨大版本	依赖方案能力
冲突处理	基本无应用级冲突	订阅端约束可能冲突	核心难点
DDL 同步	物理层天然同步	通常需额外管理	更复杂

PITR 和流复制可以结合：

Primary
-> Streaming Replication 到 Standby
-> WAL Archive 到对象存储
-> Standby 负责 HA/读扩展
-> WAL Archive 负责误操作时间点恢复

Native Logical Replication 还要明确限制：默认不复制 schema / DDL，不复制 sequence 当前值，不复制 large object；UPDATE / DELETE 依赖合适的 replica identity，通常是主键；初始阶段会先做数据快照复制，再持续应用增量变更；订阅端约束、触发器、权限和冲突处理需要单独设计。跨大版本切流前必须同步 sequence，否则新库开放写入后可能发生主键冲突。

工程实践/坑点：

流复制不能替代备份，误删会同步；
逻辑复制不能简单替代物理复制，它更适合数据管道和迁移；
逻辑复制默认不复制 DDL 和 sequence 值，大版本升级要把 schema 演进、序列同步和切流校验纳入流程；
复制槽会保留 WAL，消费者挂了可能导致 WAL 堆积；
读副本有复制延迟，不适合所有读写一致性场景；
BDR/PGD 多主写入要处理冲突、序列、唯一键、应用幂等和业务收敛规则，不能当成“内置多活开关”。

面试加分回答：

我会把物理复制用于 HA 和读副本，把逻辑复制用于 CDC、灰度迁移和跨版本升级，把 BDR 视为需要强业务约束的特殊多活方案，而不是默认高可用方案。

16. Patroni、repmgr、Fencing 和接入层如何组成生产级高可用？

合并来源：原题 29、31、32。

对应主讲章节：第 23 章：高可用心智模型、第 23 章：Fencing 与 pre_promote、第 23 章：Backup/PITR 与 HA 边界。

面试短答：

生产级 PostgreSQL 高可用要先从 RPO/RTO 出发，再选择 Streaming Replication、同步级别、自动选主工具、DCS、Fencing、接入层、备份恢复和演练。Patroni 通过 etcd/Consul/Kubernetes DCS 管理 leader lock 和 failover；repmgr 也能管理复制集群和故障切换；但 leader election 不等于 fencing，任何 HA 工具也不能替代备份、监控和故障演练。

深入原理：

典型架构：

Go Service
-> PgBouncer / HAProxy / VIP
-> PostgreSQL Primary
-> PostgreSQL Standby
-> DCS(etcd/Consul/K8s)
-> Backup + WAL Archive

Patroni 的关键职责：

能力	作用
DCS Leader Lock	判断谁是合法 Primary
健康检查	发现 Primary 失效
Promotion	提升合适 Standby
`pg_rewind`	旧主回归时修复时间线分叉
REST API	给 HAProxy 判断读写端点
配置管理	管理复制和切换参数

Fencing 解决的问题是 Split Brain。DCS leader lease 解决“谁是合法 leader”的协调问题；正常情况下 Patroni 无法续租会主动降级旧主，但如果旧主进程卡死、主机失控或无法执行降级，仅靠逻辑 lease 不等于物理隔离。自动选主前必须证明旧主不能继续写，否则两个 Primary 同时接受写入会破坏数据一致性。生产里通常要结合 watchdog、STONITH、云 API 断电/隔离、存储撤权或 pre_promote 外部检查，形成真正的 fencing。

接入层要解决客户端路由：

写请求 -> 当前 Primary
只读请求 -> 允许延迟的 Standby
故障切换 -> 旧连接失效、连接池重建、应用有界重试

高可用的完整决策链应该是：

业务允许丢多少数据
-> 异步还是同步复制
-> 候选副本允许落后多少
-> 如何证明旧主不能写
-> 客户端如何发现新主
-> 旧连接如何处理
-> 提交结果未知时应用如何重试或对账
-> 旧主如何 pg_rewind 或重建

工程实践/坑点：

Patroni 是 HA 编排工具，不是备份工具；
Leader election 不等于 Fencing；DCS lease、watchdog、STONITH/云隔离和 pre_promote 检查要分层理解；
两节点无仲裁面的自动 failover 风险很高，容易 split brain；
PgBouncer 和 HAProxy 的健康检查必须区分 primary / replica；
故障切换期间应用要处理连接重置、只读错误、未知提交和幂等重试；
数据库断连不一定代表事务没有提交，Commit 附近错误必须按幂等键或业务状态查询确认；
旧主恢复不能直接接回集群，要检查时间线并用 pg_rewind 或重建；
repmgr 更偏复制管理和切换工具，Patroni 更强调 DCS 协调和自动化 HA。

面试加分回答：

我不会把“装了 Patroni”当作高可用完成。完整方案还要有 Fencing 防 split brain、接入层路由、连接池重建、应用幂等、备份/PITR、监控告警，以及定期 switchover、failover 和恢复演练。

七、从简单到困难的回答主线

1. 基础对象主线

表
-> 数据类型与约束
-> 主键和索引
-> DDL 锁
-> 分区与生命周期

面试表达：

PostgreSQL 表设计要同时考虑数据正确性、查询路径、写入成本和长期维护。建表只是起点，真正的生产设计还包括约束、索引、在线变更、VACUUM 和分区策略。

2. 并发维护主线

MVCC
-> UPDATE/DELETE 产生新 tuple version
-> 旧版本在旧 Snapshot 下仍可能可见
-> 全局可见性边界推进后才可回收
-> VACUUM 清理
-> autovacuum 自动维护
-> 长事务、复制槽、standby feedback 阻塞清理
-> 表膨胀和 Index Only Scan 退化

面试表达：

MVCC 提升并发，但不是免费的。它把阻塞变少了，也把清理旧版本的责任交给 VACUUM 和 autovacuum。

3. 事务与锁主线

隔离级别
-> Snapshot 与行锁
-> 40001 / 40P01
-> 完整事务重试
-> Commit Unknown
-> 幂等键和状态查询

面试表达：

事务重试不是重发最后一条 SQL，而是重新执行完整业务事务。40001 和 40P01 可以在预算内重试；Commit 阶段错误不能直接判断失败，必须靠幂等事实确认。

4. 性能排查主线

pg_stat_statements 找 Top SQL
-> pg_stat_activity 看现场等待
-> EXPLAIN ANALYZE 看真实计划
-> auto_explain 捕获偶发慢计划
-> pgBadger 看日志趋势

面试表达：

我会先找累计影响最大的 SQL，再回到执行计划和等待事件，用证据判断是索引、统计信息、锁、I/O、连接池还是 WAL 问题。

5. 后端工程语义主线

约束消除竞态
-> ON CONFLICT 原子 UPSERT
-> 连接池准入和会话语义
-> Outbox 解决本地事务与事件发布
-> 目标端幂等
-> 对账和补偿

面试表达：

资深后端要把数据库能力落到业务语义：唯一约束比先查后写可靠，PgBouncer 改变连接语义，Outbox 解决双写窗口，幂等键负责重试和未知提交后的事实确认。

6. 可靠性主线

WAL
-> Crash Recovery
-> recovery.signal
-> WAL Archive
-> Base Backup
-> PITR
-> Timeline
-> 恢复演练

面试表达：

WAL 保证数据库能从崩溃中恢复，WAL 归档和基础备份一起提供 PITR。备份是否可靠，最终看连续 WAL、timeline 判断、恢复演练和 RPO/RTO 实测。

7. 高可用主线

Streaming Replication
-> Patroni / repmgr
-> DCS
-> Fencing
-> HAProxy / PgBouncer
-> Commit Unknown 对账
-> 应用幂等和重试
-> PITR 兜底

面试表达：

高可用不是一个插件，而是一套系统。复制解决节点故障，Fencing 防 split brain，接入层负责路由，应用负责旧连接、未知提交和幂等恢复，PITR 负责误操作兜底。

八、最容易被追问的判断题

追问	简短回答
`updated_at DEFAULT now()` 会自动随 UPDATE 更新吗？	不会。默认值只用于新行，后续更新要由应用显式设置或触发器维护。
PostgreSQL 为什么需要 VACUUM？	MVCC 旧版本留在 heap 中，等全局可见性边界确认不再需要后，需要 VACUUM 回收、推进 freeze、更新 visibility map。
`xmax != 0` 就说明行被删除了吗？	不一定。`xmax` 也可能表示行锁或 MultiXact，要结合 infomask、事务状态和 Snapshot。
`40001` 可以只重试最后一条 SQL 吗？	不应该。要重试完整业务事务，并有上限、退避和幂等保护。
数据库断连就说明事务没提交吗？	不一定。Commit 附近断连属于结果未知，要按幂等键或业务状态查询事实。
`EXPLAIN ANALYZE` 只是解释计划吗？	不是，它会真的执行 SQL；写语句要在可控环境或事务回滚中使用。
Index Only Scan 一定不访问表吗？	不一定。visibility map 不全可见时仍需访问 heap 判断可见性。
分区表一定更快吗？	不一定。只有查询能利用分区键做 partition pruning，才会明显减少扫描范围。
分区父表上的唯一约束可以不包含分区键吗？	不可以。原生 `UNIQUE` / `PRIMARY KEY` 必须覆盖全部分区键列。
CTE 一定能优化 SQL 吗？	不一定。它可能被内联，也可能物化，关键看版本、写法和执行计划。
`SKIP LOCKED` 能用于普通业务查询避免等待吗？	不适合。它返回不一致视图，主要用于竞争式任务领取。
PgBouncer transaction pooling 和普通连接一样吗？	不一样。它会改变会话状态语义，`LISTEN`、session lock、部分 prepared statement 和临时表要谨慎。
Streaming Replication 能防误删吗？	不能。误删会同步到从库，防误删要靠 PITR。
Patroni 是否等于备份？	不是。Patroni 解决自动选主和 HA 编排，备份恢复仍要靠 base backup、WAL archive 和 PITR。
Logical Replication 能替代 Streaming Replication 吗？	不能简单替代。逻辑复制适合迁移、CDC、部分同步；物理复制更适合 HA 和完整读副本。
Native Logical Replication 会复制 DDL 和 sequence 值吗？	默认不会。迁移和切流要单独处理 schema、sequence、replica identity 和冲突。
BDR 是不是 PostgreSQL 内置多活方案？	不是。它更接近 EDB PGD/BDR 扩展产品能力，多主写入需要强业务约束和额外治理。
pg_stat_activity 里 active 就是 CPU 忙吗？	不一定。active 可能在等锁、I/O、WAL、客户端或网络。
`VACUUM FULL` 能随便在线跑吗？	不能。它会重写表并拿重锁，生产要评估窗口或用更温和的重建方案。

九、最后的面试收束

如果面试官让你“系统讲一下 PostgreSQL”，可以按下面这段收束：

我会从 PostgreSQL 的多进程架构讲起，说明一条 SQL 如何经过 Parser、Planner、Executor，并通过 Buffer、WAL 和存储层完成读写。然后讲表和索引设计：表设计要保护数据语义，索引要服务查询路径并控制写入成本。再讲 MVCC：它提升并发，但旧版本要等全局可见性边界推进后才能回收，所以需要 VACUUM、autovacuum、长事务和复制槽治理。事务语义上，我会区分隔离级别、行锁、40001/40P01 完整重试，以及 Commit Unknown 的幂等对账。性能排查上，我会用 pg_stat_statements 找 Top SQL，用 pg_stat_activity 和 pg_blocking_pids() 看现场等待，用 EXPLAIN (ANALYZE, BUFFERS) 定位真实瓶颈，并说明它会真的执行 SQL。可靠性上，WAL、base backup、recovery.signal、WAL archive 和 timeline 组成 PITR；高可用上，Streaming Replication、Patroni/repmgr、DCS、硬 Fencing、HAProxy/PgBouncer、应用幂等、Outbox 和恢复演练共同组成生产方案。

这套回答的好处是：从基础对象到内核机制，再到性能和架构，层次是递进的；每个点都能继续追问，也能落回工程实践。

第 24 章：PostgreSQL 面试题精讲

0. 合并后的面试路线

一、基础篇：先把对象、表和索引讲清楚

1. PostgreSQL 的整体架构是什么？和 MySQL 最大差异在哪里？

2. PostgreSQL 中如何设计和管理生产级表？

3. PostgreSQL 索引怎么设计、创建和治理？

4. 数据导入导出、逻辑备份和迁移怎么选？

二、机制篇：从 MVCC、WAL 到大表生命周期

5. MVCC、VACUUM、autovacuum 和表膨胀是什么关系？

6. 事务隔离、并发异常和完整事务重试怎么讲？

7. 行锁、死锁、SKIP LOCKED 和任务队列怎么讲？

8. WAL、Checkpoint、Crash Recovery 和 WAL 归档分别解决什么问题？

9. 分区、继承、TOAST 如何处理大表和大对象？

三、性能篇：从执行计划到证据链

10. 如何阅读执行计划并优化 SQL？

11. 如何建立 PostgreSQL 性能观测和慢 SQL 证据链？

四、后端工程篇：连接池、幂等和业务语义

12. PgBouncer 和连接池语义怎么讲？

13. 如何设计业务一致性、幂等键、ON CONFLICT 和 Outbox？

五、恢复篇：从备份到 PITR

14. 如何设计 PostgreSQL 备份恢复体系？

六、架构篇：复制、高可用与故障切换

15. Streaming Replication、Logical Replication 和 BDR 有什么区别？

16. Patroni、repmgr、Fencing 和接入层如何组成生产级高可用？

七、从简单到困难的回答主线

1. 基础对象主线

2. 并发维护主线

3. 事务与锁主线

4. 性能排查主线

5. 后端工程语义主线

6. 可靠性主线

7. 高可用主线

八、最容易被追问的判断题

九、最后的面试收束

7. 行锁、死锁、`SKIP LOCKED` 和任务队列怎么讲？

13. 如何设计业务一致性、幂等键、`ON CONFLICT` 和 Outbox？