最近在工作中深度使用了PostgreSQL的一些高级特性，比如MVCCGIN索引WAL逻辑复制分区表CTE窗口函数等等用起来确实很强大很方便，但是我发现很多人，虽然天天在用PostgreSQL但是对这些高级特性的底层原理并不了解只知道怎么用不知道为什么这么设计底层是怎么实现的这就导致遇到性能问题，或者奇怪的行为时不知道怎么排查怎么优化。

作为一个喜欢刨根问底的工程师我这段时间专门研究了一下PostgreSQL几个核心高级特性的底层实现原理看了部分源码也做了一些实验验证今天就来分享一下我的理解和心得深入剖析PostgreSQL高级特性的底层机制帮助大家，不仅知其然更知其，所以然。

一、MVCC（多版本并发控制）原理剖析

首先，来说说PostgreSQL最核心的特性之一MVCC（Multi-Version Concurrency Control多版本并发控制）这是PostgreSQL实现高并发读写不阻塞的关键机制也是很多人用了很久，但是没搞懂的特性。

1. 为什么需要MVCC

在没有MVCC的数据库里（比如早期的MySQL MyISAM或者一些简单的数据库）并发控制主要靠锁读的时候，加共享锁写的时候，加排他锁这样就会导致一个问题写会阻塞读读也会阻塞写在高并发场景下性能很差用户体验很不好，比如一个长事务在更新某张表的数据其他用户就都读不了这张表了要等事务提交才能读这，显然是不能接受的。

为了解决这个问题就有了MVCC的思路核心思想是写不阻塞读读也不阻塞写怎么实现呢就是通过保存数据的多个版本当一个事务在更新数据的时候，不是直接覆盖原来的数据而是创建一个新版本的数据原来的版本保留这样其他读事务仍然可以读原来版本的数据不需要等写事务提交写事务也不需要等读事务结束这样读写就互不阻塞了并发性能大大提升。

2. PostgreSQL的MVCC实现方式

PostgreSQL的MVCC实现方式和其他数据库（比如MySQL InnoDB）不太一样PostgreSQL是通过在数据行上保存版本信息来实现的具体来说，每一行数据都有几个隐藏的系统字段用来记录这行数据的版本信息主要有：

  • xmin：创建这行数据的事务ID（Transaction ID）也就是哪个事务插入，或者更新了这行数据产生了这个版本。
  • xmax：删除，或者更新这行数据的事务ID如果这行数据还没有被删除，或者更新xmax就是0（或者无效事务ID）。
  • cmin/cmax：事务内部的命令ID用来在同一个事务内部区分不同命令产生的版本，因为一个事务里可能有多个SQL命令每个命令都可能产生新版本需要区分。
  • ctid：这行数据的物理位置（块号+块内偏移）如果行被更新了新版本的ctid会指向旧版本的位置形成一个版本链。

通过这些隐藏字段PostgreSQL就能知道每一行数据的每个版本是哪个事务在什么时候创建的哪个事务在什么时候删除的，然后根据当前事务的ID和隔离级别判断哪个版本的数据对当前事务是可见的。

3. 可见性判断规则

PostgreSQL判断一个版本的行数据对当前事务是，否可见主要依据以下规则（以读已提交隔离级别为例）：

  • 如果xmin等于当前事务ID并且xmax为0（或者xmax的事务回滚了）说明这行是，当前事务自己创建的还没被删除可见。
  • 如果xmin的事务在当前事务开始之前，就已经提交了，并且xmax为0（或者xmax的事务回滚了，或者xmax的事务在当前事务开始之后，才开始）说明这行是已经提交的事务创建的还没被删除，或者删除它的事务还没提交对当前事务可见。
  • 如果xmin的事务还没提交（在当前事务开始之后，才开始，或者正在进行）说明这行是未提交的事务创建的对当前事务不可见。
  • 如果xmax的事务在当前事务开始之前，就已经提交了说明这行已经被删除了对当前事务不可见。

通过这些规则PostgreSQL就能在多个版本的行数据中找到对当前事务可见的那个版本实现了读写不阻塞的并发控制。

4. 膨胀（Bloat）问题与VACUUM

PostgreSQL这种MVCC实现方式有一个问题就是旧版本的数据不会自动删除会一直留在数据文件里导致数据文件越来越大这就是所谓的"膨胀"（Bloat）问题，比如一个事务更新了一行数据就会产生一个新版本旧版本还留在数据文件里，如果频繁更新删除数据就会产生大量的旧版本数据文件会膨胀得很厉害查询性能也会下降，因为扫描的时候，要跳过很多不可见的旧版本。

为了解决这个问题PostgreSQL有一个叫VACUUM的机制用来清理那些已经没有任何事务可见的旧版本数据回收空间VACUUM的工作原理是扫描表的数据文件检查每一行的xmin和xmax判断这行是否还对某个活跃事务可见，如果已经没有任何活跃事务能看到这行了就把这行标记为可回收，然后回收它占用的空间供新数据使用，另外VACUUM还会更新可见性映射（Visibility Map）和统计信息帮助查询优化器更好地生成执行计划。

PostgreSQL有自动VACUUM的机制（autovacuum）会在后台自动运行VACUUM清理旧版本数据，但是，如果更新删除非常频繁，或者有长事务一直不提交（长事务会阻止旧版本被清理，因为长事务可能还需要看那些旧版本）就可能导致膨胀问题比较严重需要手动VACUUM或者VACUUM FULL来回收空间这也是使用PostgreSQL需要注意的一个点要监控表的膨胀情况及时处理避免影响性能。

二、索引原理剖析（B-Tree、GIN、GiST）

接下来说说PostgreSQL的索引机制索引是数据库性能优化最重要的手段之一PostgreSQL支持多种索引类型，比如B-TreeHashGINGiSTSP-GiSTBRIN等等不同的索引类型适合不同的场景这里重点剖析最常用的B-Tree和比较高级的GIN索引的原理。

1. B-Tree索引原理

B-Tree（B树注意不是二叉树是平衡多路搜索树）是PostgreSQL默认的索引类型也是最常用的索引类型适合等值查询和范围查询，比如WHERE id = 123WHERE created_at > '2019-01-01'等等大部分场景下用B-Tree索引就够了。

PostgreSQL的B-Tree索引实现是B+Tree的变种（严格来说是Lehman-Yao高并发B-Tree）特点是所有数据都在叶子节点内部节点只存键值用来导航叶子节点之间，用双向链表连接方便范围扫描B-Tree的高度一般很低（3到4层就能索引几千万甚至上亿的数据）所以查询效率很高每次查询只需要几次磁盘IO就能找到目标数据。

B-Tree索引的插入删除更新都能保持树的平衡不会出现退化的情况，而且支持高并发操作通过页面锁和死键（dead key）等机制实现了读写不阻塞的并发访问，另外PostgreSQL的B-Tree索引还支持唯一索引（UNIQUE）表达式索引（CREATE INDEX ON table (lower(name))）部分索引（CREATE INDEX ON table (col) WHERE condition）等高级功能非常灵活。

2. GIN索引原理

GIN（Generalized Inverted Index通用倒排索引）是PostgreSQL的一种高级索引类型适合索引包含多个元素的复合数据类型，比如数组全文搜索的tsvectorJSONB的键值等等GIN索引特别适合"包含"类的查询，比如WHERE arraycol @> ARRAY[1,2,3]（数组包含某些元素）WHERE totsvector('english', content) @@ plaintotsquery('hello world')（全文搜索）WHERE jsonbcol @> '{"key": "value"}'（JSONB包含某些键值）这些查询用普通B-Tree索引是没法高效处理的，但是用GIN索引就很高效。

GIN索引的核心思想是倒排索引（Inverted Index）和搜索引擎的原理一样具体来说，对于一个复合类型的列（比如数组，或者tsvector）GIN索引会把它拆成多个元素（比如数组的每个元素，或者tsvector的每个词）然后为每个元素建立一个倒排链表记录哪些行包含这个元素这样当查询"包含某些元素"的时候，就能快速通过倒排链表找到所有包含这些元素的行效率很高。

比如有一个数组列tags存文章的标签有一行数据的tags是['postgres', 'database', 'sql']GIN索引就会为'postgres''database''sql'这三个元素分别建立倒排项记录这行数据包含这些元素当查询WHERE tags @> ['postgres', 'sql']的时候GIN索引就会找包含'postgres'的行集合和包含'sql'的行集合，然后取交集就是，同时包含这两个元素的行效率很高。

GIN索引的缺点是写入开销比较大，因为插入，或者更新一行数据的时候，需要更新多个倒排项（每个元素都要更新）所以写入性能比B-Tree差一些，另外GIN索引占用的空间也比较大，因为要存很多倒排项，但是对于读多写少的场景，或者需要高效查询复合类型的场景GIN索引是非常值得的能大大提升查询性能。

PostgreSQL的GIN索引还支持fastupdate选项（默认开启）用来优化写入性能原理是把小批量的插入更新先缓存在一个pending列表里不立即更新主索引等pending列表积累到一定量，或者查询的时候，再批量合并到主索引这样能减少随机IO提升写入性能，但是查询的时候，需要，同时查主索引和pending列表可能稍微慢一点，不过一般影响不大。

三、WAL（预写日志）原理剖析

接下来说说PostgreSQL的WAL（Write-Ahead Logging预写日志）机制这是PostgreSQL保证数据持久性和，崩溃恢复的核心机制也是实现复制和PITR（时间点恢复）的基础。

1. 为什么需要WAL

数据库在处理写操作（插入更新删除）的时候，需要修改数据页面（Data Page）如果每次修改都直接把数据页面写回磁盘那性能会很差，因为数据页面的修改是随机IO而且一个事务可能修改多个数据页面需要多次随机写性能很低，另外，如果事务提交了，但是数据页面还没来得及写回磁盘这时候数据库崩溃了（比如断电，或者进程崩溃）那已提交的事务的修改就会丢失这就违反了事务的持久性（Durability）要求。

为了解决这些问题就有了WAL的机制核心思想是"预写日志"也就是在修改数据页面之前，先把修改的内容记录到WAL日志里，并且WAL日志要先刷到磁盘，然后再修改内存中的数据页面数据页面不用立即写回磁盘可以等后面批量刷回（checkpoint的时候）这样，即使数据库崩溃了内存中的数据页面丢失了也可以通过WAL日志重放（Redo）把已提交的事务的修改恢复回来保证数据不丢失，而且WAL日志是顺序写的性能比随机写数据页面高很多，所以整体性能也提升了。

2. WAL的工作原理

PostgreSQL的WAL机制工作流程大致是这样的：

  1. 事务执行写操作（INSERT/UPDATE/DELETE）修改内存中（shared buffer）的数据页面在修改之前，先把修改的内容（包括修改前和修改后的数据，或者只记录修改的差异取决于wal_level）记录到WAL缓冲区（WAL buffer）里生成一条WAL记录（WAL Record）。
  2. 当事务提交的时候，必须把这个事务相关的所有WAL记录从WAL缓冲区刷到磁盘上的WAL文件里（fsync）保证WAL记录持久化了，然后事务才算提交成功返回给客户端这就是WAL的"预写"要求提交时WAL必须先落盘。
  3. 内存中被修改的数据页面（脏页）不用立即写回磁盘等到checkpoint的时候（或者shared buffer满了需要淘汰脏页的时候）才批量把脏页写回磁盘的数据文件里在脏页写回磁盘之前，必须保证对应的WAL记录已经刷到磁盘了（这叫WAL的优先级规则Write-Ahead Logging rule）不然，如果脏页先写回磁盘，但是WAL还没落盘这时候崩溃了就没法恢复了。
  4. 如果数据库崩溃了重启的时候，会进行崩溃恢复（Crash Recovery）从最后一次checkpoint的位置开始重放WAL日志把所有已提交的事务的修改重新应用到数据页面上把未提交的事务的修改回滚掉这样就能把数据库恢复到崩溃前一致的状态保证数据不丢失也不出现不一致。

3. WAL的相关配置和调优

WAL机制有一些重要的配置参数会影响性能和可靠性这里简单说几个：

  • wal_level：WAL日志的级别决定WAL记录的详细程度可选值有minimalreplicalogicalminimal只记录崩溃恢复需要的最少信息replica记录足够支持流复制的信息logical记录足够支持逻辑复制的信息级别越高WAL记录越详细产生的WAL量越大，但是能支持更多功能（复制逻辑复制等）生产环境一般用replica或者logical。
  • synchronous_commit：同步提交开关，如果开启（默认）事务提交时必须等WAL刷到磁盘才返回成功保证持久性，但是性能稍差，如果关闭事务提交时不用等WAL落盘就返回成功性能更好，但是，如果崩溃可能丢失最近已提交的少量事务数据对持久性要求不高的场景可以关闭提升性能。
  • walbuffers：WAL缓冲区的大小用来缓存还没刷到磁盘的WAL记录，如果太小高并发写的时候，可能出现WAL缓冲区满了要等刷盘的情况影响性能一般设置为sharedbuffers的1/32左右，或者几MB到几十MB。
  • checkpointtimeout / maxwalsize：checkpoint的触发条件checkpointtimeout是时间间隔（默认5分钟）maxwalsize是，WAL文件的最大大小（默认1GB）达到任一条件就触发checkpointcheckpoint太频繁会影响性能（频繁刷脏页）太稀疏会导致崩溃恢复时间长（要重放很多WAL）需要根据场景调优。

四、复制原理剖析（流复制与逻辑复制）

最后说说PostgreSQL的复制机制这是实现高可用和读写分离的基础PostgreSQL支持物理复制（流复制）和，逻辑复制两种方式这里简单剖析一下原理。

1. 流复制（物理复制）原理

流复制（Streaming Replication）是PostgreSQL最常用的复制方式属于物理复制原理是把主库（Master/Primary）产生的WAL日志实时发送给从库（Standby/Slave）从库收到WAL日志后不断重放（Redo）把主库的修改应用到从库的数据文件上这样从库的数据就和主库保持一致了，因为是直接重放WAL物理日志，所以叫物理复制复制的粒度是数据块（或者WAL记录）不是逻辑的表，或者行。

流复制的工作流程大致是这样的：

  1. 主库开启归档模式（archivemode = on）和合适的wallevel（至少replica）配置maxwalsenders参数允许从库连接拉取WAL。
  2. 从库先通过pg_basebackup工具从主库做一个基础备份（Base Backup）把主库的所有数据文件拷贝过来作为从库的初始数据，同时记录备份开始时的WAL位置（LSNLog Sequence Number）。
  3. 从库配置recovery.conf（PostgreSQL 12之后，是postgresql.auto.conf和standby.signal文件）指定主库的连接信息（hostportuser等）然后启动从库从库会以recovery模式启动连接到主库从基础备份时记录的LSN位置开始请求主库发送之后，的WAL日志。
  4. 主库有一个walsender进程负责把WAL日志实时发送给从库的walreceiver进程从库收到WAL日志后先写到本地的WAL文件里，然后由startup进程重放WAL应用到数据文件上这样从库的数据就不断和主库保持同步了。
  5. 流复制支持异步复制（默认）和同步复制（synchronouscommit = on配置synchronousstandby_names）异步复制下主库提交事务不用等从库收到WAL性能好，但是主库崩溃可能丢失少量数据同步复制下主库提交事务要等至少一个从库收到并刷WAL才返回数据更安全，但是性能稍差，而且从库不可用会影响主库写入。

流复制的优点是实现简单性能好能保证数据完全一致（因为是物理复制）适合高可用和读写分离场景缺点是复制粒度粗不能只复制部分表，或者部分数据主从版本要一致（大版本一般要相同）而且从库在recovery模式下只能读不能写（虽然可以开hot_standby允许读查询）。

2. 逻辑复制原理

逻辑复制（Logical Replication）是PostgreSQL 10开始内置的复制方式属于逻辑复制和物理复制不同逻辑复制不是直接重放WAL物理日志而是把WAL解析成逻辑的变更（比如哪张表的哪一行被插入/更新/删除了具体值是什么）然后把这些逻辑变更发送给订阅端订阅端收到后在自己的数据库里执行对应的操作这样就实现了数据的复制。

逻辑复制的工作流程大致是这样的：

  1. 主库（发布端Publisher）设置wal_level = logical创建发布（PUBLICATION）指定要复制哪些表（可以是，全部表也可以是部分表）以及复制哪些操作（INSERT/UPDATE/DELETE/TRUNCATE）。
  2. 从库（订阅端Subscriber）创建订阅（SUBSCRIPTION）指定主库的连接信息和，要订阅的发布名称创建订阅后从库会和主库建立连接开始同步数据。
  3. 主库有一个逻辑解码（Logical Decoding）的进程（walsender的逻辑复制模式）负责读取WAL日志把物理的WAL记录解析成逻辑的变更（row-level的变更）然后发送给从库。
  4. 从库有一个apply进程（worker进程）负责接收主库发送的逻辑变更，然后在本地数据库里执行对应的INSERT/UPDATE/DELETE操作把数据同步过来从库的表结构需要和主库一致（列名类型等要匹配）但是不需要完全一样（可以有额外的列，或者索引等）。
  5. 逻辑复制也支持初始数据同步（创建订阅时会自动把发布表的现有数据拷贝过来）和增量同步（之后，的变更实时同步）还支持表级的复制（可以只复制某几张表）和跨大版本复制（比如PostgreSQL 10到PostgreSQL 12）。

逻辑复制的优点是复制粒度细可以只复制部分表，或者部分数据支持跨大版本复制和跨平台复制订阅端可以读写（不是recovery模式）可以在订阅端创建自己的表索引视图等适合数据汇总数据分发升级迁移等场景缺点是实现比物理复制复杂性能稍差（因为要解析WAL和重新执行SQL）而且不复制DDL（表结构变更需要手动同步）也不保证完全的顺序和一致性（虽然一般是一致的）需要注意冲突处理。

五、写在最后

以上就是我对PostgreSQL几个核心高级特性的底层原理剖析包括MVCC索引（B-TreeGIN）WAL复制（流复制逻辑复制）这些都是PostgreSQL最核心也最常用的高级特性理解它们的底层原理，不仅能帮我们更好地使用这些特性还能帮我们在遇到性能问题，或者奇怪行为的时候，快速定位原因针对性地优化和解决。

当然PostgreSQL的高级特性远不止这些，还有很多，比如分区表（Declarative Partitioning）CTE（WITH查询）窗口函数（Window Functions）全文搜索（Full-Text Search）JSONB扩展（Extensions）等等每一个都有很深的底层原理和实现细节限于篇幅这篇文章就不一一展开了，如果大家感兴趣以后可以再写文章专门剖析。

最后想说的是作为工程师我们使用任何技术，或者工具都不应该只停留在"会用"的层面还应该尽量去理解它的底层原理和实现机制知其然更知其，所以然这样才能真正掌握它用好它在遇到问题的时候，才能游刃有余而不是束手无策PostgreSQL是一个非常优秀的开源数据库功能强大代码质量高文档完善非常值得我们深入学习和研究希望这篇文章能帮大家对PostgreSQL的高级特性有更深的理解也希望大家能在工作中用好PostgreSQL让它发挥出最大的价值。

愿大家都能在技术的道路上不断深入不断进步，不仅会用工具更能理解工具驾驭工具成为真正的技术高手加油！