PostgreSQL笔记23：Checkpoint机制与脏页处理深度解析

纲要

• Checkpoint（检查点）机制
  • Checkpoint 的核心作用
  • Checkpoint 的触发条件
  • Checkpoint 的执行流程
• 脏页（Dirty Page）处理
  • 写入流程与 WAL（预写式日志）
  • 脏页刷盘机制
• WAL 日志回收
  • 回收策略与 min_wal_size / max_wal_size
  • wal_recycle 参数
• FPW（全页写，Full Page Write）
  • FPW 的设计背景
  • FPW 对性能的影响与监控
• 崩溃恢复（Crash Recovery）
  • Redo 点与 Checkpoint 位置
  • pg_control 文件中的关键信息
• 监控视图
  • pg_stat_checkpointer（PostgreSQL 17+）
  • pg_stat_bgwriter（PostgreSQL 16 及更早版本）

写入流程回顾

在深入 Checkpoint 机制之前，有必要先回顾 PostgreSQL 的核心写入流程。

PostgreSQL 采用 WAL（Write-Ahead Logging，预写式日志） 机制。当数据发生变更时，系统会执行以下操作：

1. 在 WAL 缓冲区中生成一条日志记录，描述此次变更。
2. 同时修改共享缓冲区（Shared Buffers）中的对应数据页，将其标记为脏页（Dirty Page）。
3. 事务提交（COMMIT）时，仅要求 WAL 日志落盘，而非数据页落盘。

由于 WAL 日志采用顺序追加写入，而数据页的写入是随机 I/O，这种设计显著提升了写入性能。事务提交时只需确保 WAL 日志已持久化，即便数据库随后崩溃，也可以通过重放 WAL 日志恢复已提交的事务。

然而，这种设计也引出了一个关键问题：数据页不可能永远延迟落盘。如果脏页长期不写入磁盘，WAL 日志将持续增长，最终耗尽磁盘空间。PostgreSQL 通过 Checkpoint 机制来解决这一问题。

Checkpoint 机制

Checkpoint 的核心作用

Checkpoint（检查点）是事务序列中的一个特定点，在该点上，所有在检查点之前写入 WAL 的变更都已持久化到数据文件中。

Checkpoint 主要完成两件事：

1. 刷脏：将 Shared Buffers 中的所有脏页（包括数据页、WAL 缓冲区、CLOG 缓冲区等）写入磁盘。
2. WAL 回收：在刷脏完成后，Checkpoint 会回收不再需要的旧 WAL 日志段，防止 pg_wal 目录无限膨胀。

Checkpoint 的触发条件

PostgreSQL 源码中定义了多种 Checkpoint 触发类型：

触发类型	说明
CHECKPOINT_IS_SHUTDOWN	正常停机（pg_stop / smart / fast 模式）
CHECKPOINT_END_OF_RECOVERY	恢复结束
CHECKPOINT_IMMEDIATE	手动执行 CHECKPOINT 命令
CHECKPOINT_FORCE	强制检查点
CHECKPOINT_WAIT	等待型检查点
CHECKPOINT_REQUESTED	请求型检查点
CHECKPOINT_CAUSE_XLOG	max_wal_size 即将超出
CHECKPOINT_CAUSE_TIME	checkpoint_timeout 超时

在实际运行中，Checkpoint 主要由以下两种条件自动触发：

• 时间阈值：checkpoint_timeout 参数控制，默认 5 分钟。
• 空间阈值：max_wal_size 参数控制，默认 1 GB。

两者以先到者为准。如果自上次 Checkpoint 以来没有写入任何 WAL，即使 checkpoint_timeout 已过，新的 Checkpoint 也会被跳过。

此外，数据库超级用户也可以通过 CHECKPOINT 命令手动触发检查点：

CHECKPOINT;

Checkpoint 的执行流程

Checkpoint 的执行是一个渐进过程，而非瞬时操作：

1. 建立 Redo 点：在 WAL 中写入一条 Redo 记录，标记恢复的起始位置。
2. 刷脏：将截至 Redo 点累积的所有脏页写入磁盘。
3. Fsync：确保所有变更文件持久化。
4. 写入 Checkpoint 记录：在 WAL 中写入一条特殊的 Checkpoint 记录。
5. 回收 WAL：回收 Redo 点之前的 WAL 段。

关键参数说明

参数	默认值	说明
checkpoint_timeout	5min	两次自动检查点之间的最大时间间隔
max_wal_size	1GB	WAL 总大小的软上限，达到后触发检查点
min_wal_size	80MB	WAL 回收后保留的最小大小
checkpoint_completion_target	0.9	检查点完成时间占检查点间隔的比例
checkpoint_warning	30s	检查点间隔过短时的告警阈值
wal_recycle	on	是否回收旧 WAL 段（重命名复用）
full_page_writes	on	是否启用全页写

checkpoint_completion_target 用于平滑检查点的 I/O 负载。默认值 0.9 意味着检查点将在下一个检查点开始前完成 90% 的工作，将 I/O 峰值分散到整个检查点间隔内。

脏页处理机制

脏页的生成

脏页是指 Shared Buffers 中被修改过但尚未写入磁盘的数据页。每次数据变更（INSERT、UPDATE、DELETE）都会在 Shared Buffers 中产生脏页。

脏页的刷盘途径

脏页通过以下途径写入磁盘：

1. Checkpointer 进程：在 Checkpoint 时刷写所有脏页。
2. BgWriter 进程（后台写进程）：在后台持续刷写脏页，保持有足够的干净缓冲区可用。
3. 后端进程：当需要获取缓冲区但无可用干净缓冲区时，后端进程自行刷写脏页。

BgWriter 与 Checkpointer 的分工

• BgWriter（后台写进程） ：持续运行，定期将脏页写入磁盘，目的是平滑 I/O 负载，避免在 Checkpoint 时产生大量 I/O 突发。
• Checkpointer（检查点进程） ：在 Checkpoint 触发时，负责将所有脏页刷盘并推进 WAL 回收。

两者协同工作，确保脏页既不会无限累积，也不会因频繁刷盘而影响性能。

WAL 日志回收

回收机制

WAL 日志存储在 pg_wal 目录中，每个段文件默认 16 MB。随着数据库运行，WAL 段会不断增长。Checkpoint 完成后，Redo 点之前的 WAL 段不再需要，可以被回收或删除。

回收（Recycle） 是指将旧的 WAL 段文件重命名，作为新的段文件复用。这避免了频繁创建新文件的系统开销。

-- 查看当前WAL段文件
SELECT pg_ls_waldir() LIMIT 10;

回收策略

PostgreSQL 通过以下参数控制 WAL 回收策略：

• min_wal_size：回收后 pg_wal 目录保留的最小大小。
• max_wal_size：pg_wal 目录的软上限。
• wal_recycle：是否启用 WAL 段回收（默认开启）。

当 WAL 总大小超过 max_wal_size 时，Checkpoint 会触发并回收旧的 WAL 段，将 pg_wal 大小控制回 min_wal_size 附近。

关于 wal_recycle

wal_recycle 参数控制是否将旧的 WAL 段文件重命名以供复用。如果设置为 off，旧的 WAL 段将被直接删除而非回收。在大多数生产环境中，建议保持默认值 on。

FPW（全页写，Full Page Write）

设计背景

PostgreSQL 的数据页默认大小为 8 KB，而操作系统的页缓存通常为 4 KB，磁盘的扇区大小为 512 字节。当 PostgreSQL 将一个 8 KB 的数据页写入磁盘时，可能发生以下情况：

• 前 4 KB 写入成功
• 后 4 KB 写入失败（操作系统崩溃或断电）

这会导致数据页不完整（部分写入），即所谓的"断裂页"（Torn Page）。

FPW 机制

为了解决这一问题，PostgreSQL 引入了 FPW（Full Page Write，全页写） 机制：

在每次 Checkpoint 之后，对数据页的第一次修改，会将整个数据页（8 KB）的镜像记录到 WAL 中。

这样，即使发生部分写入，崩溃恢复时也可以从 WAL 中读取完整的页镜像进行覆盖恢复。

FPW 对性能的影响

FPW 虽然保证了数据一致性，但也带来了显著的性能开销：

1. WAL 膨胀：每个数据页的首次变更都会产生 8 KB 的 WAL 记录，远大于增量记录的几十到几百字节。
2. 写入效率下降：大量 WAL 写入占用 I/O 带宽，影响整体吞吐量。
3. Checkpoint 频率影响：Checkpoint 越频繁，FPW 产生的 WAL 越多。

监控 FPW 占比

可以通过 pg_stat_bgwriter（PostgreSQL 16 及更早）或 pg_stat_checkpointer（PostgreSQL 17+）视图监控 FPW 情况。

-- PostgreSQL 16 及更早版本
SELECT 
    buffers_checkpoint,
    buffers_clean,
    maxwritten_clean,
    buffers_backend,
    buffers_alloc
FROM pg_stat_bgwriter;

-- 计算FPW占比（近似）
-- 如果 buffers_checkpoint 远大于 buffers_clean，说明Checkpoint承担了主要的刷脏工作
-- FPW导致的WAL膨胀可以通过WAL段大小增长率来间接评估

-- PostgreSQL 17+ 
SELECT 
    num_timed,
    num_requested,
    write_time,
    sync_time,
    buffers_written
FROM pg_stat_checkpointer;

pg_stat_checkpointer 视图在 PostgreSQL 17 中引入，将原本属于 pg_stat_bgwriter 的检查点统计信息独立出来。

如果发现 FPW 占比过高（例如 WAL 中超过 50% 是 FPW 记录），建议考虑：

• 适当调大 checkpoint_timeout 和 max_wal_size，降低 Checkpoint 频率。
• 评估是否可以在确保数据安全的前提下调整 full_page_writes 参数。

崩溃恢复与 Checkpoint

Redo 点与 Checkpoint 位置

在崩溃恢复时，PostgreSQL 需要确定从 WAL 的哪个位置开始重放。这涉及两个关键概念：

• Redo 点（Redo Record） ：Checkpoint 开始时写入 WAL 的位置。恢复从该点开始。
• Checkpoint 位置（Checkpoint Location） ：Checkpoint 完成时写入 WAL 的位置。

这两个位置都记录在 pg_control 文件中。

pg_control 文件中的关键字段：

• latest_checkpoint_location：最近一次 Checkpoint 记录的位置。
• latest_checkpoint_redo_location：最近一次 Checkpoint 对应的 Redo 点位置。

恢复时，系统从 Redo 点开始重放 WAL，直到 WAL 末尾，确保所有已提交事务的变更都被应用到数据文件中。

pg_control_checkpoint() 函数

PostgreSQL 9.6 及以上版本提供了 pg_control_checkpoint() 函数，用于查看当前 Checkpoint 状态：

SELECT * FROM pg_control_checkpoint();

监控与诊断

pg_stat_checkpointer（PostgreSQL 17+）

PostgreSQL 17 引入了专用的检查点统计视图：

SELECT 
    num_timed,          -- 由超时触发的检查点次数
    num_requested,      -- 由请求触发的检查点次数
    write_time,         -- 刷脏耗时（毫秒）
    sync_time,          -- Fsync耗时（毫秒）
    buffers_written,    -- 写入的缓冲区数量
    stats_reset         -- 统计信息重置时间
FROM pg_stat_checkpointer;

-- 重置统计信息
SELECT pg_stat_reset_shared('checkpointer');

pg_stat_bgwriter（PostgreSQL 16 及更早）

在 PostgreSQL 16 及更早版本中，检查点统计信息包含在 pg_stat_bgwriter 视图中：

SELECT 
    checkpoints_timed,      -- 由超时触发的检查点次数
    checkpoints_req,        -- 由请求触发的检查点次数
    checkpoint_write_time,  -- 刷脏耗时（毫秒）
    checkpoint_sync_time,   -- Fsync耗时（毫秒）
    buffers_checkpoint      -- 检查点期间写入的缓冲区数量
FROM pg_stat_bgwriter;

日志监控

启用 log_checkpoints 参数后，PostgreSQL 会在日志中记录每次 Checkpoint 的详细信息：

ALTER SYSTEM SET log_checkpoints = on;
SELECT pg_reload_conf();

典型日志输出示例：

LOG: checkpoint starting: time
LOG: checkpoint complete: wrote 155 buffers (0.1%); 0 WAL file(s) added, 0 removed, 0 recycled; write=15.810 s, sync=0.013 s, total=15.904 s; sync files=63, longest=0.003 s, average=0.000 s; distance=783 kB, estimate=2315 kB

通过分析这些日志，可以评估 Checkpoint 的耗时和 I/O 压力，进而调优相关参数。

API 速览

1. CHECKPOINT 命令

所属：PostgreSQL SQL 命令

语法：

CHECKPOINT;

说明：强制执行一个即时检查点，无需等待定时检查点。仅超级用户可执行。

示例：

-- 手动触发检查点
CHECKPOINT;

-- 在事务中执行
BEGIN;
CHECKPOINT;
COMMIT;

2. pg_control_checkpoint()

所属：PostgreSQL 系统函数（9.6+）

签名：pg_control_checkpoint() RETURNS record

返回值：包含当前检查点状态信息的记录。

示例：

SELECT * FROM pg_control_checkpoint();

-- 输出示例:
-- checkpoint_location | redo_location | ...
-- --------------------+---------------+-----
-- 0/302ECB0          | 0/302E9A0     | ...

3. pg_stat_checkpointer

所属：PostgreSQL 系统统计视图（17+）

列定义：

列名	类型	说明
num_timed	bigint	由超时触发的检查点次数
num_requested	bigint	由请求触发的检查点次数
write_time	double precision	刷脏总耗时（毫秒）
sync_time	double precision	Fsync总耗时（毫秒）
buffers_written	bigint	写入的缓冲区总数
stats_reset	timestamp with time zone	统计信息重置时间

示例：

-- 查看检查点统计
SELECT * FROM pg_stat_checkpointer;

-- 重置统计
SELECT pg_stat_reset_shared('checkpointer');

4. pg_stat_bgwriter

所属：PostgreSQL 系统统计视图（所有支持版本）

列定义（检查点相关）：

列名	类型	说明
checkpoints_timed	bigint	由超时触发的检查点次数
checkpoints_req	bigint	由请求触发的检查点次数
checkpoint_write_time	double precision	刷脏总耗时（毫秒）
checkpoint_sync_time	double precision	Fsync总耗时（毫秒）
buffers_checkpoint	bigint	检查点期间写入的缓冲区数量

示例：

SELECT 
    checkpoints_timed,
    checkpoints_req,
    checkpoint_write_time,
    checkpoint_sync_time,
    buffers_checkpoint
FROM pg_stat_bgwriter;

5. pg_ls_waldir()

所属：PostgreSQL 系统函数

签名：pg_ls_waldir() RETURNS SETOF record

返回值：pg_wal 目录中的文件列表。

示例：

SELECT name, size, modification 
FROM pg_ls_waldir() 
ORDER BY modification DESC 
LIMIT 10;

Demo 简单示例

以下是一个使用 Node.js 和 pg 驱动演示 Checkpoint 监控的完整示例。

运行说明

环境要求：

• Node.js 16+
• PostgreSQL 17+（推荐，以支持 pg_stat_checkpointer）
• pg 驱动

安装依赖：

npm init -y
npm install pg

配置数据库：
确保 PostgreSQL 正在运行，并创建一个测试数据库：

CREATE DATABASE checkpoint_demo;

代码说明

import { Client } from 'pg';

// 数据库连接配置
const config = {
    host: 'localhost',
    port: 5432,
    database: 'checkpoint_demo',
    user: 'postgres',
    password: 'your_password'
};

async function main() {
    const client = new Client(config);
    await client.connect();

    try {
        // 1. 创建测试表
        await client.query(`
            CREATE TABLE IF NOT EXISTS checkpoint_test (
                id SERIAL PRIMARY KEY,
                data TEXT,
                created_at TIMESTAMP DEFAULT NOW()
            )
        `);
        console.log('✅ 测试表已就绪');

        // 2. 查看当前检查点统计（PostgreSQL 17+）
        const statsBefore = await client.query(`
            SELECT 
                num_timed,
                num_requested,
                write_time,
                sync_time,
                buffers_written
            FROM pg_stat_checkpointer
        `);
        console.log('📊 检查点统计（操作前）:');
        console.table(statsBefore.rows[0]);

        // 3. 生成一些写入负载
        console.log('📝 生成写入负载...');
        const insertPromises = [];
        for (let i = 0; i < 10000; i++) {
            insertPromises.push(
                client.query('INSERT INTO checkpoint_test (data) VALUES ($1)', [
                    `Test data ${i}`
                ])
            );
        }
        await Promise.all(insertPromises);
        console.log('✅ 写入负载完成');

        // 4. 手动触发 Checkpoint
        console.log('🔄 手动触发 Checkpoint...');
        await client.query('CHECKPOINT');
        console.log('✅ Checkpoint 完成');

        // 5. 查看更新后的检查点统计
        const statsAfter = await client.query(`
            SELECT 
                num_timed,
                num_requested,
                write_time,
                sync_time,
                buffers_written
            FROM pg_stat_checkpointer
        `);
        console.log('📊 检查点统计（操作后）:');
        console.table(statsAfter.rows[0]);

        // 6. 查看 WAL 使用情况
        const walInfo = await client.query(`
            SELECT 
                pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0') AS total_wal_bytes,
                pg_wal_lsn_diff(
                    pg_current_wal_lsn(), 
                    pg_control_checkpoint().redo_location
                ) AS wal_since_redo_bytes
        `);
        console.log('📁 WAL 信息:');
        console.log(`  总 WAL 字节数: ${walInfo.rows[0].total_wal_bytes}`);
        console.log(`  自 Redo 点以来 WAL 字节数: ${walInfo.rows[0].wal_since_redo_bytes}`);

        // 7. 清理
        await client.query('DROP TABLE checkpoint_test');
        console.log('🧹 清理完成');

    } catch (err) {
        console.error('❌ 错误:', err);
    } finally {
        await client.end();
    }
}

main();

/* ============================================================
   PostgreSQL 原生指令注释
   ============================================================

   -- 查看检查点统计 (PostgreSQL 17+)
   SELECT num_timed, num_requested, write_time, sync_time, buffers_written 
   FROM pg_stat_checkpointer;

   -- 查看检查点统计 (PostgreSQL 16 及更早)
   SELECT checkpoints_timed, checkpoints_req, checkpoint_write_time, 
          checkpoint_sync_time, buffers_checkpoint 
   FROM pg_stat_bgwriter;

   -- 手动触发检查点
   CHECKPOINT;

   -- 查看当前 WAL LSN
   SELECT pg_current_wal_lsn();

   -- 查看 Checkpoint 控制信息
   SELECT * FROM pg_control_checkpoint();

   -- 查看 WAL 段文件
   SELECT name, size, modification FROM pg_ls_waldir() ORDER BY modification DESC;

   -- 启用检查点日志
   ALTER SYSTEM SET log_checkpoints = on;
   SELECT pg_reload_conf();
*/

技术点总结

本 Demo 演示了以下核心技术：

1. Checkpoint 手动触发：使用 CHECKPOINT 命令强制执行检查点。
2. 检查点统计监控：通过 pg_stat_checkpointer 视图查看检查点执行情况。
3. WAL 位置查询：使用 pg_current_wal_lsn() 和 pg_control_checkpoint() 获取 WAL 位置信息。
4. 写入负载模拟：通过批量插入生成脏页，触发检查点刷脏。
5. 统计信息对比：对比操作前后的检查点统计，观察 buffers_written 等指标的变化。

多语言示例

基于前文的 Node.js 示例，以下分别提供 Go、Python 和 Java 语言的完整实现，均使用对应的 PostgreSQL 驱动，执行相同的操作流程：创建测试表、查看检查点统计、生成写入负载、手动触发 Checkpoint、查看更新后的统计和 WAL 信息、清理表。

Go 语言示例

使用 pgx 驱动（推荐）或 lib/pq，本示例采用 pgx/v5。

运行说明

环境要求：

• Go 1.19+
• PostgreSQL 17+（推荐以支持 pg_stat_checkpointer）
• 安装依赖：go get github.com/jackc/pgx/v5

配置数据库：与 Node.js 示例相同，创建 checkpoint_demo 数据库。

代码说明

package main

import (
    "context"
    "fmt"
    "log"
    "os"
    "time"

    "github.com/jackc/pgx/v5"
)

func main() {
    ctx := context.Background()

    // 连接配置
    connStr := "postgres://postgres:your_password@localhost:5432/checkpoint_demo"
    conn, err := pgx.Connect(ctx, connStr)
    if err != nil {
        log.Fatalf("无法连接数据库: %v", err)
    }
    defer conn.Close(ctx)

    // 1. 创建测试表
    _, err = conn.Exec(ctx, `
        CREATE TABLE IF NOT EXISTS checkpoint_test (
            id SERIAL PRIMARY KEY,
            data TEXT,
            created_at TIMESTAMP DEFAULT NOW()
        )
    `)
    if err != nil {
        log.Fatalf("创建表失败: %v", err)
    }
    fmt.Println("✅ 测试表已就绪")

    // 2. 查看当前检查点统计 (PostgreSQL 17+)
    var numTimed, numRequested int64
    var writeTime, syncTime float64
    var buffersWritten int64
    err = conn.QueryRow(ctx, `
        SELECT num_timed, num_requested, write_time, sync_time, buffers_written
        FROM pg_stat_checkpointer
    `).Scan(&numTimed, &numRequested, &writeTime, &syncTime, &buffersWritten)
    if err != nil {
        log.Fatalf("查询统计失败: %v", err)
    }
    fmt.Println("📊 检查点统计（操作前）:")
    fmt.Printf("  num_timed=%d, num_requested=%d, write_time=%.2fms, sync_time=%.2fms, buffers_written=%d\n",
        numTimed, numRequested, writeTime, syncTime, buffersWritten)

    // 3. 生成写入负载
    fmt.Println("📝 生成写入负载...")
    batch := &pgx.Batch{}
    for i := 0; i < 10000; i++ {
        batch.Queue("INSERT INTO checkpoint_test (data) VALUES ($1)", fmt.Sprintf("Test data %d", i))
    }
    br := conn.SendBatch(ctx, batch)
    // 必须消费完所有结果
    for i := 0; i < batch.Len(); i++ {
        _, err = br.Exec()
        if err != nil {
            log.Fatalf("批量插入失败: %v", err)
        }
    }
    br.Close()
    fmt.Println("✅ 写入负载完成")

    // 4. 手动触发 Checkpoint
    fmt.Println("🔄 手动触发 Checkpoint...")
    _, err = conn.Exec(ctx, "CHECKPOINT")
    if err != nil {
        log.Fatalf("Checkpoint失败: %v", err)
    }
    fmt.Println("✅ Checkpoint 完成")

    // 5. 查看更新后的检查点统计
    err = conn.QueryRow(ctx, `
        SELECT num_timed, num_requested, write_time, sync_time, buffers_written
        FROM pg_stat_checkpointer
    `).Scan(&numTimed, &numRequested, &writeTime, &syncTime, &buffersWritten)
    if err != nil {
        log.Fatalf("查询统计失败: %v", err)
    }
    fmt.Println("📊 检查点统计（操作后）:")
    fmt.Printf("  num_timed=%d, num_requested=%d, write_time=%.2fms, sync_time=%.2fms, buffers_written=%d\n",
        numTimed, numRequested, writeTime, syncTime, buffersWritten)

    // 6. 查看 WAL 使用情况
    var totalWalBytes, walSinceRedoBytes int64
    err = conn.QueryRow(ctx, `
        SELECT
            pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0') AS total_wal_bytes,
            pg_wal_lsn_diff(
                pg_current_wal_lsn(),
                pg_control_checkpoint().redo_location
            ) AS wal_since_redo_bytes
    `).Scan(&totalWalBytes, &walSinceRedoBytes)
    if err != nil {
        log.Fatalf("查询WAL信息失败: %v", err)
    }
    fmt.Println("📁 WAL 信息:")
    fmt.Printf("  总 WAL 字节数: %d\n", totalWalBytes)
    fmt.Printf("  自 Redo 点以来 WAL 字节数: %d\n", walSinceRedoBytes)

    // 7. 清理
    _, err = conn.Exec(ctx, "DROP TABLE checkpoint_test")
    if err != nil {
        log.Fatalf("清理失败: %v", err)
    }
    fmt.Println("🧹 清理完成")
}

// 原生 SQL 注释参见 Node.js 示例中的注释，此处略。

技术点总结

• 使用 pgx 驱动的 Batch 功能批量执行插入，提高性能。
• 通过 QueryRow 获取单行统计信息。
• 手动执行 CHECKPOINT 命令。
• 使用 pg_control_checkpoint() 获取 Redo 位置。

Python 示例

使用 psycopg2（或 asyncpg），本示例采用 psycopg2 同步版本。

运行说明

环境要求：

• Python 3.8+
• PostgreSQL 17+（推荐）
• 安装依赖：pip install psycopg2-binary

代码说明

import psycopg2
from psycopg2 import sql

def main():
    # 连接配置
    conn = psycopg2.connect(
        host="localhost",
        port=5432,
        database="checkpoint_demo",
        user="postgres",
        password="your_password"
    )
    conn.autocommit = True  # 便于执行CHECKPOINT
    cur = conn.cursor()

    try:
        # 1. 创建测试表
        cur.execute("""
            CREATE TABLE IF NOT EXISTS checkpoint_test (
                id SERIAL PRIMARY KEY,
                data TEXT,
                created_at TIMESTAMP DEFAULT NOW()
            )
        """)
        print("✅ 测试表已就绪")

        # 2. 查看当前检查点统计 (PostgreSQL 17+)
        cur.execute("""
            SELECT num_timed, num_requested, write_time, sync_time, buffers_written
            FROM pg_stat_checkpointer
        """)
        row = cur.fetchone()
        print("📊 检查点统计（操作前）:")
        print(f"  num_timed={row[0]}, num_requested={row[1]}, write_time={row[2]}ms, sync_time={row[3]}ms, buffers_written={row[4]}")

        # 3. 生成写入负载
        print("📝 生成写入负载...")
        # 使用批量插入提高效率
        data_list = [(f"Test data {i}",) for i in range(10000)]
        cur.executemany("INSERT INTO checkpoint_test (data) VALUES (%s)", data_list)
        print("✅ 写入负载完成")

        # 4. 手动触发 Checkpoint
        print("🔄 手动触发 Checkpoint...")
        cur.execute("CHECKPOINT")
        print("✅ Checkpoint 完成")

        # 5. 查看更新后的检查点统计
        cur.execute("""
            SELECT num_timed, num_requested, write_time, sync_time, buffers_written
            FROM pg_stat_checkpointer
        """)
        row = cur.fetchone()
        print("📊 检查点统计（操作后）:")
        print(f"  num_timed={row[0]}, num_requested={row[1]}, write_time={row[2]}ms, sync_time={row[3]}ms, buffers_written={row[4]}")

        # 6. 查看 WAL 使用情况
        cur.execute("""
            SELECT
                pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0') AS total_wal_bytes,
                pg_wal_lsn_diff(
                    pg_current_wal_lsn(),
                    pg_control_checkpoint().redo_location
                ) AS wal_since_redo_bytes
        """)
        total, since_redo = cur.fetchone()
        print("📁 WAL 信息:")
        print(f"  总 WAL 字节数: {total}")
        print(f"  自 Redo 点以来 WAL 字节数: {since_redo}")

        # 7. 清理
        cur.execute("DROP TABLE checkpoint_test")
        print("🧹 清理完成")

    except Exception as e:
        print(f"❌ 错误: {e}")
    finally:
        cur.close()
        conn.close()

if __name__ == "__main__":
    main()

# 原生 SQL 注释参见 Node.js 示例。

技术点总结

• 使用 psycopg2 的 executemany 进行批量插入。
• 设置 autocommit=True 以便于执行 CHECKPOINT（因为 CHECKPOINT 不能在事务块中执行）。
• 使用游标获取单行和多行结果。
• 统计信息查询与 Node.js 一致。

Java 示例

使用 JDBC 驱动（org.postgresql:postgresql）。

运行说明

环境要求：

• Java 11+
• PostgreSQL 17+（推荐）
• Maven 或 Gradle 引入依赖：org.postgresql:postgresql:42.6.0

Maven 依赖：

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.6.0</version>
</dependency>

代码说明

import java.sql.*;

public class CheckpointDemo {
    public static void main(String[] args) {
        String url = "jdbc:postgresql://localhost:5432/checkpoint_demo";
        String user = "postgres";
        String password = "your_password";

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            conn.setAutoCommit(true); // 允许执行CHECKPOINT

            // 1. 创建测试表
            try (Statement stmt = conn.createStatement()) {
                stmt.execute("""
                    CREATE TABLE IF NOT EXISTS checkpoint_test (
                        id SERIAL PRIMARY KEY,
                        data TEXT,
                        created_at TIMESTAMP DEFAULT NOW()
                    )
                """);
                System.out.println("✅ 测试表已就绪");
            }

            // 2. 查看当前检查点统计 (PostgreSQL 17+)
            String queryStats = "SELECT num_timed, num_requested, write_time, sync_time, buffers_written FROM pg_stat_checkpointer";
            try (PreparedStatement pstmt = conn.prepareStatement(queryStats);
                 ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    System.out.println("📊 检查点统计（操作前）:");
                    System.out.printf("  num_timed=%d, num_requested=%d, write_time=%.2fms, sync_time=%.2fms, buffers_written=%d%n",
                            rs.getLong("num_timed"),
                            rs.getLong("num_requested"),
                            rs.getDouble("write_time"),
                            rs.getDouble("sync_time"),
                            rs.getLong("buffers_written"));
                }
            }

            // 3. 生成写入负载 (使用批量插入)
            System.out.println("📝 生成写入负载...");
            String insertSQL = "INSERT INTO checkpoint_test (data) VALUES (?)";
            try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
                for (int i = 0; i < 10000; i++) {
                    pstmt.setString(1, "Test data " + i);
                    pstmt.addBatch();
                    if (i % 1000 == 0) {
                        pstmt.executeBatch(); // 每1000条提交一次
                    }
                }
                pstmt.executeBatch();
            }
            System.out.println("✅ 写入负载完成");

            // 4. 手动触发 Checkpoint
            System.out.println("🔄 手动触发 Checkpoint...");
            try (Statement stmt = conn.createStatement()) {
                stmt.execute("CHECKPOINT");
            }
            System.out.println("✅ Checkpoint 完成");

            // 5. 查看更新后的检查点统计
            try (PreparedStatement pstmt = conn.prepareStatement(queryStats);
                 ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    System.out.println("📊 检查点统计（操作后）:");
                    System.out.printf("  num_timed=%d, num_requested=%d, write_time=%.2fms, sync_time=%.2fms, buffers_written=%d%n",
                            rs.getLong("num_timed"),
                            rs.getLong("num_requested"),
                            rs.getDouble("write_time"),
                            rs.getDouble("sync_time"),
                            rs.getLong("buffers_written"));
                }
            }

            // 6. 查看 WAL 使用情况
            String walQuery = """
                    SELECT
                        pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0') AS total_wal_bytes,
                        pg_wal_lsn_diff(
                            pg_current_wal_lsn(),
                            pg_control_checkpoint().redo_location
                        ) AS wal_since_redo_bytes
                    """;
            try (PreparedStatement pstmt = conn.prepareStatement(walQuery);
                 ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    long totalWalBytes = rs.getLong("total_wal_bytes");
                    long walSinceRedoBytes = rs.getLong("wal_since_redo_bytes");
                    System.out.println("📁 WAL 信息:");
                    System.out.println("  总 WAL 字节数: " + totalWalBytes);
                    System.out.println("  自 Redo 点以来 WAL 字节数: " + walSinceRedoBytes);
                }
            }

            // 7. 清理
            try (Statement stmt = conn.createStatement()) {
                stmt.execute("DROP TABLE checkpoint_test");
                System.out.println("🧹 清理完成");
            }

        } catch (SQLException e) {
            System.err.println("❌ 错误: " + e.getMessage());
            e.printStackTrace();
        }
    }
}

// 原生 SQL 注释参见 Node.js 示例。

技术点总结

• 使用 JDBC 批量操作（addBatch / executeBatch）提高插入效率。
• 设置 setAutoCommit(true) 以便执行 CHECKPOINT。
• 使用 PreparedStatement 和 ResultSet 查询统计信息。
• 统计信息查询与 Node.js 一致。

多语言对比

特性	Node.js (pg)	Go (pgx)	Python (psycopg2)	Java (JDBC)
驱动/库	pg	pgx/v5	psycopg2	postgresql JDBC
连接方式	new Client(config)	pgx.Connect(ctx, connStr)	psycopg2.connect(...)	DriverManager.getConnection(url, user, password)
批量插入	Promise.all 或 INSERT 多个值	pgx.Batch	executemany	addBatch / executeBatch
执行CHECKPOINT	client.query('CHECKPOINT')	conn.Exec(ctx, "CHECKPOINT")	cur.execute("CHECKPOINT")	stmt.execute("CHECKPOINT")
查询单行	client.query(...).rows[0]	conn.QueryRow(...).Scan(...)	cur.fetchone()	rs.next() 后 rs.getXxx(...)
查询多行/批量结果	rows 数组	Batch 消费	fetchall 或遍历	ResultSet 遍历
事务控制	默认自动提交，可手动 BEGIN	默认自动提交，可手动	默认自动提交（autocommit=True）	默认自动提交（setAutoCommit(true)）
异步支持	原生 async/await	原生 context 支持	同步（有异步版 asyncpg）	同步（有异步版 vertx-pg-client 等）
错误处理	try/catch	返回 error	try/except	try/catch (SQLException)
依赖管理	npm	go mod	pip	Maven/Gradle
适用场景	JavaScript/TypeScript 全栈	高性能微服务	数据科学/脚本	企业级后端

选择建议：

• Node.js：适合全栈 JavaScript 团队，异步非阻塞 I/O 优势明显。
• Go：适合追求高并发、低延迟的场景，编译型语言，部署简单。
• Python：适合快速原型开发、数据分析结合数据库操作的场景。
• Java：适合大型企业级应用，丰富的生态和成熟的 ORM（如 Hibernate）集成。

官方文档

• PostgreSQL Documentation: WAL Configuration
• PostgreSQL Documentation: pg_stat_checkpointer
• PostgreSQL Documentation: pg_stat_bgwriter
• PostgreSQL Documentation: CHECKPOINT Command
• PostgreSQL Documentation: pg_control_checkpoint()

参考链接

• PostgreSQL Wiki: Checkpoints
• PostgreSQL Wiki: WAL Internals
• pgPedia: Checkpoint
• pgPedia: pg_stat_checkpointer
• PostgreSQL 17: New catalog view pg_stat_checkpointer

总结

本文深入解析了 PostgreSQL 的 Checkpoint 机制与脏页处理流程，涵盖以下核心技术要点：

• WAL 机制：事务提交仅需 WAL 落盘，数据页延迟写入，通过顺序写提升性能。
• Checkpoint 核心作用：刷脏（将所有脏页写入磁盘）和 WAL 回收（防止 pg_wal 无限膨胀）。
• Checkpoint 触发条件：checkpoint_timeout（默认 5 分钟）和 max_wal_size（默认 1 GB），以先到者为准。
• 脏页刷盘途径：Checkpointer 进程（检查点时）、BgWriter 进程（后台持续）和后端进程（紧急情况）。
• WAL 回收策略：通过 min_wal_size、max_wal_size 和 wal_recycle 控制，旧段文件被重命名复用。
• FPW（全页写） ：Checkpoint 后首次变更的数据页整页写入 WAL，防止部分写入导致的数据损坏，但会带来 WAL 膨胀和性能开销。
• 崩溃恢复：从 Redo 点开始重放 WAL，pg_control 文件记录 Checkpoint 位置和 Redo 位置。
• 监控视图：pg_stat_checkpointer（PostgreSQL 17+）和 pg_stat_bgwriter（PostgreSQL 16 及更早）提供检查点执行统计。

合理配置 Checkpoint 相关参数是 PostgreSQL 性能调优的重要环节。过于频繁的 Checkpoint 会增加 I/O 负载和 FPW 开销，而过于稀疏的 Checkpoint 则会导致崩溃恢复时间变长。生产环境中应结合业务负载和 RTO 要求，通过监控数据持续优化 checkpoint_timeout、max_wal_size 和 checkpoint_completion_target 等参数。