PostgreSQL 架构与存储

理解 PostgreSQL，关键是理解四个词：进程、页、tuple、WAL。

零基础可以先记住：

PostgreSQL 表数据通常存在 heap 文件中，一行被更新后往往会生成新的 tuple 版本，旧版本不会立刻消失，而是等 VACUUM 清理；WAL 负责保证崩溃恢复和复制。

架构概览

mermaid

flowchart TD
    A["客户端连接"] --> B["Postmaster 主进程"]
    B --> C["Backend 进程"]
    C --> D["解析 / 重写 / 优化 / 执行"]
    D --> E["Buffer Cache"]
    E --> F["表和索引页"]
    D --> G["WAL"]
    G --> H["磁盘持久化 / 崩溃恢复 / 复制"]

PostgreSQL 常见是多进程模型：一个连接通常对应一个后端进程。连接很多时，如果没有连接池，会带来较大进程和内存成本，所以生产常配 PgBouncer 这类连接池。

进程模型为什么重要

MySQL 常见是线程模型，PostgreSQL 常见是多进程模型。每个客户端连接通常由一个 backend process 处理。

mermaid

flowchart TD
    A["Postmaster"] --> B["Backend 1"]
    A --> C["Backend 2"]
    A --> D["Backend 3"]
    B --> E["处理连接1"]
    C --> F["处理连接2"]
    D --> G["处理连接3"]

这带来的生产影响：

连接不是越多越好，每个连接都有内存和进程开销。
Java 连接池最大连接数要控制，不能每个服务实例都开几百个连接。
高并发短连接场景常使用 PgBouncer。
排查连接堆积时要看 pg_stat_activity。

常用排查：

sql

select state, count(*)
from pg_stat_activity
group by state;

一条 SQL 的执行流程

mermaid

flowchart TD
    A["SQL 文本"] --> B["Parser 解析"]
    B --> C["Rewriter 重写"]
    C --> D["Planner 生成计划"]
    D --> E["Executor 执行"]
    E --> F["访问表 / 索引 / Buffer"]
    F --> G["返回结果"]

阶段	作用
Parser	词法、语法分析
Rewriter	规则重写，例如视图展开
Planner	基于统计信息选择执行计划
Executor	按计划扫描表、索引、Join、聚合

Planner 为什么可能选错计划

优化器要根据统计信息估算扫描多少行、Join 成本、排序成本。如果统计信息不准，或者数据分布非常倾斜，计划就可能不合适。

mermaid

flowchart TD
    A["SQL"] --> B["读取统计信息"]
    B --> C["估算行数和成本"]
    C --> D["选择 Seq Scan / Index Scan / Join 方式"]
    D --> E["Executor 执行"]

所以 PostgreSQL 慢 SQL 排查要看 EXPLAIN ANALYZE，因为它能同时给出估算和真实执行。

表、页和 Tuple

PostgreSQL 表由数据页组成，页中存放 tuple。tuple 可以理解为“某一行的某个版本”。

mermaid

flowchart TD
    A["Table"] --> B["Page 8KB"]
    B --> C["Tuple 版本 1"]
    B --> D["Tuple 版本 2"]
    B --> E["Tuple 版本 3"]

更新一行时，PostgreSQL 通常不是原地覆盖旧 tuple，而是写入一个新 tuple，旧 tuple 等没有事务需要它后再由 VACUUM 清理。

Heap、Page、Tuple、TID

概念	说明
heap	普通表的数据存储区域
page	8KB 数据页，表和索引都按页组织
tuple	行版本，不只是“逻辑行”
TID / ctid	tuple 的物理位置，类似 `(block, offset)`

查询一行时，底层通常要找到它所在的页，再找到页内 offset。

sql

select ctid, id, asset_no
from asset
where id = 1;

ctid 会随着更新变化，不适合作为业务主键，但能帮助理解 PostgreSQL 的物理位置。

xmin、xmax 和可见性

PostgreSQL tuple 头部会记录事务相关信息，常见是 xmin 和 xmax。

字段	简化理解
`xmin`	创建这个 tuple 的事务 ID
`xmax`	删除或更新这个 tuple 的事务 ID，未删除时通常为空

查询时，PostgreSQL 会根据当前事务快照判断某个 tuple 对当前查询是否可见。

mermaid

flowchart TD
    A["读取 tuple"] --> B["查看 xmin/xmax"]
    B --> C["结合当前事务快照"]
    C --> D{"这个版本是否可见"}
    D -- "可见" --> E["返回该 tuple"]
    D -- "不可见" --> F["跳过或找其他版本"]

这就是 PostgreSQL MVCC 的核心：不是只看一行数据，而是判断某个行版本对当前事务是否可见。

MVCC 和 VACUUM

mermaid

flowchart TD
    A["update 一行"] --> B["生成新 tuple"]
    A --> C["旧 tuple 变成旧版本"]
    C --> D{"还有事务可能看见旧版本吗"}
    D -- "有" --> E["暂时保留"]
    D -- "没有" --> F["VACUUM 清理"]

如果长事务一直不结束，旧版本不能清理，表和索引就会膨胀。PostgreSQL 运维里 VACUUM 很重要，原因就在这里。

update 为什么生成新 tuple

sql

update asset
set owner_id = 2002
where id = 1;

可以简化理解为：

mermaid

flowchart TD
    A["旧 tuple<br/>owner_id=1001"] --> B["update"]
    B --> C["旧 tuple 标记 xmax"]
    B --> D["新 tuple<br/>owner_id=2002"]
    D --> E["索引指向新版本或 HOT 链"]
    C --> F["等待 VACUUM 清理"]

这样做的好处是读写并发更好：老事务仍然可以看到旧版本，新事务可以看到新版本。

代价是旧版本会占空间，必须依赖 VACUUM 清理。

HOT 更新是什么

HOT 是 Heap-Only Tuple。简单理解：如果更新没有修改任何索引列，PostgreSQL 可能只在 heap 页里串起新旧 tuple，不必为每个索引都插入新索引项。

mermaid

flowchart TD
    A["更新非索引列"] --> B{"同一页有空间吗"}
    B -- "有" --> C["生成 HOT tuple"]
    C --> D["索引仍指向 HOT 链入口"]
    B -- "没有" --> E["普通更新，索引也要维护"]

HOT 的意义：

减少索引膨胀。
降低更新成本。
提升频繁更新非索引列的性能。

如果表的 fillfactor 太满，页里没有空间放新版本，HOT 机会会减少。

Autovacuum 做什么

Autovacuum 是自动清理和统计维护机制，不是“可有可无的后台任务”。

它主要做：

清理 dead tuple。
更新统计信息，帮助优化器估算。
冻结老事务 ID，防止事务 ID 回卷风险。

mermaid

flowchart TD
    A["表发生大量 update/delete"] --> B["产生 dead tuple"]
    B --> C["Autovacuum 判断阈值"]
    C --> D["清理 dead tuple"]
    D --> E["更新统计信息"]
    E --> F["减少膨胀并改善计划"]

如果 Autovacuum 被关闭、参数太保守或长事务阻挡清理，就会出现表越来越大、查询越来越慢。

Visibility Map 和 Index Only Scan

PostgreSQL 的 Index Only Scan 不只是“字段都在索引里”就一定完全不访问 heap。它还要确认 heap page 上的 tuple 对所有事务都可见。

Visibility Map 用来标记某个 heap page 是否 all-visible。

mermaid

flowchart TD
    A["Index Only Scan"] --> B["索引包含查询字段"]
    B --> C{"Visibility Map 标记 all-visible 吗"}
    C -- "是" --> D["可直接从索引返回"]
    C -- "否" --> E["访问 heap 检查可见性"]

这解释了为什么 VACUUM 对 Index Only Scan 有帮助：VACUUM 能更新 Visibility Map。

WAL

WAL 是 Write-Ahead Logging，先写日志，再认为数据修改可靠。

mermaid

flowchart TD
    A["修改数据页"] --> B["生成 WAL 记录"]
    B --> C["事务提交时 WAL 按策略落盘"]
    C --> D["数据页稍后刷盘"]
    D --> E["宕机后用 WAL 恢复"]

WAL 的作用：

崩溃恢复。
复制。
Point-in-Time Recovery。
逻辑解码和数据同步。

WAL 和数据页刷盘

PostgreSQL 提交事务时，不一定把数据页立刻刷盘。它依赖 WAL 先持久化，宕机后再重放 WAL 恢复数据页。

mermaid

flowchart TD
    A["修改 Buffer 中的数据页"] --> B["生成 WAL 记录"]
    B --> C["commit 等待 WAL 按策略落盘"]
    C --> D["返回提交成功"]
    D --> E["后台稍后刷脏页"]
    E --> F["宕机后可用 WAL 重放"]

这和 MySQL redo、SQL Server transaction log、Oracle redo 在思想上类似：先用顺序日志保证恢复，再异步写数据页。

Checkpoint

Checkpoint 会把一部分脏页写入磁盘，并记录恢复起点，减少崩溃恢复时需要重放的 WAL 量。

mermaid

flowchart TD
    A["持续写入产生 WAL 和脏页"] --> B["Checkpoint 触发"]
    B --> C["后台写出脏页"]
    C --> D["记录检查点位置"]
    D --> E["缩短恢复需要重放的 WAL 范围"]

Checkpoint 太频繁会带来写 IO 峰值；太少则恢复时间和 WAL 保留压力可能增加。生产要结合写入压力、磁盘 IO、恢复目标调整。

为什么更新会导致膨胀

sql

update asset
set owner_id = 2002
where id = 1;

在 PostgreSQL 中，这可能产生新版本 tuple。旧版本不会立即消失。大量更新、删除后，如果 VACUUM 跟不上，就会出现：

表膨胀。
索引膨胀。
扫描更多页。
查询变慢。

商业场景：采集表越跑越慢

医疗资产采集平台有一张 asset_collect_task，任务状态不断从 待采集 -> 采集中 -> 成功/失败 更新。

如果每天大量更新：

sql

update asset_collect_task
set status = 'SUCCESS',
    updated_at = now()
where id = 1001;

可能出现：

每次更新生成新 tuple。
旧 tuple 变成 dead tuple。
如果长事务存在，VACUUM 清不掉。
表和索引膨胀。
原本很快的任务列表扫描更多页。

排查：

sql

select
  relname,
  n_live_tup,
  n_dead_tup,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
from pg_stat_user_tables
where relname = 'asset_collect_task';

如果 n_dead_tup 很高，且 last_autovacuum 很久没运行，要继续查长事务和 autovacuum 配置。

排查方向

现象	可能原因
表越来越大	dead tuple 没及时清理
查询逐渐变慢	表或索引膨胀、统计信息不准
VACUUM 无法回收	有长事务阻止旧版本清理
连接很多	缺少连接池或连接泄漏

常用排查 SQL

查看长事务：

sql

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

查看锁等待：

sql

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

查看表 dead tuple：

sql

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

查看 SQL 计划：

sql

explain (analyze, buffers)
select id, asset_no
from asset
where owner_id = 1001
order by created_at desc
limit 20;

常见坑

坑	后果	正确理解
连接数开太大	进程和内存成本高	应用连接池限流，必要时 PgBouncer
忽略 VACUUM	表和索引膨胀	Autovacuum 是核心机制
长事务不提交	dead tuple 无法清理	监控 `xact_start`
认为 Index Only Scan 不访问表	计划误判	Visibility Map 未标记时仍可能查 heap
JSONB 滥用	执行计划和约束变复杂	核心字段列化
只看耗时不看 Buffers	找不到读页成本	`explain analyze buffers`

面试标准回答

text

PostgreSQL 常见是多进程架构，客户端连接由后端进程处理，所以生产要控制连接数并常配连接池。SQL 会经过解析、重写、优化和执行。数据以 8KB 页为单位存储，普通表通常是 heap，页中保存 tuple，tuple 是某一行的某个版本。PostgreSQL 的 MVCC 通过 xmin、xmax 和事务快照判断 tuple 可见性，更新通常生成新 tuple，旧 tuple 在没有事务需要后由 VACUUM 清理，因此长事务会阻止旧版本清理，引发表和索引膨胀。WAL 是预写日志，用于崩溃恢复、复制、时间点恢复和逻辑解码。Index Only Scan 还依赖 Visibility Map，Autovacuum 对清理旧版本、更新统计信息和维护可见性非常关键。

JDK 8/11/17/21 LTS演进

线程池

PostgreSQL 架构与存储 ​

架构概览 ​

进程模型为什么重要 ​

一条 SQL 的执行流程 ​

Planner 为什么可能选错计划 ​

表、页和 Tuple ​

Heap、Page、Tuple、TID ​

xmin、xmax 和可见性 ​

MVCC 和 VACUUM ​

update 为什么生成新 tuple ​

HOT 更新是什么 ​

Autovacuum 做什么 ​

Visibility Map 和 Index Only Scan ​

WAL ​

WAL 和数据页刷盘 ​

Checkpoint ​

为什么更新会导致膨胀 ​

商业场景：采集表越跑越慢 ​

排查方向 ​

常用排查 SQL ​

常见坑 ​

面试标准回答 ​

PostgreSQL 架构与存储

架构概览

进程模型为什么重要

一条 SQL 的执行流程

Planner 为什么可能选错计划

表、页和 Tuple

Heap、Page、Tuple、TID

xmin、xmax 和可见性

MVCC 和 VACUUM

update 为什么生成新 tuple

HOT 更新是什么

Autovacuum 做什么

Visibility Map 和 Index Only Scan

WAL

WAL 和数据页刷盘

Checkpoint

为什么更新会导致膨胀

商业场景：采集表越跑越慢

排查方向

常用排查 SQL

常见坑

面试标准回答