一、PostgreSQL 整体架构概述

PostgreSQL 采用经典的 C/S 架构（客户端/服务器），是一个进程架构的数据库系统（区别于 MySQL 的线程架构）。当客户端发起连接请求时，Postmaster 主进程会 fork 出一个独立的 backend 进程来服务该连接。

二、进程架构详解

1. Postmaster 主进程

Postmaster 是 PostgreSQL 的守护进程，是整个数据库实例的"总管"。

主要职责：

• 初始化数据库实例
• 监听端口（默认 5432），接受客户端连接请求
• 为每个连接 fork 一个独立的 Backend 进程
• 管理辅助进程的启动和停止
• 处理参数配置重载（pg_ctl reload）

# 查看 Postmaster 进程
ps aux | grep postgres

# 输出示例：
# postgres  1234  0.0  0.1  ... /usr/local/pgsql/bin/postgres
# postgres  1235  0.0  0.0  ... postgres: checkpointer
# postgres  1236  0.0  0.0  ... postgres: background writer
# postgres  1237  0.0  0.0  ... postgres: walwriter
# postgres  1238  0.0  0.0  ... postgres: autovacuum launcher
# postgres  1239  0.0  0.0  ... postgres: stats collector
# postgres  1240  0.0  0.0  ... postgres: logical replication launcher
# postgres  1300  0.0  0.0  ... postgres: user postgres [local] idle

2. Backend 服务进程

每个客户端连接对应一个独立的 Backend 进程，负责处理该连接的所有请求。

Backend 进程的生命周期：

1. Postmaster 接收连接请求
2. 完成认证和权限检查
3. Fork 新的 Backend 进程
4. 进程处理该连接的所有 SQL 请求
5. 客户端断开后，Backend 进程终止

-- 查看当前活动的 Backend 进程
SELECT pid, usename, application_name, client_addr, state, query 
FROM pg_stat_activity;

-- 查看连接数统计
SELECT count(*), state FROM pg_stat_activity GROUP BY state;

3. 辅助进程

-- 查看辅助进程状态
SELECT pid, backend_type, wait_event_type, wait_event
FROM pg_stat_activity
WHERE backend_type LIKE '%writer%' 
   OR backend_type LIKE '%checkpointer%'
   OR backend_type LIKE '%vacuum%';

三、内存架构详解

1. 共享内存（Shared Memory）

所有 Backend 进程和辅助进程共享的内存区域，在 PostgreSQL 启动时分配。

-- 查看共享内存使用情况
SELECT name, setting, unit, short_desc 
FROM pg_settings 
WHERE name LIKE '%shared%' OR name LIKE '%buffer%';

-- 查看共享内存命中率（重要性能指标）
SELECT 
    sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) * 100 AS cache_hit_ratio
FROM pg_statio_user_tables;

共享内存组件：

2. 本地内存（Local Memory）

每个 Backend 进程独立分配的内存，在进程 fork 时分配。

-- 查看当前会话的内存配置
SHOW work_mem;
SHOW maintenance_work_mem;
SHOW temp_buffers;

-- 设置会话级内存（临时）
SET work_mem = '128MB';
SET maintenance_work_mem = '512MB';

四、存储架构详解

1. 物理存储结构

PostgreSQL 数据目录（PGDATA）结构：

PGDATA/
├── PG_VERSION                    # PostgreSQL 版本号
├── base/                         # 数据库目录
│   ├── 1/                        # 数据库 OID=1（template0）
│   │   ├── 1247                  # 表文件（OID=1247）
│   │   ├── 1247_fsm              # 空闲空间映射
│   │   ├── 1247_vm               # 可见性映射
│   │   └── ...
│   └── 16384/                    # 数据库 OID=16384（用户数据库）
├── global/                       # 全局系统表
│   ├── pg_control                # 控制文件
│   └── pg_database               # 数据库列表
├── pg_wal/                       # WAL 日志目录
│   ├── 000000010000000000000001  # WAL 段文件（16MB）
│   └── archive_status/           # 归档状态
├── pg_xact/                      # 事务状态（Commit Log）
├── pg_subtrans/                  # 子事务状态
├── pg_multixact/                 # 多事务状态
├── pg_logical/                   # 逻辑解码数据
├── pg_replslot/                  # 复制槽
├── pg_stat_tmp/                  # 统计信息临时文件
├── pg_tblspc/                    # 表空间符号链接
├── postgresql.conf               # 主配置文件
├── postgresql.auto.conf          # 自动生成的配置（ALTER SYSTEM）
├── pg_hba.conf                   # 客户端认证配置
├── pg_ident.conf                 # 用户映射配置
└── log/                          # 日志目录（如果配置）

2. 表文件内部结构

一个表在磁盘上对应多个文件：

数据文件内部结构（页结构）：

┌─────────────────────────────────────┐
│            Page Header              │  24 bytes
│  pd_lsn, pd_checksum, pd_flags,    │
│  pd_lower, pd_upper, pd_special     │
├─────────────────────────────────────┤
│          Item Pointer Array         │  4 bytes × 数量
│  (偏移量, 长度, 标志)                │
├─────────────────────────────────────┤
│                                     │
│          Free Space                 │  空闲区域
│                                     │
├─────────────────────────────────────┤
│          Heap Tuple Data            │  行数据（从下往上填）
│                                     │
├─────────────────────────────────────┤
│          Special Space              │  索引等特殊用途
└─────────────────────────────────────┘

-- 查看表文件信息
SELECT 
    relname,
    relpages,           -- 页数（每页8KB）
    reltuples,          -- 估算行数
    pg_relation_size(oid) / 8192 AS actual_pages,
    pg_total_relation_size(oid) AS total_size
FROM pg_class 
WHERE relname = 'users';

-- 查看表和索引的实际大小
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_table_size(schemaname||'.'||tablename)) AS table_size,
    pg_size_pretty(pg_indexes_size(schemaname||'.'||tablename)) AS index_size,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size
FROM pg_tables
WHERE schemaname = 'public';

3. WAL（预写日志）结构

WAL（Write-Ahead Logging）是 PostgreSQL 实现 ACID 持久性的核心机制。

WAL 段文件命名：

00000001 00000000 00000001
│        │        │
│        │        └── 段内序号（每段16MB）
│        └─────────── Timeline ID（时间线）
└──────────────────── 日志序号（Log ID）

-- 查看 WAL 配置
SHOW wal_level;              -- replica / logical
SHOW wal_buffers;            -- WAL 缓冲区大小
SHOW wal_writer_delay;       -- WAL Writer 延迟
SHOW checkpoint_timeout;     -- 检查点超时
SHOW min_wal_size;           -- 最小 WAL 大小
SHOW max_wal_size;           -- 最大 WAL 大小

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

-- 查看 WAL 统计
SELECT * FROM pg_stat_wal;

五、查询执行流程

1. 解析器（Parser）

• 检查 SQL 语法
• 生成原始解析树

-- 查看解析后的查询树（原始格式）
SELECT * FROM users WHERE id = 1;

2. 分析器（Analyzer）

• 语义分析：表是否存在、列是否存在
• 生成查询树

3. 重写器（Rewriter）

• 应用视图规则
• 实现规则系统

-- 创建视图后，查询视图会被重写为对基表的查询
CREATE VIEW active_users AS SELECT * FROM users WHERE status = 'active';

-- 实际执行时会被重写
SELECT * FROM active_users;
-- 重写为：SELECT * FROM users WHERE status = 'active';

4. 规划器（Planner）

• 生成多种可能的执行计划
• 选择代价最低的计划

-- 查看执行计划（不实际执行）
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';

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

-- 查看执行计划的详细信息
EXPLAIN (ANALYZE, BUFFERS, TIMING, FORMAT JSON) 
SELECT * FROM users WHERE email = 'alice@example.com';

5. 执行器（Executor）

• 执行计划树
• 处理表扫描、索引扫描、连接操作
• 返回结果

六、并发控制：MVCC

PostgreSQL 使用 MVCC（多版本并发控制） 实现高并发，避免读写阻塞。

MVCC 核心原理

关键字段：

• xmin：插入该行的事务ID
• xmax：删除/更新该行的事务ID（0表示未删除）
• ctid：行的物理位置

-- 查看元组头信息（需要扩展）
CREATE EXTENSION IF NOT EXISTS pageinspect;

-- 查看数据页内容
SELECT * FROM heap_page_items(get_raw_page('users', 0));

-- 查看可见性映射
SELECT * FROM pg_visibility_map('users');

事务隔离级别

-- 查看当前隔离级别
SHOW transaction_isolation;

-- 设置隔离级别
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM users WHERE id = 1;
COMMIT;

七、配置文件体系

1. 配置文件层次

2. 配置参数分类

-- 查看所有配置参数
SELECT name, setting, unit, context, vartype, short_desc 
FROM pg_settings 
ORDER BY name;

-- 查看修改需要重启的参数
SELECT name, setting, context 
FROM pg_settings 
WHERE context IN ('postmaster', 'sighup');

-- 动态修改参数（不需要重启）
ALTER SYSTEM SET work_mem = '32MB';
SELECT pg_reload_conf();

-- 恢复默认值
ALTER SYSTEM RESET work_mem;

参数上下文（context）说明：

3. 重要配置参数速查表

八、架构最佳实践

1. 单机优化配置模板

# postgresql.conf 生产环境配置示例

# ============================================
# 连接设置
# ============================================
listen_addresses = '*'
port = 5432
max_connections = 300
superuser_reserved_connections = 3

# ============================================
# 内存设置（假设服务器内存32GB）
# ============================================
shared_buffers = 8GB                    # 25% of RAM
work_mem = 32MB                         # 适合复杂查询
maintenance_work_mem = 1GB              # VACUUM/INDEX 操作
effective_cache_size = 24GB             # 75% of RAM
wal_buffers = 64MB

# ============================================
# 检查点设置
# ============================================
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
max_wal_size = 32GB
min_wal_size = 8GB

# ============================================
# 日志设置
# ============================================
log_destination = 'stderr'
logging_collector = on
log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB
log_min_duration_statement = 1000      # 记录 >1秒的查询
log_statement = 'ddl'                  # 记录 DDL 语句
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

# ============================================
# 统计信息
# ============================================
track_activities = on
track_counts = on
track_io_timing = on
track_functions = 'pl'
track_activity_query_size = 4096

# ============================================
# 自动清理
# ============================================
autovacuum = on
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02
autovacuum_vacuum_cost_delay = 10ms
autovacuum_max_workers = 5

# ============================================
# 查询优化器
# ============================================
random_page_cost = 1.1                 # SSD 设置较低值
effective_io_concurrency = 200
enable_partitionwise_join = on
enable_partitionwise_aggregate = on
jit = on

2. 架构选择指南