PostgreSQL 特性全景与选型指南本文将按“定位与哲学 → 核心特性 → 与 MySQL 对比 → 场景化选型 →

概述

系列定位说明：本文是 PostgreSQL 深度内核与工程实战系列 的总览篇。在深入源码与内部机制之前，本文旨在为读者建立 PostgreSQL 设计哲学、核心特性、与 MySQL 差异以及选型边界的全局认知。后续篇章将依次覆盖环境搭建、数据类型、索引、MVCC、查询优化、高级查询、JSON/全文搜索、分区与复制、高可用、性能调优、安全机制、扩展与 FDW、反模式排查及实战项目。

总结性引言：在关系型数据库领域，MySQL 凭借互联网浪潮成为使用最广泛的开源数据库，而 PostgreSQL 则凭借其学术级严谨、丰富特性与强大扩展能力，被公认为“最先进的开源关系型数据库”。PostgreSQL 的 MVCC 无锁并发、五大索引体系、原生 JSONB 支持、窗口函数与递归 CTE、高度可扩展的插件和 FDW 体系，使其在复杂分析、GIS、金融等场景中具有不可替代的优势。本文将按“定位与哲学 → 核心特性 → 与 MySQL 对比 → 场景化选型 → 生态与 Spring 整合”的路径，系统呈现 PostgreSQL 的技术全景。

核心要点：

核心定位：先进的开源关系型数据库，兼顾标准兼容与扩展能力。
六大特性：MVCC 无锁并发、五大索引体系、丰富原生数据类型、高度可扩展、完整事务支持、物理+逻辑复制。
与 MySQL 对比：从架构、MVCC、索引、复杂查询、JSON、扩展、许可证等维度深入对比。
场景化选型：通过复杂分析、GIS、金融、Web 应用、混合负载等场景给出明确的选型建议。
生态全景：核心工具链、高可用、监控、连接池。
Spring 生态整合全景：spring-data-jdbc/jpa、MyBatis、jOOQ、R2DBC 的定位差异与适用场景。

文章组织架构图

flowchart TB
    A[1. 定位与设计哲学] --> B[2. MVCC 与无锁并发]
    A --> C[3. 五大索引体系]
    A --> D[4. 丰富原生数据类型]
    A --> E[5. 插件与FDW扩展]
    A --> F[6. 完整事务支持]
    A --> G[7. 物理与逻辑复制]
    B --> H[8. 与 MySQL 全方位深度对比]
    C --> H
    D --> H
    E --> H
    F --> H
    G --> H
    H --> I[9. 场景化技术选型决策框架]
    I --> J[10. PostgreSQL 生态全景]
    J --> K[11. Spring 生态整合全景]
    K --> L[12. 面试高频专题]

架构图说明：

总览说明：全文 12 个模块从 PostgreSQL 的定位出发，依次展开六大核心特性，然后进入与 MySQL 的对比和场景化选型，最后以生态全景、Spring 整合和面试题收尾。
逐模块说明：模块 1 建立 PostgreSQL 的设计哲学根基；模块 2-7 逐一剖析其核心技术优势；模块 8 揭示两大数据库的差异与适用边界；模块 9 提供落地的决策框架；模块 10-11 展示生态与开发整合；模块 12 面试巩固。
关键结论：PostgreSQL 的核心竞争力在于 MVCC 无锁并发、五大索引体系、丰富的原生数据类型与高度可扩展的插件体系。理解这些特性以及与 MySQL 的差异，是做出正确选型的前提。

1. PostgreSQL 的定位与设计哲学

1.1 从开源数据库演进看 PostgreSQL

PostgreSQL 的历史可追溯到 1986 年加州大学伯克利分校的 POSTGRES 项目，其设计目标是探索关系模型与对象模型的融合。1996 年更名为 PostgreSQL，以强调其 SQL 标准兼容性。经过 30 余年的学院与社区共同打磨，PostgreSQL 16.x 已然成为功能最完备、标准兼容度最高的开源关系型数据库。

相比之下，MySQL 诞生于 1995 年，最初由 Michael Widenius 为满足 Web 应用的快速读写需求而设计，以简单、快速、易用为核心目标。两大数据库走上的不同演进路径深刻影响了各自的架构设计。

1.2 核心设计取舍

多进程架构：PostgreSQL 采用经典的“每连接一进程”模型。每个客户端连接都会 fork 一个独立的 Backend 进程，进程间通过共享内存（Shared Memory）和信号量进行协调。这种设计带来了天然的内存隔离和崩溃隔离：单个连接的进程崩溃不会影响主进程和其他连接。缺点在于上下文切换开销较高，连接数暴增时系统负担大。因此，PG 生态中连接池（如 PgBouncer）几乎是生产部署的标配。

MySQL 8.x 默认使用多线程模型，一个进程管理多个连接线程，线程间共享内存。线程切换成本低，适合高连接数的 OLTP 场景，但线程间内存访问需要复杂的锁机制保护，且一个线程的异常可能污染整个进程空间。

学术级严谨与标准兼容：PostgreSQL 始终坚持优先实现 SQL 标准，对 ACID、复杂查询、约束、触发器、窗口函数、递归 CTE 等提供完整支持。其事务隔离级别实现了真正的 SERIALIZABLE（基于 Serializable Snapshot Isolation），而非 MySQL 的间隙锁近似方案。

“功能完备、标准兼容” vs “快速、够用”：MySQL 在互联网快速迭代中形成了“够用就好”的哲学，早期甚至不支持事务和子查询，通过存储引擎插件化实现了灵活性。PostgreSQL 则追求内核功能的完备性，提供丰富的原生数据类型、多种索引结构、可扩展的运营商自定义能力。这种差异使得 PostgreSQL 在复杂业务场景中具有强大的表达力，MySQL 则在简单读写场景中保持轻便高效。

flowchart TB
    subgraph PG[PostgreSQL 核心架构 - 多进程模型]
        PM[Postmaster 主进程] -->|fork| BE1[Backend 进程 1]
        PM -->|fork| BE2[Backend 进程 2]
        PM -->|fork| BEN[Backend 进程 N]
        SM[共享内存 Shared Memory] --> BE1
        SM --> BE2
        SM --> BEN
        BG[辅助进程] -->|WAL Writer| WW[WAL Writer 进程]
        BG -->|Checkpointer| CP[Checkpointer 进程]
        BG -->|Autovacuum Launcher| AV[Autovacuum Worker 进程群]
        BG -->|Stats Collector| SC[统计信息收集进程]
    end

图表主旨概括：该图展示 PostgreSQL 的多进程架构，主进程 Postmaster 负责监听请求并 fork 后端进程，所有进程通过共享内存和辅助进程协调，形成稳定、隔离的运行体系。

逐层/逐元素分解：

Postmaster 主进程：监听端口，为每个新连接创建 Backend 进程，自身不处理查询。
Backend 进程：执行 SQL 查询，每个连接拥有独立内存空间，崩溃不会扩散。
共享内存：存放缓冲区（shared_buffers）、锁表、事务状态等信息，是进程间数据交换的核心介质。
辅助进程：WAL Writer 负责预写日志刷盘；Checkpointer 管理检查点；Autovacuum 自动回收死元组；Stats Collector 汇总统计信息。

设计原理映射：多进程架构借鉴了 UNIX 哲学中的“隔离优于共享”，每个服务单元独立运行，降低耦合，充分利用现代多核 CPU 的并行能力。通过共享内存实现受控的数据交换，而非无限制的线程共享，符合数据库对稳定性的严苛要求。

工程联系与关键结论：生产环境中必须配置连接池（如 PgBouncer）以限制并发 Backend 进程数，防止进程过多导致上下文切换风暴。同时，合理设置 shared_buffers 大小（通常为物理内存的 25%~40%）可显著提升缓存命中率。

2. 核心特性一：MVCC 与无锁并发

2.1 PostgreSQL 的 MVCC 实现：元组版本链

PostgreSQL 的多版本并发控制（MVCC）核心在于：更新或删除一行时，不直接修改原有数据，而是在表中插入一个新版本（新的元组），并标记旧元组的 xmax（失效事务 ID）。每个元组头部包含 xmin（创建该元组的事务 ID）和 xmax。读取操作根据事务快照决定哪些元组可见，无需加锁，从而实现读写不阻塞。

-- 查看隐藏系统列（需在 psql 中显式查询）
SELECT xmin, xmax, * FROM accounts WHERE id = 1;
-- 结果示例：
--  xmin | xmax | id | balance
-- ------+------+----+---------
--   100 |    0 |  1 |   500.00
-- xmax=0 表示该元组当前有效，未被删除

当事务 101 更新余额时，PG 执行以下操作：

将原元组的 xmax 设为 101。
插入新元组，xmin = 101，xmax = 0，余额为新值。

sequenceDiagram
    participant T100 as 事务100 (已提交)
    participant T101 as 事务101 (更新中)
    participant Table as 表 Heap Page
    Note over Table: 初始: 元组A (xmin=100, xmax=0, balance=500)
    T101->>Table: UPDATE accounts SET balance=600 WHERE id=1
    Table-->>Table: 标记元组A: xmax=101
    Table-->>Table: 插入元组B: xmin=101, xmax=0, balance=600
    Note over T100,T101: 并发读: 事务100快照看到元组A，事务101看到元组B

图表主旨概括：该时序图演示 PostgreSQL 更新操作产生的元组版本链，读事务根据快照选择合适版本，实现无锁读。

逐层/逐元素分解：

事务快照：每个事务开始时记录活跃事务列表，据此判断 xmin、xmax 是否可见。
版本链：同一行的多个历史版本存储在同一表空间（Heap）中，未回收的旧版本导致表膨胀。
VACUUM：后台进程标记不再需要的旧元组为可重用空间，防止表无限膨胀。

设计原理映射：PG 将旧版本原地保留，以空间换时间，避免行级锁竞争，代价是需要定期 VACUUM 清理。这不同于 MySQL InnoDB 的 Undo Log 机制。

工程联系与关键结论：必须合理配置 Autovacuum 参数（autovacuum_vacuum_scale_factor, autovacuum_vacuum_threshold），防止更新频繁的大表出现严重膨胀和性能衰减。监控表膨胀率是 DBA 的核心日常工作。

2.2 与 MySQL InnoDB Undo Log 的对比

MySQL InnoDB 的 MVCC 通过 Undo Log（回滚段） 实现：聚簇索引中总是保存最新版本的数据，旧版本被移入 Undo Log，读取时通过回滚指针构建历史版本。PostgreSQL 则是旧版本与新版本同存于表数据页中。

这种差异导致：

表膨胀：PG 的死元组需 VACUUM 回收，MySQL 的 Undo 记录由 Purge 线程清理。PG 的表膨胀问题比 MySQL 更为突出，但也因此获得了更高效的更新回滚（直接标记 xmax 即可）。
回滚速度：PG 事务回滚几乎瞬间完成，只需将事务标记为 Aborted；MySQL 则需要应用 Undo Log 执行逻辑回滚，大事务回滚可能极为缓慢。
版本链遍历：PG 仅在表内链式扫描旧版本；MySQL 需要沿 Undo 链回溯，在某些复杂查询中可能引发额外 I/O。

在隔离级别上，PG 的 READ COMMITTED 和 REPEATABLE READ 都依赖快照，区别仅在于快照获取时机。MySQL 的 REPEATABLE READ 则结合间隙锁提供部分幻读保护。详细对比见模块 8。

3. 核心特性二：五大索引体系

PostgreSQL 拥有五种内置索引类型，每种针对不同数据类型和查询模式优化：

索引类型	内部结构	适用场景	MySQL 对应
B-Tree	平衡树	通用等值、范围查询，排序	B+Tree（支持聚簇）
Hash	哈希表	仅等值查询，无法排序	仅 Memory 引擎支持 Hash 索引
GiST	广义搜索树	几何数据、全文搜索、自定义类型	无原生支持
GIN	广义倒排索引	数组、JSONB、全文搜索	无原生支持，MySQL 全文索引内部类似
BRIN	块范围索引	超大型时序表，物理位置相关性强的列	无原生支持

PG 无聚集索引概念：MySQL InnoDB 的表数据按主键 B+Tree 聚簇存储，二级索引存储主键值回表。PostgreSQL 的索引全部是二级索引，索引元组中存储的是数据行的物理位置（TID），没有回表开销，但也没有按主键物理排序的特性。这种设计使得 PostgreSQL 的随机主键插入不会导致页分裂和频繁的平衡开销，但范围扫描可能不如聚簇索引紧凑。

flowchart LR
    subgraph BTree[B-Tree]
        BT[通用均衡] --> BT1[等值/范围查询]
        BT --> BT2[排序/唯一约束]
    end
    subgraph Hash[Hash]
        HA[精准匹配] --> HA1[等值查询]
    end
    subgraph GiST[GiST]
        GI[广义搜索树] --> GI1[几何/PostGIS]
        GI --> GI2[全文搜索辅助]
    end
    subgraph GIN[GIN]
        GN[倒排索引] --> GN1[JSONB/数组]
        GN --> GN2[全文搜索加速]
    end
    subgraph BRIN[BRIN]
        BR[块范围] --> BR1[时序数据/大表扫描]
    end
    BT --> Choose{查询模式判断}
    HA --> Choose
    GI --> Choose
    GN --> Choose
    BR --> Choose

图表主旨概括：该图展示五类索引的定位与决策路径，从查询模式出发映射到最优索引选择。

逐层/逐元素分解：

B-Tree：通用索引，几乎所有需要排序和范围查询的列都应优先考虑。
Hash：仅适用于 = 操作符，不支持范围查询和排序，适合高基数列的精确匹配，但通常 B-Tree 也能胜任。
GiST：可扩展的索引框架，允许开发者自定义数据类型和运算符，是 PostGIS 空间索引的基石。
GIN：倒排索引，适合复合数据类型的包含查询（如 JSONB @> 运算符），常与 jsonb_path_ops 结合使用。
BRIN：为极大规模、物理存储顺序与逻辑顺序相关联的数据设计，索引体积极小。

设计原理映射：PG 通过操作符类和索引访问方法抽象，让每一种索引可以支持多种数据类型，只需实现对应操作符。这种可扩展性远超 MySQL 的单一 B+Tree 体系，使 PG 在搜索、空间、时序等细分领域具备专业级性能。

工程联系与关键结论：在选择索引时，切勿对所有列默认使用 B-Tree。对于 JSONB 查询，GIN 索引可降低 90% 以上的查询时间；对于亿级时序表，BRIN 索引可实现千分之一的索引体积，极大节省存储。使用 EXPLAIN 验证索引策略是开发者的本能。

示例：

-- 为 JSONB 列创建 GIN 索引
CREATE INDEX idx_events_payload ON events USING GIN (payload jsonb_path_ops);

-- 查询包含特定键值的事件
SELECT * FROM events WHERE payload @> '{"event_type": "click"}';

4. 核心特性三：丰富原生数据类型

PostgreSQL 提供丰富且实用的原生数据类型，使开发者能用数据库原生功能表达业务语义，避免在应用层拼凑逻辑。

4.1 数组类型

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    tags TEXT[]  -- 字符串数组
);
INSERT INTO products (name, tags) VALUES ('Laptop', ARRAY['electronics', 'portable']);
SELECT * FROM products WHERE 'electronics' = ANY(tags);

数组类型配合 GIN 索引可实现高效的标签匹配，优于 MySQL 中的逗号分隔字符串或关联表，减少 JOIN，提升读取性能。

4.2 JSON 与 JSONB

JSONB 是二进制存储的 JSON，支持索引、高效查询和数据去重。与之相对，json 类型仅保留原始文本，查询时需重新解析。

CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY,
    profile JSONB
);
INSERT INTO user_profiles VALUES (1, '{"name": "Alice", "preferences": {"theme": "dark"}}');
-- 查询所有使用暗色主题的用户，使用 GIN 索引加速
CREATE INDEX idx_profile ON user_profiles USING GIN (profile);
SELECT * FROM user_profiles WHERE profile @> '{"preferences": {"theme": "dark"}}';

相较 MySQL 的 JSON 类型（从 5.7 引入），PG JSONB 的优势在于：

二进制存储，无需重复解析。
完善的 GIN 索引支持，能高效执行 @>、? 等包含和存在操作符。
支持 JSON Path 查询（SQL/JSON 标准），表达能力更强。
MySQL 的 JSON 基本只能建立虚拟列索引，对嵌套复杂查询的优化有限。

4.3 网络地址类型（INET/CIDR）

CREATE TABLE access_logs (
    ip INET,
    country TEXT
);
-- 存储 IPv4/IPv6，支持子网包含查询
SELECT * FROM access_logs WHERE ip << '192.168.1.0/24';

网络地址类型内置了子网运算函数，非常适合网络管理、IP 黑白名单等场景，避免了在应用层进行字符串分拆运算。

4.4 UUID 与 Range 类型

UUID 类型存储 36 字符的通用唯一标识符，占用空间仅 16 字节，比存为 VARCHAR(36) 更高效。Range 类型（int4range, tsrange 等）可表示数值或时间范围，并支持范围包含、重叠等操作，适合预订系统、价格有效期等逻辑。

CREATE TABLE room_bookings (
    room_id INT,
    during TSRANGE,
    EXCLUDE USING GIST (room_id WITH =, during WITH &&) -- 防止重叠预订
);

5. 核心特性四：高度可扩展的插件与 FDW

5.1 Extension 机制

PostgreSQL 的 CREATE EXTENSION 允许以插件形式加载代码，扩展内核功能。插件可以定义数据类型、索引访问方法、运算符、后台 Worker 等。常用核心扩展：

pg_stat_statements：追踪 SQL 执行统计，是性能调优的必备扩展。
PostGIS：将 PG 变为专业 GIS 空间数据库。
pg_trgm：三元组相似度索引，用于模糊搜索。
uuid-ossp：生成 UUID。
postgres_fdw：联邦查询。

CREATE EXTENSION pg_stat_statements;
SELECT query, calls, mean_exec_time FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;

Extension 的加载机制依赖动态库（.so），插件需放置于 shared_preload_libraries 或在会话级加载。这种机制与 MySQL 的插件式存储引擎有本质区别：MySQL 的插件主要用于替换数据存储和访问方式（InnoDB, MyISAM 等），而 PG 的扩展可深入到执行器、优化器、索引等几乎任何层次。

5.2 FDW（Foreign Data Wrapper）与数据联邦

FDW 实现了 SQL/MED 标准，使 PostgreSQL 可以像查询本地表一样查询外部数据源（其他 PG 实例、MySQL、文件、MongoDB 等）。

CREATE EXTENSION postgres_fdw;
CREATE SERVER remote_pg FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '192.168.1.100', dbname 'analytics');
CREATE USER MAPPING FOR current_user SERVER remote_pg OPTIONS (user 'reporter', password 'secret');
IMPORT FOREIGN SCHEMA public FROM SERVER remote_pg INTO public;
SELECT * FROM remote_sales WHERE amount > 1000; -- 透明查询远程表

PostgreSQL 16 进一步增强了 FDW 的并行扫描和聚合下推能力，可以在外部数据源直接执行聚合，减少网络传输。此能力在数据中台、微服务架构的数据聚合层中极具价值，是 MySQL 原生不具备的联邦能力（MySQL 的 Federated 引擎功能弱且性能差）。

6. 核心特性五：完整事务支持

PostgreSQL 全面实现 ACID，支持四种隔离级别。与 MySQL 8.x 的重要区别在于：

默认隔离级别：PG 默认 READ COMMITTED，MySQL 默认 REPEATABLE READ。PG 的 REPEATABLE READ 是真正的快照隔离，不会锁住间隙（无间隙锁），因此无法完全防止写倾斜等幻读，但提供了 SERIALIZABLE 借助 Serializable Snapshot Isolation (SSI) 实现真正的可串行化，并能检测冲突自动回滚其中一个事务。
SSI 实现：PG 通过跟踪序列化冲突图（Snapshot Isolation 的扩展），在提交时检查是否存在违反可串行化的依赖环。若检测到，则回滚事务并报 serialization failure。应用层只需重试。MySQL 的 SERIALIZABLE 本质上是 REPEATABLE READ 加上 SELECT ... FOR SHARE 锁定所有选中行，性能较差且无法保证完全的串行化效果。
谓词锁：PG 的 SSI 使用 SIREAD 锁（谓词锁）来检测冲突，代价比物理锁低。

-- 启动可串行化事务
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT SUM(balance) FROM accounts WHERE branch = 'NY';
-- ... 业务逻辑
COMMIT; -- 若检测到冲突，报错：ERROR: could not serialize access

对于金融、账务等需要严格数据一致性的系统，PG 的 SERIALIZABLE 提供了无需应用层实现分布式锁或悲观锁的高安全方案。

7. 核心特性六：物理与逻辑复制

7.1 物理流复制

基于 WAL（Write Ahead Log）的流复制是 PG 原生的高可用基础。主库的 WAL Sender 进程不断发送 WAL 记录到备库的 WAL Receiver 进程，备库应用 WAL 保持与主库一致。支持同步复制和异步复制，通过 synchronous_commit 参数控制。

关键配置：

# postgresql.conf (主库)
wal_level = replica
max_wal_senders = 5
synchronous_commit = on   # 或 remote_apply 等

流复制提供的是物理日志流，因此要求主备操作系统和数据库主版本一致，不能跨版本，但性能极高、延迟低。

7.2 逻辑复制

逻辑复制从 10 版本引入，通过发布（Publication）和订阅（Subscription）模型传输逻辑变化（行级更改），与物理格式解耦。优点包括：

支持跨版本升级（例如 14 → 16）。
支持仅复制部分表或仅发布 INSERT/UPDATE。
可在不同操作系统间复制。

-- 主库创建发布
CREATE PUBLICATION my_pub FOR TABLE users, orders;
-- 备库创建订阅
CREATE SUBSCRIPTION my_sub CONNECTION 'host=master dbname=prod' PUBLICATION my_pub;

当需要从 PG 将数据变更实时同步到数据仓库（如 ClickHouse）或构建 CQRS 架构的命令查询分离时，逻辑复制是绝佳工具。这与 MySQL 的默认基于行或语句的复制类似，但 PG 的逻辑复制是原生发布/订阅模型，不需要配置复杂的 binlog 解析。

对比 MySQL GTID 复制：MySQL 的组复制（Group Replication）支持多主，PG 的逻辑复制目前仅支持单向（有第三方插件支持多主）。但 PG 的插件 pglogical 和 Patroni 生态在故障切换方面更加灵活。

8. 与 MySQL 的全方位深度对比

8.1 核心差异总览

维度	PostgreSQL 16	MySQL 8.0
架构模型	多进程，连接 fork 独立进程	多线程，单进程多线程
MVCC 实现	元组版本链，原地多版本，需 VACUUM	Undo Log 回滚段，需 Purge 线程
索引	B-Tree, Hash, GiST, GIN, BRIN	B+Tree，仅个别引擎支持 Hash/全文索引
数据类型	数组，JSONB，UUID，INET，Range 等丰富原生	JSON（仅文本存储，部分优化），空间索引等
复杂查询	窗口函数、递归 CTE、`LATERAL` 子查询、物化视图	支持窗口函数、递归 CTE，但优化度与成熟度弱
扩展能力	Extension 和 FDW 极度开放，可修改内核行为	插件存储引擎为主，功能有限
事务隔离	默认 RC，支持全四种，SSI 真正可串行化	默认 RR，间隙锁实现部分串行化
复制	物理流复制 + 逻辑复制（发布/订阅）	异步/半同步复制，组复制（MGR）多主
JSON	JSONB 二进制、GIN 索引、SQL/JSON Path	JSON 优化少，虚拟列索引
许可证	PostgreSQL License（类似 MIT）	GPL，企业使用有潜在合规约束
社区与驱动	统一社区，节奏稳健	Oracle 主导，方向偏云化

8.2 关键差异深入剖析

MVCC 的本质区别：PG 将历史版本保存在数据页，当更新频繁时表膨胀剧烈，必须依赖 Autovacuum 回收死元组。MySQL 通过 Purge 线程在后台清理 Undo，但表膨胀较轻。PG 的回滚几乎无代价，大事务友好；MySQL 大事务回滚可能耗时比事务本身还长。

索引体系的能力边界：PG 的 GiST 和 GIN 将全文搜索、数组包含、空间查询的性能推到专业搜索引擎级别，而 MySQL 实现类似功能需依赖外部工具（如 Elasticsearch + Geo 索引）。BRIN 索引为时序和日志场景带来颠覆性的存储效率，MySQL 无等效方案。

JSON 处理的性能：JSONB 的二进制存储比 MySQL 的 JSON 文本型性能高出一个量级，尤其是在频繁读取和嵌套查询时。PG 支持索引 JSONB 中的任意路径，MySQL 仅能对虚拟列部分索引。

扩展生态的开放性：PostgreSQL 的 Extension 框架允许第三方扩展提供新的聚合函数、数据类型、权限钩子，甚至自定义表访问方法（Table AM）。举例来说，TimescaleDB 作为时序扩展直接在 PG 内核上改造成超表，而 MySQL 只能通过存储引擎或外部中间件实现，架构兼容性差。

flowchart LR
    subgraph PG[PostgreSQL]
        P_MVCC[元组版本链 + VACUUM]
        P_INDEX[GiST/GIN/BRIN 专业索引]
        P_JSON[JSONB + GIN 索引]
        P_EXT[Extension / FDW 联邦]
        P_SERIAL[SSI 真串行化]
    end
    subgraph MySQL[MySQL]
        M_MVCC[Undo Log + Purge]
        M_INDEX[B+Tree 为主]
        M_JSON[JSON 文本存储]
        M_EXT[插件式引擎]
        M_SERIAL[间隙锁近似串行化]
    end
    P_MVCC -- 差异 --> M_MVCC
    P_INDEX -- 差异 --> M_INDEX
    P_JSON -- 差异 --> M_JSON
    P_EXT -- 差异 --> M_EXT
    P_SERIAL -- 差异 --> M_SERIAL

图表主旨概括：横向对比 PostgreSQL 与 MySQL 在五大关键能力上的核心实现差异，凸显设计哲学的不同。

逐层/逐元素分解：每个维度的差异都映射到实际业务表现，例如 MVCC 差异决定了对更新密集场景的优化方向，索引差异决定了搜索和时序场景的技术选型。

设计原理映射：PG 倾向于在数据库内部提供完备的解决方案，MySQL 倾向于提供精简内核并通过外部工具组合。这源自 PG 的学术起源与 MySQL 的互联网实用主义。

工程联系与关键结论：若项目重度依赖复杂分析、JSON 搜索、地理空间、数据联邦，PostgreSQL 是生产力碾压的选择；若以简单 CRUD、高并发键值查找为主，且团队已深度绑定 MySQL 生态，则 MySQL 的生产成本更低。

9. 场景化技术选型决策框架

9.1 明确推荐 PostgreSQL 的场景

复杂分析与报表：窗口函数、递归 CTE、LATERAL join、物化视图、并行查询使得复杂 SQL 表现卓越。
GIS 地理信息系统：PostGIS 扩展是行业标准，提供 1000+ 空间函数、栅格分析和拓扑支持，MySQL 8 的空间能力仅限基础 OGC 标准。
金融、医疗等高一致性要求：SSI 可串行化隔离级别使业务逻辑无需引入复杂的锁协调。
混合负载 (OLTP + OLAP)：通过分区表、并行查询、FDW 联邦查询，一套数据库即可支撑交易和分析。
需要丰富数据类型与自定义扩展：网络地址、JSONB、数组、Range 等类型可减少应用层逻辑，提升开发效率。
多模态或搜索需求：内置全文搜索、JSON 搜索、模糊匹配（pg_trgm）可替代 Elasticsearch 的简单使用场景。

9.2 可优先考虑 MySQL 的场景

简单 CRUD 为主的 Web 应用：如内容管理系统、博客，PG 的优势无法发挥，MySQL 更轻量。
团队现有技术栈深度绑定 MySQL：迁移成本过高，需考虑学习曲线和踩坑周期。
只读/读多写少场景：MySQL 的读扩展能力成熟，PG 同样优秀，但现有运维工具链更熟。
需要多主复制：MySQL 的 Group Replication 提供原生多主，PG 的逻辑复制多主需借助 pgEdge 或 BDR 等商业/高级扩展。

9.3 迁移考量

从 MySQL 迁移到 PostgreSQL 需重点关注：

数据类型映射：TINYINT → SMALLINT，DATETIME → TIMESTAMP，TEXT 与 VARCHAR 类似。
SQL 语法差异：LIMIT & OFFSET 相同，但字符串拼接用 ||，正则用 ~，布尔值为 TRUE/FALSE。
存储过程：MySQL 存储过程语法差异大，需改写为 PL/pgSQL。
自增ID：MySQL 的 AUTO_INCREMENT 需替换为 SERIAL 或 IDENTITY 列。
工具：可使用 pgloader 进行自动迁移，大部分场景可完成 90% 以上转换。

10. PostgreSQL 生态全景

10.1 核心工具链

psql：功能强大的交互式终端，支持元命令（\dt, \d+, \x, \timing），是调试和管理的不二之选。
pgbench：内置性能基准测试工具，可自定义事务脚本，评估硬件及配置变更影响。
pg_dump / pg_restore：逻辑备份与恢复，支持并行备份和选择性恢复，适合非海量数据库的迁移和备份。

10.2 高可用方案

Patroni + etcd/Consul：业界主流的高可用自动化方案，基于 Python 的模板，实现自动故障转移和 VIP 管理。
repmgr：较早期的 PG 故障转移工具，配置简单但灵活性低于 Patroni。
Pgpool-II：老牌连接池、负载均衡和 HA 中间件，功能全但性能开销较大。

10.3 监控与调优

pg_stat_statements：标准化 SQL 性能视图，是慢查询分析的起点。
PMM (Percona Monitoring and Management)：支持 PG 的开源监控平台，提供丰富的 Grafana 仪表盘。
Grafana + Prometheus + postgres_exporter：可定制度高，与云原生生态完美集成。

10.4 连接池：PgBouncer

PgBouncer 是轻量级连接池，支持三种池模式：

session：一个后端连接服务于一个客户端连接的整个会话。
transaction：后端连接仅在事务期间被占用（推荐）。
statement：每个语句后即释放连接，适合自动提交场景。

设置 pool_mode = transaction 配合 max_client_conn 与 default_pool_size，可大幅降低 PG 的进程数，提高吞吐。

11. Spring 生态中的 PostgreSQL 整合全景

Spring Boot 集成 PostgreSQL 提供了多种数据访问抽象，选择需根据业务复杂度与团队偏好进行权衡。

技术	定位	适用场景	特点
spring-data-jdbc	轻量聚合式 JDBC 封装	简单 CRUD，充血模型应用	无延迟加载，直接映射聚合根，清晰直观
spring-data-jpa + Hibernate	全自动 ORM	标准实体关系，快速原型	自动 DDL、缓存，复杂查询可能产生 N+1 问题
MyBatis	半自动 SQL 映射	复杂查询、遗留数据库	纯 SQL 控制，动态 SQL 强大，需手写映射
jOOQ	类型安全 SQL 构建	复杂报表、需要精细 SQL 控制	将 SQL 转化为 Java DSL，编译期类型检查，完美支持 PG 专有语法
R2DBC	响应式数据库驱动	WebFlux 非阻塞应用	基于 Reactive Streams，适合全异步架构

Spring 生态整合全景对比图：

flowchart TD 
    App[Spring Boot Application] --> D[JDBC/Datasource]
    App --> R[R2DBC Connection]
    D --> JDBC[spring-data-jdbc]
    D --> JPA[spring-data-jpa / Hibernate]
    D --> MyBatis[MyBatis]
    D --> jOOQ[jOOQ]
    R --> R2DBC_Impl[R2DBC PostgreSQL Driver]
    JDBC -- 简单映射 --> CRUD[聚合CRUD]
    JPA -- ORM --> Entity[实体关系映射]
    MyBatis -- XML/注解SQL --> Complex[复杂查询]
    jOOQ -- DSL --> TypeSafe[类型安全 SQL]
    R2DBC_Impl -- Reactive --> NonBlocking[非阻塞流式]

图表主旨概括：展示 Spring Boot 与 PostgreSQL 的五种主要整合方式，根据抽象级别和异步支持区分。

逐层/逐元素分解：

JDBC 同步栈：spring-data-jdbc 最轻量，适合 DDD 聚合；JPA 功能最强但黑盒；MyBatis 让开发者完全掌控 SQL；jOOQ 将 SQL 类型安全推到极致，完美利用 PG 窗口函数、LATERAL、JSONB 操作符等高级特性。
R2DBC 异步栈：适用于响应式微服务，配合 WebFlux 和 Reactive Streams，连接池需使用 r2dbc-pool。

设计原理映射：不同抽象层反映了“有多少数据库特性应向应用层暴露”的权衡。jOOQ 能生成类型安全的 PG 专有 SQL，与 PG 的强大功能最为契合；JPA 则弱化了数据库特性，追求可移植性。

工程联系与关键结论：对于 PostgreSQL 项目，强烈推荐 jOOQ 用于复杂业务查询，它可完全利用数据库的高级分析功能；普通 CRUD 可选择 spring-data-jdbc 或 JPA；异步架构选择 R2DBC。避免在需要密集使用 JSONB、窗口函数时采用 JPA 的 JPQL，以免丧失 PG 表达力。

示例配置（application.yml）：

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/mydb
    username: app_user
    password: secret
    hikari:
      maximum-pool-size: 20
  jpa:
    show-sql: true
    properties:
      hibernate:
        dialect: org.hibernate.dialect.PostgreSQLDialect

12. 面试高频专题

12.1 PostgreSQL 和 MySQL 的本质区别是什么？各自的适用场景？

一句话回答：PostgreSQL 是追求功能完备、标准兼容的学院派数据库，适合复杂查询、数据分析和扩展需求；MySQL 是追求简单快速、易于扩展的互联网派数据库，适合高吞吐、简单逻辑的 Web 应用。
详细解释：
- 架构上，PG 多进程隔离性强，MySQL 多线程上下文切换小但隔离弱。PG 的 MVCC 通过原地多版本 + VACUUM 实现无锁读，回滚极快；MySQL 通过 Undo Log + Purge 实现，回滚代价高。
- 功能上，PG 提供了 GiST/GIN/BRIN 等专项索引，JSONB 等原生类型，以及 SSI 真正串行化；MySQL 索引类型单一，JSON 简单，事务依赖间隙锁。
- 适用场景：PG 适合分析型系统、GIS、金融、数据中台；MySQL 适合读多写少、键值查找为主的互联网应用。
多角度追问：
- 为什么 PG 在高并发写场景下可能不如 MySQL？→ 多进程上下文切换和表膨胀问题。
- 何时必须选择 PG 而非 MySQL？→ 需要真实可串行化事务或密集空间查询时。
- 如何从 MySQL 平滑迁移到 PG？→ 利用 pgloader 并注意数据类型和存储过程改写。
加分回答：PG 的 Extension 框架使开发者可以近乎内核级地扩展功能，如 TimescaleDB 和 Citus，代表了数据库向“可编程数据平台”演进的方向，这在 MySQL 生态中难以实现。

12.2 PostgreSQL 的 MVCC 是如何实现的？与 MySQL 的 Undo Log 有什么不同？

一句话回答：PG 通过元组版本链（原地多版本）实现 MVCC，历史版本存于表空间，依靠事务快照和 xmin/xmax 确定可见性；MySQL 通过 Undo Log 将历史版本存于回滚段，聚簇索引只保留最新版本。
详细解释：
- PG 更新时插入新元组，旧元组标记 xmax，读取时根据快照滤除。旧版本由 VACUUM 回收，未回收则导致表膨胀。
- MySQL 将前镜像写入 Undo，聚簇索引内更新为最新值。读取时需沿回滚指针构造旧版本。Purge 线程后台清理。
- 关键差异：PG 回滚即刻完成，大事务回滚不影响性能；MySQL 回滚需应用 Undo，大事务回滚慢。PG 表膨胀显著，需精细调整 Autovacuum。
多角度追问：
- 为什么 PG 需要 VACUUM 而 MySQL 不需要？→ 因为旧版本留在数据页需标记回收。
- PG 的 Hot Standby 备库如何基于 MVCC 提供一致性读？→ 利用快照冲突处理。
- 在什么场景下 MySQL 的 Undo 机制可能崩溃？→ 大事务生成大量 Undo，导致系统表空间膨胀，回滚占用资源过高。
加分回答：PG 内置的 Heap-Only Tuple (HOT) 机制能在特定条件下避免索引更新，减少写放大，这是对原地多版本的优化。MySQL 的隐式锁与 Undo 结合方式也有类似考虑但机制不同。

12.3 PostgreSQL 支持哪些索引类型？各自的适用场景是什么？

一句话回答：PG 支持 B-Tree、Hash、GiST、GIN、BRIN 五种索引，分别适用于通用查询、等值、几何与全文辅助、复合数据类型包含、以及大表时序范围查询。
详细解释：
- B-Tree：排序、范围、等值，最通用。
- Hash：仅等值，适用性低。
- GiST：可扩展的广义搜索树，PostGIS 依赖它构建空间索引，也用于全文搜索辅助。
- GIN：倒排索引，适合 JSONB 包含查询和数组 @> 操作。
- BRIN：块范围索引，为物理顺序相关的大表提供极小化的索引。
多角度追问：
- 为什么 PG 的 B-Tree 没有类似 MySQL 的回表问题？→ 索引直接指向 Heap 元组物理 TID，无需回表检索主键。
- GIN 索引的构建和更新性能如何？→ 构建慢，更适合读多写少的表。
- 在时序场景下，BRIN 比 B-Tree 好在哪？→ 索引体积缩小数百倍，允许将全部索引保留在内存。
加分回答：PG 的索引支持并行扫描和仅索引扫描（Index-Only Scan），结合 Visibility Map 可跳过大量数据页，这是基于 MVCC 元组可见性地图的优化。

12.4 PostgreSQL 的 JSONB 与 MySQL 的 JSON 有什么区别？性能上有何差异？

一句话回答：JSONB 是二进制格式，允许 GIN 索引和高效查询；MySQL 的 JSON 本质是文本存储，索引能力弱，性能差距在复杂嵌套查询中更明显。
详细解释：
- JSONB 存储时解析一次，后续查询无需重复解析。支持 @>、?、#> 等丰富操作符，并可用 SQL/JSON Path。
- MySQL JSON 列存储为二进制文本，但内部仍需解析；索引只能通过虚拟列 + 普通索引实现，无法直接索引任意路径。
- 实测显示，对百万级文档的包含查询，PG + GIN 可达到毫秒级，MySQL 可能需要全表扫描或额外冗余字段。
多角度追问：
- JSONB 更新时会产生什么开销？→ 类似 MVCC 产生新元组，更新频繁需调整 FILLFACTOR。
- 何时仍然应该使用文本 json 而非 jsonb？→ 仅需保留原始 JSON 且不查询，或关心精确空白保持。
- 能否结合 JSONB 和关系表进行 JOIN？→ 可，通过函数转换为记录或与普通表索引关联。
加分回答：PG 的 JSON Path 支持表达式索引，能针对特定路径加速，这是 SQL 标准高级特性，为文档与关系混合模型提供了坚实基础。

12.5 PostgreSQL 的扩展机制是怎样的？FDW 是什么？

一句话回答：扩展通过 CREATE EXTENSION 加载动态库，可深度定制内核；FDW 实现 SQL/MED 标准，让 PG 像查询本地表一样访问外部数据源。
详细解释：
- Extension 可提供新数据类型、索引访问方法、后台 Worker、操作符等，如 postgis、pg_stat_statements。
- FDW 通过 postgres_fdw 等实现联邦查询，支持谓词下推、聚合下推（PG16 增强），可跨数据库甚至异构系统进行实时查询。
多角度追问：
- 编写一个自定义 FDW 需要做什么？→ 实现 Callback 函数（计划、执行、分析）。
- FDW 与 dblink 有何不同？→ FDW 是 SQL 标准，更透明且支持事务管理。
- Extension 的 shared_preload_libraries 是什么样的加载阶段？→ 在 Postmaster 启动时加载，可添加共享内存钩子和后台进程。
加分回答：PG 的扩展机制使用 Hook 函数和操作符类注册表，使得第三方扩展与内核交互几乎无性能损耗，这种设计使 PG 成为“数据库开发平台”。

12.6 PostgreSQL 和 MySQL 在事务隔离级别实现上有何不同？

一句话回答：PG 通过快照隔离实现 RR 和 RC，并提供基于 SSI 的真正 SERIALIZABLE；MySQL 的 RR 级依赖间隙锁防止部分幻读，SERIALIZABLE 只是加强了 SELECT 锁定。
详细解释：
- PG 的 RC 每次语句取新快照，RR 事务只取一次快照，无间隙锁，写倾斜需升级到 SERIALIZABLE。
- MySQL RR 加间隙锁防止幻读，导致较高的锁冲突，但降低了应用需要处理冲突的重试逻辑。
- PG 的 SSI 使用 SIREAD 谓词锁，检测冲突并回滚，提供无锁的完全可串行化。
多角度追问：
- 为什么 PG 的 RR 不能完全防止幻读？→ 因为没有谓词锁，可能发生写倾斜。
- 什么业务必须使用 SERIALIZABLE？→ 金融转账、库存扣减等有约束关系的场景。
- MySQL 的间隙锁有什么副作用？→ 可能造成死锁，影响并发插入。
加分回答：PG 的 SSI 实现基于 Cahill 等人的论文，采用保守冲突检测，能保证串行化同时保持高并发。

12.7 PostgreSQL 的流复制和逻辑复制有什么区别？各自的适用场景？

一句话回答：流复制基于 WAL 物理日志，延迟低、只能全库复制且要求同构；逻辑复制基于发布/订阅模型，可复制部分表、跨版本，适用于数据集成。
详细解释：
- 流复制用于高可用、读写分离，主备架构。
- 逻辑复制用于跨版本升级、数据仓库集成、微服务数据分发。
多角度追问：
- 如何实现级联复制？→ 备库可再作为主库向下游发送 WAL。
- 逻辑复制能否处理 DDL？→ 默认不复制 DDL，需借助扩展或自行同步。
- 延迟抖动时，同步流复制会怎样影响主库？→ 导致事务提交阻塞。
加分回答：在 PG16 中，逻辑复制支持并行应用，大幅提升吞吐，并允许从 standby 进行逻辑订阅，减少主库负载。

12.8 在 Spring Boot 中，如何选择合适的 PostgreSQL 数据访问技术？

一句话回答：简单 CRUD 选 spring-data-jdbc 或 JPA，复杂 SQL 和 PG 专有特性选 jOOQ，响应式架构选 R2DBC。
详细解释：
- spring-data-jdbc 遵循 DDD 聚合，无延迟加载，适合微服务。
- JPA 快速开发，但 Hibernate 生成的 SQL 可能无法利用 PG 的高级特性。
- jOOQ 编译时类型安全，完美支持窗口函数、JSONB 操作符、递归 CTE。
多角度追问：
- 为什么大型项目倾向于 jOOQ 而非 JPA？→ 更强的 SQL 控制力，避免 N+1 和黑盒优化。
- R2DBC 与 WebFlux 协同的优势？→ 非阻塞端到端，高并发节省线程资源。
加分回答：jOOQ 可使用代码生成器将 PG 内置函数和类型映射为 Java 枚举和方法，实现完全的数据库感知。

12.9 PostgreSQL 的多进程架构对高并发有什么影响？如何优化？

一句话回答：多进程模型天然隔离但也消耗更多内存和上下文切换，必须使用连接池降低并发进程数。
详细解释：
- 直接对外暴露 PG 连接会导致连接数暴涨，每个进程分配 work_mem 可能导致内存耗尽。
- 使用 PgBouncer 事务模式，将数千客户端连接复用到几十个后端进程。
多角度追问：
- max_connections 应设置多少？→ 通常 100-500，取决于内存。
- 多进程架构对 NUMA 亲和性有何影响？→ 需绑定进程到特定 socket。
加分回答：最新 PG 支持内置连接池（pgbouncer 的替代），社区正在讨论引入线程模式可选性，但目前进程模型仍是主流。

12.10 PostgreSQL 的 `VACUUM` 是做什么的？为什么 MySQL 没有类似机制？

一句话回答：VACUUM 回收死元组并整理空间，因为 PG 的 MVCC 将旧版本保存在表空间，MySQL 通过 Undo 将旧版本移出表空间，故不需要 VACUUM。
详细解释：
- VACUUM 标记死元组为可用空间，并更新可见性地图，防止事务 ID 回卷。
- 若缺少 VACUUM，表不断膨胀，最终可能达到事务 ID 环绕保护极限，强制停机。
多角度追问：
- Autovacuum 的触发条件是什么？→ 死元组比例超过阈值。
- 如何监控表膨胀？→ pg_stat_user_tables 的 n_dead_tup 和 n_live_tup。
加分回答：PG 16 引入了 VACUUM 的内存优化和并行处理，减少了大规模表的清理锁持有时间。

12.11 一个从 MySQL 迁移到 PostgreSQL 的项目，需要注意哪些问题？

一句话回答：关注数据类型映射、SQL 语法改写、存储过程重写、性能基准回测和运维监控适配。
详细解释：
- 数据类型：TINYINT -> SMALLINT，DATETIME -> TIMESTAMP，TEXT 无差异。
- 语法：字符串拼接 ||，正则 ~，布尔值 TRUE/FALSE。
- 存储过程：需用 PL/pgSQL 重写，或考虑放在应用层。
- 运维：将 MySQL 的慢查询分析和监控方案切换为 pg_stat_statements + PMM。
多角度追问：
- 如何使用 pgloader 自动化迁移？→ 编写迁移脚本，处理转换规则。
- 迁移后性能下降可能的原因？→ 索引缺失，或 PG 默认统计信息采样不足，执行计划偏差。
加分回答：迁移前应分析应用 SQL，哪些使用了 MySQL 专有语法（如 INSERT ... ON DUPLICATE KEY），用 PG 的 INSERT ... ON CONFLICT 替换。

12.12 （系统设计题）设计一个支持千万级用户的社交平台数据存储方案

需求：存储用户动态 Feed、好友关系图，支持复杂推荐查询。结合 PostgreSQL 特性设计库表、索引和查询优化。

一句话回答：利用关系表存储好友关系，使用 PG 的门户数组和递归 CTE 实现图遍历，Feed 采用时间分区表 + BRIN 索引，结合 JSONB 灵活存储动态元数据，复杂推荐用窗口函数和物化视图。
详细解释：
- 好友关系：friendships (user_id, friend_id, created_at)，双向存两条记录或使用 intarray 扩展。查询共同好友用数组交集 ARRAY_AGG 或递归 CTE。
- Feed：posts 表按 created_at 月份分区，user_id 建 B-Tree，created_at 建 BRIN 索引。每个用户首页 Feed 通过 WHERE user_id IN (friends_list) ORDER BY created_at DESC LIMIT 50，利用分区剪除和 BRIN 跳跃扫描。
- 推荐查询：计算好友的互动频率（窗口函数 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY interaction_count DESC)），生成推荐排名。复杂计算可创建物化视图，定时刷新。
- 文档灵活性：用户动态内容用 JSONB 存附加属性，GIN 索引支持灵活检索。
多角度追问：
- 如何处理热点用户 Feed 访问？→ 使用 Redis 缓存聚合后的 Feed 时间线，写通过 PG。
- 图遍历深度过大怎么办？→ 利用递归 CTE 的 max_recursion_depth 限制，或借助 AGE 图扩展。
- 分区表如何进行跨分区范围查询优化？→ constraint_exclusion = partition，确保查询裁剪分区。
加分回答：若社交关系图极复杂，可集成 Apache AGE 作为图扩展，在 PG 内直接执行 Cypher 图查询，省去外部图数据库。同时，利用 Citus 扩展可实现水平分片，应对十亿级用户规模。

PostgreSQL 核心特性速查表

特性	原理摘要	适用场景	关键配置/命令
MVCC 无锁读	元组版本链 + 事务快照	高并发读写混合	`autovacuum_vacuum_scale_factor`
五大索引	B-Tree, GiST, GIN, BRIN	空间、JSONB、时序	`CREATE INDEX ... USING GIN ...`
JSONB	二进制 JSON + GIN 索引	文档存储 / 灵活 schema	`jsonb_path_ops` 索引
扩展与 FDW	动态库加载，SQL 联邦查询	跨库分析、功能增强	`CREATE EXTENSION postgres_fdw;`
SSI 可串行化	谓词锁检测冲突	金融/库存严格一致性	`BEGIN ISOLATION LEVEL SERIALIZABLE;`
物理/逻辑复制	WAL 流复制与发布订阅	高可用、数据集成	`CREATE PUBLICATION / SUBSCRIPTION`
丰富数据类型	数组、INET、Range	网络审计、排期系统	避免字符串拼接，利用内置运算
PgBouncer	事务级连接池	高并发连接管理	`pool_mode = transaction`
jOOQ 集成	类型安全 DSL	复杂 SQL 控制	代码生成器，窗口函数支持
Autovacuum	后台回收死元组	防止表膨胀	`log_autovacuum_min_duration`

延伸阅读：

《PostgreSQL: The Definitive Guide》
PostgreSQL 16 官方文档
The Internals of PostgreSQL (interdb.jp)
PostgreSQL 源码分析系列

PostgreSQL 特性全景与选型指南

概述

文章组织架构图

1. PostgreSQL 的定位与设计哲学

1.1 从开源数据库演进看 PostgreSQL

1.2 核心设计取舍

2. 核心特性一：MVCC 与无锁并发

2.1 PostgreSQL 的 MVCC 实现：元组版本链

2.2 与 MySQL InnoDB Undo Log 的对比

3. 核心特性二：五大索引体系

4. 核心特性三：丰富原生数据类型

4.1 数组类型

4.2 JSON 与 JSONB

4.3 网络地址类型（INET/CIDR）

4.4 UUID 与 Range 类型

5. 核心特性四：高度可扩展的插件与 FDW

5.1 Extension 机制

5.2 FDW（Foreign Data Wrapper）与数据联邦

6. 核心特性五：完整事务支持

7. 核心特性六：物理与逻辑复制

7.1 物理流复制

7.2 逻辑复制

8. 与 MySQL 的全方位深度对比

8.1 核心差异总览

8.2 关键差异深入剖析

9. 场景化技术选型决策框架

9.1 明确推荐 PostgreSQL 的场景

9.2 可优先考虑 MySQL 的场景

9.3 迁移考量

10. PostgreSQL 生态全景

10.1 核心工具链

10.2 高可用方案

10.3 监控与调优

10.4 连接池：PgBouncer

11. Spring 生态中的 PostgreSQL 整合全景

12. 面试高频专题

12.1 PostgreSQL 和 MySQL 的本质区别是什么？各自的适用场景？

12.2 PostgreSQL 的 MVCC 是如何实现的？与 MySQL 的 Undo Log 有什么不同？

12.3 PostgreSQL 支持哪些索引类型？各自的适用场景是什么？

12.4 PostgreSQL 的 JSONB 与 MySQL 的 JSON 有什么区别？性能上有何差异？

12.5 PostgreSQL 的扩展机制是怎样的？FDW 是什么？

12.6 PostgreSQL 和 MySQL 在事务隔离级别实现上有何不同？

12.7 PostgreSQL 的流复制和逻辑复制有什么区别？各自的适用场景？

12.8 在 Spring Boot 中，如何选择合适的 PostgreSQL 数据访问技术？

12.9 PostgreSQL 的多进程架构对高并发有什么影响？如何优化？

12.10 PostgreSQL 的 VACUUM 是做什么的？为什么 MySQL 没有类似机制？

12.11 一个从 MySQL 迁移到 PostgreSQL 的项目，需要注意哪些问题？

12.12 （系统设计题）设计一个支持千万级用户的社交平台数据存储方案

PostgreSQL 核心特性速查表

12.10 PostgreSQL 的 `VACUUM` 是做什么的？为什么 MySQL 没有类似机制？