跳至主要內容

PostgreSQL 体系架构

HSXISAWD2026/3/20大约 13 分钟数据库PostgreSQL架构MVCCWAL

PostgreSQL 体系架构

一、整体架构概览

PostgreSQL 采用典型的客户端/服务器架构，由多个进程协同工作。

二、进程结构

2.1 进程概览

# 查看 PostgreSQL 进程
ps aux | grep postgres

# 典型输出：
# postgres  1234  postmaster                    # 主进程
# postgres  1235  checkpointer                  # 检查点进程
# postgres  1236  background writer             # 后台写进程
# postgres  1237  walwriter                     # WAL 写进程
# postgres  1238  autovacuum launcher           # 自动清理启动器
# postgres  1239  stats collector               # 统计收集器
# postgres  1240  logical replication launcher  # 逻辑复制启动器
# postgres  1241  postgres: user db [active]    # 后端进程

2.2 主进程（Postmaster）

Postmaster 是 PostgreSQL 的主进程，负责：

  • 监听客户端连接请求
  • 为每个客户端连接创建后端进程（fork）
  • 管理共享内存
  • 启动和监控辅助进程
  • 处理信号（如 SIGHUP 重新加载配置）

2.3 后端进程（Backend Process）

每个客户端连接对应一个后端进程，负责执行 SQL 语句。

2.4 后台辅助进程

进程名功能描述
Background Writer定期将脏页写入磁盘，减轻检查点压力
WAL Writer将 WAL 缓冲区内容写入 WAL 文件
Checkpointer执行检查点，确保数据持久化
Autovacuum Launcher启动自动 VACUUM 工作进程
Autovacuum Worker执行自动清理和分析
Stats Collector收集统计信息
Archiver归档 WAL 文件（如果启用）
WAL Sender向从库发送 WAL（流复制）
WAL Receiver从主库接收 WAL（从库）
Logical Replication Launcher管理逻辑复制
-- 查看当前进程信息
SELECT pid, usename, application_name, client_addr, state, query
FROM pg_stat_activity;

-- 查看后台进程
SELECT pid, wait_event_type, wait_event, state, backend_type
FROM pg_stat_activity
WHERE backend_type != 'client backend';

三、内存结构

3.1 内存架构总览

3.2 共享缓冲区（Shared Buffers）

共享缓冲区是 PostgreSQL 最重要的缓存区域，用于缓存数据页。

-- 查看缓冲区使用情况
SELECT
    c.relname,
    pg_size_pretty(count(*) * 8192) AS buffered,
    round(100.0 * count(*) / (SELECT setting FROM pg_settings WHERE name='shared_buffers')::integer, 1) AS buffers_percent,
    round(100.0 * count(*) * 8192 / pg_relation_size(c.oid), 1) AS percent_of_relation
FROM pg_class c
INNER JOIN pg_buffercache b ON b.relfilenode = c.relfilenode
INNER JOIN pg_database d ON b.reldatabase = d.oid AND d.datname = current_database()
GROUP BY c.oid, c.relname
ORDER BY count(*) DESC
LIMIT 20;

3.3 本地内存配置

# work_mem：排序和哈希操作使用的内存
# 注意：每个操作独立使用，复杂查询可能使用多倍
work_mem = 64MB

# maintenance_work_mem：维护操作使用的内存
# VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY 等
maintenance_work_mem = 512MB

# temp_buffers：临时表使用的缓冲区
temp_buffers = 32MB

# 有效缓存大小（用于查询优化器估算）
# 包括 shared_buffers + 操作系统文件缓存
effective_cache_size = 6GB

3.4 内存配置建议

参数建议值说明
shared_buffers物理内存的 25%最重要的缓存配置
effective_cache_size物理内存的 50-75%用于优化器估算
work_mem总内存/(max_connections*2)根据并发调整
maintenance_work_mem总内存的 5% 或 1GB维护操作用
-- 查看内存相关配置
SELECT name, setting, unit, context
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'maintenance_work_mem',
               'effective_cache_size', 'temp_buffers', 'wal_buffers');

四、存储结构

4.1 数据目录结构

$PGDATA/
├── base/                    # 数据库文件目录
│   ├── 1/                   # template1 数据库
│   ├── 13761/              # template0 数据库
│   └── 16384/              # 用户数据库
│       ├── 16385           # 表文件
│       ├── 16385_fsm       # 空闲空间映射
│       ├── 16385_vm        # 可见性映射
│       └── ...
├── global/                  # 全局对象（如 pg_database）
│   ├── pg_control          # 控制文件
│   └── ...
├── pg_wal/                  # WAL 日志文件
│   ├── 000000010000000000000001
│   └── ...
├── pg_xact/                 # 事务提交日志（CLOG）
├── pg_multixact/            # 多事务状态
├── pg_subtrans/             # 子事务数据
├── pg_twophase/             # 两阶段提交状态
├── pg_commit_ts/            # 提交时间戳
├── pg_notify/               # LISTEN/NOTIFY 状态
├── pg_stat/                 # 统计信息
├── pg_stat_tmp/             # 临时统计信息
├── pg_logical/              # 逻辑复制状态
├── pg_replslot/             # 复制槽
├── pg_tblspc/               # 表空间符号链接
├── postgresql.conf          # 主配置文件
├── pg_hba.conf             # 客户端认证配置
├── pg_ident.conf           # 用户名映射
├── postmaster.pid          # 进程 ID 文件
└── postmaster.opts         # 启动参数

4.2 表文件结构

4.3 Tuple（行）结构

-- 查看表的物理存储信息
SELECT
    pg_relation_filepath('users') AS filepath,
    pg_relation_size('users') AS size,
    pg_size_pretty(pg_relation_size('users')) AS pretty_size;

-- 查看表的页面和行信息
SELECT
    relname,
    relpages,    -- 页面数
    reltuples,   -- 行数估算
    pg_size_pretty(pg_relation_size(oid)) AS size
FROM pg_class
WHERE relname = 'users';

-- 使用 pageinspect 扩展查看页面详情
CREATE EXTENSION IF NOT EXISTS pageinspect;

SELECT * FROM page_header(get_raw_page('users', 0));
SELECT * FROM heap_page_items(get_raw_page('users', 0));

4.4 表空间

-- 创建表空间
CREATE TABLESPACE fastspace LOCATION '/ssd/pg_data';

-- 在指定表空间创建表
CREATE TABLE large_table (
    id SERIAL PRIMARY KEY,
    data TEXT
) TABLESPACE fastspace;

-- 修改表的表空间
ALTER TABLE large_table SET TABLESPACE pg_default;

-- 查看表空间
SELECT spcname, pg_tablespace_location(oid) AS location
FROM pg_tablespace;

-- 设置默认表空间
SET default_tablespace = 'fastspace';

五、MVCC 机制

5.1 什么是 MVCC

MVCC（Multi-Version Concurrency Control，多版本并发控制）是 PostgreSQL 实现高并发的核心机制。它通过为每个事务维护数据的多个版本，实现读写互不阻塞。

5.2 事务 ID（XID）

-- 查看当前事务 ID
SELECT txid_current();

-- 查看事务 ID 范围
SELECT
    txid_current() AS current_xid,
    txid_snapshot_xmin(txid_current_snapshot()) AS xmin,
    txid_snapshot_xmax(txid_current_snapshot()) AS xmax;

-- 行级别的事务信息
SELECT
    xmin,    -- 插入该行的事务 ID
    xmax,    -- 删除/更新该行的事务 ID（0 表示未删除）
    ctid,    -- 物理位置 (页号, 行号)
    *
FROM users;

5.3 可见性判断

5.4 VACUUM 清理

MVCC 会产生旧版本数据（死元组），需要 VACUUM 清理。

-- 手动执行 VACUUM
VACUUM users;                    -- 普通 VACUUM
VACUUM VERBOSE users;            -- 显示详细信息
VACUUM FULL users;               -- 完全重写表（锁表）
VACUUM ANALYZE users;            -- VACUUM + 更新统计信息

-- 查看自动 VACUUM 配置
SHOW autovacuum;
SHOW autovacuum_vacuum_threshold;
SHOW autovacuum_vacuum_scale_factor;

-- 查看表的死元组情况
SELECT
    schemaname,
    relname,
    n_live_tup,      -- 活元组数
    n_dead_tup,      -- 死元组数
    n_mod_since_analyze,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'users';

-- 查看需要 VACUUM 的表
SELECT
    schemaname || '.' || relname AS table_name,
    n_dead_tup,
    n_live_tup,
    round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

六、WAL 日志机制

6.1 WAL 概述

WAL（Write-Ahead Logging）是 PostgreSQL 的预写日志机制，用于保证数据持久性和支持数据恢复。

6.2 WAL 配置

# WAL 级别
# minimal: 只记录崩溃恢复所需的最少信息
# replica: 支持 WAL 归档和流复制
# logical: 支持逻辑复制
wal_level = replica

# WAL 缓冲区大小
wal_buffers = 64MB

# WAL 同步方式
# fsync: 确保 WAL 写入磁盘
fsync = on

# WAL 同步方法
synchronous_commit = on

# 检查点配置
checkpoint_timeout = 5min          # 检查点间隔
max_wal_size = 4GB                 # 触发检查点的 WAL 大小
min_wal_size = 1GB                 # 最小保留 WAL
checkpoint_completion_target = 0.9 # 检查点完成目标

# WAL 归档（用于 PITR 和复制）
archive_mode = on
archive_command = 'cp %p /archive/%f'

6.3 WAL 文件管理

# WAL 文件位置
ls -la $PGDATA/pg_wal/

# WAL 文件命名
# 000000010000000000000001
# ^^^^^^^^ ^^^^^^^^ ^^^^^^^^
#   时间线    逻辑ID    段ID

# 每个 WAL 文件默认 16MB

-- 查看当前 WAL 位置
SELECT pg_current_wal_lsn();

-- 查看 WAL 文件信息
SELECT * FROM pg_ls_waldir() ORDER BY modification DESC LIMIT 10;

-- 手动切换 WAL 文件
SELECT pg_switch_wal();

-- 手动执行检查点
CHECKPOINT;

-- 查看检查点信息
SELECT
    checkpoints_timed,
    checkpoints_req,
    checkpoint_write_time,
    checkpoint_sync_time,
    buffers_checkpoint
FROM pg_stat_bgwriter;

6.4 检查点（Checkpoint）

七、查询处理流程

7.1 SQL 执行阶段

7.2 执行计划查看

-- 查看执行计划
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- 查看详细执行计划（包含实际执行信息）
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

-- 更详细的输出
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM users WHERE email = 'test@example.com';

-- JSON 格式输出（便于程序解析）
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM users WHERE email = 'test@example.com';

配置建议

参数建议值说明
shared_buffers物理内存 25%共享缓冲区
effective_cache_size物理内存 50-75%优化器估算
work_mem总内存/(max_connections*2)排序内存
wal_levelreplica支持复制
max_wal_size4GB检查点触发

运维要点

  1. 定期监控死元组，确保 autovacuum 正常工作
  2. 关注 WAL 日志增长，合理配置检查点
  3. 监控共享缓冲区命中率
  4. 定期分析慢查询，优化执行计划
Default footer
Copyright © 2026 HSXISAWD