【数据库学习】Postgres(PG)原理及底层实现

兔兔西

9632人浏览 · 2023-03-23 09:21:37

兔兔西 · 2023-03-23 09:21:37 发布

1，事务原理

事务（transaction）：
是用户定义的一组数据库操作，要么全做要么全不做，失败即回滚。
事务是恢复和并发控制的基本单元。

保存点（savePoint）
在一个大的事务中，可以把操作过程分成几个部分，第一个部分执行成功后可以建一个保存点，若后面的部分执行失败，则回滚到此保存点，而不必回滚整个事务。

事务的实现即：RDBMS采取何种技术确保事务的ACID特性？

回退（rollback）：
撤销sql执行过程。事务管理可以管理insert、update、delete语句；不能回退create、drop操作。

RDBMS（Relational Database Management System，关系数据库管理系统）
是指包括相互联系的逻辑组织和存取这些数据的一套程序 (数据库管理系统软件)。
关系数据库管理系统就是管理关系数据库，并将数据逻辑组织的系统。

1）事务特性（ACID）

1>原子性（Atomic）

事务是数据库的逻辑工作单位。要么都做，要么都不做。==》通过MVCC保证

2> 一致性（Consistency）==》最终目的

事务完成时，数据必须处于一致状态，数据的完整性约束没有被破坏，事务在执行过程中发生错误，会被回滚到事务开始前的状态。

3>隔离性（Isolation）

事务允许多个用户对同一个数据进行并发访问，而不破坏数据的正确性和完整性。==》用锁和MVCC来保证。

读读不存在并发问题；
读写通过MVCC来解决并发问题；
写写通过加锁来解决并发问题。

4>永久性（Durability）

事务一旦提交，所做修改会永久的保存在数据库中。==》保证了db的可靠性，用WAL日志来实现

其中一致性是事务的最终目的，为了达到一致性需要保证原子性、隔离性、永久性。

那么pg是怎么完成ACID的呢？

2）pg隔离级别

在标准SQL规范中，定义了4个事务隔离级别（由低到高）：

	读未提交（RU级别、read uncommitted）	读已提交（read committed）	可重复读（repeatable read）	序列化（serializable）
允许操作	允许事务读取未被其他事务提交的变更	允许事务读取已经被其他事务提交的变更	事务读取数据时，禁止其他事务对这个字段进行更新	所有事务都一个接一个地串行执行
可能存在问题	脏读、不可重复读、幻读	不可重复读、幻读	幻读==》添加间隙锁解决此类问题	数据安全。但是添加大量行锁会导致大量超时和锁竞争问题。
避免问题
举例	事务读到了其他事务未提交的数据。其他事务回滚导致脏读。	同一个事务读了两次数据，分别读取了其他事务提交的内容，但是两次结果不一致。这就是不可重复读。	事务读了两次数据，不管数据怎么修改，都只读第一次的数据。==》导致幻读：每次select时，mvcc的read view不会变化。但是其他事务做了新增操作，真实的数据和当前的read view不同。
数据库		oracle、pg默认隔离级别	mysql默认隔离级别

pg仅支持2种隔离级别：读已提交（默认）、可串行化。

事务隔离级别的实现：

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

1>读已提交隔离级别（默认）

每个命令都是从一个新的快照开始执行的。

当前事务看不见其它未提交事务的数据；
当前事务可以看到自己未提交的数据；
一个事务里两个select，两次select读到的数据可能不是同一个快照。第二次select的时候会看到其它事务已提交的数据。
同一个事务中，第一次更新之后，其它事务获得锁进行数据的更新，当前事务中的更新操作需要等到其它事务结束或者回滚再进行操作。

为什么使用RC级别而不使用RR级别？
提高并发度并降低死锁概率。

2>可串行化隔离级别（开销大）

每个命令都是从事务开始时的快照开始执行的。

当前事务看不见其它未提交事务的数据；
当前事务可以看到自己未提交的数据；
只读事务不存在冲突：一个事务里两个select，两次select读到的数据一致。
串行化冲突：更新事务与其它更新事务冲突需要重试。
同一个事务中，第一次更新之后，其它事务获得锁进行数据的更新，当前事务中的更新操作需要等到其它事务结束或者回滚再进行操作。如果其它事务回滚，当前事务正常进行；如果其它事务提交，那么当前事务回滚（ERROR: could not serialize access due to concurrent update），需要重试。

3）多版本并发控制（MVCC，Multi-Version Concurrency Control）

MVCC是数据库并发访问时，保证数据一致性的一种方法。实现MVCC的方法有以下两种：

写新数据时，把原数据移到一个单独的位置，如回滚段中，其它用户读数据时，从回滚段中把原数据读出来。（Oracle和Mysql数据库中的InnoDB引擎使用这种方法）
写新数据时，原数据不删除，而是把新数据插入进来。（pg使用这种方法）==》相当于每个事务看到的都是之前一小段时间的数据快照（某一个数据库版本）。

1>原子性保证

事务ID：XID、txid（transaction id）
pg中每个事务开始时，事务管理器都会分配一个唯一id，从3开始递增。
32位无符号整数，取值空间：2^32-1；如果超过范围从头开始算，称为事务回卷。

pg中表中有以下4个内置表字段，每个tuple的更新时是先del旧的再insert新的tuple。

字段	说明	默认值	举例
xmin	insert tuple 时的 xid
xmax	del tuple 时的 xid	0，表示未删除
cmin	事务内部 insert 的命令ID	0，递增
cmax	事务内部 del 的命令ID	0，递增
ctid	磁盘上的物理位置，格式：(page,offset)		(0,1)表示0号page的第1个位置。如果xmax=0，表示最新版本；如果xmax！=0，ctid指向更新后的元组，形成了版本链。

insert tuple：xmin=事务id xmax=0 ctid=(0,1)，指向当前元组
delete tuple：xmax=事务id
update tuple，先delete，再insert： tupleOld的xmax=事务id ctid指向新的元组，tupleNew的xmin=事务id ctid指向当前元组

原子性：通过当前事务id对tuple进行标记，不管是commit还是rollback操作都可以通过xmin和xmax保证事务的原子性。

2>事务隔离性保证

a）不同事务的可见性：xmin xmax

在不同事务中，可以根据xmin和xmax判断事务可见性。

快照（SnapshotData）
维护了以下一些信息：

TransactionId xmin; // 记录了未提交并活跃的事务最小xid，如果t_xid < xmin则元组数据已提交：可见
TransactionId xmax; //记录了已提交事务最大xid+1，如果t_xid >= xmax 则元组未提交：不可见
TransactionId *xip; // 活动事务id列表

对于t_xid在[xmin, xmax)之间数据，需要结合clog日志判断其修改的数据是否可见

每次select获取当前db SnapshotData，判断数据的可见性。
区分元组t_xmin和快照s_xmin，对于当前元组数据：

t_xmin<s_xmin && t_xmax == 0，元组插入且事务已提交，可见；
t_xmin<s_xmin && t_xmax !=0 && t_xmax <s_xmin，元组已删除，不可见；
t_xmin<s_xmin && t_xmax !=0 && t_xmax >s_xmax，元组删除但未提交，可见；
其它：需要结合clog进行判断。

b）同一事务的可见性：cmin cmax

cmin、cmax 用于同一个事务中实现版本可见性判断

3>事务持久性保证

a）clog（commit log）日志

clog（commit log）：
pg记录事务状态。包括以下四种：
transaction_status_in_progress =0x00：表示事务正在进行中
transaction_status_committed =0x01：表示事务已提交
transaction_status_aborted =0x02：表示事务已回滚
transaction_status_sub_committed =0x03：表示子事务已提交

结构：数组，由缓存(SLRU Buffer Pool )中一系列的8K页面组成。
数组下标对应事务txid，数组内容则为事务状态。每个事务状态2bit，一个块8KB可以存储8KB*8/2 = 32K个事务的状态。
当shutdown pg或Checkpoint运行时，CLOG数据会由内存写入pg_clog（pg 10后叫pg_xact）目录中的文件。这些文件被命名为0000,0001，最大256KB。当pg启动时，会加载这些文件用于初始化CLOG。

CLOG数据会不断增长，但并非所有数据都是必要的，清理过程也会定期清理掉不再需要的CLOG页面和文件。

pg可以通过调用三个内部函数——TransactionIdIsInProcess、TransactionIdDidCommit和TransactionIdDidAbort，读取CLOG返回所请求事务状态。

b）Hint Bits

判断元组的可见性非常频繁，每次从缓存或者磁盘读取clog信息依然不够高效，引入了Hint Bits概念。t_informask中存储的一些标志位保存了插入/删除该元组的事务的状态。

元组中的 Hint Bits采用延迟更新策略，并不会在事务提交或者回滚时主动更新所有操作过的元组Hint Bits。
等到第一次访问（可能是VACUUM，DML或SELECT）该元组并进行可见性判断时：

如果Hint Bits已设置，直接读取Hint Bits的值。
如果Hint Bits未设置，则调用函数从CLOG中读取事务状态。如果事务状态为COMMITTED或ABORTED，则将Hint Bits设置到元组的t_informask字段。如果事务状态为INPROCESS，由于其状态还未到达终态，无需设置Hint Bits。

4>MVCC的优缺点

pg在事务提交前，只需要访问原来的数据；提交后，系统更新元组的存储标识，直到Vaccum进程回收为止。

相比InnoDB和Oracle，pg多版本优势在于：

事务回滚可以立即完成；
数据可以进行很多更新，不必像Oracle和InnoDB那样需要经常保证回滚段不会被用完，也不会像Oracle数据库那样，经常遇到ORA-1555错误的困扰。

劣势在于：

旧数据需要Vaccum清理。
旧版本数据的存在降低查询速率，需要扫描更多的数据块。

4）表膨胀问题

1>Visibility Map机制：

官方文档：Routine Vacuuming
Visibility Map中标记了哪些page中是没有dead tuple的，数据量很小可以cache到内存中。这有两个好处：

当vacuum时，可以直接跳过这些page
进行index-only scan时，可以先检查下Visibility Map。这样减少fetch tuple时的可见性判断，从而减少IO操作，提高性能

2>vacuum（表空间优化、收缩表）

VACUUM寻找不再被别的任何事务任何人看到的行。这些行可能是页的中间几行。

一般pg会有个异步任务自动执行，如果突然有大量数据执行update全表等操作，会让磁盘空间瞬间翻倍，需要手动执行vacuum，但是这个操作会锁表，用的时候慎重。

--加表名指定表 不加表名表示全局处理
vacuum t_lxs;
--获取表空间大小
--vacuum允许 pg重用该空间，但是，它不会将该空间返回给操作系统。
SELECT pg_relation_size('t_lxs');--8192
DELETE FROM t_lxs;
--如果从表中的某个位置开始，ALL rows are dead，VACUUM可以截断表。
VACUUM t_lxs;
SELECT pg_relation_size('t_lxs');--0
--但是大表末尾总有那么几行数据，靠VACUUM几乎很难释放空间。通过使用VACUUM FULL重排数据的磁盘位置，可以解决表膨胀的问题。但是这个操作会直接锁表。一定要在业务低频使用时进行。
VACUUM FULL t_lxs;

5）事务id回卷

1>系统预留事务ID：0 1 2

0 1 2是系统预留ID，这三个ID比任何普通xid都要旧。

InvalidTransactionId=0 无效的事务ID；表示还未分配事务ID。
BootstrapTransactionId=1 表示系统表初始化时的事务ID；表示Initdb服务正在初始化系统表。
FrozenTransactionId=2 冻结的事务ID。

2>回卷

xid一直递增达到2^32为最大值，然后继续从3开始，以前的xid比新的xid大，回卷后无法按xid大小判断数据可见性。

回卷问题导致事务id可见性的判断怎么解决？

pg规定，最早和最新两个事务之间年龄差对多为2³¹。==>事务冻结来保证
即：xid空间虽然有2³²，作为一个环被一分为二，对某个特定的xid，其后2³¹个xid属于未来，均不可见；其前2³¹个xid属于过去，可见。
俩个事务之间id的计算使用2^31取模的方法来进行事务的比较：diff = (int32) (id1 - id2);来判断id1<id2

举例：txid=100的事务，[101,2³¹+100] 均为不可见事务；[2³¹+101, 99] 均为可见事务。
在这里插入图片描述代码解析：

/*
 * TransactionIdPrecedes --- is id1 logically < id2?
 */
bool TransactionIdPrecedes(TransactionId id1, TransactionId id2) // 结果返回一个bool值
{
	int32		diff;
	if (!TransactionIdIsNormal(id1) || !TransactionIdIsNormal(id2)) //若其中一个不是普通id，则其一定较新（较大）
		return (id1 < id2);

	diff = (int32) (id1 - id2);
	return (diff < 0);
}

普通xid比较：diff = (int32) (id1 - id2);
注意：int 32带符号，第一位表示符号位，取值范围为 [-2^(n-1), 2^(n-1)-1]，即[-2³¹,2³¹-1]。
当两个txid（32位）相减 (id1 - id2)>2³¹时，发生回卷，转换位int32时符号位从0变成了1，(int 32) (id1 - id2)是个负数。此时id1<id2==true，id1事务更小更旧，id1对id2是可见的。
举例：id1=2³¹+101，id2=100。id1-id2=2³¹+1，int32转换后这个值是-1，小于0，因此id1的事务反而要旧，id2可见id1，id1不可见id2。
但是如果xid=100确实是很久很久的事务，那么对于2³¹+101这个事务看见，此时上面的判断就是错的。为了避免这种问题，pg必须保证一个数据库中两个有效的事务之间的年龄最多是2³¹（同一个数据库中，存在的最旧和最新两个事务txid相差不得超过2³¹）。这时靠事务冻结来保证。

3>事务冻结（freeze）

当超过2³¹时，就把旧的事务换成一个FrozenTransactionId=2的特殊事务，当正常事务ID与冻结事务ID比较时，会认为正常xid比FrozenTransactionId更新。

a）freeze实现：

pg9.4之前freeze方法：
直接将符合条件的元组的t_xmin设置为2，回收原来的xid。但这样实现的问题是：
一）当前可见的数据页需要全部扫描，带来大量的IO扫描；
二）符合条件的元组需要更新xmin，造成大量脏页，带来大量IO
pg9.4后对freeze优化：
不直接修改t_xmin，而是：
一）只更新元组头结点的t_infomask为HEAP_XMIN_FROZEN，表示该元组已经被冻结过（frozen）；
二）有些插入操作，也可以直接将记录置为frozen，例如大批量的COPY数据，insert into等；
三）如果整个page所有记录已经frozen，则在vm文件中标记为FROZEN，冻结清理会跳过该页，减少了IO扫描。

b）freeze优化：

freeze是被动触发的，可以调节pg的一些参数优化freeze，更多时候提倡用户进行主动预测需要freeze的时机，选择合适的时间（比如pg负载较低的时间）主动执行vacuum freeze命令。
目前已经有很多实现好的开源PostgreSQL vacuum freeze监控管理工具，比如flexible-freeze，能够：确定数据库的高峰和低峰期；在数据库低峰期创建一个cron job执行flexible_freeze.py；flexible_freeze.py会自动对具有最老XID的表进行vacuum freeze。

2，SQL执行原理

1）引擎执行SQL过程

客户端连接
客户端发送一条查询给服务器；
查询缓存
服务器先检查查询缓存query cache，如果命中了缓存，立即返回缓存结果。
解析器->预处理器->查询优化器
1）SQL解析parsing
解析器扫描查询并检查语法。正确则将查询文本转换成解析树。
2）预处理transition
3）优化器optimization
遍历语法树，生成若干执行计划比计算每个算子执行成本；
查询执行引擎
根据最优执行计划，调用存储引擎的API来执行分布（distribution）查询；
将结果返回给客户端。
关闭连接

2）算子原理

PG目前有19个查询算子。
每个执行计划都是一颗树，执行时自顶向下开始，自底向上计算，最终得到查询结果。
在这里插入图片描述

1>Sort

输入：1个结果集；
场景：order by、Unique算子、
原理：

sort_mem可以调整：
如果结果集的大小超过sort_mem，Sort会将输入集分发到已排序工作文件的集合中，然后再次将工作文件重新合并在一起。如果结果集适合sort_mem*1024字节，则使用 QSort 算法在内存中进行排序。

2>Seq Scan（全表扫描）

场景：查询未匹配到合适的索引、order by操作
原理：
从表头扫描到表尾，每一行数据执行where过滤，将得到的结果放入结果集中。
输出：过滤后的结果集；排序：表顺序。

3>Index Scan（索引扫描）

原理：

如果where针对索引过滤了，会直接限定索引查询范围。
返回结果排序：索引顺序。
并非所有索引都是可扫描的。可以扫描B-Tree、R-Tree和GiST索引类型；哈希索引不能。

4>Unique算子

场景：DISTINCT
输入：已排序的结果集；
输出：消除值重复的行;
原理：Sort->Seq Scan;

5>LIMIT算子

场景：limit、limit offset
输入：1个结果集；
输出：结果集的前n行，直接丢掉剩余的行

6>Group

场景：GROUP BY
原理：Sort->Seq Scan

7>Aggregate

场景：聚合函数：AVG(), COUNT(), MAX(), MIN(), STDDEV(), SUM(), and VARIANCE()
原理：

如果没有group by：Aggregate->Seq Scan;
读取结果集，计算，输出一个值；
如果有group by：Aggregate->Group->Sort->Seq Scan;

8>Append

场景：UNION、表继承
输入：2+个结果集
原理：返回第一个输入集的所有行，然后拼接第二个输入集的所有行。

9>Result

性能影响不大。
场景：

常量查询；

select timeofday();
where 1==1；

辅助Append算子；

10>Nested Loop（嵌套循环）

场景：inner joins, left outer joins, and unions；
因为不遍历整个内表，不能处理其它连接：full, right join

SELECT * FROM customers, rentals
WHERE customers.customer_id = rentals.customer_id;

原理：
第一个表作为外表，全部加载进内存，挨个轮询遍历；
第二个表作为内表，搜索（全表扫描或者索引扫描）满足连接条件的行
场景：
适合两个表的数据量都比较少的情况(最简单的 table join 方式)。
1）外表为小表，且过滤后的数据量较少。
2）内表的关联列上有高效索引(主键或者唯一性索引)。

11>Merge Join

输入：2个表，一个外表一个内表；
原理：
1）两个表按连接字段排序：index scan->sort
2）两个表分别按行读取，2个指针分别读外表、内表，同上指针指向相同的连接字段；
由于有序所以只需要同步遍历两张表就可以连接。
场景：inner joins, outer joins, and unions
对于那些连接列上有索引的表（已排好序）Merge JOIN性能会优于Hash JOIN。

12>Hash算子、 Hash Join算子

两个算子一起工作。

场景： inner joins, left outer joins, and unions
hash join一般针对那些没有索引或者其中任一个有索引的大表。

--所有等值连接都可以转换为inner joins
SELECT * FROM customers, rentals
WHERE customers.customer_id = rentals.customer_id;

原理：
1）加载内表（小表）进内存，建立hash索引：散列表；
表很大的情况下并不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要有较大的临时段从而尽量提高I/O 的性能。
2）遍历外表，每读一行，对连接列计算hash值，匹配对应的散列表，连接。

13>Subquery Scan算子、Subplan算子

Subquery Scan用于union子句；
Subplan用于子选择。
场景：用于内部标记，将输入集中每一行加入结果集。

14>Tid Scan

用于事务中检索元组id（ctid），很少用到。

15>Materialize（物化）

场景：Merge join算子对于内部输入集不可重新定位的情况，认为物化一个子选择比重复选择代价更高。

16>Setop

Setop Intersect 算子
遇到 INTERSECT时使用。
Setop Intersect All 算子
遇到 INTERSECT ALL时使用。
Setop Except 算子
遇到 EXCEPT时使用。
Setop Except All 算子
遇到 EXCEPT ALL时使用。

3）where原理

select的别名是否可以作为where查询条件？
不能，因为执行计划中where在select之前。

4）缓存

shared_buffers所代表的内存区域可以看成是一个以8KB的block为单位的数组，即最小的分配单位是8KB。这正好是一个page的大小，每个page以page内部的元数据(Page Header)互相区分。

这样，当Postgres想要从disk获取(主要是table和index)数据(page)时，他会(根据page的元数据)先搜索shared_buffers，确认该page是否在shared_buffers中，如果存在，则直接命中，返回缓存的数据以避免I/O。
如果不存在，Postgres才会通过I/O访问disk获取数据(显然要比从shared_buffers中获取慢得多)。

1>缓存淘汰

以页为单位，cache满的时候，会淘汰不常用的页。淘汰后的数据则进行刷盘，但是一般数据都是通过WAL+Checkpointer保证修改的数据刷盘，而不用等到cache满了才进行刷盘。

2>如何观察是否走cache

使用explain时，Shared read表示来自disk，Shared hit则是已经在cache中。
在这里插入图片描述

3，进程架构模型

启动pg，主进程为Postmaster（pg的bin目录下，是一个指向Postgres的链接）。Postmaster是整个数据库实例的总控进程，负责启动和关闭数据库实例，同时fork出一些与数据库实例相关的辅助进程，并对其进行管理。

辅助进程	作用	配置
Logger	系统日志	参数logging_collect设置为on时启动该辅助进程

每次客户端与数据库建立连接时，pg数据库都会启动一个服务进程来为该连接服务，故而是进程架构模型，而MySQL是线程架构模型。当某个服务进程报错时，Postmaster主进程会自动完成系统恢复，恢复过程中停掉所有的服务进程，然后进行数据的一致性恢复，恢复完成后数据库才能接受新的连接。

1）autovacuum进程

autovacuum 是 postgresql 里非常重要的一个服务端进程，能够自动运行，在一定条件下自动触发对 dead tuples 进行清理并对表进行分析。
在pg中更新、删除行后，数据行并不会马上从数据块中清理掉，而是需要等VACUUM时时清理。为了加快VACUUM速度并降低对系统I/O性能的影响，pg8.4.1之后为每个数据块文件加了一个后缀为“_vm”的文件（可见性映射表文件，VM文件）。这个文件为每个数据块存储了一个标志位，标记数据块中是否存在要清理的tuple。

VACUUM有两种方式：

Lazy VACUUM ：使用VM文件，扫描部分数据块。
Full VACUUM ：全量扫描数据块。

vacuum相关的配置：

参数名	说明	优化思路
autovacuum	默认为on，表示是否开起autovacuum。当需要冻结xid时，尽管此值为off，PG也会进行vacuum。
autovacuum_naptime	下一次vacuum的时间，默认1min	通过缩短实际，调整回收频率，减少每次回收量，可以减小wal压力
log_autovacuum_min_duration	向日志打印autovacuum的统计信息（以及资源消耗），大于阈值，输出这次autovacuum触发的事件的统计信息。 “-1”表示不记录。“0”表示每次都记录。
autovacuum_max_workers	最大同时运行的worker数量，不包含launcher本身。	CPU核多、IO优秀时，当DELETE\UPDATE非常频繁时适量调多点。注意最多可能消耗这么多内存： # autovacuum_max_workers * autovacuum mem(autovacuum_work_mem)
autovacuum_vacuum_threshold	默认50。与autovacuum_vacuum_scale_factor（默认值为20%）配合使用。当update,delete的tuples数量超过autovacuum_vacuum_scale_factor*table_size+autovacuum_vacuum_threshold时，进行vacuum。	改小可以降低vacuum触发条件，提高vacuum频率
autovacuum_analyze_threshold	默认50。与autovacuum_analyze_scale_factor（默认10%）配合使用。当update,insert,delete的tuples数量超过autovacuum_analyze_scale_factor*table_size+autovacuum_analyze_threshold时，进行analyze。	改小可以降低vacuum触发条件，提高vacuum频率
autovacuum_freeze_max_age和autovacuum_multixact_freeze_max_age	前者200 million，后者400 million。离下一次进行xid冻结的最大事务数。如果表的事务ID年龄大于该值, 即使未开启autovacuum也会强制触发FREEZE，并告警Preventing Transaction ID Wraparound Failures。	设置较大值，减少因事务id消耗造成全表扫描的频率。（1000million、1200million）
autovacuum_vacuum_cost_delay	如果为-1，取vacuum_cost_delay值。autovacuum触发的vacuum、freeze、analyze的平滑化调度。	设置过大，会导致AUTOVACUUM launcher触发的vacuum耗时过长。特别是大表，耗时会非常长，可能导致膨胀等问题。可以调小一点，0.
autovacuum_vacuum_cost_limit	如果为-1，到vacuum_cost_limit的值，这个值是所有worker的累加值。
vacuum_freeze_table_age	当表的年龄大于vacuum_freeze_table_age，则自动转换成vacuum freeze	调高可以降低vacuum freeze的频率
vacuum_multixact_freeze_table_age	当表的年龄大于autovacuum_freeze_max_age，也会强制触发vacuum freeze	调高可以降低vacuum freeze的频率

如果开启了autovacuum，当垃圾记录数大于 autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor*reltuples ，autovacuum launcher触发普通的vacuum。
当表的年龄大于vacuum_freeze_table_age，则自动转换成vacuum freeze。
如果开启了autovacuum，当新增记录数大于autovacuum_analyze_threshold + autovacuum_analyze_scale_factor*reltuples，autovacuum launcher触发analyze。
即使没有开启autovacuum，当表的年龄大于autovacuum_freeze_max_age，也会强制触发vacuum freeze。

4，pg物理存储结构：堆表结构

Relation：表（table）或索引（Index）。

Page：磁盘中的数据块。

Buffer：内存中的数据块。

1）堆表结构

每个表由若干文件组成
表大小超过 RELSEG_SIZE 个块就会被切分成多个文件；
堆表和索引是分开存储的，而mysql是索引组织的数据存储。
pg的索引存储：索引、tuple地址。
每个文件由若干块(block)构成；
每个数据块存储了多行数据（tuple）。

1>base目录

每个数据库，对应$PGDATA/base/oid下的一个子目录。

#查看各个数据库的oid
testdb=# select oid, datname from pg_database;
  oid  |  datname  
-------+-----------
 13025 | postgres
 16384 | testdb
     1 | template1
 13024 | template0
(4 rows)

每张表，对应$PGDATA/base/{dboid}/{relfilenode}
1）relfilenode一般情况下和和tboid一致，但有些情况下也会变化，如TRUNCATE、REINDEX、CLUSTER以及某些形式的ALTER TABLE。
2）在$PGDATA/base/{dboid}中通常会包含三种文件：

文件类型	举例	说明
表数据及索引文件	`16385`	文件最大1GB，超大文件会划分成1GB大小的段：`filenode、filenode.1、filenode.2`
空闲空间映射文件	`16385_fsm`	每一个堆和索引关系（除了哈希索引）都有一个空闲空间映射（FSM）来保持对关系中可用空间的跟踪。
可见性映射文件	`16385_vm`	提供当前所有page与活动事务可见的元组

--查询表所在磁盘的位置
select pg_relation_filepath('表名')
-- out
base/16384/50947

--查看表可见的block
testdb=# SELECT * FROM pg_visibility('testtb2');
 blkno | all_visible | all_frozen | pd_all_visible 
-------+-------------+------------+----------------
     0 | t           | f          | t
(1 row)

2）数据块（页）

数据块（页）大小为 BLCKSZ，默认 8K，最大为32K。
在这里插入图片描述

块的结构：

块头(Page Header) ：
a. 块的checksum值；
b. 空闲空间的起始位置和结束位置；
c. 特殊数据的起始位置；
d. 其它信息。
行指针：向后顺序排列。是一个32bit的数字，具体结构：
a. 行内容偏移量，15bit；能表示的最大偏移量是2¹⁵，因此pg中块最大为32kb
b. 指针的标记，2bit；
c. 行内容的长度，15bit。
空闲空间：行数据指针和行数据内容之间的空间。
行内容：从块尾向前反向排列。

-- 查询表页数、行数
SELECT relpages as 页个数, reltuples as 行个数 FROM pg_class WHERE relname = '表名'

-- 查询占空间大小(排名前20) - 待考证
SELECT table_schema || '.' || table_name                                                         AS table_full_name,
       pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size
FROM information_schema.tables
ORDER BY pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC
limit 20;

3）Tuple（数据行、元组）

pg中元组由三部分组成——元组头结点、空值位图、用户数据。
在这里插入图片描述

HeapTupleHeaderData结构：Database Page Layout，pg提供了pageinspect插件，可查看指定表对应的page header内容。
相关字段说明：

字段	说明	备注
t_xmin	保存插入该元组的事务txid（该元组由哪个事务插入）	对应pgclass内置字段xmin
t_xmax	保存更新或删除该元组的事务txid。若该元组尚未被删除或更新，则t_xmax=0，即invalid	对应pgclass内置字段xmax
t_cid	保存命令标识（command id,cid），指在该事务中，执行当前命令之前还执行过几条sql命令（从0开始计算）	对应pgclass内置字段cmin cmax
t_ctid	一个指针，保存指向自身或新元组的元组的标识符（tid）	对应pgclass内置字段ctid
t_xvac	存储的是VACUUM FULL 命令的事务ID
t_infomask2	number of attributes, plus various flag bits
t_infomask	各种标志位，标识元组的属性、状态	比如：是否具有空属性、是否具有变长的属性、是否包含外部存储的字段、事务提交状态
t_hoff	offset to user data，行header的长度

5，锁机制

使用多版本并发控制（MVCC）比锁定模型（mysql的锁机制）的优势：读锁和写锁不再互斥。

1）pg常见锁

Postgres 有 3 种锁机制：表级锁，行级锁和建议性锁。

1>表锁

表锁由内置的 SQL 命令获得的，同时可以通过锁命令来明确获取。

表锁	获得情况	冲突对象	备注
访问共享（ACCESS SHARE）	SELECT读表	ACCESS EXCLUSIVE
行共享（ROW SHARE	SELECT FOR UPDATE 和 SELECT FOR SHARE 读表	EXCLUSIVE、 ACCESS EXCLUSIVE
行独占（ROW EXCLUSIVE）	UPDATE、INSERT 和 DELETE 读表	SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE
访问独占（ACCESS EXCLUSIVE）	ALTER TABLE，DROP TABLE，TRUNCATE，REINDEX，CLUSTER 和 VACUUM FULL 命令读表时获得	与所有模式冲突(包括其自身)	LOCK 命令的默认模式。保证事务是可以访问该表的唯一事务。
共享更新独占（SHARE UPDATE EXCLUSIVE）	VACUUM（不含FULL），ANALYZE，CREATE INDEX CONCURRENTLY，和一些 ALTER TABLE 的命令获得该锁
共享（SHARE）	CREATE INDEX 命令在查询中引用的表上获得该锁。
共享行独占（SHARE ROW EXCLUSIVE）	不被任何命令隐式获取。
排他（EXCLUSIVE）	这个锁模式在事务获得此锁时只允许读取操作并行。它不能由任何命令隐式获取。

2>行锁

行级锁不影响对数据的查询，它们只阻塞对同一行的写入。
行锁容易发生死锁。==》 pg自动侦测死锁条件，然后退出其中一个事务从而允许其它事务完成来解决死锁问题。

9.1版本的行锁主要分为两类，后续版本有基于此的扩充，但是大差不差，都是功能的细分。

锁分类	获得方式	备注	版本
排他锁	SELECT FOR UPDATE	即使没有实际的修改，也会获取到锁
共享锁	SELECT FOR SHARE	允许其它事务同时获取共享锁，此时，事务的更新、删除或排他锁都不被允许。

3>页面级别的共享/排他锁

用于控制共享缓冲池中表页面的读/写。这些锁在抓取或者更新一行后马上被释放。不需要过多关注

2）谓词锁定（pg不支持）

谓词锁定：
禁止当前事务插入另一个并发事务的where条件，直到其它任务提交

--隐患：当前事务的select中的where条件与另一个并发事务的insert内容相同，导致并发事务：A和B的执行顺序不同结果不同。

--事务1
SELECT ... WHERE class = 1;
Insert class = 10 ...
--事务1
SELECT ... WHERE class = 10;
Insert class = 1 ...

如何处理这种情况？

insert select 合成一条sql
将业务逻辑融入了sql中，扩展性差。
加锁
CAS

3）使用

1>死锁的查询与释放


--查死锁
select pid from pg_locks where relation= (
	select oid from pg_class where relname='表名'
);
--释放锁
--方式1：
select pg_cancel_backend('上面查询到的pid');
--方式2：
select pg_terminate_backend('上面查询到的pid');

4）服务器参数说明

通过show 参数 sql语句可以查看当前设置值，修改后需要重启计算机才可以生效。

参数	默认值	说明	调优
deadlock_timeout	1s	进行死锁检测之前在一个锁上等待的总时间（以毫秒计）。增加这个值就减少了浪费在无用的死锁检测上的时间，但是减慢了报告真正死锁错误的速度。	在一个高负载的服务器上，可能需要增大它。这个值的理想设置应该超过通常的事务时间
log_lock_waits	off		如果一个会话等待某个类型的锁的时间超过deadlock_timeout的值，该参数决定是否在数据库日志中记录这个信息。
max_locks_per_transaction	64	共享锁表跟踪在 `max_locks_per_transaction * ( max_connections + max_prepared_transactions )` 个对象（如表）上的锁。因此，在任何一个时刻，只有不超过这么多个可区分对象能够被锁住，超过这个数量会报错。	如果想在一个事务中使用很多不同表的查询（例如查询一个有很多子表的父表），则需要提高这个值。
max_connections	151	表示允许客户端并发连接的最大数量	最小值为1，最大值为100000
max_prepared_transactions		设置可以同时处于prepared状态的事务的最大数目	设置为0表示不使用预备事务；否则max_prepared_transactions至少与max_connections一样大，以便每个会话都可以有一个待处理的预备事务。

6, 数据库存储原理

1）硬盘

寻址慢（ms级）
带宽（G/MBPS）

2）内存

寻址快（ns级）
带宽 = 带宽×总线宽度÷8(单位换算) 很快

3）4k对齐

硬盘中文件保存的基本单元是扇区，不管文件大小，都要占用一个扇区的空间。
机械硬盘（Hard Disk Drive，HDD）一个扇区是512字节，固态硬盘（SSD）一个扇区是4K字节。
微软操作系统常用的NTFS格式，默认扇区大小512字节，并且规定前63个扇区是保留的。
如果“4K不对齐”，那么从第63个扇区结束，往后的每一个簇都会跨越两个物理单元，占据前一个单元的一部分和后一个单元的一部分。
而“4K对齐”主要是将硬盘的逻辑扇区从第64个扇区开始对齐，即操作系统从第64个扇区开始读写数据，这样就跨过了63扇区的特性，解决了每一个簇跨两个物理单元读写的问题。
SSD的最小扇区为4k（1页），写入时不允许像机械硬盘一样覆盖写入，而是擦除写入。如果4k不对齐，数据跨页，那么每次写操作都会写入2个不同的扇区，需要写入两次，造成性能下降。4对齐保证了操作系统的逻辑单元与ssd硬件的闪存页对应起来，写入4k数据只需要写一次即可。
4k对齐提高硬盘工作效率、提高硬盘工作寿命。
4k对齐本质就是扇区对齐，随着扇区变大，会有8k对齐、16k对齐…

4）数据库查询优化

随着文件变大，速度查询变慢？-> 访问硬盘受到IO瓶颈影响。
dataPage：数据库合并文件到页（每一页4k），每次读取一页数据只需要1次IO（多个文件只需要一次IO）。->查找一个文件还是要经过很多次IO读入内存进行筛选，速度依然很慢。
建立索引：4k存储模型，指向很多dataPage。也存储在磁盘中。
关系数据库行存储：必须给出每个列的类型->字节宽度确定了，空数据填充->行宽度确定->增删改不需要移动数据，直接复写即可。
B+Tree：只要查询命中索引，那么直接命中叶子结点，确定dataPage范围。->减少磁盘寻址时间
数据在磁盘和内存中大小不一样。
因为磁盘中没有指针，数据会比内存中膨胀（同一个数据要写很多遍）。

7，逻辑解码（Logical Decoding）

pg9.4主要功能，是向最终实现逻辑复制迈出的一大步。

1）功能

从WAL（预写式日志）日志中，读取数据库更新信息；
“翻译”（Decode）成逻辑的形式；
例如一个元组流或者 SQL 语句流。可发送到远程从库做数据同步。

WAL日志（Write-Ahead Logging，预写日志）

二进制文件；
记录了db数据文件的每次变更，最初设计是为了db异常崩溃后，能够重放最后一次checkpoint之后的日志文件，把数据块推到最终的一致状态，避免数据丢失和不一致。
如果备份数据块数据不一致，也可以通过重放WAL日志恢复数据到某个时间点（check point）。==>基于时间点备份（PITR，Point-in-Time Recovery）
目录
pg_wal下。（10版本之前是pg_xlog目录）

2）场景

主从同步等复制方案；
DBA在数据库宕机，并发生主从切换后，检查原主库有哪些更新宕机前未同步到从库，并手动同步来弥补丢失的（已提交）的更新。

主数据库（Primary db，Master db）
备数据库（Standby db）

审计

3）复制槽（Replication Slot）

可以用于逻辑复制：Logical Replication Slot，也可以用于物理复制：Physical Replication Slot。这里主要讲逻辑复制相关的概念。

一个槽对应一个消费者，同一时间只能有一个消费者消费数据，不同时间可以对应不同的消费者。
所以在使用逻辑复制时，设置 max_replication_slots >=订阅者数量。
pg启动时，预先在共享内存中分配内存。
数据持久化，通过标记LSN消费位置避免重复消费。
每一个槽有自己的状态，允许不同的消费者从该数据库的更改流中的不同点开始接收更改。在检查点持久化槽到pg_replslot/目录中的二进制状态文件。如果db崩溃，槽可能会回到一个较早的 LSN，有重复消费的问题，此时通过restart lsn域记录消费者消费位置。
由于槽不知道消费者状态，即使无连接使用，WAL 和来自于系统目录的行就不能被VACUUM移除，导致资源浪费。所以无用的槽应该删除它。

【 restart lsn】：
从库apply的日志的编号作为restart lsn保存在replication slot中并存到磁盘, 主库会保留日志不被archive，容易导致磁盘爆满

4）主从复制方案

对比项	物理复制（流复制）	逻辑复制
复制范围	实例级别（不可选）	表级（可选）
原理	主库将预写日志（WAL）流发送给备库，备库接收到WAL日志流后进行重放	逻辑复制将 WAL 二进制文件解析成一定格式的逻辑变化信息，比如从 WAL 中解析指定表上发生的 DML 逻辑变化信息，之后主库将逻辑变化信息发送给备库，备库收到 WAL 逻辑解析信息后再应用日志。（不是把主库收到的sql直接执行一遍）
DDL操作	支持对DDL操作进行复制，比如主库上新建表、给已有表加减字段时会自动同步到备库	主库上的 DDL 操作不会复制到备库。另外还有很多其它的限制，很多sql操作不支持复制
读写	主库可读可写，从库只读	从库可读可写
版本	大版本必须一致	支持跨大版本

1>逻辑复制（base-file，WAL日志归档）

选择部分表去复制。

i>原理

master db写WAL日志；
master db解析WAL日志成一定的逻辑变化，scp到standby db；
==》备库会落后主库一个WAL日志文件，具体落后多长时间取决于master db生成一个wal文件的时间。
standby db收到 WAL 逻辑解析信息后执行。

ii>冲突

备库修改数据后，主库同步过来数据也会被更新。
如果同步数据违反任何限制，复制立刻停止，等待手动修复。
主从事务独立；
当复制UPDATE 或者DELETE操作时，丢失的数据不会产生冲突，这样的操作将被忽略。

iii>限制

10.1版本

不复制数据库模式和DDL命令；
不复制TRUNCATE；
不复制大对象（oid）字段；
复制只能从基表到基表；
分区表不可复制，但是可以一对一地复制分区层次结构。
外部表不可复制。
序列不可复制。
视图、物化视图不可复制。

11.2版本

在之前限制的基础上，支持TRUNCATE命令的复制。

iv>发布节点配置

# 发布节点 设置成logical才支持逻辑复制
wal_level = logical

# 设置值需大于订阅节点的数量
max_replication_slots = 160

# 由于每个订阅节点和流复制备库在主库上都会占用主库上一个WAL发送进程，因此此参数设置值需大于max_replication_slots参数值加上物理备库数量
max_wal_senders = 180

# 设置逻辑复制进程数，应大于订阅节点的数量，并且给表同步预留一些进程数量，此参数默认值为4
max_logical_replication_workers = 190

# 设置系统支持的最大后台进程数，默认值为8，如果有备库，备库上此参数必须大于或等于主库上的此参数配置值，此参数调整后需重启数据库生效。
max_worker_processes = 200

发布节点上逻辑用户至少需要 replication 角色权限
发布节点上需要发布的表如果需要将 UPDATE/DELETE 操作同步到订阅节点，需要给发布表配置复制标识（主键字段或者全表字段）
发布时可以选择发布 INSERT、UPDATE、DELETE，DML 操作中的一项或多项，默认是发布这三线
支持一次发布一个数据库中的所有表
一个数据库中可以有多个发布
发布节点配置文件 pg_hba.conf 需做相应配置，允许订阅节点链接
发布表上的 DDL 操作不会自动同步到订阅节点，如果发布节点上发布的表执行了 DDL 操作，需要手工给订阅节点的相应表执行 DDL

v>订阅节点配置

一个数据库中可以有多个订阅
必须具有超级用户权限才可以创建订阅
创建订阅时需指定发布节点连接信息和发布名称
创建订阅时默认不会创建发布节点的表，因此创建订阅前需要手工创建表
订阅支持启动、停止操作
订阅节点的表结构建议和发布节点一致，尽管订阅节点的表允许有额外的字段
发布节点给发布增加表时，订阅节点需要刷新订阅才能同步新增的表

vi>常用命令

发布节点：

--查看发布
SELECT * FROM pg_publication;
SELECT * FROM pg_user;
--查看发布表
SELECT * FROM pg_publication_tables;
--查看消费延迟
select pid, client_addr, state, sync_state,
pg_wal_lsn_diff(sent_lsn, write_lsn) as write_lag,
pg_wal_lsn_diff(sent_lsn, flush_lsn) as flush_lag,
pg_wal_lsn_diff(sent_lsn, replay_lsn) as replay_lag
from pg_stat_replication;

订阅节点:

select * from pg_subscription;

2>物理复制（streaming replication，流复制）

复制整个库的实例。

pg9.x版本之后支持。
有两种方式：

同步方式，几乎没有延时。
异步方式，落后时间取决于网络延迟和standby的i/o能力。

实现：
发布订阅配置；
主库将预写日志（WAL）流发送给备库，备库接收到WAL日志流后进行重放；

3>复制架构

i>直连复制架构

在这里插入图片描述

ii>级联复制架构

在这里插入图片描述
【备库1（级联备库，Cascading standby）】一方面接收来自【主库】发送的 WAL ，另一方面将 WAL 日志发送给【备库2】。

优点：

小幅降低主库 CPU 压力
减少主库带宽压力
异地建立多个备库时，这种部署方案将大幅降低跨机房网络流量

5）使用

8，通信原理

1）客户端连接

客户端连接pg服务器
后端建立一个backend（后台进程）为其服务
后端通过ps过滤本机ip可以查看到连接。
PostgresMain()中循环获取sql命令，每次读取8k大小。
如果客户端发起Query Cancellation（取消查询，如psql下Ctrl+c），则pg后台进程收到sigint信号，触发中断处理机制。
此时回滚当前事务、退出后台进程、断开连接。

9，数据清理机制

1）日志创建清理机制

pg的日志创建、保持、清理机制中，涉及以下几个模块：

1>日志写入（WAL writer）进程

负责异步把WAL日志刷入磁盘；与此同时，其他普通后台进程，也可能会同步的将WAL日志刷入磁盘。
该进程在写日志时，如果日志文件不存在，则会新建文件。

2>检查点（checkpointer）进程

检查是否有checkpoint timeout或checkpoint request信号；
检查上次checkpoint后是否有wal日志写入，没有则直接结束。
在WAL中插入检查点日志信息。
处理上一次检查点前的日志指针（理论上这之前的日志文件都可以删除了，实际上做日志文件预分配），回收并将老文件rename成新文件。上一次检查点之后的日志不会删除。
此时就会产生一些幽灵日志，这些日志文件名对应了还没产生的日志号（未来的日志文件），但修改时间是很早以前。

相关配置参数	说明	备注
wal_keep_segments	用于指定pg_wal目录中保存的过去的wal文件（wal 段）的最小数量	在每个检查点的时候，从目前日志位置往前wal_keep_segments个日志文件不会删除。每个segment文件16M; 如果连接到发送服务器的备用服务器滞后超过wal_keep_segment个文件，发送服务器可能会删除备用服务器仍然需要的WAL段，在这种情况下流复制连接将被终止。因此，下游连接最终也会失败。(但是，如果使用了WAL归档，备用服务器可以通过从归档中获取段来恢复。)
replication slot	逻辑复制槽，会影响主库的日志归档。
checkpoint_segments	为一个可配置的参数，控制了两个检查点间产生的日志文件数量。
checkpoint_completion_target	两个检查点之间时间间隔（即每次检查点操作完成的时间）

日志的空间至少为：保留的日志空间 + 预分配空间 + 正在被写入的那个文件，即稳定状态下可达的最大值（优先使用预分配的空间，不再增大）：

segment数量 = 
max(wal_keep_segments, checkpoint_segments + checkpoint_segments*checkpoint_completion_target) + 2 * checkpoint_segments + 1 + 1

3>日志归档（archiver）进程

4>日志发送进程（WAL sender）

10，数据恢复

1）时间线（timeline）

针对以下场景，增加时间线功能以确保pg可以还原任意时间的数据库文件：
BDA还原了周三12:00AM的数据，后来意识到这样不对，想恢复周四8:00AM的数据。此时db在不断运行中，会产生与旧的WAL文件重名的文件，这些文件进入归档目录时，会覆盖原来的旧日志，导致恢复数据库需要的WAL文件丢失。为了避免这种情况，需要区分原始数据库历史生成的WAL文件和完成恢复之后继续运行产生的（重名的）新WAL文件。
这种场景引入时间线就可以解决。每当归档文件恢复完成后，创建一个新的时间线用来区别新生成的WAL记录。

1>相关文件

每次创建一个新的时间线，就会产生一个.history文件，文件非常小，也能像WAL文件一样被归档到WAL归档目录。

## history文件
00000002.history
00000003.history
## WAL文件名由时间线和日志序号组成
00000003000000000000001A
00000003000000000000001B

内容：原时间线history文件的内容 + 追加一条当前时间线切换记录。内容说明如下：

* <parentTLI> <switchpoint> <reason>
 *
 *      parentTLI       ID of the parent timeline
 *      switchpoint     XLogRecPtr of the WAL position where the switch happened
 *      reason          human-readable explanation of why the timeline was changed

$ cat 00000004.history
1	0/140000C8	no recovery target specified
2	0/19000060	no recovery target specified
3	0/1F000090	no recovery target specified

2>新时间线的出现场景

即时恢复(PITR) 配置recovery.conf文件：

restore_command = 'cp /mnt/server/archivedir/%f %p' //从归档目录恢复日志
 recovery_target_time = '2015-7-16 12:00:00 ' //指定归档时间点，如没指定恢复到故障前的最后一完成的事务
 recovery_target_timeline = 'latest' //指定归档时间线，’latest’代表最新的时间线分支，如没指定恢复到故障前的pg_control里面的时间线
 standby_mode = ‘off’ //打开后将会以备库身份启动，而不是即时恢复

设置好recovery.conf文件后，启动数据库，将会产生新的timeline，而且会生成一个新的history文件。恢复的默认行为是沿着与当前基本备份相同的时间线恢复。如果你想恢复到某些时间线，你需要指定的recovery.conf目标时间线recovery_target_timeline，不能恢复到早于基本备份分支的时间点。