9.PostgreSQL的技术内幕

PostgreSQL的技术内幕

表中的系统字段

每个表都有多个系统字段，这些字段是由系统隐式定义的。
这些系统字段在psql中使用“\d”命令返回的结果中并不显示，所以需要记住实际表中还存在这些隐含字段。因为表中已隐含这些名字的字段，所以用户定义的名称不能与这些字段的名称相同，这一限制与名字是否为关键字没有关系，即使字段名称用双引号括起来也不行。

这些系统字段如下：

·oid：行对象标识符（对象ID）。该字段只有在创建表时使用了“with oids”或配置数“default_with_oids”的值为真时出现。该字段的类型是oid（类型名和字段名相同）。
·tableoid：包含本行的表的oid。对父表（该表存在有继承关系的子表）进行查询时，使用此字段就可以知道某一行来自父表还是子表，以及是来自哪个子表。tableoid可以和pg_class的oid字段连接起来获取表名字。
·xmin：插入该行版本的事务ID。
·xmax：删除此行时的事务ID，第一次插入时，此字段为0。如果查询出来此字段不为0，则可能是删除这行的事务还未提交，或者是删除此行的事务回滚了。
·cmin：事务内部的插入类操作的命令ID，此标识是从0开始的。
·cmax：事务内部的删除类操作的命令ID，如果不是删除命令，此字段为0。
·ctid：一个行版本在它所处的表内的物理位置。后面将重点介绍oid、xmin、xmax、cmin、cmax、ctid，而
tableoid比较简单，就不详细介绍了。

oid

ctid

ctid表示数据行在它所处的表内的物理位置。ctid字段的类型是tid。尽管ctid可以非常快速地定位数据行，但每次VACUUM FULL之后，数据行在块内的物理位置会移动，即ctid会发生变化，所以ctid是不能作为长期的行标识符的，应该使用主键来标识逻辑行。

select ctid, id from t limit 10;
ctid | id
--------+----
(0,1) | 1
(0,2) | 1
(0,3) | 2
(0,4) | 3
(0,5) | 4
(0,6) | 5
(0,7) | 6
(0,8) | 7
(0,9) | 8
(0,10) | 9
从上例中可以看出，ctid由两个数字组成，第一个数字表示数据行所在的物理块的物理块号，第二个数字表示数据行在物理块中的行号。

xmin、xmax、cmin、cmax

xmin、xmax、cmin、cmax这4个字段在多版本实现中用于控制数据行是否对用户可见。PostgreSQL将修改前后的数据存储在相同的结构中，分为以下几种情况：

·新插入一行时，将新插入行的xmin填写为当前的事务ID，xmax填“0”。
·修改这一行时，实际上新插入一行，原数据行上的xmin不变，xmax改为当前的事务ID，新数据行上的xmin填为当前的事务ID，xmax填“0”。
·删除一行时，把被删除行上的xmax填写当前的事务ID。从上面的叙述中就可以知道，xmin就是标记插入数据行的事务ID，而xmax就是标记删除数据行的事务ID。

注意：
从上面的叙述中就可以知道，xmin就是标记插入数据行的事务ID，而xmax就是标记删除数据行的事务ID。

没有修改数据行的操作，因为修改数据行，实际上就是把原数据行上的xmax标记上自己的事务ID（相当于打上删除标记），然后再新插入一条记录。

cmin和cmax用于判断同一个事务内的不同命令导致行版本的变化是否可见。
如果一个事务内的所有命令都是严格顺序执行的，那么每个命令都能看到之前该事务内的所有变更，这种情况下不
需要使用命令标识。一般编程中，遍历一个数组或列表时，是不允许在遍历过程中删除或增加元素的，因为这样会导致逻辑错误。而在数据库中，对游标进行遍历时，可以对游标引用的表进行插入或删除行的操作而不会出现逻辑错误，这是因为游标是一个快照，遍历过程中的删除或增加操作不会影响游标的数据，遍历游标时看到的是声明游
标时的数据快照而不是执行时的数据，所以它在扫描数据时会忽略声明游标后对数据的变更，因为这些变更对该游标都应该是无效的。

游标后续看到的数据都是声明游标之前的快照，相当于游标与后续的命令并发交错执行，这与事务之间的交错执行类似，存在数据可见性的问题。

PostgreSQL使用与解决事务内可见性问题类似的方法引入了命令ID的概念。行上记录了操作这行的命令ID，当其他命令读取这行数据时，如果当前的命令ID大于等于数据行上的命令ID，说明这行数据是可见的；如果当前的命令ID小于数据行上的命令ID，则这条数据不可见。

命令ID的分配规则如下：

·每个命令使用事务内一个全局命令标识计数器的当前值作为当前命令标识。
·事务开始时，命令标识计数器被置为初值“0”。
·执行更新性的命令时，如INSERT、UPDATE、DELETE、SELECT...FOR UPDATE，在SQL命令执行后命令标识计数器加1。
·当命令标识计数器经过不断累加又回到初值“0”时，报错“cannot have more than 2^32-1 commands in a transaction”，即一个事务中命令的个数最多为232-1个。

多版本并发控制

多版本并发控制（Multi-Version Concurrency Control，MVCC），是数据库中并发访问数据时保证数据一致性的一种方法。

多版本并发控制的原理

在并发操作中，当正在写时，如果有用户在读，这时写可能只写了一半，如一行的前半部分刚写入，后半部分还没有写入，这时可能读的用户读取到的数据行的前半部分数据是新的，后半部分数据是原来的，这就导致了数据一致性问题。解决这个问题的最简单的方法是使用读写锁，写的时候不允许读，正在读的时候也不允许写，但这种方法会导致读和写的操作不能并发执行。

于是，有人想到了一种能够让读写并发执行的方法，这种方法就是MVCC。MVCC方法是写数据时，原数据并不删除，并发的读还能读到原数据，这样就不会有数据一致性问题了。

实现MVCC的方法有以下两种。

·第一种：写新数据时，把原数据移到一个单独的位置，如回滚段中，其他用户读数据时，从回滚段中把原数据读出来。
·第二种：写新数据时，原数据不删除，而是把新数据插入进来。

PostgreSQL数据库使用的是第二种方法，而Oracle数据库和MySQL数据库中的InnoDB引擎使用的是第一种方法。

PostgreSQL中的多版本并发控制

前面讲过，PostgreSQL中的多版本实现是通过把原数据留在数据文件中，新插入一条数据来实现多版本的功能的。如上所述，每张表上都有4个系统字段“xmin”“xmax”“cmin”“cmax”，这4个字段就是为多版本的功能而添加的。

• 当两个事务同时访问记录时，通过参考xmin和xmax的标记判断记录的版本，根据版本号与自己当前的事务标识进行比较，确定自己的数据权限。
• 当删除数据时，记录并没有从数据块中被删除，空间也没有立即释放。

PostgreSQL的多版本实现中首先要解决的是原数据的空间释放问题。PostgreSQL通过运行Vaccum进程来回收之前的存储空间，默认PostgreSQL数据库中的AutoVacuum是打开的，也就是说，当一个表的更新量达到一定值时，AutoVacuum自动回收空间。当然也可以关闭AutoVacuum进程，然后在业务低峰期手动运VACUUM命令来回收空间。

在PostgreSQL中，若一个事务执行失败，在数据文件中该事务产生的数据并不会在事务回滚时被清理掉。为什么要这样做呢？为什么不在事务提交时把这些数据标记成有效，而在事务回滚时把这些数据标记成无效呢？这是出于效率的考虑。若事务提交或回滚时再次标记数据，那这些数据就有可能会被刷新到磁盘中，再次标记会导致另一次I/O，从而降低性能。

PostgreSQL多版本的优劣分析

Oracle数据库和MySQL数据库的InnoDB引擎也都实现了多版本的功能，但它们与PostgreSQL的实现方式是不一样的，在这两个数据库中，旧版本的数据并不记录在原先的数据块中，而是被记录在回滚段中，如果要读取旧版本的数据，需要根据回滚段的数据重构旧版本数据。
P
ostgreSQL的多版本机制与Java虚拟机的垃圾回收机制比较相像。事务提交前，只需要访问原来的数据即可；提交后，系统更新元组的存储标识，直到Vaccum进程收回为止。

相对于InnoDB和Oracle，PostgreSQL的多版本的优势在于以下几点：

·事务回滚可以立即完成，无论事务进行了多少操作。
·数据可以进行很多更新，不必像Oracle和InnoDB那样需要经常保证回滚段不会被用完，也不会像Oracle数据库那样，经常遇到“ORA-1555”错误的困扰。

相对于InnoDB和Oracle，PostgreSQL的多版本的劣势在于以下几点：

·旧版本数据需要清理。PostgreSQL清理旧版本称为VACUUM，并提供了VACUUM命令进行清理。
·旧版本的数据会导致查询更慢一些，因为旧版本的数据存储于数据文件中，查询时需要扫描更多的数据块。

物理存储结构

目录

• PostgreSQL的技术内幕
  • 表中的系统字段
    • oid
    • ctid
    • xmin、xmax、cmin、cmax
  • 多版本并发控制
    • 多版本并发控制的原理
    • PostgreSQL中的多版本并发控制
    • PostgreSQL多版本的优劣分析
  • 物理存储结构
    • PostgreSQL中的术语
    • 数据块结构
    • Tuple结构
    • 数据块空闲空间管理
      • FSM（“Free Space Map）
    • 可见性映射表文件
  • 控制文件解密
    • 控制文件介绍
    • 数据库的唯一标识串解密
      • ✅ 查看命令（任一节点执行，主备结果一致）
      • 通过唯一标识符查看数据库创建的时间
      • 🔍 快速验证主备是否同属一套系统
      • 常见 FAQ
    • Checkpoint信息解密
      • ostgresql 和oracle和mysql 的检查点对比，以及哪些情况会触发检查点
      • 一、核心机制对比
      • 二、触发场景对照表（✔ = 会触发，✖ = 不会）
      • 三、小结速记
    • 与Standby相关的信息
  • WAL文件解密
    • WAL文件介绍
      • WAL文件名的秘密
    • WAL文件循环复用原理
  • PostgreSQL的特色功能
    • postgresql的规则系统

PostgreSQL数据库目前不支持使用裸设备和块设备，所以PostgreSQL数据库的表中的数据总是存储在一个或多个物理的数据文件中。具体的数据文件又分为多个固定大小的数据块，每行数据就存放在这些数据块中。本节主要讲解PostgreSQL数据文件中的数据块的结构原理及一些附加文件的存储原理。

PostgreSQL中的术语

PostgreSQL中有一些术语与其他数据库中的名称不一样，了解了这些术语的含义，就能更好地看懂PostgreSQL中的文档。
与其他数据库不同的术语有如下几个:

·Relation：表示表或索引，也就是其他数据库的Table或Index。
具体表示的是Table还是Index需要看具体情况。
·Tuple：表示表中的行，在其他数据库中使用Row来表示。
·Page：表示在磁盘中的数据块。
·Buffer：表示在内存中的数据块。

数据块结构

数据块的结构如图10-1所示。

数据块结构示意图

数据块的大小默认是8KB，最大是32KB，一个数据块中存储了多行的数据。
块中的结构是先有一个块头，后面记录了块中各个数据行的指针，行指针是向后顺序排列的，而实际的数据行内容是从块尾向前反向排列的。行数据指针与行数据之间的部分就是空闲空间。

• 块头记录了如下信息：

·块的checksum值。
·空闲空间的起始位置和结束位置。
·特殊数据的起始位置。
·其他一些信息。

• 行指针是一个32bit的数字，具体结构如下：

·行内容的偏移量，占用15bit。
·指针的标记，占用2bit。
·行内容的长度，占用15bit。

行指针中表示行内容的偏移量是15bit，能表示的最大偏移量是2^15=32768，因此在PostgreSQL中，块的最大大小是32768，即32KB。

Tuple结构

在PostgreSQL数据库中，Tuple是指数据行。行的结构如图10-2所示。
从图10-2中可以看出，行的物理结构是先有一个行头，后面跟了各项数据。

行头中记录了以下重要信息。

·oid、ctid、xmin、xmax、cmin、cmax、ctid：这些信息的含义在前面已介绍过。
·natts&infomask2：字段数，其中低11位表示这行有多少个列。其他的位则是HOT（Heap Only Touples）技术及行可见性的标志位。
·infomask：用于标识行当前的状态，比如行是否具有OID，是否有空属性，共有16位，每位都代表不同的含义。
·hoff：表示行头的长度。
·bits：是一个数组，用于标识该行上哪些字段（列）为空。

数据块空闲空间管理

FSM（“Free Space Map）

在表中的数据块中插入、更新和删除数据会在表中产生旧版本的数据，这些旧版本数据通过Vacuum进程的清理会在数据块中产生空闲空间。再向表中插入数据时，最好的办法就是继续使用这些旧数据块中的空闲空间，如果所有的新数据都分配新的数据块，会导致数据文件不断膨胀。

当插入新行时，如果多个数据块中都有空闲空间，应把数据行插到哪个有空闲空间的数据块中呢？首先，有空闲空间的数据块不一定能容纳下新的数据行，所以要插入一行数据时，首先要快速找到一个数据块，且此数据块中的空闲空间能够放下此数据行。要完成这一操作，要实现以下两个功能：

要完成这一操作，要实现以下两个功能：

·首先是要记录每个数据块空闲空间的大小。
·查找时，不能一个一个地找，要实现快速查找。

PostgreSQL数据库使用一个名为“FSM”的文件记录每个数据块的空闲空间。FSM是英文“Free Space Map”的缩写。

PostgreSQL为缩小FSM文件的大小，只使用一个字节来记录一个数据块中的空闲空间，很明显一个字节是无法记录空闲空间实际大小的，该字节值实际上代表空闲空间的一个范围，其方法如表10-2所示。

从表10-2中可以看到，如果该字节值为“0”，则表示数据块中存在的空闲空间大小的范围为0~31字节；如果为“1”，则表示空闲空间大小的范围为32～63字节，然后以此类推。

在PostgreSQL 8.4之前的版本中，使用一个全局的FSM文件来记录所有表文件的空闲空间，但这会导致管理的复杂和低效，所以从PostgreSQL 8.4版本之后，对每个数据文件创建一个名为“<表oid>_fsm”的文件，如假设一个表“test01”的OID为“25566”，则它的FSM文件名为“25566_fsm”。

可见性映射表文件

在PostgreSQL中更新、删除行后，数据行并不会马上从数据块中被清理掉，而是需要等VACUUM时清理。为了能加快VACUUM清理的速度并降低对系统I/O性能的影响，PostgreSQL在8.4.1版本之后为每个数据文件加了一个后缀为“_vm”的文件，此文件被称为可见性映射表文件，简称VM文件。VM文件中为每个数据块存储了一个标志位，用来标记数据块中是否存在需要清理的行。有该文件后，做VACUUM扫描此文
件时，如果发现VM文件中该数据块上的位表示该数据块没有需要清理的行，VACUUM就可以跳过对这个数据块的扫描，从而加快VACUUM清理的速度。

VACUUM有两种方式，一种被称为“Lazy VACUUM”，另一种被称为“Full VACUUM”，VM文件仅在Lazy VACUUM中使用，Full VACUUM操作则需要对整个数据文件进行扫描。

控制文件解密

控制文件介绍

PostgreSQL的控制文件记录了数据库的重要信息:

- 数据库的系统标识符“system_identifier”
- 系统表版本“Catalog versionnumber”
- 实例状态
- Checkpoint信息
- 数据页的块大小
- WAL日志的页大小及文件大小
- 一些实例备份和恢复信息

所以PostgreSQL的控制文件与Oracle数据库的控制文件的作用基本相同，都是记录数据库的重要信息，只是在细节上有所不同，PostgreSQL的控制文件没有Oracle数据库中的那么复杂。

在PostgreSQL中提供了pg_controldata命令显示控制文件中的内容：

[postgres@sit-mid ~]$ pg_controldata
WARNING: Calculated CRC checksum does not match value stored in file.
Either the file is corrupt, or it has a different layout than this program
is expecting.  The results below are untrustworthy.

WARNING: invalid WAL segment size
The WAL segment size stored in the file, 2048 bytes, is not a power of two
between 1 MB and 1 GB.  The file is corrupt and the results below are
untrustworthy.

pg_control version number:            942
Catalog version number:               201409291
Database system identifier:           7245106149576711114
Database cluster state:               in production
pg_control last modified:             2025年02月24日 星期一 09时16分22秒
Latest checkpoint location:           0/45FC600
Latest checkpoint's REDO location:    0/429A298
Latest checkpoint's REDO WAL file:    045FC6000000000000008534
Latest checkpoint's TimeLineID:       73385472
Latest checkpoint's PrevTimeLineID:   0
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0:1
Latest checkpoint's NextOID:          1993
Latest checkpoint's NextMultiXactId:  24653
Latest checkpoint's NextMultiOffset:  1
Latest checkpoint's oldestXID:        0
Latest checkpoint's oldestXID's DB:   1883
Latest checkpoint's oldestActiveXID:  1
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint:            2025年02月24日 星期一 09时15分41秒
Fake LSN counter for unlogged rels:   0/0
Minimum recovery ending location:     0/0
Min recovery ending loc's timeline:   0
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
wal_level setting:                    unrecognized wal_level
wal_log_hints setting:                on
max_connections setting:              0
max_worker_processes setting:         64
max_wal_senders setting:              8
max_prepared_xacts setting:           0
max_locks_per_xact setting:           1093850759
track_commit_timestamp setting:       off
Maximum data alignment:               131072
Database block size:                  64
Blocks per segment of large relation: 32
WAL block size:                       1996
Bytes per WAL segment:                2048
Maximum length of identifiers:        65793
Maximum columns in an index:          0
Maximum size of a TOAST chunk:        1788814551
Size of a large-object chunk:         0
Date/time type storage:               64-bit integers
Float8 argument passing:              by reference
Data page checksum version:           0
Mock authentication nonce:            0000000000000000000000000000000000000000000000000000000000000000

数据库的唯一标识串解密

“Database system identifier” 是 PostgreSQL 集群级别（实例级别）的终极唯一标识，它是在 initdb 时生成的 64 位整数，整个集群（主+所有备）复制关系里永远相同，不会因为角色切换、时间线变化、备库提升而改变。

数据库的唯一标识串是在Initdb初始化数据库实例时生成的，它是一个64bit的整数。该整数由当前的时间戳和执行Initdb进程的PID的两个部分组成，生成的算法可参见PostgreSQL源码xlog.c中的BootStrapXLOG函数，内容如下：

pg_control version number:
gettimeofday(&tv, NULL);
sysidentifier = ((uint64) tv.tv_sec) << 32;
sysidentifier |= ((uint64) tv.tv_usec) << 12;
sysidentifier |= getpid() & 0xFFF;

从上面的算法中我们可以知道，高44位的时间戳中由于取了时间戳的微秒部分，所以重复的概率极低。低12位是进程PID。
PostgreSQL 的 Database system identifier 由两部分拼成：

• 创建时的时间戳（秒级，32 bit）
• 创建时的进程 PID（16 bit）
  initdb 把这两段拼成一个 64 位整数，保证同一台机器、不同时间，或同一时间、不同机器都极难重复，从而全球唯一。

✅ 查看命令（任一节点执行，主备结果一致）

-- SQL 方式
SELECT system_identifier FROM pg_control_system();

-- 操作系统方式（无需登录数据库）
pg_controldata $PGDATA | grep "Database system identifier"

输出示例：
Database system identifier:       7162869466821718113

通过唯一标识符查看数据库创建的时间

SELECT to_timestamp(((6531601841114581486>>32) & (2^32-1)::bigint));

🔍 快速验证主备是否同属一套系统

1.主库执行上面命令拿到 ID。
2.备库执行同样命令。
3.两个数字完全一致 → 属于同一套复制拓扑；
若不同 → 备库是“外来”实例，绝对不能直接搭流复制，需重做 pg_basebackup。

常见 FAQ

问题	回答
能改吗？	不能，除非重新 initdb。
主备角色切换后会变吗？	不会，终身不变。
时间线（timeline）升了会变吗？	不会，timeline 只是 WAL 分支号，systemid 固定。
和 OID、ctid、filenode 区别？	后者都是库/表/行级标识；systemid 是整个 PostgreSQL 实例的“身份证号码”。

一句话总结
想确认“这几台是不是一套 PostgreSQL 复制家族”，直接比对 system_identifier —— 数字相同即同宗，不同即异源。

Checkpoint信息解密

我们先简单介绍一下什么是检查点（Checkpoint），可以想象一个场景：如果WAL重做日志可以无限地增大，如果仅从不丢失数据的角度来看是不需要把缓冲池中的脏数据块写入磁盘的，因为当发生宕机时，完全可以通过WAL重做日志来恢复整个数据库系统中的数据到宕机发生的时刻。但这种想法明显存在以下几个问题：

·WAL重做日志不可以无限增大，因为WAL日志会占用一定的空间。
·重放WAL日志会占用时间，不可能一个数据库宕机后我们花费很长时间来进行恢复，通常需要在有限的时间内完成恢复，如在几分钟之内完成。
·缓冲区不可能无限大，所以不管怎么样，都需要把一定的脏数据刷新到磁盘中，需要考虑必须要先刷新哪些脏数据等问题。

所以检查点只是一个数据库事件，该事件触发后将会执行一个操作，而此操作可以保证把事件之前的脏数据全部刷新到磁盘中。

ostgresql 和oracle和mysql 的检查点对比，以及哪些情况会触发检查点

以下从“检查点实现机制”与“触发场景”两个维度，对 PostgreSQL、Oracle、MySQL（InnoDB）进行横向比较，并给出各产品最常见的触发条件一览表，方便快速对照。

一、核心机制对比

维度	PostgreSQL	Oracle	MySQL (InnoDB)
检查点类型	只有“全量”(full)一种，内部用spread方式分批写脏页，无增量概念	1. 完全检查点(full) 2. 增量检查点(incremental) 3. 局部/线程检查点(partial/thread)	1. Sharp(全量) 2. Fuzzy/Incremental(模糊/增量) —— 日常主要方式
脏页写进程	background writer(bgwriter) + checkpointer	DBWn（可多进程）	page-cleaner线程（可并发）
配合日志写	WAL → 先写日志再写数据；检查点刷脏后把 redo 点写回 pg_control	LGWR 先写 redo → DBWn 按检查点队列写脏块 → CKPT 更新控制文件	redo buffer → log thread → 模糊检查点异步刷脏
恢复速度控制	由 checkpoint_timeout + max_wal_size 共同决定；无 MTTR 目标	FAST_START_MTTR_TARGET 直接设定实例恢复秒数，增量检查点保证在该时间内完成	innodb_io_capacity / max_dirty_pages_pct 间接影响恢复时长，无秒级 MTTR 参数
用户命令	CHECKPOINT (superuser)	ALTER SYSTEM CHECKPOINT;	FLUSH TABLES ... FOR EXPORT / innodb_checkpoint_age 仅内部

二、触发场景对照表（✔ = 会触发，✖ = 不会）

触发场景	PostgreSQL	Oracle	MySQL(InnoDB)	备注
1. 时间间隔到达	✔ checkpoint_timeout (默认5 min)	✔ 3-s 心跳 + LOG_CHECKPOINT_TIMEOUT	✔ innodb_io_capacity 控制模糊频率	三者均有“定时”逻辑
2. WAL/redo 量超阈值	✔ WAL ≥ max_wal_size	✔ 联机日志切换/最小日志文件 90%	✔ redo 空间 ≥ 75% 或 innodb_max_dirty_pages_pct	PostgreSQL 与 Oracle 均用“日志用量”做硬阈值
3. 日志切换(log switch)	✖（PG 无日志组概念）	✔ 每次 switch logfile	✔ 模糊检查点(类似增量)	Oracle 日志切换一定触发；MySQL 8+ 类似
4. 正常关库	✔ shutdown/smart/fast	✔ shutdown normal/immediate	✔ 正常关服务	均做“完全”检查点保证干净落盘
5. 手动命令	✔ CHECKPOINT	✔ ALTER SYSTEM CHECKPOINT	✖ 无公开命令(内部 ib_checkpoint)	MySQL 只能通过外部 flush 间接影响
6. 表空间只读/脱机	✖	✔ ALTER TABLESPACE ... READ ONLY/OFFLINE	✖	Oracle 会触发局部检查点
7. 开始热备	✔ pg_start_backup() 隐含 force	✔ ALTER TABLESPACE/DATABASE BEGIN BACKUP	✔ FLUSH TABLES ... FOR EXPORT	均要保证备份瞬间一致性
8. 实例恢复结束	✔ end-of-recovery checkpoint	✔ 打开数据库时自动	✔ crash-recovery 后	防止再次重做，标记一致点
9. 参数/内部阈值	✔ 计算出的 CheckPointSegments	✔ FAST_START_MTTR_TARGET	✔ innodb_io_capacity & dirty page%	内部算法不同，目的都是平衡 I/O 与恢复时长

三、小结速记

• 1.PostgreSQL
  • 只有“全量”检查点，但通过 checkpoint_completion_target 把脏页摊平到整个周期，用户体验接近“增量”。
  • 两大触发器：时间 (checkpoint_timeout) + WAL 量 (max_wal_size)。
• 2.Oracle
  • 日常由增量检查点负责，3 秒心跳+MTTR 目标；完全检查点只在关库、日志切换、手动等场景才做。
  • 类型最丰富（full/incremental/partial/thread），可精细控制。
• 3.MySQL (InnoDB)
  • 主流是模糊/增量检查点；sharp 仅存在于关库或 innodb_fast_shutdown=0 时。
  • 触发逻辑耦合在 redo 空间占比与脏页比例，参数 innodb_io_capacity/max_dirty_pages_pct 是调优关键。

这样即可根据场景快速判断“谁的检查点更频繁”或“为什么关库时间差距大”。

与Standby相关的信息

如果我们用pg_controldata显示备库的控制文件会发现以下两项不同。
在主库中下面这两项都是“0/0”和“0”：

Minimum recovery ending location: 0/0
Min recovery ending loc's timeline: 0

而在备库中这两项不为“0”，示例如下：

Minimum recovery ending location: 0/271E81A8
Min recovery ending loc's timeline: 1

WAL文件解密

WAL文件介绍

WAL文件是“Write Ahead Log”的简称，就是数据库重做日志，与Oracle的Redo Log的功能是一样的。

WAL文件在PostgreSQL9.X及以下版本是在pg_xlog目录下的，而在PostgreSQL10.X及以上版本是在pg_wal目录下的。

WAL文件名的秘密

初学者会看不懂24个字母长度的WAL文件名，为了解释清楚其中的奥秘，我们先解释一下另一个概念LSN，即“Log SequenceNumber（日志序列号）”，是一个不断增长的8字节（64bit）长数字，用于记录WAL日志的绝对位置，随着数据库WAL日志的不断增加，LSN也会不断地增长。

而WAL文件名的24个字符由三部分组成，如图10-4所示
WAL文件名由下面三部分组成。

·时间线：英文为timeline，是以1开始的递增数字，如1，2，3，…。
·LogId：32bit长的一个数字，是以0开始递增的，如0，1，2，3，…。实际为LSN的高32bit。
·LogSeg：32bit长的一个数字，是以0开始递增的，如0，1，2，3，…。LogSeg是LSN的低32bit的值再除以WAL文件大小（通常为16MB）的结果。注意：当LogId为0时，LogSeg是从1开始的。

WAL日志文件默认大小为16MB，如果想改变其大小，在PostgreSQL10.X及之前的版本中需要重新编译程序，在PostgreSQL11.X版本之后，可以在Initdb初始化数据库实例时指定WAL文件的大小。

WAL文件循环复用原理

当我们查看WAL日志的目录时，从表面上来看好像是旧文件不断地被删除，新的WAL文件会不断地产生。熟悉Oracle数据库的人会发现这点与Oracle数据库中的Redo Log不一样。Oracle数据库中Redo Log的个数固定，是会被循环覆盖的。这种以循环覆盖的方式写Redo Log的机制在文件系统上相比于append方式有更高的性能，因为对于文件系统来说，如果用append方式，文件不断地增加时，除了添加的内容数据，文件的尺寸大小数据也需要持久化下去，这样相当于每次写产生了两次I/O，一次是内容数据的I/O，一次是记录文件大小的I/O。而对于Oracle数据库是先初始化Redo Log，真正在写Redo Log时是覆盖写，这样每次写，只有一次I/O。

所以从表面上来看，PostgreSQL不是循环覆盖写，这样看起来，PostgreSQL在WAL日志这一块要比Oracle性能低，但实际上，PostgreSQL也是循环覆盖写，WAL写的性能并不比Oracle中低，下面我们详细讲解一下，PostgreSQL循环覆盖写的原理。

PostgreSQL的循环覆盖写是通过把旧的WAL日志“重命名”来实现的。发生一次Checkpoint之后，此Checkpoint点之前的WAL日志文件都可以删除，而PostgreSQL中一般并不会将其删除，而是“重命名”旧的WAL文件使之成为一个新的WAL文件。

所以WAL文件目录下文件序号最大的那个WAL文件并不是当前正在写的WAL文件，因为这个WAL文件有可能是前一次Checkpoint时重命名旧文件产生的。我们用一个示例来说明这种情况。

查看当前正在写的WAL日志

select pg_walfile_name(pg_current_wal_lsn());

上面的SQL语句中先用函数“pg_current_wal_lsn”获得当前正在写的LSN号，然后用函数“pg_walfile_name”找出当前LSN号对应的WAL文件

PostgreSQL的特色功能

postgresql的规则系统

PostgreSQL 的规则系统（Rule System）是一套查询重写机制，它允许用户定义“规则”，把某些 SQL 查询在运行前自动改写成另一种形式，再交给优化器/执行器去跑。
核心要点如下：
1.工作阶段
规则发生在解析之后、规划器之前。
输入和输出都是一棵“查询树”（Query Tree），只是把树形结构做了宏展开式的替换，语义层不变。
2.规则 vs 触发器
规则：改写查询本身，对所有会话可见，发生在真正执行之前。
触发器：数据行已经确定要插入/更新/删除时，才在指定表上执行一段函数，可看到行级新旧值。

3.典型用途
实现可更新视图：把对视图的 INSERT/UPDATE/DELETE 改写成对底层基表的操作。
查询分流或审计：把对敏感表的 SELECT 改写成带 WHERE 过滤或 UNION 日志表的查询。
兼容旧版语法、实现“软”行级安全（RLA）等。
4.创建语法

CREATE [ OR REPLACE ] RULE name AS ON {SELECT|INSERT|UPDATE|DELETE}
TO table_name [ WHERE condition ]
DO [ ALSO | INSTEAD ] { command | (command; command ...) }

- ALSO：先执行原查询，再执行规则命令。
- INSTEAD：完全替换原查询。

5.限制与注意

• 规则没有触发器那种 OLD/NEW 伪记录，也不能抛异常做校验；复杂逻辑请用触发器。
• 多条规则可能产生歧义，顺序由系统内部定，调试需看重写后的计划。
• 对 COPY、TRUNCATE、ALTER 等命令无效；分区表场景下规则往往不如继承+触发器直观。

一句话：规则系统就是“提前把 SQL 改掉”的宏工具，适合视图更新和批量查询改写；真正要“看到行值、做校验、抛错误”还得靠触发器