PostgreSQL 架构原理详解：进程、存储、MVCC、WAL、索引与备份恢复

本文版本基线：PostgreSQL 18 当前稳定系列，撰写日期为 2026-07-28。PostgreSQL 19 此时仍处于 Beta 阶段，因此正文不以 19 的实验特性作为生产结论。

本文中的“PG”“PostgreSQL”“pgsql”均指 PostgreSQL。

PostgreSQL 并不是一个“收到 SQL 后直接读写文件”的程序。它更像一套由多个独立进程、共享内存、查询执行器、事务可见性规则、缓存管理器、WAL 日志与后台维护进程共同组成的数据库操作系统。

理解 PostgreSQL，最好抓住三条主线：

SQL 如何被解析、优化并执行；
一行数据如何被组织、缓存、修改并持久化；
多个事务如何在并发下看到彼此的数据，同时保证崩溃后可恢复。

本文会沿着这三条主线，把 PostgreSQL 的整体架构、进程架构、存储引擎、备份恢复、事务、索引和内部数据结构串成一张完整地图。

1. 先建立一张 PostgreSQL 全景图

从宏观上看，PostgreSQL 可以拆成六层：

客户端与协议层；
主进程与后端进程层；
SQL 编译与执行层；
事务、锁与并发控制层；
缓冲区、WAL 与存储访问层；
文件系统与持久化介质层。

flowchart TB
    subgraph Client[客户端层]
        APP[业务应用]
        PSQL[psql / 管理工具]
        DRIVER[JDBC / libpq / ORM]
        POOL[连接池]
    end

    subgraph Server[PostgreSQL 服务端]
        PM[Postmaster 主进程<br/>监听、认证入口、进程监管]

        subgraph Processes[服务端进程层]
            BE1[Client Backend 1]
            BE2[Client Backend 2]
            BG[后台维护进程]
            REP[复制相关进程]
            PW[并行查询 Worker]
            AIO[PG18 I/O Worker]
        end

        subgraph Query[SQL 处理层]
            PARSE[Parser / Analyzer]
            REWRITE[Rewriter]
            PLAN[Planner / Optimizer]
            EXEC[Executor]
        end

        subgraph Tx[事务与并发控制]
            MVCC[MVCC / Snapshot]
            LOCK[Lock Manager]
            SSI[Serializable SSI]
            XACT[Transaction Status]
        end

        subgraph Memory[共享内存与缓存]
            SB[Shared Buffers]
            WB[WAL Buffers]
            PROC[ProcArray / Lock Tables]
        end

        subgraph Storage[存储访问层]
            TAM[Table Access Method]
            IAM[Index Access Method]
            BUF[Buffer Manager]
            SMGR[Storage Manager]
            WAL[WAL Manager]
        end
    end

    subgraph Disk[持久化层]
        DATA[数据文件 / 表空间]
        WALFILE[pg_wal]
        XACTFILE[pg_xact / pg_multixact]
        ARCHIVE[WAL Archive / Backup]
    end

    APP --> POOL
    PSQL --> PM
    DRIVER --> POOL --> PM
    PM --> BE1
    PM --> BE2
    PM --> BG
    PM --> REP
    BE1 --> PARSE --> REWRITE --> PLAN --> EXEC
    BE2 --> PARSE
    EXEC --> MVCC
    EXEC --> LOCK
    EXEC --> TAM
    EXEC --> IAM
    TAM --> BUF
    IAM --> BUF
    BUF <--> SB
    BUF --> SMGR --> DATA
    WAL <--> WB
    WAL --> WALFILE
    XACT --> XACTFILE
    WALFILE --> ARCHIVE
    AIO --> BUF

可以把一次请求简化为：

客户端连接
  -> Postmaster 接受连接并创建 Backend 进程
  -> Backend 解析 SQL
  -> 重写查询
  -> 优化器生成执行计划
  -> Executor 按计划请求元组
  -> MVCC 判断元组是否可见
  -> Buffer Manager 从 Shared Buffers 或磁盘取得页面
  -> 修改时先生成 WAL
  -> 提交时确保必要 WAL 已持久化
  -> 后台进程随后将脏页写入数据文件

其中最容易误解的一点是：PostgreSQL 的核心服务器不是“一个进程里开很多业务线程”，而是典型的多进程架构。

2. PostgreSQL 的“线程架构”：准确说是多进程架构

2.1 Process per connection

PostgreSQL 采用客户端/服务器模型。服务器启动后，首先存在一个主进程，通常称为：

postgres；
postmaster；
server supervisor process。

它负责：

监听 TCP 或 Unix Domain Socket；
接受新连接；
启动认证流程；
为客户端连接创建独立 Backend 进程；
启动和监管各种后台进程；
在子进程异常退出时执行恢复或整体重启策略。

每个普通客户端连接通常对应一个独立的 client backend process。这个 Backend 在连接生命周期内完成：

协议解析；
SQL 解析、重写和规划；
执行计划；
事务管理；
访问共享缓存；
与客户端传输结果。

flowchart LR
    C1[Client 1] --> PM[Postmaster]
    C2[Client 2] --> PM
    C3[Client 3] --> PM

    PM --> B1[Backend Process 1]
    PM --> B2[Backend Process 2]
    PM --> B3[Backend Process 3]

    B1 --> SHM[(Shared Memory)]
    B2 --> SHM
    B3 --> SHM

    SHM --> SB[Shared Buffers]
    SHM --> LOCKS[Lock Tables]
    SHM --> PROC[ProcArray]
    SHM --> WALBUF[WAL Buffers]

这与 MySQL 常见的“一个服务器进程中为连接分配线程”并不相同。

2.2 为什么采用多进程

多进程模型有几个明显特征：

隔离性较强

单个 Backend 发生普通进程级错误，不会直接破坏其他 Backend 的私有地址空间。

但要注意：各 Backend 仍共享数据库共享内存。PostgreSQL 将某些异常视为可能污染共享状态，因此一个 Backend 出现严重崩溃时，Postmaster 可能终止其他子进程，并通过 crash recovery 重建一致状态。不能简单理解为“某个进程崩了，其他进程一定完全不受影响”。

利用操作系统进程调度

每个连接由操作系统独立调度。进程之间通过以下机制协作：

共享内存；
信号；
信号量；
latch；
pipe；
lightweight lock；
heavyweight lock。

连接成本更值得关注

连接不只是一个 socket，还会带来：

Backend 进程；
事务和锁管理槽位；
本地内存上下文；
catalog cache；
可能被并行放大的 work_mem；
进程切换与调度成本。

所以 PostgreSQL 的常规生产架构通常不会让数千个应用请求直接等价为数千条长期数据库连接，而是通过应用连接池或外部连接池控制活跃 Backend 数量。

max_connections 不是“越大吞吐越高”的旋钮。超过 CPU、内存和存储能力后，更多 Backend 往往只会制造排队、上下文切换与内存压力。

2.3 PostgreSQL 主要后台进程

不同配置和版本下进程会变化。PostgreSQL 18 常见进程如下：

进程	主要职责
Postmaster	监听连接，创建并监管子进程
Client Backend	为一个客户端连接执行 SQL 与事务
Checkpointer	在检查点过程中将脏页刷入数据文件并写检查点记录
Background Writer	提前写出部分脏缓冲页，降低前台请求寻找可用缓冲区时的突发写入
WAL Writer	将 WAL Buffers 中的日志逐步写入 WAL 文件
Autovacuum Launcher	调度自动清理任务
Autovacuum Worker	执行 VACUUM、ANALYZE、冻结等维护工作
Archiver	将完成的 WAL 段归档到外部位置
Startup Process	启动恢复、崩溃恢复或备库回放 WAL
WAL Sender	主库向备库或逻辑订阅端发送 WAL
WAL Receiver	备库从上游接收 WAL
Logical Replication Launcher/Worker	逻辑复制调度与应用
Parallel Worker	执行并行扫描、连接、聚合等计划片段
WAL Summarizer	生成 WAL 摘要，为增量物理备份等能力提供基础
I/O Worker	PostgreSQL 18 的 worker 异步 I/O 后端

flowchart TB
    PM[Postmaster]

    PM --> CLIENT[Client Backends]
    PM --> CKPT[Checkpointer]
    PM --> BGW[Background Writer]
    PM --> WALW[WAL Writer]
    PM --> AVL[Autovacuum Launcher]
    AVL --> AVW1[Autovacuum Worker]
    AVL --> AVW2[Autovacuum Worker]
    PM --> ARC[Archiver]
    PM --> STARTUP[Startup Process]
    PM --> WALS[WAL Sender]
    PM --> WALR[WAL Receiver]
    PM --> LRL[Logical Replication Launcher]
    LRL --> LRW[Logical Replication Worker]
    CLIENT --> PARALLEL[Parallel Workers]
    PM --> SUM[WAL Summarizer]
    PM --> IOW[I/O Workers]

2.4 PG18 的异步 I/O 是否意味着 PostgreSQL 变成多线程

不是。

PostgreSQL 18 引入了新的异步 I/O 基础设施，并可以选择不同 I/O 方法，例如：

worker：由专用 I/O worker 进程执行异步操作；
io_uring：在支持的平台上使用 Linux io_uring；
sync：同步 I/O。

这改变了部分读取、预取和后台处理的 I/O 调度方式，但 PostgreSQL 的连接处理、查询执行、事务管理仍然建立在多进程架构上。

更准确的表达是：

PostgreSQL 18 是“多进程架构 + 共享内存 + 可并行执行 + 新异步 I/O 基础设施”，而不是传统意义上的“单进程多线程数据库”。

3. 连接建立与 SQL 执行链路

3.1 建立连接

连接建立大致经历：

客户端向监听地址发起连接；
Postmaster 接受连接；
服务端建立 Backend；
根据 pg_hba.conf 等规则选择认证方式；
Backend 初始化用户、数据库、搜索路径、GUC 等会话状态；
开始处理 PostgreSQL 前后端协议消息。

客户端可以使用：

Simple Query Protocol：一次发送一段 SQL 文本；
Extended Query Protocol：拆分为 Parse、Bind、Describe、Execute、Sync 等阶段；
Prepared Statement：复用已解析或已规划的信息，并在通用计划与定制计划之间选择。

3.2 一条 SELECT 的内部阶段

sequenceDiagram
    participant C as Client
    participant B as Backend
    participant P as Parser/Analyzer
    participant R as Rewriter
    participant O as Planner/Optimizer
    participant E as Executor
    participant M as MVCC
    participant BM as Buffer Manager
    participant D as Data/Index Files

    C->>B: SELECT ...
    B->>P: 词法、语法、语义分析
    P-->>B: Parse Tree / Query Tree
    B->>R: 应用规则、视图展开
    R-->>B: Rewritten Query Tree
    B->>O: 枚举路径并估算成本
    O-->>B: Chosen Plan Tree
    B->>E: 初始化执行器
    loop 拉取下一批元组
        E->>BM: 请求表页或索引页
        alt Shared Buffers 命中
            BM-->>E: 返回缓存页面
        else 未命中
            BM->>D: 读取页面
            D-->>BM: 页面数据
            BM-->>E: 返回缓存页面
        end
        E->>M: 判断元组对当前快照是否可见
        M-->>E: 可见 / 不可见
    end
    E-->>B: Result Tuples
    B-->>C: RowDescription / DataRow / CommandComplete

Parser 与 Analyzer

Parser 负责把 SQL 文本转成语法树，Analyzer 在数据库目录中解析：

表和列；
数据类型；
函数和操作符；
隐式类型转换；
权限与语义合法性。

结果不再只是字符串，而是带有数据库对象标识和类型信息的 Query Tree。

Rewriter

重写器会处理：

视图展开；
rule system；
某些行级安全策略；
将一个原始查询变为一个或多个重写后的 Query Tree。

PostgreSQL 的视图通常不是在 Executor 里临时“打开”，而是在重写阶段将视图定义展开到查询树中。

Planner / Optimizer

优化器不会直接选择“看起来最短”的 SQL 写法，而是生成多种 Path，并根据统计信息估算：

顺序扫描还是索引扫描；
Index Scan、Index Only Scan 还是 Bitmap Heap Scan；
Nested Loop、Hash Join 还是 Merge Join；
连接顺序；
排序、聚合和去重方式；
是否并行；
预计行数、I/O 成本、CPU 成本和内存成本。

典型内部对象可以概括为：

Query Tree
  -> RelOptInfo
      -> Path / IndexPath / JoinPath / AggPath ...
  -> cheapest path
  -> Plan Tree

当连接表数量非常多时，穷举连接顺序会呈组合爆炸，PostgreSQL 可在达到阈值后使用 GEQO 遗传算法搜索近似优解。

Executor

Executor 初始化 Plan Tree 后，通常按火山模型式的 next tuple 接口驱动各计划节点：

Limit
  -> Sort
      -> Hash Join
          -> Seq Scan orders
          -> Hash
              -> Index Scan customers

上层节点向下层节点请求元组，下层节点返回一条或一批结果。不同节点维护各自的执行状态，例如：

扫描游标；
Hash Table；
Sort State；
Aggregate State；
Tuple Slot；
参数与表达式上下文。

3.3 优化器只相信统计信息，不会读懂业务愿望

优化器的估算主要来自：

pg_class 的行数、页数估计；
pg_statistic / pg_stats；
空值比例；
distinct 值数量；
Most Common Values；
Histogram Bounds；
列相关性；
扩展统计信息中的多列依赖、MCV 和 ndistinct。

当统计信息过旧或无法表达数据分布时，常见结果是：

估算 100 行，实际 1,000,000 行
  -> 选择 Nested Loop
  -> 内层被执行大量次数
  -> 查询突然从毫秒级变成分钟级

因此 EXPLAIN (ANALYZE, BUFFERS, WAL) 的第一要务不是只看“有没有走索引”，而是比较每个节点的：

estimated rows；
actual rows；
loops；
shared hit/read/dirtied/written；
temp read/write；
WAL records/bytes；
实际执行时间。

4. PostgreSQL 内存架构

PostgreSQL 的内存不能只看 shared_buffers。它至少分为：

数据库共享内存；
每个 Backend 的私有内存；
操作系统页缓存；
临时文件与内存映射区域。

flowchart TB
    subgraph SHARED[PostgreSQL 共享内存]
        SB[Shared Buffers<br/>缓存表页和索引页]
        WB[WAL Buffers<br/>缓存 WAL 记录]
        LT[Lock Tables]
        PA[ProcArray / PGPROC]
        SI[Shared Invalidation]
        ST[统计与控制结构]
    end

    subgraph LOCAL[每个 Backend 的私有内存]
        MC[Memory Contexts]
        WM[work_mem<br/>每个排序、Hash 等操作]
        MW[maintenance_work_mem]
        TB[temp_buffers<br/>临时表页面]
        CACHE[Catalog / Relation Cache]
        PLAN[Parser / Planner / Executor State]
    end

    subgraph OS[操作系统]
        PC[OS Page Cache]
        VM[Virtual Memory]
        FS[File System]
    end

    LOCAL <--> SHARED
    SB <--> PC
    WB --> FS
    PC <--> FS

4.1 Shared Buffers

shared_buffers 是 PostgreSQL 自己管理的数据页缓存。表页和索引页进入共享缓冲池后，不同 Backend 可以复用。

一个 Buffer 大致包含两类信息：

Buffer Descriptor：标签、引用计数、使用次数、脏页标记、锁状态；
Buffer Page：实际 8KB 页面内容。

页面通过类似以下 BufferTag 定位：

tablespace OID
+ database OID
+ relation file node
+ fork number
+ block number

缓冲区替换使用近似 clock-sweep 机制，而不是简单 LRU。一个页面被频繁使用时会提高 usage count；扫描器寻找可淘汰 Buffer 时逐步降低 usage count，最终选择无引用且使用次数为零的页面。

4.2 WAL Buffers

WAL Buffers 暂存 Backend 生成的 WAL record。提交时，事务通常只要求与自身提交有关的 WAL 被刷到持久介质，而不要求对应数据页立即写入表文件。

这正是 WAL 能降低随机数据页同步写压力的基础。

4.3 work_mem 不是“每连接只分配一次”

work_mem 的危险点在于：它是每个可能使用工作内存的执行节点的上限，不是每个连接的一次性总额。

例如一个查询可能同时存在：

两个 Hash Join；
一个 Hash Aggregate；
三个 Sort；
并行查询的多个 worker。

粗略风险模型是：

潜在内存压力
≈ 活跃查询数
× 每个查询的内存型节点数
× 并行进程数
× work_mem

实际分配是按需发生，并非每次都触顶，但把 work_mem 全局设置得过大，仍可能在并发峰值时造成内存雪崩。

4.4 Memory Context

PostgreSQL 不倾向于在每个代码路径上手工逐块释放内存，而是使用层次化 Memory Context：

TopMemoryContext
  -> CacheMemoryContext
  -> MessageContext
  -> PortalContext
      -> QueryContext
          -> ExecutorState
          -> ExprContext

一个语句或事务结束时，可以整体释放所属 Context。优点是：

生命周期清晰；
异常跳转时容易统一清理；
减少复杂执行路径中的零散内存管理错误。

4.5 为什么 PostgreSQL 与 OS 会“双重缓存”

一个数据页可能同时存在于：

PostgreSQL Shared Buffers；
操作系统 Page Cache。

这不完全是浪费。PostgreSQL 负责数据库语义层的页面状态、锁、脏页与替换策略；OS 负责文件系统缓存、预读、写回和设备调度。现代 PostgreSQL 仍依赖操作系统缓存，不应把机器全部内存都分给 shared_buffers。

5. PostgreSQL 的存储引擎与访问方法

5.1 PostgreSQL 有没有“存储引擎”

答案不能简单说“有”或“没有”。

与 MySQL 通过 InnoDB、MyISAM 等引擎进行明显产品级切换不同，PostgreSQL 长期以来默认使用自身的 heap table、WAL、MVCC 和 buffer manager，用户日常不会在多个内置事务型存储引擎间选择。

但从内核扩展架构看，PostgreSQL 已经提供：

Table Access Method，表访问方法；
Index Access Method，索引访问方法。

表访问方法定义关系如何完成：

顺序扫描；
并行扫描；
元组插入、更新和删除；
锁元组；
VACUUM；
ANALYZE 采样；
表重写；
TID 访问。

索引访问方法则定义：

如何构建索引；
如何插入和扫描索引项；
支持哪些操作符策略；
是否支持有序扫描、唯一性、并行扫描、Index Only Scan 等能力。

默认表访问方法是 heap。扩展可以注册新的访问方法，但这不意味着第三方 Table AM 能自动绕开 PostgreSQL 的所有事务、WAL、快照和执行器约束。

5.2 存储访问调用链

flowchart TB
    SQL[Executor]
    TAM[Table Access Method API]
    IAM[Index Access Method API]
    HEAP[heapam]
    BTREE[nbtree / gin / gist / brin ...]
    BUF[Buffer Manager]
    SMGR[Storage Manager]
    FD[File Descriptor / VFS Layer]
    FS[File System]
    DEV[SSD / NVMe / SAN]

    SQL --> TAM --> HEAP --> BUF
    SQL --> IAM --> BTREE --> BUF
    BUF --> SMGR --> FD --> FS --> DEV

从职责上看：

Executor 关心“扫描或修改元组”；
Table AM 关心“表以何种方式提供元组”；
Index AM 关心“索引如何定位 TID”；
Buffer Manager 关心“数据库页面是否在缓存、是否脏、如何锁定”；
Storage Manager 关心“关系文件和 block I/O”；
文件系统和块设备负责最终持久化。

5.3 Heap 不是堆数据结构

PostgreSQL 的 heap table 中，“heap”表示数据行不按某个键永久排序存放，而不是算法课中的二叉堆。

表中的元组大致按插入和页面空间情况放入 heap page。索引中的叶子项保存键值和指向 heap tuple 的 TID。

所以 PostgreSQL 的普通索引是二级索引：

index key
  -> heap TID: (block number, item offset)
  -> heap page
  -> heap tuple
  -> MVCC visibility check

索引本身通常不能完全决定元组对当前事务是否可见，因为 MVCC 信息主要在 heap tuple header 中。Index Only Scan 能减少 heap 访问，但它仍依赖 Visibility Map 判断某个 heap page 是否已全部可见。

6. 物理存储架构：从集群到元组

6.1 Cluster、Database、Schema、Relation 的关系

PostgreSQL 术语中的 database cluster，不是分布式集群，而是由一个 PostgreSQL 实例管理的一整套数据库集合，通常对应一个 PGDATA 数据目录。

逻辑层次可以理解为：

flowchart TB
    CLUSTER[Database Cluster / PGDATA]
    CLUSTER --> DB1[Database A]
    CLUSTER --> DB2[Database B]
    DB1 --> S1[Schema public]
    DB1 --> S2[Schema finance]
    S2 --> T1[Table]
    S2 --> I1[Index]
    S2 --> SEQ[Sequence]
    S2 --> MV[Materialized View]

一个 PostgreSQL 实例管理一个 database cluster；
一个 cluster 可以包含多个 database；
database 之间通常不能在普通 SQL 中直接跨库 JOIN；
schema 是 database 内的命名空间；
table、index、sequence、materialized view 等都属于 relation 或与 relation 机制密切相关。

6.2 PGDATA 中的关键目录

常见目录和文件如下：

目录或文件	作用
`base/`	默认表空间中各数据库的数据文件
`global/`	集群级系统表，例如与整个 cluster 相关的 catalog
`pg_wal/`	WAL 段文件
`pg_xact/`	事务提交、回滚状态
`pg_multixact/`	MultiXact 成员与偏移信息，常用于多个事务共享行锁状态
`pg_tblspc/`	指向用户表空间目录的符号链接
`pg_replslot/`	复制槽持久化状态
`pg_logical/`	逻辑解码相关状态
`pg_stat/`	永久统计文件
`pg_stat_tmp/`	临时统计信息
`pg_subtrans/`	子事务状态
`pg_commit_ts/`	可选的事务提交时间戳
`pg_twophase/`	两阶段提交的 prepared transaction 状态
`postgresql.conf`	主要配置文件，实际位置也可外置
`pg_hba.conf`	客户端认证规则
`PG_VERSION`	数据目录主版本标识
`global/pg_control`	检查点、系统标识、恢复所需的关键控制信息

不要在 PostgreSQL 运行时手工修改、复制或删除这些内部文件。数据库文件不是普通业务文件，绕过数据库内核操作，很容易制造无法恢复的一致性问题。

6.3 Relation、Fork、Segment、Page

一张表不是永远只对应一个文件。更准确的物理层级是：

flowchart TB
    R[Relation]
    R --> MAIN[Main Fork<br/>实际表或索引页面]
    R --> FSM[Free Space Map Fork<br/>_fsm]
    R --> VM[Visibility Map Fork<br/>_vm]
    R --> INIT[Initialization Fork<br/>_init，仅 unlogged 关系]

    MAIN --> S0[Segment 0]
    MAIN --> S1[Segment 1]
    MAIN --> SN[Segment N]

    S0 --> P0[Page 0]
    S0 --> P1[Page 1]
    S0 --> PX[Page ...]

    P1 --> LP[Line Pointers]
    P1 --> TUPLE[Heap Tuples]

Fork

一张普通 heap 表通常可能拥有：

main fork：保存真实 heap page；
FSM fork：保存页面剩余空间的近似信息；
VM fork：保存页面 all-visible 和 all-frozen 标志。

Unlogged relation 还会有 init fork，用于崩溃后重新初始化，因为 unlogged relation 不依靠完整 WAL 保证数据恢复。

Segment

关系超过单个文件大小阈值后，会被拆分为多个 segment。默认情况下，关系文件常按约 1GB 分段：

这里的数字通常是 relfilenode，而不是业务表名。表经过 VACUUM FULL、CLUSTER、某些 ALTER TABLE 或重写操作后，relfilenode 可能变化。

可以通过以下方式查看关系文件路径：

1	`SELECT pg_relation_filepath('public.orders');`

Page / Block

PostgreSQL 默认数据库页大小通常为 8KB，页是 Buffer Manager、WAL full-page image 和磁盘 block 访问的核心单位。

页大小是编译期参数，绝大多数发行版和生产部署使用 8KB。

6.4 Heap Page 的内部布局

一个普通 heap page 可以抽象为：

flowchart TB
    PAGE[8KB Heap Page]
    PAGE --> HDR[PageHeaderData<br/>LSN、校验和、lower、upper、special 等]
    PAGE --> LP[ItemIdData 数组<br/>Line Pointer 1..N]
    PAGE --> FREE[Free Space]
    PAGE --> ITEMS[Tuple Data<br/>从页尾向前增长]
    PAGE --> SPECIAL[Special Space<br/>Heap 页通常为空，索引页可使用]

    LP --> I1[Offset + Length + Flags]
    I1 --> T1[HeapTupleHeader + Null Bitmap + User Data]

页面两端会向中间增长：

页头后面的 line pointer 数组向后增长；
tuple data 从页尾向前增长；
两者之间是可用空间。

这种布局有一个重要好处：元组在页内移动时，索引所指向的 line pointer 位置可以保持稳定。

PageHeaderData

页头包含的核心信息包括：

pd_lsn：最后修改该页面的 WAL LSN；
pd_checksum：启用数据校验和时的页面校验值；
pd_lower：line pointer 数组末尾；
pd_upper：tuple data 起点；
pd_special：特殊区域起点；
页面大小与版本信息；
剪枝提示等标志。

pd_lsn 对 WAL 的 write-ahead 约束非常关键：在数据页刷盘前，相关 WAL 必须先持久化到至少对应 LSN。

ItemIdData / Line Pointer

每个 line pointer 通常只有少量字节，记录：

元组在页内的偏移；
元组长度；
状态，例如 normal、redirect、dead、unused。

TID 中的 offset number 指向的不是“第 N 行业务数据”，而是 page 内第 N 个 line pointer。

6.5 Heap Tuple Header

每个 heap tuple 都带有 MVCC 和物理定位所需的头部信息，常见字段包括：

字段	含义
`t_xmin`	创建该元组版本的事务 ID
`t_xmax`	删除、更新或锁定该元组版本的事务或 MultiXact ID
`t_cid`	同一事务内部的 command ID 相关信息
`t_ctid`	当前元组位置，或更新后指向新版本的 TID
`t_infomask`	null、锁状态、事务状态 hint 等标志
`t_hoff`	用户数据在元组中的起始位置
Null Bitmap	标记哪些列为 NULL

因此一行逻辑记录可能有多个物理版本：

订单 1001 的旧版本
  xmin = 500
  xmax = 620
  ctid = (42, 9)

订单 1001 的新版本
  xmin = 620
  xmax = 0
  ctid = (42, 9)

ctid 可以用于诊断，但不能当作稳定业务主键，因为 UPDATE、表重写和 VACUUM FULL 都可能改变物理位置。

6.6 一条记录不能跨普通数据页：TOAST 如何处理大字段

PostgreSQL 普通 heap tuple 不能直接跨越多个 heap page。当一行包含大文本、JSONB、数组、bytea 等变长字段时，会使用 TOAST：

压缩字段；
将大字段移到该表对应的 TOAST 表；
在主表元组中保存 TOAST pointer；
将外置值切成约 2KB 的 chunk 存储。

flowchart LR
    ROW[主表 Heap Tuple]
    ROW --> SMALL[id / status / small columns]
    ROW --> PTR[TOAST Pointer]
    PTR --> TT[TOAST Table]
    TT --> C1[Chunk 0]
    TT --> C2[Chunk 1]
    TT --> C3[Chunk 2]

列的 storage strategy 常见有：

策略	行为
`PLAIN`	不压缩、不外置，适合固定长度或必须内联的类型
`EXTENDED`	允许压缩和外置，常见默认策略
`EXTERNAL`	允许外置，但避免压缩，可能有利于某些子串访问
`MAIN`	优先压缩并尽量留在主表，必要时仍可能外置

TOAST 对业务是透明的，但并非没有成本：

读取大字段可能产生额外 TOAST 表访问；
更新小列时是否重写大字段取决于元组和 TOAST 值复用情况；
宽表会降低每页元组数量；
SELECT * 可能无意中触发大量大字段反 TOAST。

6.7 Free Space Map

FSM 用于近似记录各 heap page 的剩余空间，帮助 INSERT 或 UPDATE 快速找到可能放得下新元组的页面。

它不是每次都精确记录字节数，而是采用紧凑的树状结构和分级值：

flowchart TB
    ROOT[FSM Root<br/>子树最大空闲空间]
    ROOT --> N1[Internal Node]
    ROOT --> N2[Internal Node]
    N1 --> P0[Heap Page 0 的空间类别]
    N1 --> P1[Heap Page 1 的空间类别]
    N2 --> P2[Heap Page 2 的空间类别]
    N2 --> P3[Heap Page 3 的空间类别]

查询“至少需要 X 空间的页面”时，可以沿树寻找满足条件的叶子项，而不必扫描整张表。

6.8 Visibility Map

VM 为每个 heap page 维护两个关键 bit：

all-visible：该页所有元组对所有当前和未来事务都可见；
all-frozen：该页所有元组已不再需要未来 anti-wraparound vacuum 冻结处理。

它的作用包括：

VACUUM 跳过不需要处理的页面；
Index Only Scan 判断是否可以不访问 heap page；
冻结与反事务 ID 回卷维护。

DML 会保守地清除相应 VM bit，VACUUM 在确认安全后设置 bit。

6.9 Buffer、Page、Tuple 与 TID 的关系

flowchart LR
    REL[Relation]
    REL --> BLOCK[Block Number]
    BLOCK --> TAG[BufferTag]
    TAG --> BUF[Shared Buffer]
    BUF --> PAGE[Page]
    PAGE --> LP[ItemId / Offset Number]
    LP --> TUPLE[Heap Tuple]
    TID[TID = Block Number + Offset Number] --> BLOCK
    TID --> LP

这组结构是理解 PostgreSQL 的核心：

Relation 确定对象；
Block Number 确定对象中的页面；
BufferTag 在共享缓存中定位页面；
Offset Number 定位 page 中的 line pointer；
TID 将 block 和 offset 组合起来；
索引叶子项通常最终指向 TID。

7. 事务系统：ACID、MVCC、锁与可见性

7.1 PostgreSQL 如何实现 ACID

属性	PostgreSQL 的主要实现机制
Atomicity 原子性	事务状态、WAL、回滚可见性、子事务与恢复机制
Consistency 一致性	数据类型、约束、触发器、外键、应用规则以及事务原子性共同保证
Isolation 隔离性	MVCC Snapshot、行锁、表锁、Predicate Lock、SSI
Durability 持久性	WAL、fsync、检查点、崩溃恢复、复制与归档策略

需要注意：数据库的一致性不是数据库自动理解所有业务规则。数据库能保证的是已声明约束和事务语义，不会自动知道“账户余额不能为负”之类未建模的业务条件。

7.2 MVCC 的核心：逻辑行与物理版本分离

在 PostgreSQL 中，UPDATE 通常不是原地覆盖旧元组，而是：

创建一个新元组版本；
将旧版本的 xmax 标记为更新事务；
让旧版本的 t_ctid 指向新版本；
不同快照根据事务状态决定看哪个版本；
未来由 VACUUM 回收不再可能被任何事务看到的旧版本。

flowchart LR
    T1[旧版本<br/>xmin=100<br/>xmax=220<br/>ctid 指向新版本]
    T2[新版本<br/>xmin=220<br/>xmax=0]
    T1 -->|t_ctid| T2

    S1[旧快照<br/>看不到事务 220 提交结果] --> T1
    S2[新快照<br/>事务 220 已提交] --> T2

DELETE 也通常不是立即从页面抹掉数据，而是设置删除事务信息。只要仍有旧快照可能需要该版本，就不能物理回收。

这解释了很多 PostgreSQL 现象：

UPDATE 会产生 dead tuple；
长事务会阻止垃圾版本回收；
大量 UPDATE/DELETE 后表文件不一定立即缩小；
VACUUM 通常只是让空间可重用，不会把文件主动还给操作系统；
VACUUM FULL 会重写表并需要更强锁，但可收缩物理文件。

7.3 Snapshot 如何判断元组是否可见

一个快照会记录某个逻辑时刻的事务边界和活跃事务集合。概念上可以理解为：

1
2
3

xmin：仍可能处于活跃状态的最老事务边界
xmax：创建快照时尚未分配的事务上界
xip：xmin 与 xmax 之间当时仍活跃的事务集合

判断元组可见性时，需要结合：

tuple xmin 是否已提交；
创建事务在快照时是否仍活跃；
tuple xmax 是否有效；
删除或更新事务是否已提交；
当前事务自身 command ID；
hint bits 与 pg_xact 中的事务状态。

简化规则如下：

创建事务已提交且对快照可见
AND
删除/更新事务不存在、未提交，或对快照尚不可见
=> 元组版本对当前快照可见

实际源码的可见性判断需要处理当前事务、子事务、MultiXact、冻结 XID、hint bit 等情况，比上述公式更复杂。

7.4 为什么 PostgreSQL 读通常不阻塞写

普通 SELECT 通过快照选择合适版本，而不是要求 UPDATE 必须等待所有读者离开，所以：

读通常不阻塞普通写；
写通常不阻塞普通读；
读者可以看到更新前的旧版本；
两个事务若修改同一行，仍然可能发生行锁等待；
DDL、显式锁、外键检查等仍可能造成阻塞。

“读写不互相阻塞”是便于理解 MVCC 的概括，不应被误读为 PostgreSQL 没有锁等待。

7.5 隔离级别

PostgreSQL 支持 SQL 标准名称中的四个隔离级别，但 Read Uncommitted 在 PostgreSQL 中按 Read Committed 行为执行。

隔离级别	快照范围	PostgreSQL 行为重点
Read Uncommitted	语句级	实际等同 Read Committed，不提供脏读
Read Committed	每条语句重新取得快照	默认级别；同一事务的两条 SELECT 可能看到不同已提交结果
Repeatable Read	事务级稳定快照	同一事务重复读取一致；可能因并发更新产生 serialization failure
Serializable	事务级快照 + SSI	检测危险依赖，保证结果等价于某种串行顺序；应用必须支持重试

Read Committed

sequenceDiagram
    participant A as Transaction A
    participant B as Transaction B

    A->>A: BEGIN
    A->>A: SELECT balance = 100<br/>Snapshot S1
    B->>B: UPDATE balance = 80
    B->>B: COMMIT
    A->>A: SELECT balance = 80<br/>新 Snapshot S2
    A->>A: COMMIT

每条语句看到语句开始前已提交的数据，因此事务内部两次查询结果可能不同。

Repeatable Read

sequenceDiagram
    participant A as Transaction A
    participant B as Transaction B

    A->>A: BEGIN ISOLATION LEVEL REPEATABLE READ
    A->>A: SELECT balance = 100<br/>固定 Snapshot S1
    B->>B: UPDATE balance = 80
    B->>B: COMMIT
    A->>A: SELECT balance = 100<br/>仍使用 S1
    A->>A: COMMIT

Serializable 与 SSI

Serializable Snapshot Isolation 不会简单把所有读都变成互斥锁。它跟踪读写依赖，通过 predicate lock 等结构识别可能形成非串行化结果的危险结构，并中止其中一个事务。

应用层必须把以下错误视为可重试的事务级事件：

serialization failure；
某些死锁错误；
乐观并发条件失败。

重试必须覆盖整个事务逻辑，而不是只重放最后一条 SQL。

7.6 锁体系

PostgreSQL 内部有多层同步机制：

Heavyweight Lock

用于数据库对象和事务级等待，可在 pg_locks 中观察，例如：

表锁；
页锁或元组相关等待；
transaction ID lock；
advisory lock；
relation extension lock。

表锁模式从 Access Share 到 Access Exclusive 具有冲突矩阵。普通 SELECT 通常取得 Access Share，许多 DDL 需要更强的锁。

Row-level Lock

行锁信息主要编码在 heap tuple header 的 xmax 和 MultiXact 中，而不是为每一行永久建立一个独立共享内存锁对象。

常见模式包括：

FOR KEY SHARE；
FOR SHARE；
FOR NO KEY UPDATE；
FOR UPDATE。

Lightweight Lock

LWLock 用于保护共享内存中的内部结构，例如 Buffer、WAL、锁表分区等。它不是 SQL 层的表锁。

Spinlock 与原子操作

用于极短临界区。若持有时间过长，会浪费 CPU，因此只保护非常小的状态更新。

Predicate Lock

Serializable SSI 用它跟踪“事务读取过哪些逻辑范围”，以发现并发事务之间可能导致序列化异常的读写依赖。它不等于普通阻塞式范围锁。

7.7 死锁检测

两个事务可能形成等待环：

flowchart LR
    A[Transaction A<br/>持有 Row 1] -->|等待 Row 2| B[Transaction B<br/>持有 Row 2]
    B -->|等待 Row 1| A

PostgreSQL 在等待超过 deadlock_timeout 等条件后构建等待关系并检测环，选择一个事务终止，以打破死锁。

降低死锁的方法不是无限增大超时，而是：

以一致顺序访问资源；
缩短事务；
避免在事务中等待用户输入或外部网络；
一次锁定所需对象；
对可重试错误做完整事务重试。

7.8 事务 ID、回卷与冻结

PostgreSQL 普通 TransactionId 本质上是有限宽度的循环编号。新旧判断依赖模运算，因此不能让极老元组的 xmin 永久不处理。

VACUUM 会把足够老且已提交的创建事务信息冻结，使元组在未来仍被视为有效。若长期不 VACUUM，数据库可能为避免事务 ID wraparound 风险而限制写入。

需要同时关注：

普通 XID 年龄；
MultiXact 年龄；
replication slot 保留的 xmin；
长事务；
prepared transaction；
逻辑复制槽；
备库反馈。

这些对象都可能延长旧版本必须保留的时间。

8. WAL、提交、检查点与崩溃恢复

8.1 WAL 的基本原则

WAL 即 Write-Ahead Logging，核心规则是：

在修改后的数据页写入持久存储之前，描述该修改的 WAL 必须先持久化。

因此事务提交时通常不需要同步写完所有随机数据页，只需要把提交记录及其之前必要 WAL 刷到可靠存储。

8.2 一次 UPDATE + COMMIT 的写入链路

sequenceDiagram
    participant C as Client
    participant B as Backend
    participant SB as Shared Buffers
    participant WB as WAL Buffers
    participant WAL as pg_wal
    participant D as Data Files
    participant CK as Checkpointer/Background Writer

    C->>B: UPDATE orders ...
    B->>SB: 锁定并修改 Heap Page<br/>产生新元组版本，页面标脏
    B->>WB: 写入 WAL Record
    C->>B: COMMIT
    B->>WB: 追加 Commit Record
    B->>WAL: Flush 到 Commit LSN
    WAL-->>B: 持久化完成
    B-->>C: COMMIT 成功
    Note over SB,D: 此时数据页可能仍只在 Shared Buffers
    CK->>SB: 选择脏页
    CK->>D: 稍后写入数据文件

这就是为什么：

COMMIT 成功不等于相关表页已经全部写入表文件；
COMMIT 成功意味着恢复所需 WAL 已达到配置要求的持久性边界；
崩溃后可以使用 WAL 重做尚未反映到数据文件的已提交修改。

8.3 WAL Record、LSN 与 WAL Segment

WAL Record

每条 WAL record 描述某个资源管理器的状态变化，例如：

heap insert/update/delete；
B-tree page split；
transaction commit；
checkpoint；
relation truncate；
visibility map 更新。

LSN

LSN，即 Log Sequence Number，表示 WAL 字节流中的位置，具有单调前进性质。常见格式类似：

1	`0/16B6C50`

LSN 用于：

判断页面修改对应的 WAL 是否已刷盘；
复制进度；
恢复位置；
归档和备份边界；
逻辑解码确认位置。

WAL Segment

WAL 字节流被切分为固定大小段文件，常见默认段大小为 16MB。段大小可在初始化 cluster 时确定。

pg_wal 中的文件名编码 timeline、log 和 segment 信息。文件可能被循环复用，不应把 pg_wal 当成永久历史仓库；永久保留应交给 archive、备份系统或复制槽策略。

8.4 Full Page Write

检查点之后某页第一次被修改时，PostgreSQL 可能把完整页面镜像写入 WAL，以防数据库崩溃时数据文件出现 torn page：页面的一部分已写入，另一部分仍是旧内容。

代价是检查点后的一段时间 WAL 量可能明显上升。

这解释了为什么过于频繁的检查点可能造成：

full-page image 增多；
WAL 放大；
I/O 抖动；
后台写压力增加。

8.5 Checkpoint 做什么

Checkpoint 的主要工作是：

记录检查点开始和恢复边界；
将检查点要求范围内的脏页逐步写回数据文件；
更新控制信息；
建立新的 redo point；
允许旧 WAL 在不再被归档、复制槽或备份需要时回收或复用。

flowchart LR
    DIRTY[Shared Buffers 中的脏页]
    WAL[已持久化 WAL]
    CKPT[Checkpoint]
    DATA[一致的数据文件基线]
    REDO[Redo Point]

    WAL --> CKPT
    DIRTY --> CKPT
    CKPT --> DATA
    CKPT --> REDO

Checkpoint 不应被理解为“每个事务的数据落盘动作”。事务提交和检查点是两个不同时间尺度：

commit 主要保证 WAL 持久性；
checkpoint 周期性推进数据文件的一致基线。

8.6 崩溃恢复

数据库异常终止后，启动恢复大致是：

flowchart TB
    START[服务器启动]
    CTRL[读取 pg_control]
    CK[定位最近有效 Checkpoint / Redo Point]
    SCAN[从 Redo Point 扫描 WAL]
    REDO[重做需要的页面修改]
    ENDREC[到达一致结束位置]
    READY[接受连接]

    START --> CTRL --> CK --> SCAN --> REDO --> ENDREC --> READY

恢复不是简单“重放所有 SQL”，而是根据 WAL record 对页面和内部结构执行 redo。

由于 MVCC 和事务状态的设计，未提交事务产生的物理版本可以留在页面中，但对正常快照不可见，之后再由 VACUUM 清理。PostgreSQL 不一定需要像某些引擎那样在恢复阶段逐行执行独立 undo 日志回滚。

8.7 Group Commit

多个事务在相近时间提交时，可以共享一次 WAL flush：

1
2
3

Transaction A commit record ┐
Transaction B commit record ├─ 一次 fsync / flush 到更后 LSN
Transaction C commit record ┘

这使得存储设备一次持久化操作可以确认多个事务，提高高并发小事务吞吐。

8.8 synchronous_commit 的边界

降低 synchronous_commit 可以减少单个事务等待 WAL 持久化的延迟，但会扩大“数据库进程或操作系统崩溃时，客户端已收到成功、事务却可能丢失”的窗口。

它通常不会破坏数据库物理一致性，但会改变业务持久性承诺。是否允许，应由业务 RPO 决定，而不是只看 TPS。

9. VACUUM、HOT 与表膨胀

9.1 为什么 PostgreSQL 必须 VACUUM

MVCC 通过保留旧版本降低读写冲突，但旧版本不会自动从物理页面消失。VACUUM 负责：

找出不再可能被任何事务看到的 dead tuple；
将对应 line pointer 和空间标记为可重用；
清理无效索引项；
更新 FSM；
设置 Visibility Map；
冻结老事务 ID；
防止 XID 和 MultiXact wraparound；
为 planner 更新部分统计信息，通常 ANALYZE 负责更完整统计采样。

stateDiagram-v2
    [*] --> Live: INSERT
    Live --> OldVersion: UPDATE
    Live --> DeadCandidate: DELETE
    OldVersion --> DeadCandidate: 新版本已提交
    DeadCandidate --> Reclaimable: 最老快照不再需要
    Reclaimable --> ReusedSpace: VACUUM 清理
    ReusedSpace --> Live: 后续 INSERT/UPDATE 复用

9.2 VACUUM 为什么通常不缩小文件

普通 VACUUM 主要把空间交还给同一张表内部复用，而不是把中间的空洞直接归还操作系统。

若表尾部存在完整空闲页，并且满足截断条件，VACUUM 可能回收部分尾部空间；但大量随机 dead tuple 分布在文件内部时，文件大小一般不会明显下降。

真正重写并压缩表的方式包括：

VACUUM FULL；
CLUSTER；
某些表重写型 ALTER TABLE；
在线重组工具。

它们通常需要额外磁盘空间、较强锁或复杂运维，不应把 VACUUM FULL 当作日常 autovacuum 的替代品。

9.3 Autovacuum 的调度逻辑

Autovacuum Launcher 周期性检查数据库，再由 worker 对表执行维护。是否触发通常与以下因素有关：

1
2
3

需要清理的变化量
> autovacuum_vacuum_threshold
  + autovacuum_vacuum_scale_factor × 表规模

ANALYZE 也有类似 threshold + scale factor 逻辑。

对超大表而言，即使 scale factor 看起来很小，乘以数亿行后仍可能意味着积累大量 dead tuple 才触发。因此生产中经常需要为热点大表单独配置：

更低的 vacuum scale factor；
更低的 analyze scale factor；
合理的 cost limit / cost delay；
更积极的 freeze 参数；
更高的 autovacuum worker 资源，但要结合 I/O 能力。

9.4 HOT：减少 UPDATE 的索引放大

HOT，即 Heap-Only Tuple，是 PostgreSQL 对特定 UPDATE 的重要优化。

通常需要同时满足：

本次 UPDATE 没有修改任何需要维护的索引列；
原 heap page 上有足够空间容纳新版本。

满足后，新版本仍放在同一 heap page，并形成 HOT chain。原索引项可以继续指向链头，不必为每个新版本新增所有索引项。

flowchart LR
    IDX[Index Entry<br/>key -> TID A]
    A[Tuple A<br/>旧版本]
    B[Tuple B<br/>HOT 新版本]
    C[Tuple C<br/>HOT 新版本]

    IDX --> A
    A -->|t_ctid| B
    B -->|t_ctid| C

HOT 的收益包括：

减少索引写入；
减少 WAL；
降低索引膨胀；
页面内可执行 HOT pruning；
提高高频更新表的吞吐。

fillfactor 可以在页面中预留空间，从而提高未来 UPDATE 在原页生成 HOT 版本的机会。但预留过多会增加表尺寸和读取页数，需要针对读写比例权衡。

可以观察：

SELECT
    relname,
    n_tup_upd,
    n_tup_hot_upd,
    CASE
        WHEN n_tup_upd = 0 THEN 0
        ELSE round(100.0 * n_tup_hot_upd / n_tup_upd, 2)
    END AS hot_update_pct,
    n_dead_tup,
    last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_tup_upd DESC;

9.5 长事务为何是 MVCC 系统的“隐形钉子户”

一个很老的活跃快照可能迫使数据库继续保留大量旧版本。常见来源包括：

应用开启事务后长时间空闲；
大查询运行数小时；
未结束的导出事务；
逻辑复制槽消费停滞；
prepared transaction 长时间不提交；
备库开启 feedback 且回放或查询长期滞后。

后果可能是：

旧快照无法前进
  -> global xmin 无法前进
  -> dead tuple 无法回收
  -> 表与索引膨胀
  -> WAL 被复制槽保留
  -> pg_wal 持续增长
  -> VACUUM 做了很多工作却收效很小

因此排查膨胀时，不能只调 autovacuum，还要先找谁在阻止清理边界推进。

10. PostgreSQL 索引体系

10.1 所有普通索引都是二级索引

PostgreSQL heap table 本身不按主键组织。即使声明了 PRIMARY KEY，也只是自动创建唯一 B-tree 索引和约束。

典型访问链路是：

flowchart LR
    Q[查询条件]
    IDX[Index Access Method]
    ENTRY[Index Entry<br/>key + TID]
    HEAP[Heap Page]
    TUPLE[Heap Tuple]
    MVCC[Visibility Check]

    Q --> IDX --> ENTRY --> HEAP --> TUPLE --> MVCC

因此“索引命中”仍可能伴随大量 heap 随机访问。优化器需要在以下成本之间选择：

扫描多少索引页；
返回多少 TID；
访问多少 heap page；
heap page 的物理相关性；
是否可以使用 bitmap 合并访问；
是否满足 index-only 条件。

10.2 B-tree

B-tree 是 PostgreSQL 最常用的索引类型，适用于：

=；
<、<=、>、>=；
BETWEEN；
IN；
IS NULL / IS NOT NULL 的部分场景；
前缀可转换为范围的模式匹配；
ORDER BY；
MIN/MAX；
唯一约束。

B-tree 页面结构

flowchart TB
    META[Metapage<br/>root、level 等元数据]
    ROOT[Root Page]
    I1[Internal Page]
    I2[Internal Page]
    L1[Leaf Page]
    L2[Leaf Page]
    L3[Leaf Page]
    L4[Leaf Page]

    META --> ROOT
    ROOT --> I1
    ROOT --> I2
    I1 --> L1
    I1 --> L2
    I2 --> L3
    I2 --> L4
    L1 <--> L2
    L2 <--> L3
    L3 <--> L4

内部页保存分隔键和下层页面指针；叶子页保存可搜索键和 heap TID。页面存在左右兄弟关系，支持并发页面分裂期间继续正确导航。

B-tree 查找

从 Root 比较 key
  -> 选择一个子页面
  -> 逐层进入 Internal Page
  -> 到达 Leaf Page
  -> 二分查找目标 key
  -> 返回一个或多个 TID

复杂度通常近似 O(log N)，但数据库性能还受页缓存、随机 I/O、重复值数量和 heap 回表影响。

页面分裂

叶子页无空间时，会发生 split：

分配新页面；
将部分 index tuple 移到新页；
更新兄弟链接和边界信息；
将分隔键插入父页面；
父页面也满时继续向上分裂；
根分裂时树高增加。

随机 UUID、时间有序 ID、单调序列等键分布会带来不同写入热点与分裂行为，不能只用“随机一定差、递增一定好”一刀切：

递增键容易集中写最右叶子页；
随机键使写入分散，但可能降低局部性并增加分裂；
高并发、缓存容量、页面填充率和数据类型大小都会改变结果。

多列 B-tree

索引 (a, b, c) 的传统直觉是“最左前缀”，但 PostgreSQL 优化器还可能利用：

对前导列的等值条件；
对后续列的范围条件；
只在索引内部过滤后续列；
在合适分布和成本条件下使用 skip scan；
Bitmap Scan 组合多个索引。

仍需牢记：列顺序直接影响可缩小的索引范围、排序能力和索引尺寸，不能因为存在 skip scan 就忽略索引设计。

10.3 Hash Index

Hash 索引把键哈希到 bucket，主要支持等值查询：

1	`WHERE token = $1`

不支持范围排序语义。现代 PostgreSQL 的 Hash 索引具备 WAL 和崩溃恢复支持，但在很多场景下 B-tree 同样能高效处理等值条件，并提供更丰富能力。因此选择 Hash 前应通过真实负载验证，而不是因为代码中出现 = 就默认采用 Hash。

10.4 GiST

GiST，即 Generalized Search Tree，是一种可扩展索引框架，而不是只为单一数据类型固定实现的树。

它允许操作符类定义：

一致性判断；
如何把条目压缩成索引键；
如何把新条目分配到页面；
页面分裂策略；
距离计算。

常见用途包括：

几何对象；
range type；
网络地址；
全文搜索的部分实现；
KNN nearest-neighbor 查询；
PostGIS 空间索引。

GiST 的内部条目经常保存一个“覆盖其子树内容的近似边界”，搜索时先排除不可能的子树，再对候选项执行 recheck。

10.5 SP-GiST

SP-GiST，即 Space-Partitioned GiST，适合自然形成非平衡分区结构的数据：

trie；
radix tree；
quadtree；
k-d tree 类空间划分。

flowchart TB
    ROOT[空间或前缀根]
    ROOT --> A[分区 A]
    ROOT --> B[分区 B]
    ROOT --> C[分区 C]
    A --> A1[更细分区]
    A --> A2[更细分区]
    B --> B1[更细分区]

它强调按空间或前缀规则分区，不要求像 B-tree 那样保持全局有序和平衡。

10.6 GIN

GIN，即 Generalized Inverted Index，是倒排索引。它适合“一行包含多个可检索元素”的数据：

数组中的元素；
JSONB 中的键和值；
全文搜索 token；
trigram 扩展产生的片段。

flowchart LR
    D1[Row TID 10<br/>java postgres ai]
    D2[Row TID 20<br/>postgres mvcc]
    D3[Row TID 30<br/>java spring]

    JAVA[java] --> PJ[Posting List<br/>10, 30]
    PG[postgres] --> PP[Posting List<br/>10, 20]
    MVCC[mvcc] --> PM[Posting List<br/>20]

    D1 -.拆分 token.-> JAVA
    D1 -.拆分 token.-> PG
    D2 -.拆分 token.-> PG
    D2 -.拆分 token.-> MVCC

GIN 的基本思想是：

1 2	`元素或 token -> 出现该元素的 TID 列表或 posting tree`

优点是包含查询非常强；代价通常包括：

写入和更新成本较高；
索引可能较大；
fastupdate 会先进入 pending list，需要后续合并；
不同 JSONB operator class 对支持操作和索引体积影响很大。

例如 JSONB 常见 operator class：

jsonb_ops：支持更广泛操作符，条目更多；
jsonb_path_ops：针对部分包含和 jsonpath 场景更紧凑，但能力范围不同。

10.7 BRIN

BRIN，即 Block Range Index，不为每行保存完整索引项，而是为一段连续 heap block 保存摘要。

flowchart TB
    H[Heap Blocks]
    H --> R1[Range 0: blocks 0-127<br/>min=2026-01-01<br/>max=2026-01-03]
    H --> R2[Range 1: blocks 128-255<br/>min=2026-01-03<br/>max=2026-01-05]
    H --> R3[Range 2: blocks 256-383<br/>min=2026-01-05<br/>max=2026-01-08]

    Q[WHERE created_at = 2026-01-06] --> R3
    R3 --> RECHECK[读取候选 Heap Pages 并 Recheck]

BRIN 特别适合：

超大表；
列值与物理插入顺序高度相关；
时间序列、日志、流水数据；
可以接受 lossy 候选范围再回表复核。

优势：

索引极小；
构建和维护成本低；
对顺序相关数据可跳过大量不相关 block range。

不适合：

数据物理分布完全随机；
查询要求极高点查选择性；
范围摘要重叠严重。

10.8 Bitmap Index Scan 不是一种持久索引

执行计划中的 Bitmap Index Scan 并不表示 PostgreSQL 创建了“位图索引”文件。

它会：

从一个或多个普通索引取得 TID；
在内存中建立 TIDBitmap；
使用 BitmapAnd 或 BitmapOr 合并；
按 heap block 顺序批量访问；
内存不足时从 exact page 降级为 lossy page；
对 lossy page 重新检查条件。

flowchart TB
    I1[Index on status]
    I2[Index on created_at]
    B1[Bitmap 1]
    B2[Bitmap 2]
    AND[BitmapAnd]
    HEAP[Bitmap Heap Scan<br/>按 Block 顺序读取]

    I1 --> B1
    I2 --> B2
    B1 --> AND
    B2 --> AND
    AND --> HEAP

它在“结果不是极少，但也没多到值得全表扫描”时很有价值。

10.9 Index Only Scan 与 INCLUDE

Index Only Scan 要同时满足：

查询需要的列都能从索引取得；
对应 heap page 在 Visibility Map 中是 all-visible，或少量情况下仍需回表确认。

1
2
3

CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at DESC)
INCLUDE (status, amount);

这里：

customer_id, created_at 是搜索和排序键；
status, amount 是 payload，不参与 B-tree 排序语义；
INCLUDE 可能提高覆盖能力，也会增大索引和写入成本；
高频更新 INCLUDE 列仍会让索引需要维护，从而降低 HOT 机会。

10.10 Partial Index 与 Expression Index

Partial Index

只索引满足谓词的行：

1
2
3

CREATE INDEX idx_orders_unpaid
ON orders (customer_id, created_at)
WHERE status = 'UNPAID';

适合小而高频的活跃子集。查询谓词必须能被优化器证明与索引谓词相容。

Expression Index

对表达式结果建立索引：

1 2	`CREATE INDEX idx_users_lower_email ON users (lower(email));`

适合大小写归一、日期截断、JSON 字段提取等，但表达式更新会增加写成本，且查询表达式要与索引定义匹配。

10.11 索引选型速查

需求	优先考虑
等值、范围、排序、唯一	B-tree
单纯等值且已验证收益	Hash
空间、范围、近邻、可扩展搜索	GiST
前缀树或空间分区结构	SP-GiST
JSONB、数组、全文 token、包含关系	GIN
超大顺序相关表的块范围过滤	BRIN
多个条件中等选择性组合	多个索引 + Bitmap Scan
查询列可被索引覆盖	B-tree + INCLUDE + Index Only Scan
仅索引活跃小子集	Partial Index
按计算结果查找	Expression Index

10.12 索引不是免费午餐

每增加一个索引，都可能增加：

INSERT 的索引写入；
DELETE 后的垃圾索引项；
UPDATE 的索引维护；
WAL 体积；
autovacuum 清理成本；
cache 占用；
planner 枚举成本；
备份与恢复体积。

判断索引是否值得保留，要同时看：

是否被使用；
是否与其他索引高度重复；
是否真正改善关键 SQL；
表写入频率；
索引大小；
扫描返回行比例；
业务峰值时延，而不是只看测试环境单次执行。

11. PostgreSQL 备份与恢复体系

PostgreSQL 官方将备份路线概括为三大类：

SQL dump；
文件系统级物理备份；
连续 WAL 归档与时间点恢复。

在 PostgreSQL 18 中，还应把增量物理备份纳入整体设计。

11.1 先区分 HA、备份与容灾

流复制：解决实例故障后的快速接管
备份：解决误删、逻辑破坏、历史恢复和长期留存
WAL 归档：解决连续恢复与 PITR
异地副本：解决机房或区域级灾难

复制不是备份：

主库误删，删除会复制到备库；
错误 DDL 会复制；
勒索、凭据泄露或运维脚本可能同时影响主备；
复制槽和 WAL 不能代替长期、隔离、不可变的备份副本。

11.2 备份路线对比

方式	粒度	跨版本能力	是否支持 PITR	主要优点	主要限制
`pg_dump` / `pg_dumpall`	数据库、schema、表等逻辑对象	较好	否	可选择对象，便于迁移和审计	大库恢复较慢，不保留物理布局
冷文件系统备份	整个 cluster	通常要求兼容二进制格式	否或需配合 WAL	简单直接	一致性要求严格，不能只复制单表文件
文件系统一致性快照	整个 cluster	同上	配合 WAL 可支持	快照速度快	必须保证所有相关卷在同一一致点
`pg_basebackup` 全量物理备份	整个 cluster	同主版本物理兼容边界	配合 WAL 支持	标准化、可在线执行、适合主备和 PITR	不能选择单表恢复
增量物理备份	整个 cluster 的变化块	物理兼容边界	配合 WAL 支持	降低传输和备份量	恢复前需组合依赖链，必须保留 manifest 与 WAL
WAL Archive	连续日志	依赖物理基线	是	可恢复到指定时间或 LSN	没有 base backup 无法独立恢复完整 cluster

11.3 逻辑备份：pg_dump

常见格式：

plain SQL：直接生成 SQL 文本；
custom：供 pg_restore 灵活选择和并行恢复；
directory：适合并行 dump 和 restore；
tar：归档格式之一。

示例：

# 自定义格式备份单个数据库
pg_dump \
  --format=custom \
  --file=appdb.dump \
  --dbname=appdb

# 查看归档内容
pg_restore --list appdb.dump

# 创建目标库后并行恢复
pg_restore \
  --jobs=8 \
  --dbname=appdb_restore \
  appdb.dump

逻辑备份的关键特征：

pg_dump 使用一致性快照，不需要长时间锁住普通读写；
只导出一个数据库时，集群级角色和表空间定义不一定包含在内；
可用 pg_dumpall --globals-only 备份角色、表空间等全局对象；
恢复顺序、扩展、所有者、权限和依赖需要验证；
大库逻辑恢复往往受建表、COPY、索引创建、约束验证和 WAL 写入影响。

逻辑备份非常适合：

跨环境迁移；
选择性恢复；
版本升级辅助；
schema 审计；
小中型数据库日常备份。

但它不能替代大型生产库的低 RTO 物理恢复方案。

11.4 文件系统级备份

直接复制 PGDATA 时必须获得一致性视图。简单地在数据库运行期间递归复制文件，可能复制到：

不同时间点的数据页；
与控制文件不匹配的状态；
不完整 WAL；
表空间缺失；
正在变更或删除的 relation segment。

安全路线通常是：

停库后冷复制；
使用数据库支持的在线 base backup；
使用能够对全部相关卷做原子一致快照的存储快照，并结合 WAL；
确保 PGDATA、外部 tablespace 和所需 WAL 都纳入一致性设计。

不能通过复制某张表对应的几个 relfilenode 文件来恢复单表，因为数据库目录、事务状态、TOAST、索引、FSM/VM、WAL 与 catalog 元数据必须协调一致。

11.5 全量物理备份：pg_basebackup

pg_basebackup 通过复制协议从运行中的 PostgreSQL 取得整个 cluster 的物理基线。

pg_basebackup \
  --host=primary-db \
  --username=replicator \
  --pgdata=/backup/base/2026-07-28 \
  --format=plain \
  --wal-method=stream \
  --checkpoint=fast \
  --progress \
  --write-recovery-conf

需要理解几个点：

它备份整个 cluster，而不是单个 database；
需要复制权限和合适的 pg_hba.conf；
WAL 必须足以覆盖备份起止区间；
--wal-method=stream 会并行流式接收所需 WAL；
--write-recovery-conf 可生成连接上游所需配置并创建 standby signal；
--checkpoint=fast 会加速开始备份，但可能带来瞬时 I/O 压力；
恢复前必须核验备份 manifest 和实际可启动性。

11.6 增量物理备份

PostgreSQL 的增量物理备份以先前备份的 manifest 为基准，只传输自该基线后发生变化的 block，并依赖 WAL summary 判断变化范围。

概念流程：

flowchart LR
    FULL[Full Base Backup<br/>Manifest F0]
    INC1[Incremental Backup 1<br/>参考 F0]
    INC2[Incremental Backup 2<br/>参考 I1 Manifest]
    COMBINE[pg_combinebackup]
    READY[可恢复的合成全量目录]
    WAL[恢复所需 WAL]

    FULL --> INC1 --> INC2
    FULL --> COMBINE
    INC1 --> COMBINE
    INC2 --> COMBINE
    COMBINE --> READY
    WAL --> READY

示例思路：

# 先有全量备份及其 backup_manifest
pg_basebackup \
  --pgdata=/backup/full-001 \
  --format=plain \
  --wal-method=stream

# 以后以先前 manifest 为基准执行增量备份
pg_basebackup \
  --pgdata=/backup/inc-002 \
  --incremental=/backup/full-001/backup_manifest \
  --format=plain \
  --wal-method=stream

# 恢复前将全量与增量链组合为完整备份
pg_combinebackup \
  --output=/restore/combined \
  /backup/full-001 \
  /backup/inc-002

实际命令应按部署版本和备份链设计校验。关键不是“增量文件存在”，而是：

依赖链所有备份均可用；
manifest 完整；
所需 WAL 可用；
pg_combinebackup 能成功生成完整恢复目录；
组合后的目录经过 pg_verifybackup 和启动恢复演练。

增量备份降低了备份窗口和传输量，但会增加恢复链管理复杂度。链过长会放大恢复失败面，通常需要周期性重新做全量基线。

11.7 WAL Archive 与 PITR

PITR，即 Point-in-Time Recovery，需要：

一个物理 base backup；
从 base backup 开始直到目标位置的连续 WAL；
restore/recovery 配置；
明确的 target time、target LSN、target name 或 target XID。

flowchart LR
    BASE[Base Backup<br/>T0]
    W1[WAL T0-T1]
    W2[WAL T1-T2]
    W3[WAL T2-T3]
    TARGET[目标时间<br/>T2 + 30s]
    RECOVERY[Restore + Replay]
    DB[恢复后的独立实例]

    BASE --> RECOVERY
    W1 --> RECOVERY
    W2 --> RECOVERY
    W3 --> RECOVERY
    TARGET --> RECOVERY
    RECOVERY --> DB

归档配置概念示例：

1 2	`archive_mode = on archive_command = 'your-idempotent-archive-command %p %f'`

archive_command 必须做到：

成功后才返回 0；
可重复执行；
不静默覆盖错误文件；
校验目标文件；
对网络或对象存储失败可重试；
有监控和积压告警。

恢复配置概念示例：

1
2
3

restore_command = 'your-restore-command %f %p'
recovery_target_time = '2026-07-28 10:15:30+08'
recovery_target_action = 'promote'

然后在数据目录创建：

1	`recovery.signal`

启动后，Startup Process 会从 base backup 的一致点开始拉取并回放 WAL，达到目标后停止或提升为可写主库。

11.8 Timeline

当一个历史恢复实例被 promote 后，会产生新 timeline。它表示 WAL 历史在某个位置发生分叉：

flowchart LR
    T1A[Timeline 1] --> P[Promote Point]
    P --> T1B[原 Timeline 1 后续]
    P --> T2[新 Timeline 2]

Timeline 允许保留旧历史，同时在恢复分叉后生成新 WAL 序列。PITR 系统必须保存 .history 文件并正确选择目标 timeline，否则可能沿错误历史恢复。

11.9 RPO 与 RTO 设计

RPO：最多能接受丢失多少数据；
RTO：从故障到恢复服务最多能接受多久。

目标	典型设计倾向
RPO 为天级	每日逻辑备份可能足够，但仍需验证恢复时间
RPO 为小时级	更高频备份或物理基线 + WAL 归档
RPO 接近零	同步复制、可靠 WAL 归档、故障切换与备份结合
RTO 很短	热备、自动化切换、预热、容量冗余
需要恢复误删前一分钟	PITR，恢复到隔离实例后提取正确数据
需要恢复单表	逻辑备份，或先做 cluster PITR 到旁路实例再导出单表

11.10 一套可信备份必须回答的问题

备份方案至少应回答：

备份了哪些 cluster、数据库、角色、表空间和扩展？
base backup 与 WAL 是否形成连续链？
备份是否加密、异地、隔离并具备不可变性？
保留策略是否覆盖业务审计周期？
谁能删除备份？是否与生产管理员权限隔离？
最近一次 pg_verifybackup 何时成功？
最近一次真实恢复演练何时完成？
实测 RTO 和理论 RTO 差多少？
恢复后的应用密钥、DNS、连接串和权限如何切换？
备份系统自身故障时如何告警？

没有做过恢复演练的备份，只能叫“希望”。数据库不会被希望恢复，最多被 runbook 恢复。

12. 把所有模块串起来：读路径与写路径

12.1 SELECT 读路径

flowchart TB
    C[Client SELECT]
    B[Backend]
    Q[Parse / Rewrite / Plan]
    E[Executor]
    IM{访问方式}
    SEQ[Seq Scan]
    IDX[Index Scan / Bitmap Scan]
    BUF{Shared Buffers 命中?}
    DISK[读取 Data / Index Page]
    SNAP[MVCC Snapshot Visibility]
    FILTER[Filter / Join / Aggregate / Sort]
    RESULT[返回结果]

    C --> B --> Q --> E --> IM
    IM --> SEQ --> BUF
    IM --> IDX --> BUF
    BUF -->|是| SNAP
    BUF -->|否| DISK --> BUF
    SNAP --> FILTER --> RESULT

读路径的性能瓶颈可能出现在完全不同的层：

连接排队；
parse/plan 频率过高；
统计信息错误导致计划错误；
索引选择性低；
Shared Buffers 和 OS Cache 未命中；
heap 回表随机 I/O；
MVCC dead tuple 过多；
Hash 或 Sort 溢写临时文件；
并行度不足或并行启动成本过高；
客户端取数或网络成为瓶颈。

因此“SQL 慢”不等于“一定缺索引”。

12.2 INSERT 写路径

flowchart TB
    INS[INSERT]
    XID[取得事务上下文 / XID]
    FSM[从 FSM 找候选页面]
    BUF[将 Heap Page 放入 Shared Buffers]
    TUP[构造 Heap Tuple<br/>xmin、null bitmap、用户数据]
    IDX[维护每个相关索引]
    WAL[生成 Heap 和 Index WAL]
    DIRTY[页面标记为 Dirty]
    COMMIT[写 Commit WAL 并 Flush]
    ACK[向客户端确认]
    LATER[Checkpoint / Writer 稍后写数据页]

    INS --> XID --> FSM --> BUF --> TUP --> IDX --> WAL --> DIRTY --> COMMIT --> ACK
    DIRTY --> LATER

写入放大的来源包括：

heap page；
每个二级索引；
WAL；
full-page image；
TOAST 表及其索引；
replica 传输；
archive；
备份存储；
后续 VACUUM 和 ANALYZE。

所以一行 1KB 数据的业务写入，最终产生的系统 I/O 可能远大于 1KB。

12.3 UPDATE 写路径

定位旧元组
  -> 获取行级锁
  -> 判断并发更新状态
  -> 构造新元组版本
  -> 若满足条件则尝试 HOT
  -> 否则维护所有受影响索引
  -> 标记旧版本 xmax / ctid
  -> 写 WAL
  -> 脏页留在 Shared Buffers
  -> COMMIT 刷 WAL
  -> 未来 VACUUM 回收旧版本

UPDATE 性能与以下设计高度相关：

是否更新索引列；
索引数量；
行宽；
page fillfactor；
是否有大 TOAST 字段；
热点行竞争；
外键检查；
触发器；
复制与归档能力；
autovacuum 是否跟得上。

12.4 DELETE 写路径

DELETE 主要标记旧元组的删除事务，并维护必要的锁和 WAL。空间回收发生在以后：

DELETE 提交
  ≠ 元组立即从文件消失
  ≠ 文件立即缩小
  = 新快照不再看到该版本
  + 等待旧快照结束
  + VACUUM 回收空间

12.5 COMMIT 为什么可以很快

在正常持久性配置下，COMMIT 的关键路径通常是顺序追加 WAL 并 flush，而不是把本事务改过的所有表页、索引页随机写回。

这是一种经典转换：

1
2
3

大量随机数据页同步写
  -> 顺序 WAL 同步写
  + 后台批量数据页写回

WAL、Shared Buffers、Checkpoint 和 MVCC 共同构成了这个转换，而不是某一个参数单独完成。

13. PostgreSQL 核心内部数据结构地图

下面列出的名称用于理解内核，不承诺跨版本源码字段完全不变。

13.1 查询处理数据结构

数据结构或概念	作用
Raw Parse Tree	由 SQL 语法解析得到，尚未完全绑定数据库对象
Query Tree	完成语义分析和对象绑定后的查询表示
RangeTblEntry	表、子查询、函数、JOIN 等 FROM 项的描述
RelOptInfo	优化器对一个 base relation 或 join relation 的内部表示
Path	一种可能的访问或连接路线，带成本和行数估计
Plan	被选中的可执行计划节点树
PlanState	Executor 运行时状态
TupleTableSlot	Executor 节点之间传递元组的抽象容器
ExprState	已初始化表达式的执行状态
EState	整个查询执行器的全局运行上下文

flowchart LR
    SQL[SQL Text]
    RAW[Raw Parse Tree]
    QUERY[Query Tree]
    REL[RelOptInfo]
    PATHS[Paths]
    PLAN[Plan Tree]
    STATE[PlanState Tree]
    SLOT[TupleTableSlot]

    SQL --> RAW --> QUERY --> REL --> PATHS --> PLAN --> STATE --> SLOT

13.2 进程与事务数据结构

数据结构或概念	作用
`PGPROC`	一个 Backend 或辅助进程在共享内存中的进程状态
`PGXACT` / 相关事务数组信息	进程的事务 ID、xmin 等并发可见性信息
ProcArray	收集活跃事务信息，支持快照和 global xmin 计算
`TransactionId`	普通事务 ID
`SubTransactionId`	子事务标识
`MultiXactId`	多个事务共同持有某行锁时的组合标识
`SnapshotData`	快照边界与活跃事务集合
Transaction Status	`pg_xact` 中提交、回滚、进行中等状态
Two-phase State	prepared transaction 的持久状态

13.3 锁与同步结构

结构	层级与用途
Lock Method / LOCK / PROCLOCK	Heavyweight lock 表与持有者关系
Wait Queue	锁等待队列
LWLock	共享内存内部结构的轻量同步
Spinlock	极短临界区的忙等锁
Latch	进程等待事件并被其他进程唤醒
Predicate Lock	Serializable SSI 的读依赖跟踪
Fast-path Lock	某些常见 relation lock 的快速路径

13.4 缓冲与存储结构

数据结构或概念	作用
Relation	已打开关系的缓存描述，包括 catalog、AM、锁等信息
RelFileLocator	物理关系文件定位信息
ForkNumber	main、fsm、vm、init 等 fork
BlockNumber	relation fork 内的页面编号
BufferTag	Shared Buffers 中页面的完整键
BufferDesc	缓冲区元数据、状态、引用计数和 usage count
PageHeaderData	页面头部
ItemIdData	line pointer
HeapTupleHeaderData	heap tuple 的 MVCC 和布局头部
ItemPointerData / TID	block number + offset number
SMgrRelation	Storage Manager 对关系文件的抽象
FSM	页面可用空间索引
VM	all-visible / all-frozen 位图
TOAST Pointer	外置变长值定位信息

13.5 WAL 结构

结构	作用
XLogRecPtr / LSN	WAL 流中的位置
WAL Record Header	总长度、前一记录位置、资源管理器、校验等元信息
Resource Manager	heap、btree、xact 等模块的 WAL redo 处理器
WAL Buffer	共享内存中的 WAL 暂存区域
WAL Segment	磁盘上的固定大小 WAL 文件
Timeline ID	WAL 历史分支标识
Replication Slot LSN	消费者确认和必须保留 WAL 的边界

13.6 索引相关结构

结构	作用
IndexTuple	普通索引项，包含索引键和 heap TID 等信息
B-tree Metapage	根页面与层级等元数据
B-tree Internal Tuple	分隔键和下层 block 指针
B-tree Leaf Tuple	索引键与 heap TID / posting list
GIN Entry	token 或可检索元素
GIN Posting List/Tree	token 对应的 TID 集合
BRIN Revmap	heap range 到 BRIN summary tuple 的映射
BRIN Summary Tuple	一个 block range 的 min/max 等摘要
TIDBitmap	Bitmap Heap Scan 的内存位图，支持 exact/lossy page

13.7 统计信息结构

优化器统计与运行统计是两套不同概念：

planner statistics：用于估算查询，主要来自 ANALYZE；
cumulative statistics：用于观察运行状态，例如 pg_stat_* 视图。

ANALYZE
  -> 采样表数据
  -> 更新 pg_statistic
  -> Planner 估算基数和选择率

运行时事件
  -> 累积统计系统
  -> pg_stat_activity / pg_stat_io / pg_stat_user_tables ...
  -> DBA 观察真实负载

14. 常用诊断 SQL

14.1 查看当前 Backend 与后台进程

SELECT
    pid,
    backend_type,
    usename,
    datname,
    application_name,
    client_addr,
    state,
    wait_event_type,
    wait_event,
    xact_start,
    query_start,
    left(query, 200) AS query
FROM pg_stat_activity
ORDER BY backend_type, query_start NULLS LAST;

重点看：

是否存在 idle in transaction；
事务是否持续过久；
当前等待 CPU、Lock、IO、Client 还是 IPC；
是否出现大量短连接；
Backend 数量是否明显高于实际活跃并发。

14.2 查找长事务

SELECT
    pid,
    usename,
    datname,
    state,
    now() - xact_start AS xact_age,
    now() - query_start AS query_age,
    wait_event_type,
    wait_event,
    left(query, 300) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

14.3 查看阻塞链

SELECT
    blocked.pid AS blocked_pid,
    blocked.usename AS blocked_user,
    blocking.pid AS blocking_pid,
    blocking.usename AS blocking_user,
    blocked.wait_event_type,
    blocked.wait_event,
    left(blocked.query, 200) AS blocked_query,
    left(blocking.query, 200) AS blocking_query
FROM pg_stat_activity AS blocked
CROSS JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS p(blocking_pid)
JOIN pg_stat_activity AS blocking
  ON blocking.pid = p.blocking_pid
ORDER BY blocked.query_start;

14.4 查看表级 VACUUM 与 dead tuple

SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    n_tup_ins,
    n_tup_upd,
    n_tup_del,
    n_tup_hot_upd,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze,
    vacuum_count,
    autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

统计值是估计和累计观测，不应把 n_dead_tup 当作字节级精确测量。评估膨胀还需要结合 relation size、页面检查和业务写入模式。

14.5 查看表与索引大小

SELECT
    n.nspname AS schema_name,
    c.relname,
    pg_size_pretty(pg_relation_size(c.oid)) AS main_size,
    pg_size_pretty(pg_table_size(c.oid)) AS table_total,
    pg_size_pretty(pg_indexes_size(c.oid)) AS indexes_total,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS relation_total
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'm', 'p')
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 50;

这些函数的口径不同：

pg_relation_size：指定 relation 的一个 fork，默认 main；
pg_table_size：表、TOAST、FSM、VM 等，不含普通索引；
pg_indexes_size：该表所有索引；
pg_total_relation_size：表相关总量。

14.6 查看索引使用情况

SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

idx_scan = 0 不一定可以立即删索引，因为：

统计可能刚重置；
索引用于约束；
只在月末或故障场景使用；
备库与主库负载不同；
执行计划可能通过 bitmap 或其他路径计数；
观察窗口可能不覆盖完整业务周期。

14.7 查看数据库 I/O

PostgreSQL 18 可通过 pg_stat_io 观察不同 backend type、object 和 context 的 I/O 行为：

SELECT
    backend_type,
    object,
    context,
    reads,
    read_bytes,
    read_time,
    writes,
    write_bytes,
    write_time,
    writebacks,
    extends,
    fsyncs
FROM pg_stat_io
ORDER BY backend_type, object, context;

具体列会受版本和 track_io_timing 等配置影响。重点是区分：

client backend 读取；
vacuum I/O；
bulk read / bulk write；
normal context；
checkpointer 和 background writer 的写回；
relation 与 temp relation。

14.8 查看 WAL 产生量

SELECT
    wal_records,
    wal_fpi,
    pg_size_pretty(wal_bytes) AS wal_bytes,
    wal_buffers_full,
    stats_reset
FROM pg_stat_wal;

若 wal_fpi 很高，需要结合：

checkpoint 频率；
full_page_writes；
工作集页面修改分布；
批量写入；
relation rewrite；
page compression 或存储层行为。

14.9 查看 Checkpoint

1 2	`SELECT * FROM pg_stat_checkpointer;`

同时结合：

1 2	`SELECT * FROM pg_stat_bgwriter;`

观察 checkpoint 次数、写页、同步耗时、前台 Backend 自己写 buffer 等指标。字段以实际 PostgreSQL 18 小版本为准。

14.10 查看复制槽对 WAL 的保留

SELECT
    slot_name,
    slot_type,
    active,
    active_pid,
    restart_lsn,
    confirmed_flush_lsn,
    wal_status,
    safe_wal_size,
    xmin,
    catalog_xmin
FROM pg_replication_slots;

复制槽会保证消费者需要的 WAL 或行版本不被过早回收，但停滞的槽也可能导致：

pg_wal 持续增长；
catalog tuple 无法清理；
磁盘被写满。

14.11 看真实执行计划

EXPLAIN (
    ANALYZE,
    BUFFERS,
    WAL,
    VERBOSE,
    SETTINGS,
    SUMMARY
)
SELECT ...;

注意：ANALYZE 会真实执行 SQL。对 UPDATE、DELETE、INSERT 或可能锁表的语句，应在安全环境中执行，或放在显式事务中验证后回滚；即便回滚，查询造成的锁、WAL、序列变化或外部副作用仍需要评估。

14.12 观察查询计划时的阅读顺序

推荐按以下顺序：

从最深层、实际最先执行的节点开始；
比较 estimated rows 与 actual rows；
将 actual time 与 loops 结合；
看过滤掉多少行；
看 Shared hit/read 和 temp I/O；
看 join 内侧是否被重复执行；
看排序、Hash 是否分批或落盘；
最后再判断索引与参数调整。

不要只盯着最上方的总时间，也不要把“出现 Seq Scan”自动判为错误。小表、返回大比例数据、物理顺序读取和并行扫描时，Seq Scan 可能就是正确计划。

15. 架构层面的生产实践

15.1 连接管理

建议：

使用连接池限制活跃数据库连接；
区分 API、任务、报表和管理连接池；
为不同负载设置合理 statement timeout；
限制 idle-in-transaction；
避免每个请求创建新物理连接；
不把 max_connections 当吞吐参数；
观察 CPU 核数、活跃 Backend 和平均 runnable queue。

15.2 内存配置

重点原则：

shared_buffers 只是数据库缓存，不是数据库全部内存；
work_mem 会被操作节点和并行 worker 放大；
maintenance_work_mem 影响 VACUUM、CREATE INDEX 等维护操作；
autovacuum worker 有自己的内存配置边界；
需要为 Backend 私有内存、OS Page Cache、WAL、扩展和监控代理留空间；
以压力测试和峰值并发模型配置，不照抄固定百分比。

15.3 Checkpoint 与 WAL

建议关注：

checkpoint 是否过于频繁；
checkpoint 写入是否平滑；
max_wal_size 是否导致被迫频繁 checkpoint；
archive 是否持续成功；
replication slot 是否导致 WAL 堆积；
存储 flush 延迟；
WAL 与数据文件是否争用同一受限设备；
full-page image 比例。

不要通过关闭 fsync、full_page_writes 等关键安全机制来“优化”生产数据库，除非明确接受数据库崩溃后不可恢复甚至物理损坏的风险。

15.4 Autovacuum

建议：

不要全局关闭 autovacuum；
为高更新大表设置表级参数；
监控 oldest xid、MultiXact、dead tuple、vacuum progress；
处理长事务和复制槽，而不是只加 worker；
定期 ANALYZE 偏斜明显的列；
使用扩展统计描述多列相关性；
把 vacuum 看作持续生产负载的一部分，而不是夜间补救任务。

15.5 表与索引设计

建议：

主键以业务稳定性和分布为优先，不迷信某一种 ID；
控制不必要索引；
高频 UPDATE 表避免把经常变化的列放入太多索引；
对 append-only 大表考虑分区与 BRIN；
对 JSONB 根据实际操作符选择 GIN operator class；
使用 partial index 聚焦活跃子集；
使用 INCLUDE 前计算索引体积和 HOT 损失；
让查询条件数据类型与索引列类型一致；
定期重审重复和长期无效索引。

15.6 备份

建议至少具备：

定期物理 base backup；
连续 WAL archive；
角色与全局对象备份；
关键表额外逻辑备份；
异地与不可变副本；
自动校验；
周期性恢复演练；
记录实测 RPO/RTO；
恢复 runbook 与责任人。

15.7 PostgreSQL 18 异步 I/O

PG18 的异步 I/O 能降低某些场景下同步等待和提升并发 I/O 能力，但它不是“打开后所有 SQL 自动变快”。评估时应关注：

平台是否支持所选 I/O method；
worker 数量与存储并发能力；
顺序扫描和预取行为；
buffer hit ratio 与真实物理读取；
CPU 是否成为新瓶颈；
查询计划是否本来就错误；
底层云盘或 SAN 的队列深度与限流。

先修复错误计划和不合理数据访问，再评估 I/O 并发，通常比盲目调高 worker 更有效。

16. 常见误区

误区一：PostgreSQL 是多线程数据库

更准确：核心是多进程架构。并行 worker、后台 worker 和 PG18 I/O worker 也主要表现为独立进程。

误区二：COMMIT 后数据页一定已写入表文件

更准确：通常先保证 WAL 达到持久性边界，数据页可由后台稍后写入，崩溃后通过 WAL redo 恢复。

误区三：UPDATE 是原地覆盖

更准确：MVCC 下通常创建新版本并标记旧版本，之后由 VACUUM 回收。

误区四：VACUUM 会立即缩小表文件

更准确：普通 VACUUM 主要让空间可内部复用；重写型操作才更可能显著收缩文件。

误区五：有索引就一定快

更准确：若返回大量行、随机回表昂贵、统计错误或索引相关性低，Seq Scan 可能更优。

误区六：主键就是聚簇索引

更准确：PostgreSQL heap table 不按主键永久组织。PRIMARY KEY 通常是唯一 B-tree + 约束。CLUSTER 也不是持续自动维护的聚簇存储。

误区七：复制等于备份

更准确：复制会同步误操作。备份必须提供历史点、权限隔离、保留策略和恢复验证。

误区八：提高 max_connections 就能提高吞吐

更准确：超过资源能力后，更多 Backend 只会增加内存、调度、锁和 I/O 竞争。

误区九：shared_buffers 命中率越接近 100% 越好

更准确：命中率需要结合工作负载。大规模顺序扫描、数据仓库或 OS Cache 行为可能使单一命中率失去解释力。

误区十：只要 autovacuum 在运行就不会膨胀

更准确：长事务、复制槽、写入速度、索引结构、vacuum 配额和表级阈值都可能让清理跟不上。

17. 最终心智模型

把 PostgreSQL 压缩成一张图：

flowchart TB
    SQL[SQL]
    PROC[Backend Process]
    PLAN[Parser + Rewriter + Planner]
    EXEC[Executor]
    SNAP[MVCC Snapshot]
    AM[Table / Index Access Method]
    CACHE[Shared Buffers]
    PAGE[Page + Tuple + TID]
    WAL[WAL + LSN]
    COMMIT[Commit]
    BG[Checkpoint / Writer / Vacuum]
    DISK[Data Files + pg_wal]
    BACKUP[Base Backup + WAL Archive + PITR]

    SQL --> PROC --> PLAN --> EXEC
    EXEC --> SNAP --> AM --> CACHE --> PAGE
    EXEC --> WAL --> COMMIT
    CACHE --> BG --> DISK
    WAL --> DISK
    DISK --> BACKUP
    BG --> SNAP

可以记成下面这段话：

PostgreSQL 由 Postmaster 管理多个 Backend 和后台进程。每条 SQL 在 Backend 中经过解析、重写、优化和执行；Executor 通过表与索引访问方法取得页面和元组；Shared Buffers 缓存数据页，MVCC Snapshot 决定元组版本是否可见；写操作先生成 WAL，COMMIT 主要等待必要 WAL 持久化，脏页由后台逐步写入数据文件；UPDATE/DELETE 产生的旧版本由 VACUUM 清理；索引保存搜索键到 heap TID 的映射；物理 base backup 与连续 WAL 共同提供崩溃恢复和时间点恢复能力。

真正掌握 PostgreSQL，不是记住几十个参数，而是能回答四个问题：

这个查询为什么选择了当前执行计划？
这个事务当前能看到哪个元组版本？
这次提交依赖哪些 WAL 和页面状态？
发生误删或崩溃后，准备从哪份基线和哪段 WAL 恢复？

能把这四个问题讲清楚，PostgreSQL 的核心架构就不再是零散名词，而是一套完整、可推演的系统。

18. 官方资料

本文主要依据 PostgreSQL 18 官方文档整理。建议继续阅读以下章节：

数据库 > PostgreSQL

#PostgreSQL #备份恢复 #PGSQL #数据库架构 #MVCC #WAL #索引 #Mermaid

PostgreSQL 架构原理详解：进程、存储、MVCC、WAL、索引与备份恢复

https://allendericdalexander.github.io/2026/07/28/archtect/db/postgresql-architecture-deep-dive/

作者

AtLuoFu

发布于

2026年7月28日

许可协议

Java 核心机制深度解析：泛型、反射、动态代理、SPI、枚举、注解、Lambda、equals/hashCode 与表达式引擎上一篇

Java 测试体系实战：JUnit、Mockito、MockMvc 与 PowerMock 下一篇