SSTables：Sorted String Table ： 每个segment文件中key值是有序（根据key值排序）并且唯一的。sstable有如下优势：

• merge简单高效
• 因为有序，不需要维护所有记录的索引，可以是稀疏索引
• 因为有序，可以按block进行压缩同时维护索引，除了降低磁盘占用以外，还可以降低磁盘io

构建和管理SSTables

因为写入是无序的，所以我们需要在内存中借助于红黑树或者avl树等数据结构来保证：任意写入，有序读取.

构建步骤如下:

1. 当写入发生时，将写入的key-value加入内存平衡树（如红黑树）中，称为”memtable”
2. 当memtable增大到一定阈值后（a few megabytes），将memtable写入磁盘生成一个SSTable文件。新写入的SSTable成为数据库中最新的segment文件。 写入SSTable到磁盘时，写入可以继续写入新的memtable
3. 读请求来时，优先在memtable中查找，然后按顺序从新到老查找磁盘上的segment文件
4. 随着时间的推移，可以后台启动合并和压缩segment文件、丢弃或者覆盖掉老的文件

这种机制工作的很好，但是存在一个问题，如果数据库宕机了，内存中的memtable还没有来得及写入磁盘，可能造成数据丢失。可以通过一个单独的log文件来记录所有的操作，这个log的作用只是用于宕机恢复memtable，每当memtable被写入磁盘，相应的log文件就可以被丢弃了

用SSTables构建LSM-tree

LSM-Tree用于 LevelDB 和 RocksDB、嵌入其他应用的key-value存储引擎。参考：Google’s Bigtable paper

SSTables + log = “Log-Structured Merge-Tree ”

LSM-tree两种文件管理策略：

1. Size-Tiered Compaction（STC）

   • 基本思想
     “当某一层（或同一大小区间）的 SSTable 文件数达到阈值时，将它们合并成一个更大的文件。”
     也就是说，不是按“层级”，而是按“文件大小”来触发 compaction。

   • 结构示意
     MemTable → SSTable

   • 优点

     • 写放大低
       每条数据在被 flush 后只会参与少数几次合并；每次合并是“几块 → 一块”，合并次数较少。
     • 写入吞吐高
       flush 后直接落地；合并是批量异步进行；对写性能非常友好。

   • 缺点

     • 读放大高
       同一 key 可能存在于多个 SSTable 中；读时需要查多个文件（除非借助 Bloom Filter）。
     • 空间放大高
       合并不够积极；多个旧版本的数据同时存在；临时文件和重复 key 较多。

   • 应用场景
     适合写多读少的场景；比如日志系统、时间序列数据库（TSDB）、写入密集的监控系统Cassandra 默认采用 STCS（Size-Tiered Compaction Strategy）

2. Level Compaction（LC）
   这种策略是 LevelDB、RocksDB 采用的，更现代化。又叫 leveled compaction 或 分层压实。

   • 基本思想：
     数据被组织成多个层级（Level 0, Level 1, Level 2…），每一层都有固定大小的空间限制，同一层内文件的 key 范围 互不重叠。

   • 层次结构：
     Level 0: 多个小 SSTable，key 范围可能重叠。
     Level 1: 较大文件，key 范围不重叠。
     Level 2: 更大文件，key 范围不重叠。
     …

   • Compaction 逻辑：
     当某一层（如 Level 0）容量超标；就选择一个 SSTable（或一组）与下一层（如 Level 1）中 key 范围重叠的文件；合并、去重、重写为更大的 SSTable，放入下一层。

   • 优点

     • 读性能优异
       除 Level 0 外，其余层内文件 key 范围不重叠；读取某个 key 只需查找每层最多一个文件；查找代价从 O(n_files) 降到 O(levels)。
     • 空间利用率高
       去重及时；每层占用空间接近固定比例；不容易膨胀。

   • 缺点

     • 写放大高
       每条数据会多次参与合并（从 L0 → L1 → L2…）；每次都要重写到下一层； 磁盘写入量是原始写入的数倍。
     • Compaction 代价大
       大文件之间的合并非常消耗 I/O；RocksDB 必须限制后台 compaction 线程数量。

   • 应用场景
     适合读写比较均衡、查询多的系统；比如 RocksDB、LevelDB、TiKV、ClickHouse 的部分引擎。

性能优化

当查找的key在数据库中不存在时，LSM-Tree算法可能会变慢：在你确认key不存在之前，你需要检查所有memtable，所有sstable对应的磁盘文件。可以使用bloom filters：它可以检查key不在数据库中（每个sstable固化时，生成对应的bloom filter文件）

Here are some tips for using omnigraffle

Draw circle

Hold down Shift while drawing a circle

Draw curve

1. select pen tool: draw custome sharps
2. Click one point on the canvas, then click another point — a straight line will appear.
3. Select the line → double-click the middle of the line → drag the new point: a curved effect will appear.

Flexible Control Over Line Arrows

When the endpoint of a line (especially an endpoint with an arrow) is connected to a shape, the default mode is “Connected Line” (automatic snapping). In this mode, the endpoint is “locked” to the shape’s boundary or center, and cannot be freely dragged.

Solution

1. Turn on Magnets
   • Select the target shape.
   • Menu Bar → Edit → Magnets → Show Magnets
   • You will see small red dots (magnets) appear around the shape.
   • These are the connectable positions.
   • Drag the endpoint of a line near the red dot, and it will snap/attach to that point.

What is Magnet?

Magnet (magnetic points) are those positions on the shape (Shape) that can attract the end points of the lines.
When you draw a line with the Line Tool, endpoints that are close to these magnetic points will “snap” to it.

Examples

配置checkpoint

checkpoint持续时间（更准确地说，是将脏缓冲区写入磁盘所需的时间）由参数 checkpoint_completion_target 决定。该参数的值表示checkpoint 期间的 I/O 分布目标，是一个比例。避免将该参数设置为 1：否则可能会导致下一个checkpoint启动时，上一个checkpoint尚未完成。虽然不会发生灾难性的后果（因为同一时间只能执行一个checkpoint），但正常运行可能仍会受到干扰。

在配置其他参数时，我们可以采用以下方法。首先，确定在两个相邻checkpoint之间应存储的 WAL 文件的合理体积。这个体积越大，系统开销就越小，但它仍然会受到可用磁盘空间和可接受的恢复时间的限制。

为了估算在正常负载下生成这一体积所需的时间，你需要记录初始的 insert LSN，并定期检查它与当前 insert 位置之间的差值

我们将前面计算出的数值视为checkpoint之间的典型间隔，因此将其用作 checkpoint_timeout 参数的值。默认设置往往偏小，通常会将其增加，例如设置为 30 分钟。

但也很可能（甚至可以说是很常见）负载会在某些时候升高，从而导致在这个时间间隔内生成的 WAL 文件体积过大。在这种情况下，必须更频繁地执行checkpoint。为了设置这样的触发机制，我们通过 max_wal_size 参数来限制恢复时所需的 WAL 文件总量。当超过这个阈值时，服务器会额外执行一次checkpoint。

恢复所需的 WAL 文件包含了上一个已完成checkpoint和当前尚未完成检查点的所有记录。因此，为了估算这些文件的总体积，应将计算出的检查点之间的 WAL 大小乘以
1 + checkpoint_completion_target。

在 PostgreSQL 11 版本之前，系统会保留两个已完成checkpoint所对应的 WAL 文件，因此估算恢复所需 WAL 总体积的倍数是：2 + checkpoint_completion_target

按照这种方式，大多数checkpoint会按计划执行，即按照 checkpoint_timeout 设置的时间间隔执行一次；但如果系统负载增加，导致生成的 WAL 文件大小超过 max_wal_size 的设定值，就会提前触发一次checkpoint。

系统还会定期将实际进度与预期数值进行比较，以监控写入进度是否达标。

实际进度由已处理的缓存页所占的比例来定义。

按时间计算的预期进度是指已过去的时间占比，其前提假设是checkpoint必须在 checkpoint_timeout × checkpoint_completion_target 的时间内完成。

按大小计算的预期进度是指已写入的 WAL 文件所占的比例，其总量根据 max_wal_size × checkpoint_completion_target 来估算。

如果脏页提前写入磁盘，checkpointer 进程会暂停一段时间；如果在时间或数据大小的任何一个参数上出现延迟，它会尽快赶上进度。由于同时考虑了时间和数据大小，PostgreSQL 能够用同一套机制来管理定时checkpoint和按需checkpoint。

一旦checkpoint完成，不再需要用于恢复的 WAL 文件将被删除；不过，系统会保留若干文件（总大小不超过 min_wal_size），用于重复利用，并通过重命名的方式保留。

这种重命名机制减少了频繁创建和删除文件所带来的开销；如果你不需要这个功能，可以通过参数 wal_recycle 将其关闭。

下图展示了在正常情况下，磁盘上 WAL 文件大小的变化趋势

需要注意的是，磁盘上实际的 WAL 文件大小可能会超过 max_wal_size 的设定值，原因包括：

• max_wal_size 参数只是一个期望目标值，而不是严格限制。如果负载突然上升，写入速度可能会落后于计划，导致 WAL 文件积压。
• 尚未被复制或归档的 WAL 文件，服务器无权删除。如果启用了流复制或持续归档功能，必须持续监控这些机制，否则非常容易导致磁盘空间耗尽。
• 你可以通过配置 wal_keep_size 参数来预留一定的磁盘空间用于存储 WAL 文件，以避免这些问题带来的风险。

参考书目

1. Egor Rogov, PostgreSQL 14 Internals, https://postgrespro.com/community/books/internals

当数据库服务正常运行时，wal文件持续不断的写入磁盘。这种写入时顺序的（sequential）：几乎没有随机访问，所以即便时hdd硬盘也能处理这样的任务。由于这种负载和典型的数据文件访问非常不一样，值得为WAL文件设置单独的物理存储，并通过符号链接替换PGDATA/PG_WAL目录，该目录链接到mount的文件系统中的目录。

在某些情况下，需要同时写和读取WAL文件。第一种情况是崩溃恢复。第二个是流复制。 Walsender流程直接从文件中读取WALENTRIES。因此，如果必需的页面仍位于主服务器的OS缓冲区中（不在shared_buffer中），replica未接收WAL条目，则必须从磁盘中读取数据。但是访问仍然是顺序而不是随机的。

wal日志条目写入有以下两种模式：

• 同步模式：在事务提交之前，必须把所有相关的 WAL 记录写入磁盘，否则不允许继续执行后续操作。
• 异步模式：事务提交会立刻返回成功，相关的 WAL 记录则由后台进程稍后再写入磁盘。

当前使用的模式由参数：synchronous_commit 确定

同步模式：为了可靠地记录一次提交，仅仅将WAL条目传递给操作系统是不够的；你必须确保磁盘同步已经成功完成。由于同步涉及实际的I/O操作（这相当慢），因此最好尽可能少地执行它。

为此，完成事务并将WAL条目写入磁盘的后端可以进行一次小的暂停，该暂停由commit_delay参数定义。但是，只有当系统中至少有commit_siblings个活跃事务时，这种情况才会发生：在这次暂停期间，其中一些事务可能会完成，而服务器将设法一次性同步所有WAL条目。这很像为某人赶进来而按住电梯门。

默认情况下，没有暂停。仅针对执行大量短时OLTP事务的数据库系统修改commit_delay参数是有意义的。

在可能出现的暂停之后，完成事务的进程会将所有累积的 WAL 条目刷新到磁盘并执行同步操作（关键是要保存提交记录以及与该事务相关的所有前置记录；至于其他记录，则只是顺便写入，因为它们不会增加额外开销）。

从这一刻起，ACID 的持久性要求便得到保证——事务被认为已经可靠提交。这就是为什么默认使用同步模式的原因。

同步提交的缺点在于延迟更长（在同步完成之前，COMMIT 命令不会返回控制权），并且系统吞吐量较低，尤其对于 OLTP loads。

异步模式：要启用异步模式，必须关闭 synchronous_commit 参数。
在异步模式下，WAL 条目由 walwriter 进程写入磁盘，该进程在“工作—休眠”之间交替运行。休眠时长由 wal_writer_delay 参数决定（默认 200ms）。

当 walwriter 从休眠中唤醒时，它会检查缓存中是否存在新的、已经完全填满的 WAL 页面。如果存在，就将这些页面写入磁盘，同时跳过当前未写满的页面；否则，它会写入当前半空的页面，因为既然已经被唤醒了。

这种算法的目的在于避免同一个页面被多次刷盘，这在数据变更频繁的负载下能带来显著的性能提升。

虽然 WAL 缓存采用环形缓冲区（ring buffer）的形式，但 walwriter 在到达缓存的最后一页时会停止；在经过一次休眠后，下一轮写入循环会从缓存的第一页重新开始。因此，在最坏的情况下，walwriter 可能需要 三次循环才能处理某个特定的 WAL 记录：

第一次，它会写出缓存尾部的所有已填满页面；（当前位置之后的所有满块）
第二次，它回到开头；（当前位置之前的所有满块）
第三次，才会处理包含目标记录的那个未填满页面。
不过，在大多数情况下，只需要 一到两次循环 就能完成。

每当写入的数据量达到 wal_writer_flush_after 时，就会执行一次同步操作；在写入循环结束时，也会再次进行同步。

与同步提交相比，异步提交更快，因为它不需要等待物理写入磁盘完成。但可靠性有所下降：在发生故障前的 3 × wal_writer_delay 时间内提交的数据可能会丢失（默认值为 0.6 秒）

在实际应用中，这两种模式是互补的。

• 在 同步模式 下，与长事务相关的 WAL 条目仍然可以 异步写入，以释放 WAL 缓冲区。
• 反之，即使在 异步模式 下，如果某个 WAL 条目所在的页即将被 从缓冲区淘汰，该条目也会被 立即刷盘，否则系统无法继续正常操作。
  在大多数情况下，系统设计者必须在 性能和持久性之间做出权衡。

synchronous_commit 参数也可以针对特定事务进行设置。如果能够在应用层将所有事务分类为 绝对关键（如处理财务数据）或 非关键，就可以在只冒非关键事务丢失风险的前提下，提升整体性能

为了了解 异步提交 可能带来的性能提升，我们可以通过 pgbench 测试，比较两种模式下的 延迟（latency）和吞吐量（throughput）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

postgres@lavm-bar1guved6:/root$ pgbench -i test
dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
vacuuming...                                                                              
creating primary keys...
done in 1.54 s (drop tables 0.11 s, create tables 0.49 s, client-side generate 0.54 s, vacuum 0.18 s, primary keys 0.23 s).
 
postgres@lavm-bar1guved6:/root$ pgbench -T 30 test
pgbench (19devel)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
maximum number of tries: 1
duration: 30 s
number of transactions actually processed: 3522
number of failed transactions: 0 (0.000%)
latency average = <strong>8.518</strong> ms
initial connection time = 4.025 ms
tps = <strong>117.400485</strong> (without initial connection time)

修改参数后跑异步模式：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

postgres@lavm-bar1guved6:/root$ psql test
psql (19devel)
Type "help" for help.
 
test=# ALTER SYSTEM SET synchronous_commit = off;
ALTER SYSTEM
test=# SELECT pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)
 
postgres@lavm-bar1guved6:/root$ pgbench -T 30 test
pgbench (19devel)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
maximum number of tries: 1
duration: 30 s
number of transactions actually processed: 9510
number of failed transactions: 0 (0.000%)
latency average = <strong>3.154</strong> ms
initial connection time = 4.383 ms
tps = <strong>317.038330</strong> (without initial connection time)

在 异步模式 下，这个简单的基准测试显示出 显著更低的延迟（latency）和更高的吞吐量（tps）。当然，每个具体系统的数值会根据当前负载有所不同，但可以清楚地看出，对于 短事务，性能提升是相当明显的。

恢复参数：

1
2
3
4
5
6
7
8
9
10
11

postgres@lavm-bar1guved6:/root$ psql test
psql (19devel)
Type "help" for help.
 
test=# ALTER SYSTEM RESET synchronous_commit;
ALTER SYSTEM
test=# SELECT pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

参考书目

1. Egor Rogov, PostgreSQL 14 Internals, https://postgrespro.com/community/books/internals

昨日和同事探讨了一下pg与mysql，发现对于mysql的innodb存储引擎了解甚少，正好周末，深入学习了解了一下，整理一下这两天学到的东西。

概念介绍

InnoDB：MySQL 的主要存储引擎
heap table：postgres数据库P表在物理上的存储（Heap File）

数据存储方式

innodb 底层存储

innodb是一个基于聚簇索引（Clustered Index）的存储引擎。

数据存储方式

• 聚簇索引存储表数据
  • 表的主键索引就是数据本身的物理存储顺序。
  • 每个页（page）一般 16KB，页内数据按主键顺序排列。
  • 非主键列数据和行信息都在叶子节点。
• 二级索引
  • 非主键索引存储的是主键值而不是行指针。
  • 查询非主键列时，需要先通过二级索引拿到主键，再去主键聚簇索引里查数据（回表，row lookup）。

页与数据组织

• 数据按 B+ 树页组织。
• 插入/更新可能导致页分裂。

postgres heap table

数据存储方式

• 数据按插入顺序存放，没有聚簇索引。
• 行标识符 TID（tuple id） 用于指向表的页和行。
• 页（默认 8KB）内存储多行 tuple。
• 没有聚簇索引，非索引扫描可能会更慢，但插入非常快。

索引

• B+ 树、哈希、GIN、GiST 等索引都是附加结构，存储独立于表。
• 回表操作是通过 TID 定位行。

对比

因为pg的存储结构导致访问数据，多了许多随机io：从索引到数据文件。导致pg的查询性能不如innnodb

pg的一些优化

CLUSTER 命令（物理重排）

1

CLUSTER t USING idx_id;

把表物理顺序按照索引顺序重排 —— 效果类似 InnoDB 聚簇索引。
缺点：

• 是离线操作；
• 之后新插入的行会破坏顺序（除非周期性 recluster）

索引仅扫描（Index Only Scan）

如果查询的字段都在索引里且可见性检查通过（可见性 map 中标记为 all-visible），可以不访问堆表，直接从索引返回结果。

BRIN 索引

BRIN 全称是 Block Range INdex，是一种 非常轻量级的索引，设计理念与 B-Tree 不同：
不是存储每一行的索引它只存储堆表的一段范围（block range）内的最小值和最大值等摘要信息。每个 BRIN 索引条目覆盖 多个物理数据页（例如 128 个 8KB 页面 = 1MB 的行数据）。
通过这些范围信息，可以快速排除不可能匹配的块，再去 heap 查找具体 tuple。
简单理解：BRIN 是“粗粒度索引”，通过块范围（block range）而非单行建立索引
非常适合 大表 + 顺序或局部相关数据：大表 + 顺序或局部相关数据：

服务器启动时，第一个启动的进程是 postmaster（新版本为postgres）。postmaster 接着会生成 startup process（启动进程），startup process 负责在发生故障时进行数据恢复。

startup process 是一个短暂的、一次性的进程，它的主要职责是在数据库启动时执行崩溃恢复或归档恢复。它完成它的工作后，就会退出。

1
2
3

postgres@lavm-bar1guved6:/root$ pg_controldata -D /home/postgres/pgdata/ |grep state
Database cluster state:               in production
postgres@lavm-bar1guved6:/root$ 

一个正常停止的服务器会处于“已关闭”（shut down）状态；而一个未运行的服务器却显示为“生产中”（in production）状态，则表明发生了故障。在这种情况下，启动进程（startup process）将自动从在同一个 pg_control 文件中找到的最新完成的检查点（checkpoint）**的起始 LSN 处开始进行恢复。

如果 PGDATA 目录中包含与备份相关的 backup_label 文件，则起始 LSN 位置会从该文件中获取。

在启动过程中，系统会从指定位置开始，逐一读取WAL（Write-Ahead Log，预写式日志）条目。如果数据页的 LSN（Log Sequence Number，日志序列号）小于当前读取到的 WAL 条目的 LSN，系统会将该 WAL 条目应用到数据页上。如果数据页的 LSN 已经大于 WAL 条目的 LSN，则不应应用该 WAL 条目；事实上，也绝不能应用，因为 WAL 条目被设计为必须严格按顺序重放。

然而，有些 WAL 条目是Full Page Image（FPI）。这类条目可以应用于页面的任何状态，因为它们会完全覆盖页面内容，无论页面原先是什么状态都不重要。因此，这种修改是幂等的（idempotent）——多次应用不会改变结果另一个幂等操作的例子是注册事务状态的变更：每个事务的状态在 CLOG（事务提交日志）中是通过设置特定位来表示的，这种设置不依赖于原来的位值。因此，不需要在 CLOG 页面中记录最近变更的 LSN（日志序列号），因为日志重放时只要设置一次这些位就够了，重复设置也不会有副作用最后，系统会执行一次 checkpoint（检查点），将恢复后的所有修改持久化到磁盘，此时 启动进程（startup process） 的任务就完成了。

WAL 日志条目会被应用到缓冲池（buffer cache）中的页面上，就像正常运行时对数据页的普通修改一样。

文件的恢复也遵循类似方式：例如，若某条 WAL 记录表明某个文件应该存在，但实际却缺失，系统就会重新创建这个文件。
一旦恢复完成，所有 unlogged relations会被它们对应的 初始化副本（init fork） 覆盖。

最后，系统会执行一次 checkpoint，将恢复后的所有修改持久化到磁盘，此时 启动进程（startup process） 的任务就完成了
在其经典形式中，恢复过程包含两个阶段：

• roll-forward阶段：重放 WAL 日志，重复执行在崩溃时丢失的操作；
• roll-back阶段：服务器中止那些在故障发生时尚未提交的事务。
  在 PostgreSQL 中，向后回滚是不需要的。恢复完成后，CLOG（事务状态日志）中对未完成事务既没有提交（commit）标记，也没有中止（abort）标记（这在技术上表示该事务处于活动状态），但因为可以确定该事务已经不再运行，所以系统会将其视为已中止（aborted）。

我们可以通过强制服务器以“立即模式”（immediate mode）停止来模拟故障：

1
2
3
4
5

postgres@lavm-bar1guved6:/root$ pg_ctl stop -m immediate
waiting for server to shut down.... done
server stopped
postgres@lavm-bar1guved6:/root$ pg_controldata -D /home/postgres/pgdata/ |grep state
Database cluster state:               in production

当我们启动服务器时，启动进程会检测到之前发生了故障，因而进入恢复模式：

1
2
3
4
5
6
7
8
9
10
11

2025-08-04 10:23:31.860 CST [487414] LOG:  starting PostgreSQL 19devel on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
2025-08-04 10:23:31.861 CST [487414] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2025-08-04 10:23:31.878 CST [487414] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2025-08-04 10:23:31.920 CST [487420] LOG:  database system was interrupted; last known up at 2025-08-01 09:36:11 CST
2025-08-04 10:23:32.098 CST [487420] LOG:  database system was not properly shut down; automatic recovery in progress
2025-08-04 10:23:32.125 CST [487420] LOG:  redo starts at 0/01B75168
2025-08-04 10:23:32.125 CST [487420] LOG:  invalid record length at 0/01B752A8: expected at least 24, got 0
2025-08-04 10:23:32.125 CST [487420] LOG:  redo done at 0/01B75270 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.01 s
2025-08-04 10:23:32.132 CST [487418] LOG:  checkpoint starting: end-of-recovery fast wait
2025-08-04 10:23:32.152 CST [487418] LOG:  checkpoint complete: wrote 0 buffers (0.0%), wrote 3 SLRU buffers; 0 WAL file(s) added, 0 removed, 0 recycled; write=0.006 s, sync=0.005 s, total=0.022 s; sync files=2, longest=0.005 s, average=0.003 s; distance=0 kB, estimate=0 kB; lsn=0/01B752A8, redo lsn=0/01B752A8
2025-08-04 10:23:32.155 CST [487414] LOG:  database system is ready to accept connections

如果服务器正在正常关闭，postmaster 会先断开所有客户端连接，然后执行最后一次检查点操作，将所有脏页（未写入磁盘的修改页面）刷写到磁盘上。

看当前的WAL位置

1
2
3
4
5

test=# SELECT pg_current_wal_insert_lsn();
 pg_current_wal_insert_lsn 
---------------------------
 0/01B75358
(1 row)

我们正常停止服务

1
2
3

postgres@lavm-bar1guved6:~$ pg_ctl stop
waiting for server to shut down.... done
server stopped

现在的数据库状态：

1
2

postgres@lavm-bar1guved6:~$ pg_controldata -D /home/postgres/pgdata/ |grep state
Database cluster state:               shut down

在WAL的末尾，我们看到了表示最后一次checkpoint的CHECKPOINT_SHUTDOWN 条目

1
2
3
4

postgres@lavm-bar1guved6:~$ pg_waldump  -p /home/postgres/pgdata/pg_wal -s 0/01B75358
rmgr: XLOG        len (rec/tot):    114/   114, tx:          0, lsn: 0/01B75358, prev 0/01B75320, desc: CHECKPOINT_SHUTDOWN redo 0/01B75358; tli 1; prev tli 1; fpw true; wal_level replica; xid 0:758; oid 24576; multi 1; offset 0; oldest xid 746 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 0; shutdown
pg_waldump: error: error in WAL record at 0/01B75358: invalid record length at 0/01B753D0: expected at least 24, got 0
postgres@lavm-bar1guved6:~$ 

最新的 pg_waldump 消息显示该工具已读取 WAL到末尾。

参考书目

1. Egor Rogov, PostgreSQL 14 Internals, https://postgrespro.com/community/books/internals

一 相关结构体说明

1
2
3
4
5
6
7
8
9
10

typedef struct LWLock
{
    uint16           tranche;       /* tranche ID */
    pg_atomic_uint32 state;         /* state of exclusive/nonexclusive lockers */
    proclist_head    waiters;       /* list of waiting PGPROCs */
#ifdef LOCK_DEBUG
    pg_atomic_uint32 nwaiters;      /* number of waiters */
    struct PGPROC    *owner;        /* last exclusive owner of the lock */
#endif
} LWLock;

• tranche
  每个 LWLock 都属于某个 tranche，tranche 是一个 int 类型的 ID，代表这把锁的用途

• state （不同版本可能会有变化，本文对应版本：PostgreSQL 19devel（开发版，尚未正式发布的 19））

  • bit0..17：共享持有者计数（一个整数计数，不是位图）
  • bit18：独占锁哨兵位（LW_VAL_EXCLUSIVE = 1<<18）
  • bit29: LW_FLAG_LOCKED，是 wait list 自身的小锁（保护等待队列操作）
  • bit30: LW_FLAG_RELEASE_OK，表示当前可以在释放路径执行唤醒
  • bit31: LW_FLAG_HAS_WAITERS，表示等待队列里有 waiter

• waiters
  等待队列的head和tail （procno）

二 Interface说明

2.1 LWLockAcquire

加锁，失败进入等待队列，直接加上锁的情况，可能会造成等待队列无效唤醒

acquire a lightweight lock in the specified mode
Side effect: cancel/die interrupts are held off until lock release.

1
2
3
4
5
6
7
8
9
10
11
12

if (mode == LW_EXCLUSIVE)
{
    lock_free = (old_state & LW_LOCK_MASK) == 0;
    if (lock_free)
        desired_state += LW_VAL_EXCLUSIVE;
}
else
{
    lock_free = (old_state & LW_VAL_EXCLUSIVE) == 0;
    if (lock_free)
        desired_state += LW_VAL_SHARED;
}

上述代码确认是否可以加锁

2.2 LWLockRelease

进行唤醒条件：

• oldstate & LW_FLAG_HAS_WAITERS：等待队列非空。这个条件确认有进程在等待锁。
• oldstate & LW_FLAG_RELEASE_OK： 允许唤醒。这个标志表示系统在上次唤醒后，已经将再次唤醒其他等待者的权限交给了被唤醒的进程。
• (oldstate & LW_LOCK_MASK) == 0：锁已空闲。这个条件确认锁当前没有被任何进程占用（无论是独占模式还是共享模式，计数都为零）

三 分配与类型（默认值，版本相关）

1. NUM_INDIVIDUAL_LWLOCKS：核心按表生成的单锁（lwlocklist.h），数量随版本变化，不是固定 56。
2. NUM_BUFFER_PARTITIONS（默认 128）：共享缓冲区 mapping hash 的分区锁 BufferMappingLock[i]。
3. NUM_LOCK_PARTITIONS（默认 16）：heavyweight lock manager 哈希分区锁（LWTRANCHE_LOCK_MANAGER）。
4. NUM_PREDICATELOCK_PARTITIONS（默认 16）：谓词锁哈希分区锁。
5. 扩展自定义：在 postmaster 启动阶段调用 RequestNamedLWLockTranche(name, n) 申请一组锁，重启后 GetNamedLWLockTranche 取基址。需要 shared_preload_libraries 的扩展通常属于此类。

全局hash

LockMethodLockHash：存储LOCK
LockMethodProcLockHash：存储PROCLOCK
LockMethodLocalHash：存储LOCALLOCK

同一个LOCK资源对象可以被多个不同的 PROCLOCK 持有，而这些 PROCLOCK 又分别属于不同的进程。

LOCKTAG

1
2
3
4
5
6
7
8
9

typedef struct LOCKTAG
{
	uint32		locktag_field1; /* a 32-bit ID field */
	uint32		locktag_field2; /* a 32-bit ID field */
	uint32		locktag_field3; /* a 32-bit ID field */
	uint16		locktag_field4; /* a 16-bit ID field */
	uint8		locktag_type;	/* see enum LockTagType */
	uint8		locktag_lockmethodid;	/* lockmethod indicator */
} LOCKTAG;

• locktag_type
  标识这个锁是针对哪类资源的。每种 LockTagType 决定了 LOCKTAG 里后面几个字段（locktag_field1 ~ locktag_field4）是怎么解释的，比如：
  LOCKTAG_TRANSACTION：xid
  LOCKTAG_RELATION：dbOid + relOid
  LOCKTAG_TUPLE：dbOid + relOid + blockNum + offNum
• locktag_lockmethodid
  • DEFAULT_LOCKMETHOD（id = DEFAULT_LOCKMETHOD)
    绝大多数用户可见的锁（relation, tuple, transactionid 等）都走它。
  • USER_LOCKMETHOD（id = USER_LOCKMETHOD)
    提供给 pg_advisory_lock() 一类的 advisory lock，用于用户自定义锁。

PROCLOCK

1
2
3
4
5
6
7
8
9
10
11
12

typedef struct PROCLOCK
{
	/* tag */
	PROCLOCKTAG tag;			/* unique identifier of proclock object */

	/* data */
	PGPROC	   *groupLeader;	/* proc's lock group leader, or proc itself */
	LOCKMASK	holdMask;		/* bitmask for lock types currently held */
	LOCKMASK	releaseMask;	/* bitmask for lock types to be released */
	dlist_node	lockLink;		/* list link in LOCK's list of proclocks */
	dlist_node	procLink;		/* list link in PGPROC's list of proclocks */
} PROCLOCK;

LOCK

1
2
3
4
5
6
7
8
9
10
11
12

typedef struct LOCK
{
	LOCKTAG		tag;			
	LOCKMASK	grantMask;		
	LOCKMASK	waitMask;		
	dlist_head	procLocks;		
	dclist_head     waitProcs;		
	int		requested[MAX_LOCKMODES];	
	int		nRequested;		
	int		granted[MAX_LOCKMODES]; 
	int		nGranted;		
} LOCK;

• waitProcs
  等待本LOCK的PGPROC链表
• waitMask
  该资源上等待的锁类型：目前有进程在等待的锁类型集合
• procLocks
  进程对于一个LOCK的持有以及request情况；如果进程占有多个LOCK，则会有多个PROCLOCK
  • 一个 LOCK（资源）可以挂很多 PROCLOCK（不同后台进程），每个 PROCLOCK 只对应一个 PGPROC + 一个 LOCK。
  • 同一进程与同一资源只会有一个 PROCLOCK；它的 holdMask 可以同时包含多个锁模式位，且内部还有引用计数在 LOCALLOCK 里，不会创建多个 PROCLOCK
  • 资源侧用 LOCK.procLocks 链表串起所有持有/等待它的 PROCLOCK；进程侧在 PGPROC.myProcLocks（见 proc.h）里串起它关联的所有 PROCLOCK

    因此：一个资源可以被多个进程持锁；一个进程可以同时持有多个资源的锁；对同一资源如需多模式/多次持有，累加在同一个 PROCLOCK 的掩码/计数里。

• grantMask
  该资源上已经授予的锁类型，LOCKMASK 每一位对应 “编号为 N 的锁模式是否已持有/等待”，位号就是 lockmode， bit0 保留不适用
• requested[i]
  当前有多少个后台对模式 i 提出请求（已授予 + 正在等待）
• nRequested：是总和

  “等待者数” = requested[i] - granted[i]（或总等待 = nRequested - nGranted），依此可以知道有没有人还在等
  授予/释放锁时同步更新这些计数，并据此调整 grantMask/waitMask，决定是否要唤醒等待队列；

• granted[i]
  当前有多少个后台成功得到了模式 i 的锁
• nGranted：
  总和

LOCKMODE

每种资源支持8种锁（lockmode）

1
2
3
4
5
6
7
8
9
10
11
12
13
14

#define NoLock					0
#define AccessShareLock			1	/* SELECT */
#define RowShareLock			2	/* SELECT FOR UPDATE/FOR SHARE */
#define RowExclusiveLock		3	/* INSERT, UPDATE, DELETE */
#define ShareUpdateExclusiveLock 4	/* VACUUM (non-FULL), ANALYZE, CREATE
									 * INDEX CONCURRENTLY */
#define ShareLock				5	/* CREATE INDEX (WITHOUT CONCURRENTLY) */
#define ShareRowExclusiveLock	6	/* like EXCLUSIVE MODE, but allows ROW
									 * SHARE */
#define ExclusiveLock			7	/* blocks ROW SHARE/SELECT...FOR UPDATE */
#define AccessExclusiveLock		8	/* ALTER TABLE, DROP TABLE, VACUUM FULL,
									 * and unqualified LOCK TABLE */

#define MaxLockMode				8	/* highest standard lock mode */

Table-Level Lock Modes

• ACCESS SHARE (AccessShareLock)
  Conflicts with the ACCESS EXCLUSIVE lock mode only.

  The SELECT command acquires a lock of this mode on referenced tables

• ROW SHARE (RowShareLock)
  Conflicts with the EXCLUSIVE and ACCESS EXCLUSIVE lock modes.

  SELECT FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, or FOR KEY SHARE

• ROW EXCLUSIVE (RowExclusiveLock)
  Conflicts with the SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes.

  The commands UPDATE, DELETE, INSERT, and MERGE acquire this lock mode on the target table.

• SHARE UPDATE EXCLUSIVE (ShareUpdateExclusiveLock)
  Conflicts with the SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes.This mode protects a table against concurrent schema changes and VACUUM runs.

  Acquired by VACUUM (without FULL), ANALYZE, CREATE INDEX CONCURRENTLY, CREATE STATISTICS, COMMENT ON, REINDEX CONCURRENTLY, and certain ALTER INDEX and ALTER TABLE variants (for full details see the documentation of these commands).

Conflicting Lock Modes

参考链接

postgres官方文档

如果后台进程（backend）需要从缓冲区中驱逐一个脏页（dirty page），它必须将这个页面写入磁盘。这种情况是不希望发生的，因为它会导致等待（例如 I/O 阻塞）——更好的方式是在后台异步地执行写入操作。

这一工作部分由 checkpointer 进程完成，但这仍然不够。因此，PostgreSQL 还引入了另一个名为 bgwriter（后台写入进程）的进程，专门用于后台写盘操作。

bgwriter 和驱逐（eviction）使用相同的缓冲区遍历算法，但有两个关键区别：

bgwriter 使用自己独立的时钟指针（clock hand），该指针从不会落后于驱逐的指针，通常还会超过它；
在遍历缓冲区时，bgwriter 不会降低页面的 usage count（使用计数）。
当一个缓冲页未被固定（unpinned）且其 usage count 为 0 时，如果它是脏的，bgwriter 就会将其刷新到磁盘。换句话说，bgwriter 在驱逐操作发生之前运行，主动地将那些很可能即将被驱逐的页面提前写入磁盘。

这样做的好处是：被选中驱逐的缓冲页很可能已经是干净的（clean），从而提高了驱逐操作的效率，避免了后台进程被迫同步写盘的代价。

总结

checkpointer 是“周期性清理工”，而 bgwriter 是“持续扫地工”。

checkpointer 负责最终的数据落盘一致性，而 bgwriter 提前清理“潜在垃圾”，让后台线程少“踩雷”。二者配合，保障了 PostgreSQL 的高并发性能和写入平滑性。

参考书目

1. Egor Rogov, PostgreSQL 14 Internals, https://postgrespro.com/community/books/internals

CPU

top

1

top -H -p <PID>

只显示指定进程 的所有线程，并实时显示它们的 CPU、内存等使用情况。

1

pstack <PID>

perf

1

perf top -p <PID>

查看函数热点，采样一段时间

1
2

perf record -p <PID> -g -- sleep 10
perf report

strace

1

strace -ttT -p <PID>

火焰图

• 安装perf

  1	sudo apt-get install linux-tools-common linux-tools-$(uname -r)

• 采样数据

  1	sudo perf record -F 99 -p <PID> -g -- sleep 30

  -F 99 每秒采样 99 次
  -p 针对指定进程
  -g 采集调用栈（火焰图需要）
  – sleep 30 采样 30 秒

• 生成调用栈

  1	sudo perf script > out.perf

• 下载 FlameGraph 工具

  1 2	git clone https://github.com/brendangregg/FlameGraph.git cd FlameGraph

• 生成火焰图

  1 2	./stackcollapse-perf.pl ../out.perf > out.folded ./flamegraph.pl out.folded > flamegraph.svg

IO

系统io

1

iostat -x 1

重点关注 await（平均等待时间）和 svctm（服务时间）。如果 await >> svctm，说明队列很长，设备忙不过来。

某个进程io

1

pidstat -d -p <PID> 1