PostgreSQL基础与架构总览

luobo · 2025 年9 月 1 日 12:56

部分内容借鉴自AI生成，请谨慎辨别。

本文从数据模型、存储与索引、并发与缓存、持久化与恢复，到复制与分区扩展，系统梳理 PostgreSQL（下文简称 PG）的核心概念与实现要点。

行（Row/Tuple）与表（Table）、模式（Schema）

行（元组 Tuple）
- PG 的基本数据单元，存储在堆表（heap）中；每个元组带有可见性元数据（如 xmin/xmax、c_tid、标志位），支持 MVCC。
- 支持丰富类型：数值/文本/时间、数组、JSON/JSONB、范围类型、几何类型、枚举、复合类型、自定义类型等。
表（Table）
- 行的逻辑容器，DDL 管理于系统目录（pg_class、pg_attribute 等）。
- 支持约束：主键/唯一/检查/外键；支持行级安全（RLS）。
- 大字段溢出采用 TOAST（The Oversized-Attribute Storage Technique）机制进行表外存储。
模式（Schema）
- 数据库内部的命名空间；同库内不同 schema 下对象可同名；搜索路径控制对象解析。

存储编码与页面/文件结构

行编码
- 行头 + 可变长度列偏移数组（null bitmap、varlena）；变长字段采用 varlena 格式，可压缩或 TOAST。
数据页（Page/Block）
- 默认 8KB（block_size 可在编译时设置）；页头 + 元组指针数组（line pointers）+ 元组数据。
- 可见性相关位（hint bits）用于加速判断；Index-Only Scan 借助 Visibility Map（VM）跳过堆访问。
关系文件与分段
- 每个表/索引对应“关系文件”，以 relfilenode 命名，位于 base/<dbOid>/ 目录；超过 1GB 分段（.1、.2…）。
- 关系 fork：主数据（.main，无扩展名）、FSM（.fsm 空闲空间图）、VM（.vm 可见性图）、INIT（.init 初始化 fork）。
- 目录概览：pg_wal（预写日志）、global（全局系统表）、base（各数据库）、pg_tblspc（表空间符号链接）。
空间管理
- FSM 跟踪页级可用空间，辅助插入定位；VM 标记“全可见/全冻结”页以服务索引仅扫描与冻结管理。

索引：B-Tree 及多种访问方法

B-Tree（默认通用索引）
- 基于 Lehman–Yao 可并发分裂的 B-Tree，右链接（right-link）简化并发扫描/分裂。
- 支持去重存储（duplicate key deduplication）、INCLUDE 覆盖列、唯一约束、并发创建（CONCURRENTLY）。
- Index-Only Scan 结合 VM 减少堆表访问；部分索引、表达式索引增强灵活性。
其他索引类型（访问方法）
- Hash：自 v10 起可 WAL 记录，支持崩溃安全与复制。
- GIN：倒排索引，适合全文检索、数组/JSONB 包含；支持并发构建与快速更新路径。
- GiST：通用有界搜索树，适合范围/空间/相似度等；支持 KNN。
- SP-GiST：分割空间结构（如前缀树、四叉树），适合高维/稀疏分布。
- BRIN：块级摘要索引，适合顺序相关的大表（时间序列、日志），体积小、构建快。
- Bloom（扩展）：近似集合索引，节省空间。
维护与重建
- REINDEX、REINDEX CONCURRENTLY；VACUUM 维护可见性，避免膨胀；CLUSTER 物理重排以优化局部性。

MVCC 并发控制与可见性

元组多版本
- 更新/删除不覆盖旧版本，而是写入新元组，旧元组标记不可见（HOT 更新可在同页内形成链，减少索引更新）。
- 可见性由 xmin/xmax（创建/删除事务 ID）及快照（Snapshot）决定；快照包含活动事务集合与最小/最大 XID。
快照与隔离级别
- Read Committed：每条语句独立快照。
- Repeatable Read：事务级快照，不可读到后续提交的数据（避免不可重复读）；可能产生幻读由 MVCC 限制。
- Serializable：采用 SSI（Serializable Snapshot Isolation），在冲突图检测下防止幻读与不可串行化执行。
Vacuum 与冻结
- VACUUM 清理死元组，回收空间；Autovacuum 后台自动执行。
- 为防止事务号回卷（XID wraparound），需冻结旧元组（freeze）；关键参数：autovacuum_freeze_max_age 等。
- MultiXact 管理多事务共享/排他行锁，存在相应的冻结与清理。

缓存与淘汰（Shared Buffers + 类 LRU）

共享缓冲池（shared_buffers）
- 所有后台/前台进程共享；采用时钟扫描（clock-sweep）近似 LRU 的策略，配合 usage count 选择淘汰。
- 脏页由背景写入器（bgwriter）与检查点进程刷盘；walwriter 负责 WAL 刷新。
OS Page Cache
- 数据文件同时受操作系统页缓存缓存；参数 effective_cache_size 影响优化器代价估计。
工作内存
- work_mem 控制排序/哈希聚合的内存阈值；maintenance_work_mem 影响创建索引、VACUUM 等维护操作。
临时与会话缓存
- 临时表有独立 temp_buffers；Hash 聚合、排序溢出到临时文件时受 temp_file_limit 限制。

持久化保障：WAL 写前日志 + Checkpoint

WAL（pg_wal）
- 变更先记录到 WAL（顺序写），再修改数据页；以 LSN（日志序列号）标识进度；支持 WAL 压缩、归档（archive_mode）。
- full_page_writes 缓解 torn page：检查点后首次修改页会写入整页镜像。
- 同步策略：synchronous_commit 控制事务等待磁盘刷写程度；组提交（group commit）提升吞吐。
检查点（Checkpoint）
- 周期性将脏页刷盘，生成一致性恢复点；参数 checkpoint_timeout、max_wal_size 等控制频率与压力。
- 崩溃恢复：从最近检查点回放 WAL 至最后提交；支持时间点恢复（PITR）、时间线（timeline）切换。
备份与恢复
- 物理备份：pg_basebackup、文件系统快照 + WAL 归档；逻辑备份：pg_dump/pg_restore。
- 恢复流程由恢复信号文件（recovery.signal、standby.signal）与 restore_command 控制。

存储与执行层关系、进程/模块架构

进程模型
- 主进程（postmaster）派生连接进程（backend）与后台工作进程：checkpointer、bgwriter、walwriter、autovacuum、archiver、stats、logical launcher、walsender/walreceiver 等。
内部子系统
- 解析器/重写器/优化器（成本驱动，统计信息、扩展统计 CREATE STATISTICS）、执行器（迭代算子）、JIT（LLVM）。
- 缓冲管理（bufmgr）、存储管理（smgr）、WAL/恢复（xlog、rmgr）、锁管理（LWLock/SpinLock/Heavyweight Lock/行级锁）。
访问方法与可插拔接口
- 表访问方法（Table AM，自 v12 提供 API，默认 heapam），索引访问方法（B-Tree、GIN、GiST 等）。
- 外部数据封装（FDW）：foreign table 访问外部数据源；后台工作者（Background Worker）可扩展。

存储子系统定位与核心接口抽象（概念化）

Table AM：定义顺序/索引扫描、插入/更新/删除、可见性、VACUUM 钩子等。
Index AM：定义键比较、插入/扫描、唯一性检查、并发重建等。
Buffer/Storage Manager：页的固定/解固定、读写与刷盘策略；关系文件创建/截断/删除。
WAL Resource Managers（rmgr）：不同子系统的 WAL 编码/重放逻辑。
目录缓存与系统目录：Relcache、pg_class/pg_attribute 等提供对象元数据与映射（relfilenode）。

单机 PostgreSQL 的本质

共享内存 + 多进程架构，通过轻量级锁与原子操作协调并发。
元组多版本 + WAL + Checkpoint 实现高并发与崩溃一致性。
成本模型驱动的优化器结合统计信息选择执行计划；支持并行查询（并行扫描/连接/聚合）。

高扩展性设计：分区、并行与复制

分区表（Declarative Partitioning）
- 分区类型：RANGE、LIST、HASH；支持默认分区；ATTACH/DETACH 在线管理。
- 分区裁剪（Pruning）在规划/执行期剔除无关分区；分区约束提升插入/查询路由效率。
- 建议为分区键建立本地索引；全局索引尚非核心特性（可借助扩展/设计规避）。
并行能力
- 并行顺序/位图/索引扫描、哈希连接、聚合；由 max_parallel_workers_per_gather 等参数控制。
读取扩展与写入扩展
- 读扩展：主从（物理）复制的只读从库承担查询。
- 写扩展：分区 + 应用层路由或逻辑复制（发布订阅）在一定程度上分担写入；更强的分布式可借助扩展。

分区数据切分逻辑

分区键与边界
- RANGE：按区间（含上/不含下）划分；LIST：按离散值集合；HASH：按分桶。
- 元数据存储在系统目录；约束保证数据落在正确分区。
路由与约束检查
- INSERT/UPDATE 自动路由到目标分区；约束排除减少无关分区访问。
- 维护操作（VACUUM/REINDEX/ANALYZE）可按分区并行或独立进行，降低集中开销。
迁移与归档
- 通过 DETACH PARTITION 快速实现“冷热分层/归档”；可单独备份/恢复分区表文件。

查询路由与元数据管理（分区场景）

规划器基于分区元数据进行裁剪与计划拆分；执行器将 DML 正确路由到分区。
统计信息可为各分区独立维护，提升选择率估计准确性；扩展统计（多列相关性/MCV）进一步提升规划质量。

流式复制（主备集群）是什么？

物理复制（Streaming Replication）
- 主库将 WAL 通过 walsender 流式发送，从库 walreceiver 接收并重放；支持级联复制、热备（热备上可读）。
- 同步复制：可配置同步备库与投票方式（任意 N 个等），保证提交确认的一致性。
- 复制槽（Replication Slot）：防止主库过早回收 WAL，确保订阅者不会丢失变更。
故障转移与提升
- 备库提升为主库（promotion）；时间线切换记录在历史文件中；支持手动/自动故障转移（需外部协调器）。
逻辑复制（Publish/Subscribe）
- 基于逻辑解码的行级变更发布/订阅，按表粒度复制，支持在线变更/跨版本/跨平台；常用于增量同步与水平扩展。

分布式 PostgreSQL 集群是什么（概念）

基于物理复制的高可用集群：一个主库 + 多个只读/只写从库（同步/异步），提供故障切换与读扩展。
基于逻辑复制的分布式部署：多主/多写的发布订阅链路，按表或分区划分写入域，应用层或中间层实现路由与冲突处理。
借助扩展的分布式集群：通过扩展实现全局分布式分片与协调（协调节点 + 工作节点、分布式执行与全局事务），用于更大规模的水平扩展。

小结与实践要点

模式与数据类型
- 合理利用 JSONB/数组/范围类型与表达式/部分索引，平衡灵活性与性能。
索引
- 针对工作负载选择 B-Tree/GIN/GiST/BRIN；使用 INCLUDE 覆盖列减少回表；定期 REINDEX/VACUUM 避免膨胀。
并发与隔离
- 根据业务设置隔离级别；关注长事务导致的膨胀与冻结压力；监控 autovacuum 与事务年龄。
缓存与参数
- shared_buffers、work_mem、effective_cache_size、max_wal_size、checkpoint 参数合理调优；监控脏页、检查点抖动。
持久化与备份
- 启用 WAL 归档与周期性基础备份，验证 PITR；选择合适的 synchronous_commit 与同步复制策略。
分区与扩展
- 按时间/范围分区提升可管理性与性能；结合逻辑复制/扩展实现读写扩展与跨区域部署。
版本与特性
- 跟进新版本的性能与可用性改进（并行/索引/统计/逻辑复制增强），评估升级收益并做充分回归测试。

特点全景详解（含插件生态与 JSONB 实战）

本文围绕 PostgreSQL（下文简称 PG）的核心优势展开：可扩展架构与丰富插件生态、强大的内核与 SQL 特性、JSONB 的“文档型”能力、观测与安全、高可用与扩展性，以及关键实践示例。

1. 可扩展架构：从内核到生态的“可插拔”设计

PG 的一大亮点是“处处可扩展”，这不是简单的插件，而是体系化的可插拔接口与对象系统。

可扩展的对象类型
- 数据类型、域（domain）、复合类型、范围类型（range）
- 操作符、函数、操作符类/族（opclass/opfamily）
- 聚合、窗口函数
- 索引访问方法（Index AM）、表访问方法（Table AM，v12+）
- 外部数据封装（FDW）
- 过程语言（PL/pgSQL、PL/Python、PL/Perl、PL/Rust 等）
- 触发器、事件触发器、后台工作进程（Background Worker）
插件加载与管理
- 安装：CREATE EXTENSION extname;
- 包含官方 contrib（随发行版提供）、社区第三方扩展（需单独安装包）。
- 控制文件（.control）与 SQL 安装脚本（.sql）定义版本与升级路径。
外部数据封装 FDW
- postgres_fdw：跨库访问 PG 实例，支持下推过滤/连接
- file_fdw：读取 CSV/文本
- 其他：oracle_fdw、mysql_fdw、odbc_fdw、mongo_fdw、hdfs_fdw 等

示例：开启常用扩展

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS hstore;
-- 如需跨库访问
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

2. 插件与生态图谱（举例）

官方 contrib（部分）
- pg_stat_statements：SQL 级性能画像（规范化语句级统计）
- pg_trgm：三元组相似度，LIKE/ILIKE/模糊检索加速
- hstore：键值对半结构化类型（早于 JSONB 的实现）
- btree_gin / btree_gist：为等值/范围提供 GIN/GiST 支持
- ltree：树路径类型与索引
- cube、earthdistance、intarray、uuid-ossp 等
社区/第三方（部分，需按需评估）
- PostGIS：空间/地理信息（行业标配）
- TimescaleDB：时间序列管理（分区、压缩、连续聚合）
- Citus：分布式分片与并行执行
- pgvector：向量相似度检索（AI/Embedding）
- pg_cron：数据库内计划任务
- pg_repack：在线重组/收缩
- pg_partman：分区自动管理
- pglogical：逻辑复制增强
- pgaudit：审计扩展
- wal2json / test_decoding：逻辑解码输出插件
- HypoPG：假设索引（用于优化器评估）

提示：在生产中使用第三方扩展前，请评估许可证、社区活跃度、版本兼容性与备份/恢复策略。

3. 强大的内核与 SQL 特性（工程级能力）

事务与并发
- MVCC（多版本并发控制），支持 Read Committed / Repeatable Read / 真正可串行化（Serializable via SSI）
- 大量 DDL 事务化（失败可回滚），少数例外如 CONCURRENTLY 类操作
SQL 能力
- 窗口函数、递归 CTE、LATERAL、行转列/列转行、UPSERT（ON CONFLICT）、RETURNING、物化视图
- 生成列（v12+）、分区（RANGE/LIST/HASH）、部分/表达式/覆盖索引（INCLUDE，v11+）
- 并行查询（扫描/连接/聚合）、JIT（LLVM）、增广统计（多列相关性/MCV）
索引类型
- B-Tree（默认）、Hash、GIN、GiST、SP-GiST、BRIN、Bloom（扩展）
- 支持部分索引、表达式索引、并发创建（CONCURRENTLY）
存储与执行
- 页式存储、WAL 写前日志、Checkpoint、PITR（时间点恢复）
- 共享缓冲池 + 近似 LRU 淘汰（clock-sweep）、后台写入器/检查点/自动清理（autovacuum）
- 表访问方法（Table AM）与索引访问方法（Index AM）可插拔
安全与隔离
- 角色/权限体系、行级安全（RLS）、逻辑复制权限控制
- SCRAM-SHA-256 身份验证、TLS；列/字段加密可借助 pgcrypto 或外部方案
- 审计（pgaudit）、SELinux 集成（SE-PostgreSQL，特定平台）
可观测性
- 系统视图 pg_stat_、pg_locks、pg_statio_、pg_stat_wal
- 扩展：pg_stat_statements、auto_explain、pg_buffercache、pg_visibility
- 计划可视化 EXPLAIN [ANALYZE, BUFFERS, WAL, SETTINGS]
高可用与复制
- 物理流复制（异步/同步）、级联复制、热备只读
- 逻辑复制（发布/订阅），用于跨版本/跨区域数据分发与升级
- 备份：pg_basebackup、WAL 归档、PITR

4. JSON/JSONB：原生“文档型”能力

PG 对 JSON 有两种存储：json（文本）与 jsonb（二进制结构化）。绝大多数检索/索引场景推荐 jsonb。

类型与存储
- jsonb 去重/规范化键顺序，支持索引；json 保持原始文本（更适合日志直存与完全还原）
- 大字段溢出使用 TOAST（透明表外存储与压缩）
常用操作符与函数（节选）
- 访问：data->'k'（jsonb）、data->>'k'（文本）、data#>'{a,b}'（路径）
- 包含/存在：@>（包含）、?（键存在）、?|（任一键）、?&（所有键）
- 变更：jsonb_set、jsonb_insert、||（合并）、-（删除键）、#-（删除路径）
- JSONPath（v12+）：jsonb_path_exists/jsonb_path_query/jsonb_path_match 等函数
索引与查询优化
- GIN(jsonb_ops)：支持多种操作（@>、?、?|、?& 等），通用但索引较大
- GIN(jsonb_path_ops)：仅优化 @>，更紧凑更快（适合“包含查询”）
- 表达式/B-Tree：对 data->>'field' 抽取并转换类型建立 B-Tree，支持等值/范围/排序
- 生成列（v12+）：将高频键固化为 STORED 列，减少运行时解析与类型转换

示例：JSONB 建模与索引

CREATE TABLE events (
  id         bigserial PRIMARY KEY,
  data       jsonb NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

-- 通用 JSONB GIN
CREATE INDEX idx_events_data_gin ON events USING gin (data);

-- 仅 @> 更快更省空间
CREATE INDEX idx_events_data_path ON events USING gin (data jsonb_path_ops);

-- 高频字段生成列 + B-Tree（推荐）
ALTER TABLE events
  ADD COLUMN user_id bigint  GENERATED ALWAYS AS ((data->>'user_id')::bigint) STORED,
  ADD COLUMN status  text    GENERATED ALWAYS AS (data->>'status') STORED,
  ADD COLUMN price   numeric GENERATED ALWAYS AS ((data->>'price')::numeric) STORED;

CREATE INDEX idx_events_user_id ON events (user_id);
CREATE INDEX idx_events_status  ON events (status);
CREATE INDEX idx_events_price   ON events (price);
CREATE INDEX idx_events_created ON events (created_at);

查询示例

-- 包含查询（走 jsonb_path_ops 或通用 GIN）
SELECT * FROM events WHERE data @> '{"status":"ok"}';

-- 键存在
SELECT * FROM events WHERE data ? 'tags';

-- 抽列后的等值与范围（走 B-Tree）
SELECT * FROM events WHERE user_id = 42 AND price BETWEEN 10 AND 20;

-- JSONPath（函数形式）
SELECT * FROM events
WHERE jsonb_path_exists(data, '$.items[*] ? (@.qty > 3)');

实践要点

写多读少：控制 GIN 数量与大小（部分索引 WHERE 条件），监控 gin_pending_list_limit 与 autovacuum
排序/范围/分组：优先“抽列/生成列 + B-Tree”，再用 GIN 覆盖长尾查询
语义明确：@> 结构匹配需与文档层级一致，避免多余层级导致索引效果变差

5. 分区、并行与大规模数据实践

分区（声明式）
- RANGE/LIST/HASH，支持默认分区；规划与执行期分区裁剪（pruning）
- 在线 ATTACH/DETACH，便于冷热分层与归档；按时间/租户维度常见
并行查询与批处理
- 并行顺序/索引/位图扫描、哈希连接与聚合
- 参数：max_parallel_workers、max_parallel_workers_per_gather 等
时间序列与超大表
- BRIN：块级摘要索引，适合时间相关/追加写；pages_per_range 调优
- 覆盖索引（INCLUDE）减少热点回表；部分索引限定“最近窗口”降低写放大
- TimescaleDB（扩展）：自动分区、连续聚合、压缩（按需选用）

6. 可观测性、调优与日常运维

诊断与画像
- pg_stat_statements：找出最耗资源的规范化 SQL
- auto_explain：捕获慢查询计划
- pg_stat_* 与 pg_statio_*：锁、I/O、缓存命中、真空进度
- EXPLAIN ANALYZE BUFFERS WAL：端到端分析
参数与资源
- shared_buffers、work_mem、effective_cache_size、maintenance_work_mem
- max_wal_size、checkpoint_timeout、wal_compression
- autovacuum_* 与 freeze 策略，避免 XID 回卷
维护
- VACUUM（含 FULL）、ANALYZE、REINDEX [CONCURRENTLY]、CLUSTER
- 物理备份 + WAL 归档 + PITR；逻辑备份 pg_dump/pg_restore

7. 安全与多租户

行级安全（RLS）
- 基于策略的细粒度访问控制（PERMISSIVE/RESTRICTIVE）
审计与合规
- pgaudit、log_statement、log_min_duration_statement
身份验证
- SCRAM-SHA-256、TLS；外部目录（LDAP、GSSAPI）集成

8. 典型“从零到一”清单

-- 1) 基础观测
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 2) 业务相关扩展（示例）
CREATE EXTENSION IF NOT EXISTS pg_trgm;      -- 模糊检索
CREATE EXTENSION IF NOT EXISTS hstore;       -- 轻量KV
-- CREATE EXTENSION postgis;                 -- 地理空间（安装后）

-- 3) JSONB 索引与抽列
CREATE INDEX IF NOT EXISTS idx_e_data ON events USING gin (data jsonb_path_ops);
ALTER TABLE events ADD COLUMN uid bigint GENERATED ALWAYS AS ((data->>'uid')::bigint) STORED;
CREATE INDEX IF NOT EXISTS idx_e_uid ON events (uid);

-- 4) 分区/热点优化（示意）
-- CREATE TABLE t (...) PARTITION BY RANGE (ts);
-- 针对最近30天数据的部分索引
-- CREATE INDEX idx_t_recent ON t (ts DESC) WHERE ts >= now() - interval '30 days';

9. 总结与实践建议

架构即能力：PG 的“可扩展接口 + 插件生态”让它既能做 OLTP，又能覆盖搜索、GIS、时序、向量等多样场景
索引优先级：等值/范围/排序优先 B-Tree；包含/键存在用 GIN；区间/空间用 GiST；时间相关大表增配 BRIN
JSONB 使用准则：高频键抽列或生成列；结构匹配用 @> + GIN；复杂筛选用 JSONPath（函数）与表达式索引
可靠性基线：WAL + Checkpoint + PITR；物理复制做 HA，逻辑复制做升级与分发
可观测与调优：开启 pg_stat_statements，建立慢 SQL 画像；结合 EXPLAIN 与统计信息迭代索引与写读模式
谨慎引入扩展：确认兼容性与运维能力，明确备份/恢复/升级路径

若需针对某一业务场景（如高并发写、跨区部署、合规要求）制定参数与索引/分区策略，可进一步提供数据规模、访问模式与 SLA 细节进行定制化建议。