PostgreSQL

PostgreSQL 是最先进的开源对象关系数据库，以其丰富的数据类型、强大的扩展性、ACID 完整支持著称。理解 PostgreSQL 的架构差异，有助于在 MySQL 和 PostgreSQL 之间做出合适的选择。

进程架构

MySQL 采用线程模型，每个连接对应一个线程；PostgreSQL 采用进程模型，每个连接对应一个服务进程。进程模型的优势是稳定性强，一个进程崩溃不影响其他进程；劣势是进程创建和切换开销大，连接数受限。PostgreSQL 使用连接池（如 PgBouncer）缓解这个问题。

PostgreSQL 的后台进程包括：Postmaster（主进程，监听连接请求）、Logger（日志进程）、Checkpointer（检查点进程）、WAL Writer（WAL 写入进程）、Autovacuum（自动清理进程）、Archiver（归档进程）、Stats Collector（统计信息收集进程）。

存储模型

堆存储与 TOAST

PostgreSQL 使用堆存储（Heap），与 InnoDB 的索引组织表不同。堆存储中，数据和索引分离，索引存储 (ctid, key)，ctid 是行在堆中的物理位置（块号和块内偏移）。堆存储的优势是更新操作不改变主键索引，劣势是二级索引需要回表。

TOAST（The Oversized-Attribute Storage Technique）是 PostgreSQL 处理大字段的机制。一行数据不能超过一个页面（默认 8KB），超过的部分会被压缩和切片，存储到独立的 TOAST 表。原表只保留 TOAST 表的指针和部分数据。TOAST 对用户透明，但理解 TOAST 有助于设计大字段的表。

MVCC 实现

PostgreSQL 的 MVCC 实现与 InnoDB 不同。InnoDB 在行中隐藏事务 ID 和回滚指针，PostgreSQL 在每行记录头中插入 (xmin, xmax) 两个隐藏字段，分别表示创建和删除该行的事务 ID。更新操作不是原地修改，而是标记旧行的 xmax，插入新行，旧行变为 dead tuple。

PostgreSQL 的版本分散在堆中，通过 (xmin, xmax) 识别版本。查找时扫描所有版本，根据事务状态判断可见性。这种设计不需要 undo log，但会导致表中积累大量 dead tuple，需要 VACUUM 清理。

VACUUM 机制

由于 MVCC 的实现方式，PostgreSQL 会产生大量 dead tuple（删除或更新的旧版本）。dead tuple 占用空间但不提供有用信息，需要清理。VACUUM 进程负责回收空间、更新事务 ID 耗尽（wraparound）、收集统计信息。

VACUUM：标记 dead tuple 为可复用，不释放磁盘空间。可以并发执行，不阻塞读写。

VACUUM FULL：重写表，释放磁盘空间，需要 ACCESS EXCLUSIVE 锁，阻塞所有操作。通常先用 VACUUM，空间仍不足时才用 VACUUM FULL。

Autovacuum：后台自动执行 VACUUM，根据 dead tuple 的数量和事务年龄触发。配置 autovacuum 相关参数调整触发阈值和频率。

事务 ID 是 32 位，最大约 40 亿，耗尽后会回绕，导致数据丢失。PostgreSQL 强制 VACUUM 防止事务 ID 耗尽，这被称为 "freeze"。

WAL 与检查点

PostgreSQL 的 WAL 与 InnoDB 类似，任何数据页修改前先写日志。WAL 服务器进程负责将 WAL 缓冲区刷盘。检查点进程定期将所有脏页刷盘，并更新检查点位置，检查点前的 WAL 可以丢弃或归档。

WAL 可以用于时间点恢复（PITR）：备份归档 WAL，恢复到指定时间点。这是 PostgreSQL 的高可用方案之一。

数据类型与扩展

丰富的数据类型

PostgreSQL 支持丰富的数据类型：数组类型（int[]、text[]）、范围类型（int4range、tsrange）、JSON/JSONB（JSONB 是二进制 JSON，查询更快）、网络地址类型（inet、cidr）、全文检索类型（tsvector）。

索引类型

PostgreSQL 支持多种索引：B-tree（默认，支持范围查询）、Hash（等值查询）、GiST（通用搜索树，支持几何、全文检索）、SP-GiST（空间分区树）、GIN（倒排索引，适合数组、JSONB、全文检索）、BRIN（块范围索引，适合有序的大表）。

扩展系统

PostgreSQL 的扩展系统是其独特优势。扩展是额外的功能模块，可以动态加载。常见扩展：PostGIS（地理空间数据）、pgvector（向量相似度搜索）、pgcrypto（加密函数）、hstore（键值存储）、pg_stat_statements（查询统计）。

CREATE EXTENSION 安装扩展，扩展添加新的函数、数据类型、索引、操作符。

查询处理

PostgreSQL 的查询处理流程与 MySQL 类似：解析 → 分析 → 重写 → 规划 → 执行。查询重写器（Query Rewriter）是 PostgreSQL 的特色，负责视图展开、规则应用、子查询展开。

优化器支持 CBO 和 RBO，统计信息包括表的行数、列的唯一值数量、MCV（Most Common Values）、直方图。EXPLAIN ANALYZE 不仅显示执行计划，还实际执行查询并显示真实的行数和时间。

PostgreSQL 支持：窗口函数、WITH 子句（CTE，公用表表达式）、递归查询（WITH RECURSIVE）、LATERAL 子查询（横向连接，子查询引用外层列）、TABLESAMPLE（表采样）。

复制与高可用

PostgreSQL 支持多种复制方式：流复制（Streaming Replication，基于 WAL 的主从复制）、逻辑复制（Logical Replication，基于逻辑解码的复制，可以选择性复制表）、同步复制与异步复制。

高可用方案：主从复制 + 自动故障转移（Patroni、repmgr）、时间点恢复（PITR）、第三方工具（Stolon、CloudNativePG）。

PostgreSQL 是功能最全面的开源数据库，适合需要复杂查询、数据类型丰富、扩展性强的场景。但进程模型和 MVCC 实现带来了更高的内存和 CPU 开销，在极简场景下可能不如 MySQL 高效。