0 概述

介绍PG新特性和一些基本的原则，总体难度中等

1 PostgresSQL 11新特性

1.1 统计信息

默认情况下，pg会为每一列建立一个统计信息，里面包含了该列的统计直方图，数量。

EXPLAIN ANALYZE SELECT * FROM tbl where col1 = 1 and col2 = 0;                            
                                                QUERY PLAN                                                
----------------------------------------------------------------------------------------------------------
 Seq Scan on tbl  (cost=0.00..194248.69 rows=100 width=8) (actual time=0.640..630.130 rows=10000 loops=1)
   Filter: ((col1 = 1) AND (col2 = 0))
   Rows Removed by Filter: 9990000
 Planning time: 0.072 ms
 Execution time: 630.467 ms
(5 rows)

但是，对于组合多列的统计信息，pg默认是将他们假设为互相独立的。所以，他们评估行数为各自可能行的乘积。例如，col1有1000个不同数值，col2有100个不同数值，所以，col1与col2联合筛选有10万个可能结果。而表的总行数为1000万行，所以预计有100个结果行。

但是，实际上返回的行有10000个结果行，而不是预计的100个结果行。那是因为col1与col2之间是有依赖关系的，col2的数值依赖于col1的后两位数字，所以，col1与col2的联合结果并没有10万个，而是仅有1000个。所以，预估行数应该为1万行。

错误的预估行数会带来什么？当预估行数较少时，优化器会选择走索引，当预估行数较多时，优化器就会选择顺序扫描。错误的预估行数会造成较差的执行计划，影响查询效率。

CREATE STATISTICS s1 (dependencies) on col1, col2 from tbl; 

解决方法是，显式告诉pg，col1与col2之间可能有依赖关系，需要对这个组合列建立相关性统计信息

CREATE STATISTICS s2 (ndistinct) on col1, col2 from tbl; 

同理，可以显式告诉pg，col1与col2之间可能有依赖关系，需要对这个组合列建立直方图的统计信息。这个有助于对col1与col2的组合列的count查询。因为当col1与col2有依赖关系时，他们的group by的count值会比较少，适合使用内存的哈希聚合计数，而不是磁盘排序计数。

create index coord_idx on tb1(x,y,(z+t));
alter index coord_ix alter column 3 set statistic 1000;

pg还支持对函数索引的某一列设置统计信息

1.2 覆盖索引

create unique index some_name on some_table using btree(id) include (name);

在创建btree索引时允许将额外的字段放在索引上，这样就能避免回表了

1.3 增强分区

#创建哈希分区
create table tab(i int,t text) partition by hash(i);

#创建%4余0分区
create table tab_1 partition of tab for value with(modules 4, reminder 0);

#创建默认分区
create table tab_default partition of tab default;

pg的分区机制被大幅提供：

• 支持自动分区，修改删除添加数据时，不需要加入触发器了
• 父表的索引自动集成到子表上，而且支持全局唯一索引
• 支持范围分区，列表分区和哈希分区
• 支持默认分区

1.4 存储过程

create procedure test_proc()
language plpgsql
as $$
begin
    create table a (aid int);
    create table b (bid int);
commit;
    create table c (cid int);
rollback;
$$;
create procedure;

存储过程与函数不同的是，允许在里面写入begin,commit这些语句，以定义事务的边界

call test_proc();

使用存储过程用call方法

1.5 其他特性

其他特性为：

• 支持并行索引创建
• 支持即时编译JIT
• 支持添加列时，如果列值是不可变的默认值，就不需要改表，速度很快

2 事务与锁定

2.1 行锁

select ... from tab where ... for update;

正常情况下，使用for update或者for share会争夺记录行锁。如果该记录已经被其他事务上锁了，那么本事务就需要等待。

select ... from tab where ... for update nowait;

nowait指定如果记录已经上锁时，就取消等待，直接返回失败。如果记录未上锁，就获得记录行锁。

select ... from tab where ... limit 2 for update skip locked;

有时候，记录会只上锁一部分，那么，skip locked就会视图越过那些已经上锁的记录，只返回那些未上锁的记录，并对这些记录获取行锁。

另外，一个重要的点是，上锁时不仅会锁定自己记录的行锁，还会锁定该行所依赖的其他记录的行锁，（外键指定这个依赖关系）。例如对t_order记录上锁时，还会对t_account的该用户上锁。

2.2 隔离级别

PG有三个隔离级别：

• 读已提交级别，
• 可重复读级别，主要场景为多步生成一致报表，以及防止更新丢失的问题。更新丢失的问题的解决，通过额外使用乐观锁，当行提交的时候，检查行版本是否已经改动来实现。
• 可串行级别，使用SSI事务，追踪事务读过的记录，在提交事务的时候，检查这些读过和写过的记录是否有改动。从而解决写倾斜的问题。

2.3 vacuum控制清理

• autovacuum_vacuum_threshold = 50
• autovacuum_vacuum_scale_factor = 0.2
• autovacuum_analyze_threshold = 50
• autovacuum_analyze_scale_factor = 0.1

pg依赖于vacuum做三件事情，清理过期事务，对表数据进行分析，以及事务号回卷。清理和分析操作配置简单一点，threshold描述的是最小修改行达到数字的标准，factor描述的是最小修改行达到表总数的比例，只有两个条件都满足时才执行清理或者分析工作。

• old_snapshot_threshold = -1;

old_snapshot_threshold强制要求了长事务的执行时间，避免长事务的存在令数据无法清理。

• vacuum_freeze_min_age，一般情况下，vaccum仅当该数据块的所有行都是可见的时候，才去执行冰冻操作。冰冻时，只有该行的年龄与最新事务的年龄大于vacuum_freeze_min_age时才执行冰冻操作。
• vacuum_freeze_table_age，但是，仅数据块的所有行都是可见条件太严格，因为某些行可能因为回滚，或者事务进行中的操作，不能被可见。因此，仅当该数据块的最旧行年龄与最新事务的年龄大于vacuum_freeze_table_age时会执行更细致的冰冻操作。
• autovacuum_freeze_max_age，最坏的情况下，一直不进行冰冻操作。该数据块的最旧行年龄与最新事务的年龄已经到达20亿的情况下，就强行停表进行vacuum的冰冻操作。

vacuum的事务号回滚的配置就更加麻烦一点了

最后，尽量避免使用vacuum full操作，它会锁表，并且重写整个关系。这个时候，无法对该表进行写操作。

3 索引

3.1 并行度

#查询并行度
set max_parallel_workers_per_gather to 5;

#创建btree索引的并行度
set max_parallel_maintenance_workers to 5;

#指定某个表的并行度
alter table t_parallel set (parallel_workers = 9);

#最大的工作进程，一般设置为CPU的核心数
set max_worker_processes;

#最大用于查询的工作进程
set max_parallel_workers;

设置执行的并行度，设置为0就是没有并行度了。

对于并行查询，我们还有以下的参数：

• parallel_tuple_cost，并行工作组向gather发送元组的惩罚点，默认为0.1
• parallel_setup_cost，启动一个并行工作组的惩罚点，默认为1000
• min_parallel_relation_size，并行查询的表的最小尺寸，默认为8MB
• min_parallel_index_scan_size，并行查询btree索引和位图扫描的最小尺寸，默认为512KB

3.2 查询规划

3.2.1 代价模型

在查询中，可以通过不同的查询路径来获取最终结果。那如何选择最快的查询路径，pg是通过代价模型，和pg_stats对表的定期统计信息来计算不同查询路径的理论消耗，如何选择理论消耗中最小的查询路径来执行。

代价模型：

• seq_page_cost，顺序扫描的惩罚点，默认为1。
• random_page_cost，随机扫描的惩罚点，默认为4。如果在SSD硬盘上，可以设置为2或者1。
• cpu_tuple_cost，cpu对每个元组应用过滤条件的惩罚点，默认为0.01。
• cpu_index_tuple_cost，cpu对索引扫描差生的惩罚点，默认为0.005。

3.2.2 统计信息

pg_stats的信息是如何影响查询规划的

• pg_stats的列直方图histogram_bounds，展示了行的选择性。如果查询的选择性很差，pg就会避免使用索引，而是直接使用顺序扫描。
• pg_stats的列相关性correlation，展示了列值与物理行序的关系。如果与物理行序的相关性为1或者-1，代表相同值或连续值是在物理位置上紧靠在一起，而相关性为0，则是几乎随机的乱序状态。所以，对条件为id <1000的查询，高相关性时就会直接回表，而低相关性的就会先写入到位图中，再回表。
• pg_stats的列独立值数目n_distinct，展示了列值的各个可能值。对于count(distinct column)的运算，n_distinct较少就能在内存用哈希聚合，太大的话就只能在磁盘排序。

cluster t_user using idx_userId;

pg允许将物理行序与索引的排序对齐，但是这种对齐是暂时的，有新旧数据改变后这种对齐会消失。另外，cluster会用ACCESS_EXCULSIVE锁

3.3 btree索引

btree索引可以加速以下操作：

• 排序，正向或者反向扫描都可以，因为btree是双向的
• 最大值和最小值，简单地获取最大和最小值
• 最左匹配原则

在pg中，btree索引还有以下特色功能：

• 函数索引
• 条件索引
• 并行创建和查询索引
• 自定义操作符
• 覆盖索引，可以将额外的列放入btree中，避免回表操作

当然，函数索引，和自定义操作符，对所有pg索引都是拥有的特色功能

3.4 位图扫描

位图扫描可以加速一下操作

• 组合多个索引的结果，将最终要筛选的块数量大幅减少。例如用A索引能筛选50%的数据，用B索引能筛选50%的数据。我们可以组合使用A索引和B索引，能筛选80%的数据。这个时候，我们就需要位图扫描来组合使用多个索引。
• 顺序回表，减少随机IO操作。因为位图的块隐含了排序操作，所以保证回表的时候是顺序IO，而不是随机IO。

3.5 gin索引

gin索引可以加速以下的操作：

• 任意多条件的组合查询

注意，gin索引的实现如下：

• 每个键都是btree组织，同时键对应的文档列表也是btree组织。所以对键的范围查询也能使用gin索引
• 文档列表是btree组织，保证了与事务一致的可见性。但是插入吞吐量小，占用空间大，查询速度也慢。
• 多个键对应的文档列表合并用的是位图扫描合并的方式，会丢失具体行号信息，只保留数据块号的合并。这是一种有损合并，造成必须要回表重检。而且合并的时间复杂度并不是最优的。

而与此对应的es，它的索引实现是这样的：

• 每个键是tire树，键对应的文档列表也是btree组织，键的查询很快，但不支持范围查询。
• 键对应的文档列表是静态的有序数组，并且用跳表作为多层索引。插入吞吐量大，占用空间小，查询速度也很慢。唯一缺点是，不支持事务一致的可见性。
• 多个键对应的文档列表合并用的是跳表轮转合并，时间复杂度甚至低于文档的数量，非常快。

3.6 排除索引

begin;

查询是否满足条件

插入或者修改操作

end;

在部分业务中，我们需要让插入或者修改操作依赖于一个查询的结果。并且插入或者修改操作本身也会影响到查询的结果。例如，航班预订，账户转账，楼层活动的楼层号争夺。如果是用上面的方法直接写，在高并发的环境下，几乎总是会出错。正确的方法是，开启相应隔离级别的并发环境，锁住查询条件不能被修改。但是这样做的并发量不高。

create table t_reservation(
  root int,
  from_to tsrange,
  excolude using gist(room with = ,from_to with &&)
)

在某些对应场景下，我们可以使用gist索引的特性，建立排除操作符。或者是使用btree的唯一索引特性。

begin;

插入或者修改操作

end;

这样就不再需要查询，直接插入就可以了，违反索引约束时数据库会报错。这样就保证数据的完整和一致性，而且避免了读操作的上锁操作和网络延迟，并发量大大提高了。

4 处理高级SQL

4.1 分组集

通过指定列，来派生不同的列组合的分组集

4.1.1 ROLLUP

select region,avg(production)
from t_oil
group by rollup(region)

不仅会生成region列分组，还会生成总数据集的分组

select region,country,avg(production)
from t_oil
group by rollup(region,country)

不仅会生成(region,country)的分组，还会生成(region)分组，和()总数据集分组。注意派生分组集的方式是左侧列合并生成

4.1.2 CUBE

select region,avg(production)
from t_oil
group by cube(region)

cube派生在单列的情况下，与rollup是一样的

select region,country,avg(production)
from t_oil
group by rollup(region,country)

cube派生不同的是，在多列的时候，不是左侧列合并生成，是任意列组合生成。例如，不仅会生成(region,country)的分组，还会生成(region)分组和(country)分组，和()总数据集分组。

4.1.3 GroupingSets

select region,country,avg(production)
from t_oil
group by grouping sets( (),(region),(country),(region,country))

GroupingSets就是直接指定多个分组集了，最灵活的方式

4.2 筛选集

select region,
  country,
  avg(production) filter (where year < 1990) as old,
  avg(production) filter (where year >= 1990) as new,
from t_oil
group by rollup(region,country)

filter就是分组后筛选数据，再执行聚合操作

4.3 有序集

有序集，就是分组后，组内排序，再执行聚合操作

4.3.1 百分位数

select region,country,
  precentile_disc(0.5) within group (order by production)
from t_oil
group by rollup(region,country) 

取50%的百分位数，实际存在的值

select region,country,
  precentile_cont(0.5) within group (order by production)
from t_oil
group by rollup(region,country) 

取50%的百分位数，使用插值

4.3.2 众数

select region,country,
  mode() within group (order by production)
from t_oil
group by rollup(region,country) 

取众数

4.4 窗口集

窗口函数，就是给与每行不同的窗口，然后在这个窗口执行聚合操作。注意，窗口并不像分组一样会合并行。

4.4.1 总集窗口

select region,country,min(production) over ()
from t_oil

每一行都以整个集合作为窗口,然后在该个窗口计算最小值

4.4.2 分组窗口

select region,country,min(production) over (partition by country)
from t_oil

每一行，以它所在的country分组作为窗口，然后在该个窗口计算最小值

4.4.3 排序窗口

select region,country,min(production) over (order by year)
from t_oil

每一行，以year作为排序，并且每一行的窗口为组内第一行到当前行作为窗口，然后在该个窗口计算最小值。所以，每一行的窗口大小都是不同的，第一行的窗口只有一行，第二行的窗口就有两行。因此，在该例子中，看到的min(production)为逐行累计最小值，不是总集的最小值。

select region,country,min(production) over (partition by country order by year)
from t_oil

排序窗口常常与分组窗口一起组合使用，实现的是按照country分组，然后在组内按照year排序作为窗口。当然，仍然是组内逐行累积的排序窗口，不是组内所有行的窗口。

4.4.4 滑动窗口

select region,country,
  min(production) over (order by year rows between 1 preceding and 1 following)
from t_oil

每一行，以year作为排序，并且每一行的窗口为前一行到后一行作为窗口，然后在该个窗口计算最小值。显然，这个窗口是滑动的，并且是前一行和后一行的共3行合起来的窗口。

unbounded preceding
unbounded following

可以指定开头，或者末尾作为滑动窗口的范围

4.5 窗口集增强函数

窗口集的功能实在太强大，有一些特殊的增强聚合参数

4.5.1 窗口内的行号

select region,country,row_number() over (partition by country order by production)
from t_oil

根据contry分组，production排序后，得出的每行序号。但是，这样做的话，同production的数据序号是不同的，不符合排名要求。

select region,country,ntile(3) over (partition by country order by production)
from t_oil

根据contry分组，每组平均分为3份数据。

4.5.2 窗口内的排名

select region,country,rank() over (partition by country order by production)
from t_oil

根据contry分组，production排序后，得出的排名rank。同production的rank是相同的，但是rank之间有空隙，一般情况下是正常可用的。

select region,country,dense_rank() over (partition by country order by production)
from t_oil

dense_rank与rank类似，唯一不同是，dense_rank没有空隙

4.5.3 窗口指定位置的行

select region,country,
  first_value(production) over (order by production),
  nth_value(production,5) over (order by production),
  last_value(production) over (order by production),
  lag(production,1) over (order by production),
  lead(production,1) over (order by production),
from t_oil

分别是获取窗口内的，第一行，第5行，最后一行。当前行的前一行，当前行的后一行。

5 日志文件和统计信息

好的工具不仅用得好，还提供了各种各样的接口提供外部监控，以了解它的运行情况

5.1 活动连接

pg_stat_activity，对当前活动连接的监控

5.2 数据库

pg_stat_database，对数据库的监控，pg中有多个database的

5.3 表

pg_stat_user_tables，对表的监控，包括增删改查行的次数，最后一次清理和分析的时间和次数等等

可以对seq_tup_read从大到小排序，从而找出那些缺少索引的表

pg_statio_user_tables，对表的IO监控，主要展示堆，索引，toast的缓冲区击中和未击中的次数，相对pg_stat_user_tables意义不大。

5.4 索引

pg_stat_user_indexs，对索引的监控

可以对idx_scan从小到大排序，从而找出那些无效索引的表

pg_statio_user_indexs，对索引的IO监控，主要展示缓冲区击中和未击中的次数，相对pg_stat_user_indexs意义不大。

5.5 语句

pg_stat_statments，对所有语句的监控，这可能是最重要的视图。不过，该语句是需要插件支持的。

可以对total_time从大到小排序，然后展示mean_time字段，从而找出查询最繁重的语句，是否有瓶颈

pg_stat_statments使用LRU的方式记录5000种查询的统计结果，如果某些查询很少被使用，它就会被置换出来，不被统计

5.6 当前事务

pg_stat_xact_user_tables，记录当前事务的统计信息，用来分析当前事务的语句是否有性能问题。

5.7 后台脏页刷新工作

pg_stat_bgwriter，展示缓冲区的脏页被刷入到磁盘的统计信息。写入磁盘有三种方式，定时较短的background_writer，定时较长的checkpoint，以及数据库连接直接写入。

5.8 后台清理工作

pg_stat_progress_vaccum跟踪vacuum的清理工作进度，比较直观

5.9 后台复制工作

主从复制的时候，我们需要看双方落后的差距，这就需要看主机的pg_stat_replication视图，和从机的pg_stat_wal_receiver视图。

pg_stat_replication视图:

pg_stat_wal_receiver视图:

6 优化查询性能

6.1 explain

explain主要看以下地方：

• 执行规划是否如期望的执行
• 加入analyze参数，查看评估行和实际行是否有巨大的差距，是的话要添加相应的analyze
• 加入buffers参数，查看是否有缓冲区未击中情况，有的话看情况加入cluster重排数据

7 存储过程

无用

8 安全性

暂时不看

9 处理备份和恢复

9.1 备份单个数据库

#直接备份test数据库输出到stdout，重定向到文件
pg_dump test > /tmp/dump.sql

#备份使用compress格式，大小大概能小90%
pg_dump -Fc test > /tmp/dump.sql

#备份直接指定输出的目标，-f参数 -h远程数据库 -U用户
pg_dump -Fc test -h 192.168.122.1 -Uosba -f /tmp/dump.sql

#备份使用目录格式，能有效利用多核来执行
pg_dump -Fd test -f /tmp/backup -j 4

备份单个数据库

9.2 备份全部数据库

#备份所有数据库，包括全局数据，例如用户，密码，统计信息等
pg_dumpall > /tmp/all.sql

#仅备份全局数据库，不包括用户数据库
pg_dumpall -g > /tmp/globals.sql

9.3 恢复

#恢复数据库，使用4个进程，内容来自于/tmp/dump.sql，恢复到new_db数据库
pgstore -d new_db -j 4 /tmp/dump.sql

10 理解备份与复制

10.1 WAL

10.1.1 意义

WAL称为预写式日志，它的特点是，数据库写数据前，先将要修改的数据块的操作顺序写入WAL日志文件中，然后修改共享内存里面的数据块缓存。最后定期通过checkpoint或者bacground_writer将内存中的数据块缓存批量同步到磁盘中。优点在于：

• 避免每次事务操作都需要进行随机IO的块操作，而是转换为顺序WAL写操作
• 批量写到磁盘中，能有效地减少多次写同一个数据块的IO操作，并且批量写执行前对数据块位置进行排序，进一步有效地减少了随机IO操作
• WAL实现了多数据块写入的原子性。数据库需要保证突然断电崩溃的时候依然能保证一致性。如果直写数据块的话，崩溃的时候有可能导致一个事务中涉及的有些数据块已经写到磁盘，另外有些数据块已经丢失的问题。但是，用了WAL日志的话，就不会出现这种情况。一个事务所需要修改的所有数据块的操作信息提前写入到WAL日志中，然后才能去写入数据块信息。因为如果写入数据块中途崩溃了，那么可以依据WAL日志信息来重做或恢复数据块内容。

archive_mode = on
archive_command = 'cp %p /archive/%f'

每个WAL文件写满以后，可以执行归档模式，将该WAL文件保存到远程的其他地方，以备WAL文件损坏造成无法恢复的问题。PG归档的时候会调用archive_command进行归档，如果命令的返回值不是0，那么PG会不断重试归档该文件。

10.1.2 定期background_writer

然后，我们引入background_writer。background_writer就是一个定时器，定时将内存中的数据块缓存写入到磁盘中。注意，每个WAL日志的一个操作号都有一个递增的lsn编码，它代表着不断递增的数据块操作。每个数据块都会有一个最后一次修改该数据块的lsn编码，它会随着background_writer一起写入到磁盘中。那么，我们假设当数据库突然断电崩溃时，WAL是怎么帮助恢复数据块的。

• 首先依次遍历每个WAL日志文件（一个日志文件包含了多个操作信息），取出它的每个操作信息。操作信息包含了对哪个数据块的操作，以及该操作的lsn编码。
• 根据这个操作信息，数据库回到数据块中，检查该数据块的lsn编码是比WAL的lsn编码更加新，还是更加旧。如果该数据块的lsn编码比较新，证明，该数据块已经被后面的WAL修改了，这次WAL不需要重做数据块。如果该数据块的lsn编码比较旧，就需要根据操作信息来执行数据块的修改操作，是添加还是删除一个元组信息。

执行完所有的WAL日志文件后，我们就能保证数据块与WAL的修改信息完全一致了

10.1.3 定期checkpoint

WAL能帮助崩溃恢复，但是从恢复过程中，我们知道所有的WAL文件都不能被删除。因为删除了旧的WAL文件，但是旧WAL文件对应的数据块可能并没有刷新到磁盘。当该数据块崩溃后，无法找到对应的WAL文件来恢复它。因此，这会造成WAL文件无限增长占用空间的问题，而且另一方面，WAL文件太多，崩溃恢复需要的时间也会很长。

因此，我们需要引入checkpoint。它的想法是，间隔一个较长的时间，例如是2小时，它需要将当前WAL对应的lsn的所有修改过数据块都必须写入到磁盘上，就是不再允许太旧的数据块一直在内存中不落地。当checkpoint完成后，我们就能将checkpoint对应的lsn以前的WAL文件安全删除了，这样就能解决WAL文件占用空间太大，恢复时间太长的问题。

值得思考的时候，checkpoint的时候，依然可以对旧的数据块进行修改操作，但是就不再是内存的同一个位置了，这样checkpoint就不会阻塞数据库的读写操作。

那么，现在崩溃恢复的过程变为：

• 首先获取最近的一次checkpoint的位置，获取它的lsn信息。
• 根据lsn信息获取在这个lsn之后的所有WAL日志文件，遍历每个WAL日志文件，取出它的每个操作信息。操作信息包含了对哪个数据块的操作，以及该操作的lsn编码。
• 根据这个操作信息，数据库回到数据块中，检查该数据块的lsn编码是比WAL的lsn编码更加新，还是更加旧。如果该数据块的lsn编码比较新，证明，该数据块已经被后面的WAL修改了，这次WAL不需要重做数据块。如果该数据块的lsn编码比较旧，就需要根据操作信息来执行数据块的修改操作，是添加还是删除一个元组信息。

10.1.4 full_page_write

WAL能保证多数据块写入的原子性，但这种保证是需要在单数据块写入的原子性有磁盘保证的情况下实现的。可是，数据块的的大小为8KB，而磁盘的一个块的大小为4KB，它仅能保证4KB的原子写操作，磁盘是不能保证单个数据块的写入8KB是原子的。也就是说，存在一个极端的情况，一个数据块的头部的lsn编码是新数据块的数据，而它的尾部则是旧数据块的数据。如果这个时候执行WAL的恢复操作，WAL检查lsn编码是新的，然后就跳过了该块的恢复过程，这造成了不一致的问题。退一步说，即使WAL检查了这个数据块是坏的，也无法恢复，因为该数据块是不一致的，只有整个数据块是全旧的一致状态，才能redo WAL。否则，在这个半新半旧的数据库中，redo WAL也是错误的。

解决方法，有三种：

• 将数据块设置为4KB，而不是8KB，以实现与磁盘的块大小对齐。多个数据块的原子性是由WAL保证的，单数据块的原子性的。但是，这样做会让数据块头部和指针信息占用比例太大，造成空间浪费。
• 改用有日志支持的文件系统，以让文件系统支持8KB的数据块写入的原子性。这个方案有点冒险，毕竟文件系统也不是完全可靠的，而且日志式的文件系统有性能损耗。
• checkpoint以后对数据块的第一次修改操作时，先将修改前的整个数据块的二进制信息都一起写入到WAL文件中，以实现当遇到半新半旧数据块时，就整个数据块回滚到旧状态，再做redo操作。对该数据块的第二次及以后的修改操作，就不再需要保存整个数据块的二进制信息了，因为崩溃恢复的过程总是在checkpoint点开始执行的。

显然，第三种方法更好，但会遇到一点小瑕疵，WAL文件增长太快，因为要存储整个数据块。一个折衷的优化方式是，延长checkpoint的定期时间，减少二进制数据块的保存次数。但代价是会延长崩溃恢复所需要的时间。

checkpoint_timeout = 2d
max_wal_size = 8GB
min_wal_size = 1GB
checkpoint_completion_target = 0.8

pg中关于checkpoint执行时间点的配置操作。

最后，现在崩溃恢复的过程变为：

• 首先获取最近的一次checkpoint的位置，获取它的lsn信息。
• 根据lsn信息获取在这个lsn之后的所有WAL日志文件，遍历每个WAL日志文件，取出它的每个操作信息。操作信息包含了对哪个数据块的操作，以及该操作的lsn编码。
• 根据这个操作信息，数据库回到数据块中。先根据数据块的校验码，确定它是否在半新半旧的损坏状态，是的话先用WAL中的二进制信息恢复到全旧的一致性状态，再redo WAL。如果校验码没问题，数据块没有损坏，则执行下一步。
• 检查该数据块的lsn编码是比WAL的lsn编码更加新，还是更加旧。如果该数据块的lsn编码比较新，证明，该数据块已经被后面的WAL修改了，这次WAL不需要重做数据块。如果该数据块的lsn编码比较旧，就需要根据操作信息来执行数据块的修改操作，也就是redo WAL。

好了，我们终于得到了一个非常可靠的，断电崩溃依然保持一致性的数据库了。

10.2 PITR备份

既然WAL可以实现崩溃恢复，让任意的一个磁盘状态恢复到一个一致的状态。那么，我们有一个新的备份想法。将整个\(PGDATA文件夹保存下来，放到另外一个机器上，然后启动的时候使用WAL文件来恢复它到一个一致的状态就可以了，这就是PITR备份的思想。为什么，保存了\)PGDATA文件夹，还需要做redo WAL文件呢？显然，保存的时候，PG仍然在不断刷新$PGDATA的数据块，不redo WAL的话，多数据块是不一致的。

无论是PITR备份，还是流复制，它们的遵循三步流程：

• 设置主服务器的配置文件，postgres.conf
• 执行pg_basebackup保存整个数据
• 将数据放到备服务器上，修改备服务器的postgres.conf和restore.conf，最后启动既可

10.2.1 主服务器配置

wal_level = replica
max_wal_senders = 10

postgres.conf文件

10.2.2 备份数据

pg_basebackup -D /target_dir -h master_server_ip --checkpoint=fast --wal-method=stream

备份数据，-D为目标文件夹，-h为连接远程的主服务器，将它的数据下载到本地。其他参数为：

• checkpoint参数为fast，就是让主服务器立即执行checkpoint，让数据块全部落地后再备份。如果checkpoint参数不是fast，那么就需要等待主服务器执行checkpoint后才能执行备份。为什么要等checkpoint，因为PITR备份需要WAL文件，而WAL文件总是依赖于某个checkpoint点开始恢复
• wal-method参数为stream，就是获取checkpoint以后的所有的WAL文件，并打包到target_dir文件夹中。获取WAL的方式为伪装为从服务器，以流的方式抓取。

10.2.3 从服务器配置

export $PGDATA=/target_dir

将备份的target_dir设置为PGDATA目录

restore_command = 'cp /archive/%f %p'
restore_target_time = '2019-04-05 15:43:12'

编辑以上的recovery.conf文件，放入PGDATA目录，直接启动即可。恢复的时候，可以指定恢复到特点时间点的数据库。

restore_command = 'cp /archive/%f %p'
restore_target_action = 'pause'

可以设置为用户指定的位置，用户需要调用pg_wal_replay_pause和pg_wal_replay_resume来进行尝试性的逐步性的重放。

10.3 异步流复制

PITR可以做将数据库恢复到任意一个时间点，那么，如果主服务器的WAL文件在运行过程中，不断传送给从服务器，从服务器执行不间断的恢复状态，那么从服务器就能与主服务器的数据保持异步的一致，从而实现了主从复制，读写分离的模式了。

10.3.1 主服务器配置

wal_level = replica
max_wal_senders = 10

postgres.conf文件

10.3.2 备份数据

pg_basebackup -D /target_dir -h master_server_ip --checkpoint=fast --wal-method=stream -R

注意额外加入了-R参数，自动生成recovery.conf文件，比较省事

10.3.3 从服务器配置

export $PGDATA=/target_dir

将备份的target_dir设置为PGDATA目录

hot_standby = on
standby_mode = on
primary_conninfo = 'host=192.168.1.1 port=5432 user=fish'

编辑以上的postgres.conf文件。hot_standby就是在恢复过程提供外部可读，standby_mode就是不允许外部可写，primary_conninfo就是主服务器的连接配置。最后启动数据库即可

10.3.4 其他

select * from pg_stat_replication

从主服务器的pg_stat_replication视图，我们重点关注以下字段：

• state，是否处于正常的复制状态
• sync_state，是同步复制，还是异步复制的模式
• sent_location，同步的位置，可以与pg_current_wal_location()做差值，就知道主从同步的落后程度了

pg_ctl -D data_dir promote

当主服务器宕机后，将从服务器提升到主服务器的命令

max_standby_streaming_delay = 30s
hot_standby_feedback = on

由于从服务器没有同步信息到主服务器，从服务器的事务执行读操作的过程，主服务器可以会传递一个drop table的信息，导致从服务器的读操作失败。所以加入max_standby_streaming_delay来延迟主服务器的冲突命令的执行，让从服务器的读操作可以继续执行下去。

另外一方面，让vacuum避免清理那些从服务器的正在读的数据行，这是通过打开hot_standby_feedback配置，让从服务器发送当前读的最旧事务ID给主服务器来实现的。代价是，会让vacuum的清理受到了延迟，可能会产生轻微的表膨胀问题。

wal_keep_segments = 16

当网络中断时，从服务器的数据延迟主服务器太多，当从服务器重新连接上主服务器后，它请求获取旧的WAL文件来恢复数据。但是主服务器可能已经执行了checkpoint操作，删除了旧的WAL文件，从而造成从服务器无法恢复到最新的状态，这时从服务器只能重新执行pg_basebackup了。为了解决这个问题，pg加入wal_keep_segments，保留一定数量的旧WAL文件，即使checkpoint执行后，checkpoint前的wal_keep_segments个WAL文件都不会被删除。

10.4 同步流复制

默认pg同步为异步流复制，就是主从之间没有复制同步的要求，从服务器可以任意延迟主服务器的状态时间。对于要求性较高的场景，例如金融行业，这是不行的。必须要使用同步复制，这样主服务器宕机，从服务器马上提升，也不会导致事务丢失的问题。

10.4.1 主服务器配置

synchronous_standby_name = 'slave1,slave2'
synchronous_commit = 'on'

设置pg必须要与两个slave1或slave2的同步复制。如果设置了两个同步的从服务器，那么其中一个从服务器挂掉，主服务器仍然可以工作。但是两个从服务器挂掉，主服务器就会拒绝对外执行写操作了。

synchronous_commit指定了事务commit返回时，要满足什么条件才算可以提交

• off，WAL文件没有立即刷到磁盘上，主服务器数据可能丢失。
• local，WAL文件立即刷到磁盘上，主服务器数据不会丢失，但从服务器的WAL文件仍然没有保证到达，从服务提升时可能丢失数据。
• remote_write，WAL文件立即刷到磁盘上，并且WAL文件发送到从服务器的操作系统上。在极端情况下，从服务器的WAL文件仍然在操作系统的缓存上，数据可能丢失。
• on，WAL文件立即刷到磁盘上，并且WAL文件发送到从服务器的操作系统上，并且刷到从服务器的磁盘上。这是保证无丢失的配置
• remote_apply，WAL文件立即刷到磁盘上，并且WAL文件发送到从服务器的操作系统上，并且刷到从服务器的磁盘上，并且保证从服务器到replay了这个WAL文件。这是读可见性的最保守commit方式。

10.4.2 备份数据

无变化

10.4.3 从服务器配置

application_name = 'slave1'

设置recovery.conf的application_name，指定自己的对外名称

10.5 复制槽

10.5.1 全局复制

select * from pg_create_logical_replication_slot('logical_slot_name','test_decoding');

创建逻辑复制槽

#取出复制槽的数据，并往前移动偏移量
select pg_logical_slot_get_changes('logical_slot_name',NULL,NUll);

#仅取出数据，不改变偏移量
select pg_logical_slot_peek_changes('logical_slot_name',NULL,NULL);

取出数据有两种方式

alter table t_demo replica identity full;

设置t_demo表格的事务日志信息为最全面的

10.5.2 局部复制

create publication pub1 for table t_test;

对表格t_test打开倾听模式

#创建复制槽
select pg_create_logical_replication_slot('sub1','pgoutput');

#创建消费者，将数据装入复制槽sub1
create subscription sub1
connection 'host=localhost dbname=test user=postgres'
publication pub1
with(create_slot=false);

设置消费

11 选取有用的扩展

11.1 bloom插件

布隆过滤器的想法是，让每个数据块的所有行建立一个布隆过滤器，将所有行的列值都插进去。那么进行数据查询的时候，我们先查询这个数据块的布隆过滤器，测试这个数据块是否有可能包含的行。没有的话直接跳过这个数据块，有的话再recheck这个数据块。

所以，布隆过滤器索引的特点是：

• 不精确索引，总是需要recheck数据块
• 查询速度提升不大，因为每次都要检查所有数据块的布隆过滤器
• 占用空间小，布隆过滤器的压缩能力就很强
• 任意列组合查询，这个确实比较方便

11.2 btree_gist和btree_gin插件

btree擅长处理单维，单值数据。gist擅长处理多维数据，gin擅长处理数组类型的多值数据。那么，如果，如果我们要建立一个索引，既要对多维数据，又要对单维数据查询，怎么办。

这就要引入btree_gist，让gist支持单维数据类型，并增加近邻搜索的能力。同样地，让gin支持单值数据类型。这就是这两个插件的意义

12 检索PostgresSQL

遇到一个有问题的PG数据库，我们应该根据以下的步骤找出问题。

12.1 活动连接

select datname,
  count(*) as open,
  count(*) filter (where state='active') as active,
  count(*) filter (where state='idle') as idle,
  count(*) filter (where state='idle in transaction') as idle_in_trans
from pg_stat_activity;

检查活动连接，根据state状态展示数量。如果idle_in_trans的数量太多，可能是因为长事务中没有正常关闭导致的，进一步的可能是没有关闭连接，也可能是客户端在事务中进行网络等IO操作导致的。

select pid,xact_start, now() - xact_start as duration
from pg_stat_activity
where state='idle in transaction'
order by 3 desc;

进一步地，我们检查idle_in_trans连接的事务打开时间有多久

select pid,query,now() - query_start as duration,
from pg_stat_activity
where state='active'
order by 3 desc;

另外，我们可以查询active连接的慢查询是否存在

12.2 慢查询

慢查询来源于两个地方，调用次数少但是执行时间慢的语句，或者是调用次数多但是执行效率不高的语句。

show log_min_duration_statement;

alter database test set log_min_duration_statement to 10000;

设置慢查询语句的阈值，执行过慢的语句会被写入日志中

select query,total_time,mean_time
from pg_stat_statement
order by total_time desc 
limit 10;

展示那些调用次数多的语句，它们的执行效率如何

12.3 检查索引

select schemaname,relname,seq_scan,
  seq_tup_read/seq_scan as avg
from pg_stat_user_tables
order by seq_tup_read desc
limit 10;

检查顺序扫描过多的表，它们很可能是缺失索引

select schemaname,relname,index_tup_read
from pg_stat_user_indexs
order by index_scan asc
limit 10;

检查索引扫描过小的索引，它们可能是多余的索引

12.4 检查IO和内存

select temp_files,temp_bytes from pg_stat_databases;
select temp_files,temp_bytes from pg_stat_user_tables;

检查临时文件，和临时文件字节过多的表，可能是sql语句写得不好，也可能是analyse没加入，也可能是没有索引，也有可能是配置的缓冲区太小了

select (blk_read_time+blk_write_time)/total_time from pg_stat_statements;

检查读写数据块的占总时间的百分比，如果超过30%的话，证明可能是IO密集型查询，更换SSD硬盘会是好的提升

vmstat 2

低的bi和bo值，但是较高的wa值，证明瓶颈可能在随机IO上

14 总结

参考资料：

• PostgreSQL中的full_page_writes的理解

• 本文作者： fishedee
• 版权声明： 本博客所有文章均采用 CC BY-NC-SA 3.0 CN 许可协议，转载必须注明出处！

相关文章

• 2020-09-01-《PostgresSQL修炼之道》读书笔记
• 2020-12-09-《数据密集型应用系统设计》读书笔记
• 2018-06-04-schema数据库管理工具
• 2016-03-16-mysql经验汇总
• 2016-03-18-分布式存储概览