PostgreSQL笔记27: 双缓存架构下的内存管理与性能观测实践

关注

原创 2026-08-19 23:45:00 285 阅读 AI 写同款· GEO 优化 ›

VCSA 6.7证书过期别慌！手把手教你通过SSH修改系统时间恢复登录（附STS证书修复脚本）本文详细介绍了VCSA 6.7证书过期的应急处理方案，包括通过SSH修改系统时间临时恢复登录、全面更新证书体系以及专项修复STS证书的实战步骤。文章还提供了后期验证和系统加固的建议，帮助运维人员有效应对证书过期问题，确保虚拟化平台的稳定运行。阅读详情

纲要

PostgreSQL 缓存架构概览
- 双缓存（Double Buffering）：shared_buffers 与操作系统页缓存（OS Page Cache）
- shared_buffers：数据库共享内存缓冲区
- OS Page Cache：操作系统级文件缓存
Linux 内存水位线与回收机制
- 内存水位线：WMARK_HIGH、WMARK_LOW、WMARK_MIN
- 异步回收：kswapd 内核线程
- 直接内存回收（Direct Reclaim）：同步阻塞
- OOM Killer 的触发
PostgreSQL 私有缓存
- SysCache：系统表缓存
- RelCache：关系缓存
- 缓存淘汰机制的缺失与 idle_session_timeout（PG 14+）
- pg_timeout 插件与手动清理方案
缓存观测与预热工具
- pg_buffercache：观测 shared_buffers 状态
- pg_prewarm：数据预热
- pgfincore：观测 OS Page Cache
- pg_buffercache_evict()（PG 17+）
shared_buffers 大小配置建议
- 经验法则：总内存的 25% ~ 40%
- 双缓存架构下的权衡

PostgreSQL 的双缓存架构

PostgreSQL 采用双缓存（Double Buffering）架构，即数据页同时被数据库自身的共享缓冲区与操作系统页缓存所缓存。

数据库层面由 shared_buffers 参数定义共享内存缓冲区的大小。当查询需要读取数据块时，PostgreSQL 优先在 shared_buffers 中查找。若未命中，则发起系统调用从操作系统页缓存读取；若操作系统页缓存也未命中，则最终从物理磁盘读取。

这种设计的核心特征在于：PostgreSQL 并不管理操作系统页缓存，而是由内核自主管理。两个缓存层相互独立，互不知晓对方缓存了哪些数据块，因此经常出现同一数据块被同时缓存在两层的现象，即双缓存。

与 Oracle 等数据库严格区分逻辑读与物理读不同，PostgreSQL 的 I/O 统计口径更为复杂——一次数据读取可能命中 shared_buffers、可能命中 OS Page Cache、也可能真正落盘，三者对应完全不同的性能特征。

Linux 内存水位线与直接内存回收

理解 PostgreSQL 双缓存架构的性能影响，首先需要掌握 Linux 内核的内存管理机制。

Linux 内核为每个内存区域（Zone）维护三条水位线（Watermark）：

水位线	内核常量	含义
高水位	`WMARK_HIGH`	空闲内存充足，系统不进行回收
低水位	`WMARK_LOW`	空闲内存偏低，触发异步回收
最低水位	`WMARK_MIN`	内存紧张，触发同步回收

当空闲内存降至低水位（WMARK_LOW）以下时，内核唤醒 kswapd 内核线程，在后台异步回收内存页。kswapd 是一个异步进程，持续扫描并尝试释放内存，以缓解内存压力。

若内存消耗速度超过 kswapd 的回收能力，空闲内存进一步降至最低水位（WMARK_MIN）以下，内核将触发直接内存回收（Direct Reclaim）。直接内存回收是同步阻塞操作：任何试图申请内存的用户态进程都必须等待内核完成内存回收后才能继续执行。此时通过 ps 命令查看，相关进程通常处于 D 状态（不可中断睡眠），典型场景包括等待 I/O 或内存回收完成。

在极端情况下，若直接内存回收仍无法释放足够内存，内核将触发 OOM Killer，依据评分机制选择终止某些进程以释放内存。

在生产环境中，可通过 sar -B 命令观察 pgscan（kswapd 扫描页数）和 pgsteal（直接回收扫描页数）等指标，判断系统是否频繁陷入内存回收压力。

SysCache 与 RelCache：进程私有缓存

除共享的 shared_buffers 外，PostgreSQL 每个后端进程还维护两类私有缓存：

SysCache（系统表缓存） ：缓存系统表的元数据信息，避免每次操作都访问系统表
RelCache（关系缓存） ：缓存表的模式信息（RelationData），包括列定义、索引信息等

原生 PostgreSQL 的 SysCache 和 RelCache 没有淘汰机制。进程首次访问某个表后，其元数据将一直保留在该进程的私有内存中，直至进程退出或收到缓存失效消息（如 DDL 操作触发的 sinval 系统广播）。

在多租户或大量表的场景下，长期运行的连接可能导致 SysCache 和 RelCache 占用大量私有内存。可通过 pmap 或 smem 等工具观测进程内存占用情况。

针对此问题，PostgreSQL 14 引入了 idle_session_timeout 参数，用于控制空闲连接（非事务中）的超时自动断开：

-- 设置空闲会话超时为 5 分钟
ALTER SYSTEM SET idle_session_timeout = '5min';
SELECT pg_reload_conf();

-- 查看当前设置
SHOW idle_session_timeout;

在 PostgreSQL 14 之前，可借助 pg_timeout 插件，或通过定期查询 pg_stat_activity 手动终止空闲连接：

-- 查询空闲连接（非事务中）
SELECT pid, state, state_change, query
FROM pg_stat_activity
WHERE state = 'idle'
  AND state_change < NOW() - INTERVAL '5 minutes'
  AND backend_type = 'client backend';

-- 终止指定连接
SELECT pg_terminate_backend(<pid>);

部分云数据库（如阿里云 RDS、腾讯云 RDS）已在内核层面为 SysCache 和 RelCache 增加了 LRU 淘汰机制，以缓解内存膨胀问题。

缓存观测与预热工具

pg_buffercache：观测 shared_buffers

pg_buffercache 是 PostgreSQL 官方 contrib 模块，提供实时检查共享缓冲区状态的能力。每个缓冲区对应视图中的一行。

安装与使用：

-- 安装扩展
CREATE EXTENSION pg_buffercache;

-- 查看共享缓冲区概要
SELECT * FROM pg_buffercache_summary();

pg_buffercache 视图核心字段：

字段	类型	说明
`bufferid`	`integer`	缓冲区 ID，范围 1 ~ `shared_buffers`
`relfilenode`	`oid`	关系文件节点号
`reltablespace`	`oid`	表空间 OID
`reldatabase`	`oid`	数据库 OID
`relblocknumber`	`bigint`	页面号
`isdirty`	`boolean`	是否为脏页
`usagecount`	`smallint`	Clock-sweep 访问计数
`pinning_backends`	`integer`	固定该缓冲区的后端数

查询特定表的缓存命中情况：

-- 获取表的 relfilenode
SELECT relfilenode, relname FROM pg_class WHERE relname = 'your_table';

-- 查询该表在 shared_buffers 中的缓存块数
SELECT count(*) FROM pg_buffercache
WHERE relfilenode = <relfilenode>
  AND reldatabase = (SELECT oid FROM pg_database WHERE datname = current_database());

PostgreSQL 17：pg_buffercache_evict()

PostgreSQL 17 在 pg_buffercache 模块中新增了 pg_buffercache_evict() 函数，允许按缓冲区 ID 从共享缓冲区中驱逐指定数据块。此外还提供了 pg_buffercache_evict_relation() 和 pg_buffercache_evict_all() 两个函数。

-- PostgreSQL 17+ 示例：驱逐指定缓冲区
SELECT pg_buffercache_evict(bufferid) FROM pg_buffercache
WHERE relfilenode = <relfilenode> LIMIT 10;

该函数仅限超级用户使用，主要用于开发测试场景。在 PostgreSQL 17 之前，若要清空 shared_buffers 中的特定数据，只能重启数据库实例。

pg_prewarm：数据预热

pg_prewarm 模块提供将关系数据预加载到操作系统缓存或 PostgreSQL 缓冲区的能力。

-- 安装扩展
CREATE EXTENSION pg_prewarm;

-- 函数签名
pg_prewarm(
    regclass,        -- 关系名称
    mode text,       -- 'prefetch' | 'read' | 'buffer'
    fork text,       -- 通常为 'main'
    first_block int8, -- 起始块，NULL 表示 0
    last_block int8   -- 结束块，NULL 表示到末尾
) RETURNS int8       -- 返回预热的块数

三种预热模式：

模式	行为	特点
`prefetch`	向操作系统发出异步预读请求	非阻塞，需 OS 支持
`read`	同步读取指定范围的块	阻塞，所有平台支持
`buffer`	将块读入 `PostgreSQL` 缓冲区	缓存到 `shared_buffers`

-- 将整个表预热到 PostgreSQL 缓冲区
SELECT pg_prewarm('your_table', 'buffer');

-- 将表预热到操作系统缓存
SELECT pg_prewarm('your_table', 'read');

-- 自动预热：配置 shared_preload_libraries
-- postgresql.conf:
-- shared_preload_libraries = 'pg_prewarm'
-- pg_prewarm.autoprewarm = true
-- pg_prewarm.autoprewarm_interval = 300s

启用自动预热后，后台工作进程会定期将 shared_buffers 中的缓冲区列表保存到 autoprewarm.blocks 文件，并在数据库重启后恢复这些块。

pgfincore：观测操作系统页缓存

pgfincore 是第三方插件，用于查看关系文件在操作系统页缓存中的状态。

-- 查看表的 OS 页缓存状态
SELECT * FROM pgfincore('your_table');

输出字段包括 pages_mem（已在内存中的页数）、os_pages_free（系统空闲页数）等。

双缓存的性能影响与配置建议

双缓存的利弊

优势：

数据库重启后，数据仍可能保留在 OS Page Cache 中，避免完全从磁盘读取
操作系统层面的 I/O 合并可减少总 I/O 次数

劣势：

同一数据块可能同时在两处缓存，造成内存浪费
shared_buffers 过大将挤压 OS Page Cache 可用内存
查询响应时间不稳定，呈现"时快时慢"的特征

shared_buffers 配置建议

PostgreSQL 官方文档建议：对于 1GB 以上内存的专用数据库服务器，shared_buffers 的合理起始值为系统内存的 25%。将超过 40% 的 RAM 分配给 shared_buffers 不太可能比较小分配量带来更好的性能。

-- 查看当前 shared_buffers 大小
SHOW shared_buffers;

-- 修改配置（需重启）
ALTER SYSTEM SET shared_buffers = '4GB';
-- 或直接在 postgresql.conf 中设置
-- shared_buffers = 4GB

双缓存观测实验

以下实验演示如何观测双缓存的实际效果：

-- 1. 创建测试表并插入数据
CREATE TABLE cache_test (id serial, data text);
INSERT INTO cache_test (data) SELECT generate_series(1, 100000)::text;

-- 2. 获取表的 relfilenode
SELECT relfilenode, relname FROM pg_class WHERE relname = 'cache_test';

-- 3. 使用 pg_prewarm 预热到 shared_buffers
SELECT pg_prewarm('cache_test', 'buffer');

-- 4. 查看 shared_buffers 中的缓存情况
SELECT count(*) FROM pg_buffercache
WHERE relfilenode = <relfilenode>;

-- 5. 执行查询并观察执行时间
EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM cache_test;

-- 6. PostgreSQL 17+: 驱逐该表的所有缓冲区
SELECT pg_buffercache_evict(bufferid)
FROM pg_buffercache
WHERE relfilenode = <relfilenode>;

-- 7. 再次执行查询，观察 buffer 命中率变化
EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM cache_test;

API 速览

pg_buffercache

API	所属库	签名	说明
`pg_buffercache` 视图	`pg_buffercache`	-	每个缓冲区一行，展示缓存状态
`pg_buffercache_summary()`	`pg_buffercache`	`() RETURNS record`	返回共享缓冲区概要统计
`pg_buffercache_usage_counts()`	`pg_buffercache`	`() RETURNS SETOF record`	按 usage count 分组统计
`pg_buffercache_evict(bufferid integer)`	`pg_buffercache`	`(integer) RETURNS boolean`	PG 17+，驱逐指定缓冲区
`pg_buffercache_evict_relation(regclass)`	`pg_buffercache`	`(regclass) RETURNS integer`	PG 17+，驱逐关系的所有缓冲区
`pg_buffercache_evict_all()`	`pg_buffercache`	`() RETURNS integer`	PG 17+，驱逐所有缓冲区

pg_prewarm

API	所属库	签名	说明
`pg_prewarm(regclass, mode, fork, first_block, last_block)`	`pg_prewarm`	`(regclass, text, text, int8, int8) RETURNS int8`	预热关系数据到缓存
`autoprewarm_start_worker()`	`pg_prewarm`	`() RETURNS void`	手动启动自动预热工作进程
`autoprewarm_dump_now()`	`pg_prewarm`	`() RETURNS int8`	立即保存当前缓存块列表

系统视图与函数

API	所属库	签名	说明
`pg_stat_activity`	系统视图	-	查询当前连接与会话状态
`pg_terminate_backend(pid integer)`	系统函数	`(integer) RETURNS boolean`	终止指定后端进程
`pg_reload_conf()`	系统函数	`() RETURNS boolean`	重新加载配置文件

Demo 简单示例

以下是一个完整的 Node.js 示例，演示如何使用 pg 驱动连接 PostgreSQL，通过 pg_buffercache 观测缓存状态，并使用 pg_prewarm 进行数据预热。

pg-cache-demo/
├── package.json
├── index.js
└── sql/
    └── init.sql

package.json

{
  "name": "pg-cache-demo",
  "version": "1.0.0",
  "description": "PostgreSQL 缓存管理演示",
  "main": "index.js",
  "scripts": {
    "start": "node index.js"
  },
  "dependencies": {
    "pg": "^8.11.0"
  }
}

index.js

const { Client } = require('pg');

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

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

  try {
    // 1. 创建测试表
    await client.query(`
      DROP TABLE IF EXISTS cache_demo;
      CREATE TABLE cache_demo (id serial, data text);
      INSERT INTO cache_demo (data)
      SELECT generate_series(1, 50000)::text;
    `);
    console.log('✅ 测试表创建完成');

    // 2. 安装扩展（如未安装）
    await client.query('CREATE EXTENSION IF NOT EXISTS pg_buffercache;');
    await client.query('CREATE EXTENSION IF NOT EXISTS pg_prewarm;');
    console.log('✅ 扩展已加载');

    // 3. 获取表的 relfilenode
    const relResult = await client.query(`
      SELECT relfilenode, relname
      FROM pg_class
      WHERE relname = 'cache_demo';
    `);
    const relfilenode = relResult.rows[0].relfilenode;
    console.log(`📋 表 relfilenode: ${relfilenode}`);

    // 4. 预热前缓存状态
    const before = await client.query(`
      SELECT count(*) as cached_blocks
      FROM pg_buffercache
      WHERE relfilenode = $1
        AND reldatabase = (SELECT oid FROM pg_database WHERE datname = current_database());
    `, [relfilenode]);
    console.log(`📊 预热前 shared_buffers 缓存块数: ${before.rows[0].cached_blocks}`);

    // 5. 使用 pg_prewarm 预热
    const warmed = await client.query(`
      SELECT pg_prewarm('cache_demo', 'buffer') as blocks_warmed;
    `);
    console.log(`🔥 预热块数: ${warmed.rows[0].blocks_warmed}`);

    // 6. 预热后缓存状态
    const after = await client.query(`
      SELECT count(*) as cached_blocks
      FROM pg_buffercache
      WHERE relfilenode = $1
        AND reldatabase = (SELECT oid FROM pg_database WHERE datname = current_database());
    `, [relfilenode]);
    console.log(`📊 预热后 shared_buffers 缓存块数: ${after.rows[0].cached_blocks}`);

    // 7. 执行查询并分析缓存命中
    const explain = await client.query(`
      EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
      SELECT count(*) FROM cache_demo;
    `);
    const plan = explain.rows[0]['QUERY PLAN'][0];
    console.log('📈 查询执行计划:');
    console.log(`   - 执行时间: ${plan['Execution Time']} ms`);
    console.log(`   - 共享缓冲区命中: ${plan['Shared Hit Blocks'] || 0} 块`);
    console.log(`   - 共享缓冲区读取: ${plan['Shared Read Blocks'] || 0} 块`);

    // 8. 查看共享缓冲区概要
    const summary = await client.query('SELECT * FROM pg_buffercache_summary();');
    console.log('📊 共享缓冲区概要:');
    console.log(`   - 已使用缓冲区: ${summary.rows[0].buffers_used}`);
    console.log(`   - 未使用缓冲区: ${summary.rows[0].buffers_unused}`);
    console.log(`   - 脏缓冲区: ${summary.rows[0].buffers_dirty}`);
    console.log(`   - 平均使用计数: ${summary.rows[0].usagecount_avg}`);

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

main();

运行说明

安装依赖：npm install pg
修改 index.js 中的数据库连接配置
确保 PostgreSQL 已安装 pg_buffercache 和 pg_prewarm 扩展
运行：npm start

技术点总结

使用 pg 驱动连接 PostgreSQL 并执行原生 SQL
通过 pg_buffercache 视图观测 shared_buffers 缓存状态
使用 pg_prewarm 函数将表数据预热到共享缓冲区
通过 EXPLAIN (ANALYZE, BUFFERS) 分析查询的缓存命中情况
调用 pg_buffercache_summary() 获取缓冲区概要统计

多语言示例

基于前文 Node.js 示例，本节提供 Go、Python 和 Java 三种语言的完整实现，功能保持一致：连接 PostgreSQL，创建测试表，安装扩展，观测 shared_buffers 缓存状态，执行 pg_prewarm 预热，并输出缓存统计信息。

Go 示例

使用 pgx 驱动（推荐），需提前安装：go get github.com/jackc/pgx/v5

pg-cache-demo-go/
├── go.mod
├── go.sum
└── main.go

go.mod

module pg-cache-demo

go 1.21

require github.com/jackc/pgx/v5 v5.5.0

main.go

package main

import (
	"context"
	"fmt"
	"log"

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

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

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

	// 1. 创建测试表
	_, err = conn.Exec(ctx, `
		DROP TABLE IF EXISTS cache_demo;
		CREATE TABLE cache_demo (id serial, data text);
		INSERT INTO cache_demo (data) SELECT generate_series(1, 50000)::text;
	`)
	if err != nil {
		log.Fatalf("创建表失败: %v", err)
	}
	fmt.Println("✅ 测试表创建完成")

	// 2. 安装扩展
	_, err = conn.Exec(ctx, "CREATE EXTENSION IF NOT EXISTS pg_buffercache;")
	if err != nil {
		log.Fatalf("安装 pg_buffercache 失败: %v", err)
	}
	_, err = conn.Exec(ctx, "CREATE EXTENSION IF NOT EXISTS pg_prewarm;")
	if err != nil {
		log.Fatalf("安装 pg_prewarm 失败: %v", err)
	}
	fmt.Println("✅ 扩展已加载")

	// 3. 获取表的 relfilenode
	var relfilenode uint32
	err = conn.QueryRow(ctx, `
		SELECT relfilenode FROM pg_class WHERE relname = 'cache_demo';
	`).Scan(&relfilenode)
	if err != nil {
		log.Fatalf("获取 relfilenode 失败: %v", err)
	}
	fmt.Printf("📋 表 relfilenode: %d\n", relfilenode)

	// 4. 预热前缓存状态
	var beforeCount int
	err = conn.QueryRow(ctx, `
		SELECT count(*) FROM pg_buffercache
		WHERE relfilenode = $1
		  AND reldatabase = (SELECT oid FROM pg_database WHERE datname = current_database());
	`, relfilenode).Scan(&beforeCount)
	if err != nil {
		log.Fatalf("查询预热前缓存失败: %v", err)
	}
	fmt.Printf("📊 预热前 shared_buffers 缓存块数: %d\n", beforeCount)

	// 5. 使用 pg_prewarm 预热
	var warmed int64
	err = conn.QueryRow(ctx, "SELECT pg_prewarm('cache_demo', 'buffer');").Scan(&warmed)
	if err != nil {
		log.Fatalf("预热失败: %v", err)
	}
	fmt.Printf("🔥 预热块数: %d\n", warmed)

	// 6. 预热后缓存状态
	var afterCount int
	err = conn.QueryRow(ctx, `
		SELECT count(*) FROM pg_buffercache
		WHERE relfilenode = $1
		  AND reldatabase = (SELECT oid FROM pg_database WHERE datname = current_database());
	`, relfilenode).Scan(&afterCount)
	if err != nil {
		log.Fatalf("查询预热后缓存失败: %v", err)
	}
	fmt.Printf("📊 预热后 shared_buffers 缓存块数: %d\n", afterCount)

	// 7. 执行查询并分析缓存命中（使用 EXPLAIN）
	var planJSON string
	err = conn.QueryRow(ctx, `
		EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
		SELECT count(*) FROM cache_demo;
	`).Scan(&planJSON)
	if err != nil {
		log.Fatalf("执行 EXPLAIN 失败: %v", err)
	}
	fmt.Println("📈 查询执行计划 (JSON):")
	fmt.Println(planJSON[:200] + "...")

	// 8. 查看共享缓冲区概要
	var buffersUsed, buffersUnused, buffersDirty int
	var usagecountAvg float64
	err = conn.QueryRow(ctx, `
		SELECT buffers_used, buffers_unused, buffers_dirty, usagecount_avg
		FROM pg_buffercache_summary();
	`).Scan(&buffersUsed, &buffersUnused, &buffersDirty, &usagecountAvg)
	if err != nil {
		log.Fatalf("获取概要失败: %v", err)
	}
	fmt.Println("📊 共享缓冲区概要:")
	fmt.Printf("   - 已使用缓冲区: %d\n", buffersUsed)
	fmt.Printf("   - 未使用缓冲区: %d\n", buffersUnused)
	fmt.Printf("   - 脏缓冲区: %d\n", buffersDirty)
	fmt.Printf("   - 平均使用计数: %.2f\n", usagecountAvg)
}

运行说明

初始化模块：go mod tidy
修改 connStr 中的数据库连接信息
确保 PostgreSQL 已安装扩展
运行：go run main.go

Python 示例

使用 psycopg2 驱动，需提前安装：pip install psycopg2-binary

pg-cache-demo-py/
├── requirements.txt
└── main.py

requirements.txt

psycopg2-binary>=2.9.9

main.py

import psycopg2
import psycopg2.extras

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

    try:
        # 1. 创建测试表
        cur.execute("""
            DROP TABLE IF EXISTS cache_demo;
            CREATE TABLE cache_demo (id serial, data text);
            INSERT INTO cache_demo (data) SELECT generate_series(1, 50000)::text;
        """)
        print("✅ 测试表创建完成")

        # 2. 安装扩展
        cur.execute("CREATE EXTENSION IF NOT EXISTS pg_buffercache;")
        cur.execute("CREATE EXTENSION IF NOT EXISTS pg_prewarm;")
        print("✅ 扩展已加载")

        # 3. 获取表的 relfilenode
        cur.execute("SELECT relfilenode FROM pg_class WHERE relname = 'cache_demo';")
        relfilenode = cur.fetchone()[0]
        print(f"📋 表 relfilenode: {relfilenode}")

        # 4. 预热前缓存状态
        cur.execute("""
            SELECT count(*) FROM pg_buffercache
            WHERE relfilenode = %s
              AND reldatabase = (SELECT oid FROM pg_database WHERE datname = current_database());
        """, (relfilenode,))
        before_count = cur.fetchone()[0]
        print(f"📊 预热前 shared_buffers 缓存块数: {before_count}")

        # 5. 使用 pg_prewarm 预热
        cur.execute("SELECT pg_prewarm('cache_demo', 'buffer');")
        warmed = cur.fetchone()[0]
        print(f"🔥 预热块数: {warmed}")

        # 6. 预热后缓存状态
        cur.execute("""
            SELECT count(*) FROM pg_buffercache
            WHERE relfilenode = %s
              AND reldatabase = (SELECT oid FROM pg_database WHERE datname = current_database());
        """, (relfilenode,))
        after_count = cur.fetchone()[0]
        print(f"📊 预热后 shared_buffers 缓存块数: {after_count}")

        # 7. 执行查询并分析缓存命中
        cur.execute("""
            EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
            SELECT count(*) FROM cache_demo;
        """)
        plan = cur.fetchone()[0][0]  # 返回 JSON 数组
        print("📈 查询执行计划:")
        print(f"   - 执行时间: {plan['Execution Time']} ms")
        print(f"   - 共享缓冲区命中: {plan.get('Shared Hit Blocks', 0)} 块")
        print(f"   - 共享缓冲区读取: {plan.get('Shared Read Blocks', 0)} 块")

        # 8. 查看共享缓冲区概要
        cur.execute("""
            SELECT buffers_used, buffers_unused, buffers_dirty, usagecount_avg
            FROM pg_buffercache_summary();
        """)
        row = cur.fetchone()
        print("📊 共享缓冲区概要:")
        print(f"   - 已使用缓冲区: {row[0]}")
        print(f"   - 未使用缓冲区: {row[1]}")
        print(f"   - 脏缓冲区: {row[2]}")
        print(f"   - 平均使用计数: {row[3]:.2f}")

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

if __name__ == "__main__":
    main()

运行说明

安装依赖：pip install -r requirements.txt
修改 main.py 中的数据库连接信息
运行：python main.py

Java 示例

使用 JDBC 驱动（需下载 postgresql-42.7.2.jar），并添加至 classpath。

pg-cache-demo-java/
├── Main.java
└── postgresql-42.7.2.jar

Main.java

import java.sql.*;
import org.json.JSONArray;
import org.json.JSONObject;

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

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            conn.setAutoCommit(true);
            Statement stmt = conn.createStatement();

            // 1. 创建测试表
            stmt.executeUpdate(
                "DROP TABLE IF EXISTS cache_demo;" +
                "CREATE TABLE cache_demo (id serial, data text);" +
                "INSERT INTO cache_demo (data) SELECT generate_series(1, 50000)::text;"
            );
            System.out.println("✅ 测试表创建完成");

            // 2. 安装扩展
            stmt.executeUpdate("CREATE EXTENSION IF NOT EXISTS pg_buffercache;");
            stmt.executeUpdate("CREATE EXTENSION IF NOT EXISTS pg_prewarm;");
            System.out.println("✅ 扩展已加载");

            // 3. 获取表的 relfilenode
            ResultSet rs = stmt.executeQuery(
                "SELECT relfilenode FROM pg_class WHERE relname = 'cache_demo';"
            );
            rs.next();
            int relfilenode = rs.getInt(1);
            rs.close();
            System.out.printf("📋 表 relfilenode: %d%n", relfilenode);

            // 4. 预热前缓存状态
            rs = stmt.executeQuery(
                "SELECT count(*) FROM pg_buffercache " +
                "WHERE relfilenode = " + relfilenode +
                " AND reldatabase = (SELECT oid FROM pg_database WHERE datname = current_database());"
            );
            rs.next();
            int beforeCount = rs.getInt(1);
            rs.close();
            System.out.printf("📊 预热前 shared_buffers 缓存块数: %d%n", beforeCount);

            // 5. 使用 pg_prewarm 预热
            rs = stmt.executeQuery("SELECT pg_prewarm('cache_demo', 'buffer');");
            rs.next();
            long warmed = rs.getLong(1);
            rs.close();
            System.out.printf("🔥 预热块数: %d%n", warmed);

            // 6. 预热后缓存状态
            rs = stmt.executeQuery(
                "SELECT count(*) FROM pg_buffercache " +
                "WHERE relfilenode = " + relfilenode +
                " AND reldatabase = (SELECT oid FROM pg_database WHERE datname = current_database());"
            );
            rs.next();
            int afterCount = rs.getInt(1);
            rs.close();
            System.out.printf("📊 预热后 shared_buffers 缓存块数: %d%n", afterCount);

            // 7. 执行查询并分析缓存命中（JSON 解析需依赖 org.json）
            rs = stmt.executeQuery(
                "EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT count(*) FROM cache_demo;"
            );
            rs.next();
            String jsonStr = rs.getString(1);
            rs.close();
            JSONArray jsonArray = new JSONArray(jsonStr);
            JSONObject plan = jsonArray.getJSONObject(0);
            System.out.println("📈 查询执行计划:");
            System.out.printf("   - 执行时间: %s ms%n", plan.get("Execution Time"));
            System.out.printf("   - 共享缓冲区命中: %s 块%n", plan.optInt("Shared Hit Blocks", 0));
            System.out.printf("   - 共享缓冲区读取: %s 块%n", plan.optInt("Shared Read Blocks", 0));

            // 8. 查看共享缓冲区概要
            rs = stmt.executeQuery(
                "SELECT buffers_used, buffers_unused, buffers_dirty, usagecount_avg " +
                "FROM pg_buffercache_summary();"
            );
            rs.next();
            System.out.println("📊 共享缓冲区概要:");
            System.out.printf("   - 已使用缓冲区: %d%n", rs.getInt(1));
            System.out.printf("   - 未使用缓冲区: %d%n", rs.getInt(2));
            System.out.printf("   - 脏缓冲区: %d%n", rs.getInt(3));
            System.out.printf("   - 平均使用计数: %.2f%n", rs.getDouble(4));
            rs.close();

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

运行说明

下载 postgresql-42.7.2.jar 和 org.json 库（如 json-20240303.jar）并置于同一目录
编译：javac -cp ".:postgresql-42.7.2.jar:json-20240303.jar" Main.java
运行：java -cp ".:postgresql-42.7.2.jar:json-20240303.jar" Main
修改数据库连接信息后即可执行。

多语言对比

维度	Node.js (pg)	Go (pgx)	Python (psycopg2)	Java (JDBC)
驱动/库	`pg` (npm)	`pgx/v5`	`psycopg2-binary`	PostgreSQL JDBC 驱动
连接方式	`new Client(config)`	`pgx.Connect(ctx, connStr)`	`psycopg2.connect(**kwargs)`	`DriverManager.getConnection(url, user, pwd)`
参数占位符	`$1, $2`	`$1, $2`	`%s`（使用 `%` 格式化）或 `%s` 配合元组	字符串拼接（或 `?` 但 PG 推荐 `$n`）
上下文支持	回调/Promise/async	原生 `context.Context`	无（同步）	无（同步）
JSON 解析	原生 `JSON.parse`	需自定义或使用 `encoding/json`	内置 `json` 模块	需外部库如 `org.json`
错误处理	try-catch	返回 `error`	try-except	try-catch
连接池	内置 `Pool`	`pgxpool`	`psycopg2.pool`	`HikariCP` 等第三方
异步支持	原生 `async/await`	原生 `goroutine` + `pgx` 异步	需 `psycopg2` 异步变体或 `asyncpg`	需 `CompletableFuture` 或响应式驱动
依赖管理	npm	go modules	pip	Maven/Gradle 或手动 jar
适用场景	快速原型、轻量服务	高性能并发微服务	数据分析、脚本自动化	企业级应用、Spring 生态

阶段性总结

以上四种语言实现均基于相同的 PostgreSQL 缓存管理流程，核心 SQL 与操作步骤完全一致，差异主要体现在驱动 API、参数化查询风格、错误处理模式及 JSON 解析方式。开发者可根据项目技术栈选择对应实现，所有示例均可在正确配置数据库连接后直接运行。

官方文档

参考链接

总结

本文系统阐述了 PostgreSQL 双缓存架构的内在机理与实践管理方法。核心要点包括：

双缓存架构：PostgreSQL 同时依赖 shared_buffers 与操作系统页缓存，两者独立运作且可能存在数据冗余，这是导致内存效率问题与查询响应时间不稳定的根本原因。
操作系统内存回收：Linux 通过 WMARK_HIGH、WMARK_LOW、WMARK_MIN 三条水位线驱动 kswapd 异步回收与直接内存回收（同步阻塞），在内存紧张时可能阻塞 PostgreSQL 进程。
私有缓存管理：SysCache 与 RelCache 无淘汰机制，长期连接可能导致内存膨胀。PostgreSQL 14 引入的 idle_session_timeout 及 pg_timeout 插件是有效的应对手段。
观测与预热工具：pg_buffercache 用于观测 shared_buffers 状态，pg_prewarm 用于数据预热，pgfincore 用于观测操作系统页缓存。PostgreSQL 17 新增的 pg_buffercache_evict() 系列函数为测试场景提供了缓冲区驱逐能力。
配置经验法则：shared_buffers 通常设置为系统总内存的 25%~40%，在双缓存架构下需为操作系统页缓存预留足够内存。