PostgreSQL笔记25：数据写入与读取流程及IO模型演进

纲要

• PostgreSQL 体系架构概览
  • 进程模型与内存结构
  • 共享内存（shared_buffers、wal_buffers）
  • 后台进程（BGWriter、WALWriter、Checkpointer）
• 数据读写流程
  • 读取路径：shared_buffers → OS Page Cache → 磁盘
  • 写入路径：脏页刷盘与 WAL 持久化
• 双缓存问题（核心难点）
  • 数据重复缓存，内存浪费
  • 操作系统 LRU 与数据库 Clock Sweep 算法不匹配
• 页面置换算法：Clock Sweep（时钟扫描）
  • usage_count 机制与指针旋转
• IO 模型演进
  • 传统 Buffered IO
  • Direct IO 引入计划（PG 16 → 18 → 19/20）
  • AIO（异步 IO）基础设施
• PostgreSQL 的独特视角：无逻辑读/物理读之分

体系架构概览

PostgreSQL 采用多进程架构，每个客户端连接对应一个独立的 backend process。内存分为：

• 共享内存：包括 shared_buffers（数据页缓存）、wal_buffers（日志缓冲）、锁结构、ProcArray 等。
• 私有内存：每个后端进程的 work_mem、maintenance_work_mem 等。

数据文件存储在 $PGDATA/base 目录下，每个表对应一个或多个文件（relfilenode），文件内部由 8KB 的页（page/block）组成。

后台进程负责将脏页刷入磁盘（BGWriter）和 WAL 日志持久化（WALWriter），Checkpointer 定期执行检查点，保证崩溃恢复点。

数据读写流程

读取路径

1. 后端进程发起数据页请求。
2. 在 shared_buffers 的哈希表中查找，若命中则直接返回。
3. 若未命中，调用 read() 系统调用，操作系统先查看自身 Page Cache，若命中则返回，否则触发磁盘 IO。
4. 数据先被加载到 Page Cache，再拷贝到 shared_buffers 中的空闲槽位。若 shared_buffers 已满，则通过 Clock Sweep 淘汰一个页。

写入路径

• 数据修改在 shared_buffers 中标记为脏页。
• 脏页不会立即落盘，而是由 BGWriter 定期批量写入，或由 Checkpointer 在检查点期间强制写入。
• WAL 日志通过 WALWriter 持久化，保证事务提交后即使宕机也能恢复。
• 写入时同样经过操作系统 Page Cache，最终由操作系统调度刷盘（除非使用 O_DIRECT 或 fsync）。

双缓存问题

PostgreSQL 同时维护 shared_buffers 和操作系统 Page Cache，导致同一数据页可能被缓存两次，造成内存浪费。更严重的是，操作系统的 LRU 淘汰算法不了解数据库的访问模式，可能将数据库即将访问的热数据淘汰，而数据库自己的 Clock Sweep 算法才能根据 usage_count 和 pin 状态做出更准确的决策。

具体表现：

• 内存利用率低，尤其在 shared_buffers 较大时，双缓存加重内存压力。
• IO 路径冗长：数据需从磁盘→OS缓存→shared_buffers，经历两次内存拷贝。
• 刷脏时，操作系统可能过度刷盘，引发性能抖动。

社区早已认识到此问题，并计划引入 Direct IO 来绕过操作系统缓存，让数据库完全自主管理 IO。

页面置换算法：Clock Sweep

shared_buffers 采用 Clock Sweep（时钟扫描）算法，代替传统 LRU。每个缓存页有一个 usage_count 计数器：

• 每次访问该页，usage_count 加 1（上限可调）。
• 一个后台扫描进程（或分配新页时的扫描）循环遍历所有页，每扫描一次将 usage_count 减 1。
• 当 usage_count 减至 0 时，该页被选中淘汰。
• 如果页被 pin 住（正在被访问），则跳过淘汰。

这种机制类似于钟表指针旋转，因此得名。它避免维护链表，且能较好地反映近期访问频率。

IO 模型演进

当前：Buffered IO（默认）

所有数据读写均经过操作系统 Page Cache。参数 wal_sync_method 可控制 WAL 是否使用 O_DIRECT，但数据文件读写始终走缓存。

演进路线

• PostgreSQL 16：引入实验性参数 debug_io_direct，允许开发者在某些场景测试 Direct IO，不可用于生产。
• PostgreSQL 17：引入 read_stream 框架，为异步 IO 准备接口。
• PostgreSQL 18：正式引入 AIO（异步 IO）基础设施，支持 io_uring（Linux）和 worker（其他平台）两种模式，可提升顺序扫描、VACUUM 等操作的性能。
• PostgreSQL 19/20（预期）：实现完整的 Direct IO，彻底避免双缓存，数据直接通过 DMA 在磁盘和 shared_buffers 间传输，减少 CPU 拷贝，并允许数据库根据自身访问模式精细管理内存。

Direct IO 的优势

• 消除双缓存，释放内存。
• 减少内核态/用户态数据拷贝，降低 CPU 开销。
• 避免操作系统盲目刷脏，减少 IO 抖动。
• 数据库可根据 usage_count 和 pin 信息智能淘汰，命中率更高。

注意事项

即使使用 Direct IO，仍然需要 fsync 或 O_DSYNC 保证持久性。目前 AIO 主要支持读操作，写操作仍为同步，后续版本会逐步增强。

API 速览

本部分列举涉及的核心 PostgreSQL 系统视图和扩展函数。

1. pg_stat_bgwriter

• 所属：系统视图（无需扩展）
• 功能：统计后台写入进程的活动信息。
• 关键字段：
  • checkpoints_timed：定期检查点次数
  • checkpoints_req：请求检查点次数
  • buffers_checkpoint：检查点期间写入的缓冲数
  • buffers_clean：BGWriter 主动清理的脏页数
  • maxwritten_clean：因写入过多而停止的次数
• 示例：

SELECT checkpoints_timed, checkpoints_req, buffers_checkpoint, buffers_clean
FROM pg_stat_bgwriter;

2. pg_buffercache 扩展

• 所属：需 CREATE EXTENSION pg_buffercache;
• 功能：查看 shared_buffers 中每个缓存页的详细信息。
• 关键字段：
  • bufferid：缓冲槽编号
  • relfilenode：表文件节点
  • reltablespace：表空间 OID
  • relblocknumber：页号
  • isdirty：是否为脏页
  • usagecount：当前访问计数
• 示例：

SELECT COUNT(*) AS total_buffers,
       SUM(CASE WHEN isdirty THEN 1 ELSE 0 END) AS dirty_buffers,
       AVG(usagecount) AS avg_usage
FROM pg_buffercache;

3. pg_stat_reset() 函数

重置所有统计计数器，用于性能测试。

SELECT pg_stat_reset();

Demo 简单示例（Node.js 完整）

以下示例使用 Node.js 的 pg 库连接 PostgreSQL，创建测试表，执行多次随机查询，并查看 shared_buffers 状态和 bgwriter 统计。

运行说明

1. 安装依赖：npm install pg
2. 确保 PostgreSQL 运行，数据库 postgres 可用。
3. 执行命令：node demo.js

代码说明

• 创建表 test_cache 并插入 1000 条数据。
• 执行 5 次随机单点查询，记录耗时。
• 查询 pg_stat_bgwriter 和 pg_buffercache（需提前建扩展）获取缓存统计。

技术点总结

• 数据库连接、参数化查询。
• 系统视图使用。
• 性能监控基础。

// demo.js
import { Client } from 'pg';

const client = new Client({
  user: 'postgres',
  password: 'postgres',
  host: 'localhost',
  database: 'postgres',
  port: 5432,
});

async function run() {
  await client.connect();

  // 创建测试表
  await client.query(`
    CREATE TABLE IF NOT EXISTS test_cache (
      id SERIAL PRIMARY KEY,
      data TEXT
    );
  `);

  // 插入 1000 条数据
  await client.query(`
    INSERT INTO test_cache (data)
    SELECT generate_series(1,1000)::text;
  `);

  // 执行 5 次随机查询
  for (let i = 0; i < 5; i++) {
    const id = Math.floor(Math.random() * 1000) + 1;
    const start = Date.now();
    const res = await client.query('SELECT * FROM test_cache WHERE id = $1', [id]);
    console.log(`Query ${i+1} took ${Date.now() - start}ms, rows: ${res.rowCount}`);
  }

  // 查看 bgwriter 统计
  const bg = await client.query('SELECT * FROM pg_stat_bgwriter');
  console.log('BGWriter stats:', bg.rows[0]);

  // 启用缓冲页查看扩展
  await client.query('CREATE EXTENSION IF NOT EXISTS pg_buffercache');
  const cache = await client.query(`
    SELECT COUNT(*) AS total_buffers,
           SUM(CASE WHEN isdirty THEN 1 ELSE 0 END) AS dirty_buffers,
           ROUND(AVG(usagecount), 2) AS avg_usage
    FROM pg_buffercache;
  `);
  console.log('Buffer cache status:', cache.rows[0]);

  await client.end();
}

run().catch(console.error);

对应的原生 PostgreSQL 指令（注释）：

-- 创建表
CREATE TABLE test_cache (id SERIAL PRIMARY KEY, data TEXT);
-- 插入数据
INSERT INTO test_cache (data) SELECT generate_series(1,1000)::text;
-- 随机查询（示例）
SELECT * FROM test_cache WHERE id = 1;
-- 查看 bgwriter
SELECT * FROM pg_stat_bgwriter;
-- 查看缓冲池（需扩展）
CREATE EXTENSION IF NOT EXISTS pg_buffercache;
SELECT COUNT(*) AS total_buffers, SUM(CASE WHEN isdirty THEN 1 ELSE 0 END) AS dirty_buffers, ROUND(AVG(usagecount), 2) AS avg_usage FROM pg_buffercache;

多语言示例

以下分别使用 Go、Python、Java 实现相同逻辑。

Go

package main

import (
    "database/sql"
    "fmt"
    "math/rand"
    "time"
    _ "github.com/lib/pq"
)

func main() {
    connStr := "user=postgres password=postgres dbname=postgres host=localhost port=5432 sslmode=disable"
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        panic(err)
    }
    defer db.Close()

    // 创建表
    _, err = db.Exec(`CREATE TABLE IF NOT EXISTS test_cache (id SERIAL PRIMARY KEY, data TEXT)`)
    if err != nil {
        panic(err)
    }
    // 插入数据
    _, err = db.Exec(`INSERT INTO test_cache (data) SELECT generate_series(1,1000)::text`)
    if err != nil {
        panic(err)
    }

    rand.Seed(time.Now().UnixNano())
    for i := 0; i < 5; i++ {
        id := rand.Intn(1000) + 1
        var data string
        start := time.Now()
        err = db.QueryRow(`SELECT data FROM test_cache WHERE id = $1`, id).Scan(&data)
        if err != nil {
            panic(err)
        }
        fmt.Printf("Query %d took %v\n", i+1, time.Since(start))
    }

    // bgwriter stats
    var checkpointsTimed, buffersCheckpoint, buffersClean int
    err = db.QueryRow(`SELECT checkpoints_timed, buffers_checkpoint, buffers_clean FROM pg_stat_bgwriter`).Scan(&checkpointsTimed, &buffersCheckpoint, &buffersClean)
    if err != nil {
        panic(err)
    }
    fmt.Printf("BGWriter: checkpoints_timed=%d, buffers_checkpoint=%d, buffers_clean=%d\n", checkpointsTimed, buffersCheckpoint, buffersClean)
}

Python

import psycopg2
import random
import time

conn = psycopg2.connect(
    host="localhost",
    database="postgres",
    user="postgres",
    password="postgres"
)
cur = conn.cursor()

cur.execute("CREATE TABLE IF NOT EXISTS test_cache (id SERIAL PRIMARY KEY, data TEXT)")
cur.execute("INSERT INTO test_cache (data) SELECT generate_series(1,1000)::text")
conn.commit()

for i in range(5):
    id = random.randint(1, 1000)
    start = time.time()
    cur.execute("SELECT data FROM test_cache WHERE id = %s", (id,))
    row = cur.fetchone()
    print(f"Query {i+1} took {(time.time()-start)*1000:.2f}ms")

cur.execute("SELECT checkpoints_timed, buffers_checkpoint, buffers_clean FROM pg_stat_bgwriter")
row = cur.fetchone()
print(f"BGWriter: checkpoints_timed={row[0]}, buffers_checkpoint={row[1]}, buffers_clean={row[2]}")

cur.close()
conn.close()

Java

import java.sql.*;
import java.util.Properties;
import java.util.Random;

public class PgDemo {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:postgresql://localhost:5432/postgres";
        Properties props = new Properties();
        props.setProperty("user", "postgres");
        props.setProperty("password", "postgres");
        Connection conn = DriverManager.getConnection(url, props);

        Statement stmt = conn.createStatement();
        stmt.execute("CREATE TABLE IF NOT EXISTS test_cache (id SERIAL PRIMARY KEY, data TEXT)");
        stmt.execute("INSERT INTO test_cache (data) SELECT generate_series(1,1000)::text");

        Random rand = new Random();
        for (int i = 0; i < 5; i++) {
            int id = rand.nextInt(1000) + 1;
            long start = System.currentTimeMillis();
            ResultSet rs = stmt.executeQuery("SELECT data FROM test_cache WHERE id = " + id);
            rs.next();
            System.out.println("Query " + (i+1) + " took " + (System.currentTimeMillis() - start) + "ms");
        }

        ResultSet bg = stmt.executeQuery("SELECT checkpoints_timed, buffers_checkpoint, buffers_clean FROM pg_stat_bgwriter");
        bg.next();
        System.out.println("BGWriter: checkpoints_timed=" + bg.getInt(1) + ", buffers_checkpoint=" + bg.getInt(2) + ", buffers_clean=" + bg.getInt(3));

        stmt.close();
        conn.close();
    }
}

多语言对比

语言	驱动/库	连接方式	参数化查询	结果处理	适用场景
Node.js	pg	Client 对象，连接字符串或配置对象	$1, $2 占位符	异步 Promise / 回调	高并发 I/O 密集型
Go	github.com/lib/pq	sql.Open("postgres", connStr)	$1, $2 占位符	同步，Scan 到变量	高性能服务端
Python	psycopg2	connect() 关键字参数	%s 占位符	游标迭代或 fetchone	数据分析、脚本
Java	org.postgresql.Driver	DriverManager.getConnection(url, props)	字符串拼接（避免，建议 PreparedStatement）	ResultSet 遍历	企业级应用

项目难点与解决方案

核心难点

• 双缓存导致内存浪费和性能波动。
• 操作系统缓存与数据库缓存策略不协调，造成不必要的 IO。
• 现有 Buffered IO 路径长，CPU 拷贝开销大。

解决方案

• 社区推动 Direct IO 和 AIO，逐步消除双缓存。
• 通过监控 pg_stat_bgwriter 和 pg_buffercache，调整 shared_buffers、bgwriter_delay 等参数缓解问题。
• 对于高频访问场景，可考虑使用较大的 shared_buffers 并减少操作系统缓存（vm.dirty_ratio 调优）。

广度

涉及数据库内核、操作系统存储栈、硬件 DMA、异步编程等。

深度

深入分析了 Clock Sweep 算法、缓存一致性、IO 路径层次。

复杂度

需要理解内核态/用户态切换、DMA、异步 IO 模型，以及 PostgreSQL 多进程并发控制。

官方文档

• PostgreSQL Official Documentation
• PostgreSQL Wiki – Asynchronous I/O
• PostgreSQL Wiki – Direct I/O

参考链接

• PostgreSQL 16 Release Notes
• CommitFest – AIO Infrastructure
• 唐成 – Direct IO 开发实践 (公开课)（请自行搜索）

总结

本文系统讲解了 PostgreSQL 的数据读写流程，重点剖析了双缓存问题的成因与影响，并介绍了 Clock Sweep 页面置换算法。随着社区推进 Direct IO 和 AIO，未来 PostgreSQL 将实现更高效的内存管理和 IO 性能，彻底告别双缓存弊端。

当前生产环境可通过监控 pg_stat_bgwriter 和 pg_buffercache 进行针对性调优，并关注新版本 IO 特性。多语言示例展示了跨语言连接 PostgreSQL 的标准实践，方便开发者快速集成。