PostgreSQL基础和原理

丰富的数据类型

• 数字
• 字符
• 数组类型int[] / text[] / int[][]
• json/jsonb类型
• xml类型
• 货币类型money
• 日期date/time/timestamp
• 布尔boolean
• 空间几何类型point/line/box/path/circle等
• 网络地址类型inet/macaddr
• 位串bit
• uuid类型
• 复合类型CREATE TYPE complex AS(a int, b int)
• 范围类型int4range/numrange/daterange/等

强大的索引类型

PostgreSQL 支持索引类型

• B-tree(默认)：适合顺序检索、范围查询
• Hash：适合简单的等式比较
• GIN：(Generalized Inverted Index)即倒排索引，存储被索引字段的VALUE或VALUE的元素，以及行号的list或tree，适合数组元素查询、全文搜索等。
• rum：GIN的加强，官方开源插件。
• GiST：GiST是一个通用的索引接口，可以使用GiST实现b-tree, r-tree等索引结构。不同的类型，支持的索引检索也各不一样。例如：1）几何类型支持位置距离排序；2）标量类型支持距离排序等等
• SP-GiST：SP-GiST类似GiST，是一个通用的索引接口，但是SP-GIST使用了空间分区的方法，使得SP-GiST可以更好的支持非平衡数据结构，例如quad-trees, k-d tree, radis tree。例如：几何类型支持正交、相交、在上下左右等
• BRIN：BRIN 索引是块级索引，有别于B-TREE等索引，BRIN记录并不是以行号为单位记录索引明细，而是记录每个数据块或者每段连续的数据块的统计信息。因此BRIN索引空间占用特别的小，对数据写入、更新、删除的影响也很小。BRIN属于LOSSLY索引，当被索引列的值与物理存储相关性很强时，BRIN索引的效果非常的好。例如时序数据，在时间或序列字段创建BRIN索引，进行等值、范围查询时效果很棒。
• bloom：基于bloom filter构造的一个索引接口，属于lossy索引，可以收敛结果集(排除绝对不满足条件的结果，剩余的结果里再挑选满足条件的结果)，因此需要二次check，bloom支持任意列组合的等值查询。
• zombodb：PostgreSQL与ElasticSearch结合的一个索引接口，可以直接用sql读写ES。
• 其它：PostgreSQL 9种索引的原理和应用场景-阿里云开发者社区 
• 可扩展性强：如向量索引(图搜索、人脸识别、个性推荐) 高维向量检索技术在PG中的设计与实践 ——杨文（缁尘）-阿里云开发者社区 

【PostgreSQL的索引文件和数据文件是完全分开的。都为非聚集索引】

表达式上的索引

索引列不必仅仅是基础列，也可以是从表的一个或多个列计算的函数或标量表达式。此功能对根据计算结果快速访问表是非常有用的。

例如，进行区分大小写比较的常见方法是使用lower()函数：SELECT * FROM test1 WHERE lower(col1) = 'value';

如果在lower(col1)函数的结果中定义了一个索引，则该查询可以使用索引：CREATE INDEX test1_idx ON test1 (lower(col1));

组合多个索引

如WHERE a = 5 AND b = 6的查询条件可以使用索引，但是像WHERE a = 5 OR b = 6这样的查询无法直接使用索引。

幸运的是，PostgreSQL能够组合多个索引（包括相同索引的多个使用）来处理单次索引扫描无法实现的情况。系统可以跨多个索引扫描形成AND和OR条件。例如，像WHERE x = 42 OR x = 47 OR x = 53 OR x = 99这样的查询可以分解为x上的索引的四个独立扫描，每次使用查询子句之一进行扫描。然后将这些扫描的结果OR化在一起以产生结果。另一个例子是，如果我们在x和y上有单独的索引，那么WHERE x = 5 AND y = 6之类的查询的一个可能的实现是将每个索引与适当的查询子句一起使用，然后将索引结果AND结合起来，以识别结果行。

要组合多个索引，系统将扫描每个所需的索引，并准备内存中的位图，为匹配该索引条件的表行的位置提供报告。然后根据查询的需要将位图进行AND和OR操作。最后，访问并返回实际的表行。

部分索引

部分索引是在表的子集上构建的索引，使用部分索引的一个主要原因是避免索引常见值。 由于搜索公共值（一个占所有表行的百分之几）的查询将不会使用索引，所以根本没有必要在索引中保留这些行。 这减少了索引的大小，这将加快使用索引的查询。 它也将加快许多表更新操作，因为索引在所有情况下都不需要更新。

CREATE INDEX access_log_client_ip_ix ON access_log (client_ip) 

WHERE NOT (client_ip > inet '192.168.100.0' AND client_ip < inet '192.168.100.255');

仅索引扫描(即覆盖索引)

PostgreSQL中的所有索引都是辅助索引，这意味着每个索引都与表的主数据区域（PostgreSQL术语中称为表的堆）分开存储。这意味着在普通的索引扫描中，每行检索都需要从索引和堆中获取数据。此外，虽然与索引条件匹配的索引条目通常在索引中靠近在一起，但是它们引用的表行可能位于堆中的任何位置。因此，索引扫描的堆访问部分涉及到堆中的大量随机访问，这可能很慢，特别是在传统的旋转介质上。

为了解决这个性能问题，PostgreSQL支持仅索引扫描(就类似其他数据库中的覆盖索引)，可以单独从索引中响应查询，而无需任何堆访问。基本思想是直接从每个索引条目返回值，而不是查询关联的堆条目。

工作流程

工作线程

• postgres/postgresmaster（常驻进程）：监听连接请求，fork子进程进行连接处理
• postgres（子进程）：连接权限检查，是否允许连接；处理请求（查询/修改等），更新事务日志（WAL日志）
• writer process：定期执行检查点，也是把共享内存上的缓存内容往数据库文件写。有点类似mysql的master thread和page cleaner thread的功能。
• WAL writer process：把共享内存上的WAL缓存在适当的时间点往磁盘写
• Archive process把WAL日志转移到归档日志里
• wal sender / wal receiver：实现postgresql复制的进程
• 其它。。。

刷盘步骤

Change发生时：

•  先将变更后内容记入WAL Buffer
•  再将更新后的数据写入Data Buffer

Commit发生时：

•  WAL Buffer刷新到Disk
•  Data Buffer写磁盘推迟

Checkpoint发生时：

•  将所有Data Buffer刷新到磁盘

PosgreSQL的磁盘文件

Page

（目录 -> 文件 -> page -> 不同文件各自的page结构）

数据库文件在Linux平台被划分为默认8K固定长度的page进行管理，通过启动参数BLCKSZ可以预设page的大小。如果page设低了，相同数据量的文件需要分裂成更多的page，IO次数和索引分裂次数都会增加，性能会降低较多；如果page设高了，page内部的数据检索效率会降低，性能一样会降低不少，一般来说8K和16K对于数据库系统来说是最优解。

1.Data数据文件

数据文件的每个page包括五个部分：

• PageHeaderData：24字节长。包含关于页面的一般信息，包括空闲空间指针。
• Item/Linp：一个记录<偏移量, 长度>对的数组，指向实际项。每个项 4 字节，在空闲空间从前往后写。
• Free space：未分配的空间（空闲空间），新Item从这个区域的开头开始分配，新Tuple从其结尾开始分配。
• Tuple：实际的数据项本身以及MVCC所需的一些隐藏字段，在空闲空间从后往前写。
• Special space：索引访问模式相关的数据。不同的索引访问方式存放不同的数据。在普通表中为空。

2.索引文件

1. pg采用的非聚集索引，所以索引和数据是分开存储的，索引page内容和数据page内容同样是5部分，一个page页相当于B树的一个节点。

2. 树结构：metaPage -> rootPage -> branchPage(0-n个非叶子节点) -> leafPage(0-n个叶子节点)

3. 索引文件每个page的结构同数据文件一样包含五个部分：

• - PageHeaderData
• - ItemList
• - FreeSpace
• - Tuple——索引字段数据、完整数据物理位置<块号,块内偏移>、其它
• - SpecialSpace——左右兄弟块号、层级等信息

 

3.XLOG事务日志（10.0以后叫WAL日志）

（Write-Ahead-Logging：日志先行机制）WAL的概念就是对数据文件的改变（包括表和索引）必须先写入日志，即日志记录刷新到永久储存之后，才能被写。遵循这个过程，就不需要在每个事务提交时都刷新数据页到磁盘，因为在宕机时可以用日志来恢复数据库：任何没有应用到数据页上的改动都可以根据日志记录重做。

当系统意外宕机后，恢复时需要回退未完成事务所做的更改，并确保已提交事务所作的更改均已生效。第一点，只要把所有pg_clog文件中记录的所有“运行中”的事务的状态置为“中止”即可，这些事务在宕机时都没有结束。第二点，必须确保事务提交时修改已真正写入到永久存储中。但是直接刷新事务修改后的数据到磁盘是很费时的，为解决这个问题于是引入了WAL(Write-Ahead Log)。

【Data刷新大量随机IO，WAL刷新少量连续IO/而且可以批量】 

为了高效管理事务日志文件,PG把日志文件划分为N个大小为16M(默认值)的WAL segment file.

4.CLOG事务状态日志文件（10.0以后改名为xact日志）

commit log事务状态日志，用于告诉PostgreSQL哪个事务已经完成、哪个还没有完成。clog是比较小的并且没有任何理由会膨胀。

四种状态：IN_PROGRESS, COMMITTED, ABORTED, and SUB_COMMITTED

PostgreSQL中的MVCC

避免读写事务之间的互相阻塞。pg的mvcc机制比起mysql会有表膨胀的问题(xlog)，从而导致占用的磁盘空间比较大，查询比较慢。

一般MVCC有2种实现方法：

• 写新数据时，把旧数据转移到一个单独的地方，如回滚段中，其他人读数据时，从回滚段中把旧的数据读出来，如Oracle数据库和MySQL中的innodb引擎。
• 写新数据时，旧数据不删除，而是把新数据插入。PostgreSQL就是使用的这种实现方法。

两种方法各有利弊，相对于第一种来说，PostgreSQL的MVCC实现方式优缺点如下：

优点

• 无论事务进行了多少操作，事务回滚可以立即完成
• 数据可以进行很多更新，不必像Oracle和MySQL的Innodb引擎那样需要经常保证回滚段不会被用完，也不会像oracle数据库那样经常遇到“ORA-1555”错误的困扰。

缺点

• 旧版本的数据需要清理。当然，PostgreSQL 9.x版本中已经增加了自动清理的辅助进程来定期清理
• 旧版本的数据可能会导致查询需要扫描的数据块增多，从而导致查询变慢

1.多版本

1）数据文件中存放同一逻辑行的多个行版本（称为Tuple）

2）每个行版本的头部记录创建以及删除该行版本的事务的ID（分别称为xmin和xmax）

3）每个事务的状态（运行中，中止或提交）记录在pg_clog文件中

4）根据上面的数据并运用一定的规则每个事务只会看到一个特定的行版本

通过MVCC读写事务可以分别在不同的行版本上工作，因此能够在互不冲突的情况下并发执行。

Tuple头部格式

• 1.t_xmin 存储的是产生这个元组的事务ID，可能是insert或者update语句
• 2.t_xmax 存储的是删除或者锁定这个元组的事务ID（只有xmax为0的元组才是最新数据）
• 3.t_cid 包含cmin和cmax两个字段，分别存储创建/删除这个元组的命令ID
• 4.t_ctid存储用来记录当前元组或新元组的物理位置（由块号，块内偏移组成）。如果这个元组被更新，则该字段指向更新后的新元组；如果这个字段指向自己且t_heap中的xmax字段为空，就说明该元组为最新版本
• 5.t_xvac 存储的是VACUUM FULL 命令的事务ID

2.事务快照SnapshotData

（同mysql的ReadView）

事务快照是用来存储数据库的事务运行情况。一个事务快照的创建过程可以概括为：
- 查看当前所有的未提交并活跃的事务，存储在数组中
- 选取未提交并活跃的事务中最小的XID，记录在快照的xmin中
- 选取所有已提交事务中最大的XID，加1后记录在xmax中
- 根据不同的情况，赋值不同的satisfies，创建不同的事务快照

其中根据xmin和xmax的定义，事务和快照的可见性可以概括为：
- 当事务ID小于xmin的事务表示已经被提交，其涉及的修改对当前快照可见
- 事务ID大于或等于xmax的事务表示正在执行，其所做的修改对当前快照不可见
- 事务ID处在 [xmin, xmax)区间的事务, 需要结合活跃事务列表与事务提交日志CLOG，判断其所作的修改对当前快照是否可见，即SnapshotData中的satisfies。

3.隔离级别的实现

需要注意的是，在PostgreSQL中：

- RU隔离级别不允许脏读，实际上和Read committed一样（与mysql有区别）

- RR隔离级别不允许幻读

根据获取快照时机的不同实现了不同的数据库隔离级别（对应代码中函数GetTransactionSnapshot）：

• 读未提交/读已提交：每个query都会获取最新的快照CurrentSnapshotData
• 重复读：所有的query 获取相同的快照都为第1个query获取的快照FirstXactSnapshot
• 串行化：使用锁系统来实现

参考：

mysql对比：PG和Mysql哪个更加适合企业-阿里云开发者社区 

德哥的postgresql博客：PostgreSQL 9种索引的原理和应用场景-阿里云开发者社区  

压测32C256G / 60W TPC-C ： PgSQL · 性能优化 · PostgreSQL TPC-C极限优化玩法-阿里云开发者社区