聚会之后的“ PostgreSQL 11新功能”

今天，我们将讨论PostgreSQL 11的最重要的功能。为什么只关注它们-因为不是每个人都需要某些功能，所以我们选择了最受欢迎的功能。

目录内容

• Jit编译
• 分区
• 指标
• 覆盖指数
• SP GiST
• 性能表现
• 沃尔
• 备份与复制
• 对于dba
• 并行执行
• 优化器
• 视窗功能
• 全文搜索更改
• 杰森（b）和全文
• PL / *程序
• pgbench
• PSQL增强

Jit编译

PostgreSQL终于引入了JIT编译，即将查询编译为二进制代码。 为此，请编译支持JIT编译的PostgreSQL (Compile time 1 (--with-llvm)) 。 同时，计算机必须具有不低于3.9的LLVM版本。

什么可以加快JIT？

• 使用WHERE子句查询，即此关键字之后的所有内容。 这并非总是必要的，但是机会是有用的。
• 目标列表的计算：在PostgreSQL术语中，这就是select和from之间的所有内容。
• 聚集体。
• 将记录从一个视图转换到另一个视图（投影）。 例如，将联接应用于两个表时，结果是一个新的元组，其中包含两个表中的字段。
• 元组变形。 任何数据库（至少是小写的）都是关系数据库的问题之一是如何从磁盘上的记录中获取字段。 毕竟，可能为空，它们具有不同的记录，通常，这不是最便宜的操作。

Compile time 2表示不使用JIT。 在PostgreSQL中，有一瞬间的查询计划，由系统决定哪些值得JIT，哪些不值得。 在这一点上，它是JIT，然后执行程序按原样执行。

JIT可插拔。 默认情况下，它与LLVM一起使用，但是您可以连接任何其他JIT。



如果您在没有JIT支持的情况下编译了PostgreSQL，则第一个设置将无法正常工作。 为开发人员实现的选项，有一些针对单个JIT功能的设置。

下一个微妙之处与jit_above_cost有关。 JIT本身不是免费的。 因此，如果查询的成本超过10万条条件鹦鹉，则PostgreSQL默认使用JIT优化，在条件鹦鹉中进行解释，分析等工作。 该值是随机选择的，因此请注意它。

但是并非总是在启用JIT后一切都可以立即工作。 通常，每个人都开始使用select * from表（id = 600查询）来尝试JIT，但他们失败了。 可能有必要以某种方式使请求复杂化，然后每个人都将生成一个庞大的数据库来组合该请求。 结果，PostgreSQL依靠磁盘的功能；它缺乏共享缓冲区和缓存的能力。

这是一个完全抽象的示例。 有9个具有不同频率的空字段，因此您可以注意到元组变形的影响。

select i as x1,
case when i % 2 = 0 then i else null end as x2,
case when i % 3 = 0 then i else null end as x3,
case when i % 4 = 0 then i else null end as x4,
case when i % 5 = 0 then i else null end as x5,
case when i % 6 = 0 then i else null end as x6,
case when i % 7 = 0 then i else null end as x7,
case when i % 8 = 0 then i else null end as x8,
case when i % 9 = 0 then i else null end as x9
into t
from generate_series(0, 10000000) i;

vacuum t;
analyze t;

PostgreSQL有很多可能性，并且要了解JIT的优势，请禁用前两行以免产生干扰，并重置阈值。

set max_parallel_workers=0;
set max_parallel_workers_per_gather=0;
set jit_above_cost=0;
set jit_inline_above_cost=0;
set jit_optimize_above_cost=0;

这是请求本身：

set jit=off;
explain analyze
select count(*) from t where
sqrt(pow(x9, 2) + pow(x8,2)) < 10000;

set jit=on;
explain analyze
select count(*) from t where
sqrt(pow(x9, 2) + pow(x8,2)) < 10000;

结果如下：

Planning Time: 0.71 ms
Execution Time: 1986.323 ms

VS

Planning Time: 0.060 ms
JIT:
Functions: 4
Generation Time: 0.911 ms
Inlining: true
Inlining Time: 23.876 ms
Optimization: true
Optimization Time: 41.399 ms
Emission Time: 21.856 ms
Execution Time: 949.112 ms

JIT帮助将请求加快了一半。 计划时间是差不多的事情，但这很可能是PostgreSQL缓存某些内容的结果，因此请忽略它。

如果总结一下，JIT编译大约需要80毫秒。 为什么JIT不免费？ 在执行请求之前，您需要对其进行编译，这也需要时间。 而且比计划多了三个数量级。 这不是一种昂贵的享受，但是由于执行时间长，它有回报。

尽管并非总是如此，但您可以通过这种方式使用JIT。

分区

如果您关注PostgreSQL中的分区，您可能会注意到它是在那里展示的。 在第10版中，当出现了分区（节）的声明式声明时，情况略有改善。 另一方面，所有内容在内部保持相同，并且工作与以前的版本大致相同，这很糟糕。
在许多方面，此问题都由pg_pathman模块解决，该模块允许使用节并在运行时的最佳时间切断它们。

在版本11中，分区得到了极大改进：

• 首先，分区表可以具有主键，该主键必须包括分区键。 实际上，这可以是半主键，也可以是主半键。 不幸的是，您不能在其上创建外键。 我希望以后会解决此问题。
• 现在，不仅可以按范围进行分区，还可以按列表和哈希进行分区。 哈希是非常原始的，表达式的其余部分用于哈希。
• 更新时，该行在各节之间移动。 以前，您必须编写触发器，但是现在它是自动完成的。

最大的问题是：我可以有多少节？ 坦白地说，该功能包含大量部分（成千上万），因此无法正常工作。 Pg_pathman做得更好。

默认情况下也设置了部分。 同样，在pg_pathman中，您可以自动创建节，这更加方便。 在这里，所有不能推到某处的东西都落在该部分中。 如果在实际系统中默认情况下会执行此操作，那么一段时间后，您会陷入困境，这很痛苦。

如果两个表通过一个分区键连接，并且分区方案匹配，则PostgreSQL 11现在能够优化分区。 这由一个特殊参数控制，该参数默认情况下处于关闭状态。

您可以分别计算每个部分的汇总，然后相加。 最后，您可以在父分区表上创建索引，然后将在与其连接的所有表上创建本地索引。

在“新增功能”部分中，提到了一件很棒的事情-在执行请求时可以丢弃部分的功能。 让我们检查一下它是如何工作的。 结果是这样一个表：



我们创建一个类型和一个由两列组成的表，这些表具有一个主键，一个bigserial列，用于插入数据。 我们创建第二个表，该表将被分区并将是第一个表的副本。 将主键添加到分区表。



该表将包含两种类型的条目：“女保姆”和“男司机”。 将会有一位女司机。 我们分为两部分，按列表划分，添加主键，并插入生成所有表的表中的所有数据。 结果完全没有意思：



注意请求。 我们从非分区表中选择所有内容，然后连接到分区表。 我们只花一小部分，只选择一种，它们会通过一种。 我们指示oss列应具有一个值。 事实证明，选择了可靠的驱动程序。

在执行时，我们特别禁用并行化，因为默认情况下PostgreSQL 11非常主动地并行化或多或少的复杂查询。 如果我们查看执行计划（解释分析），那么可以看出系统在两个部分中都添加了数据：保姆和驱动程序中，尽管保姆不在那儿。 没有对缓冲区的调用。 尽管PostgreSQL可以解决所有问题，但是花费的时间和使用的条件。 也就是说，消除分区声明无法立即生效。 也许在以后的版本中将对此进行纠正。 在这种情况下，pg_pathman模块在这种情况下可以正常工作。

指标

• 以单调方式（即B树）进行出价优化。 大家都知道，当您插入单调增长的数据时，结果并不是很快。 现在PostgreSQL能够以一种特殊的方式缓存结束页面，而不是从根到插入一直进行。 这大大加快了工作速度。
• PostgreSQL 10使得可以使用哈希索引，因为它开始使用WAL（预写日志）。 以前，我们获取了值，解锁了页面，返回了值。 对于下一个值，您必须再次阻止页面，返回，解锁等等。 现在，哈希变得更快了。 它允许您一次阻止页面以从哈希索引检索记录，从那里返回所有值并对其进行解锁。 现在，它已针对HASH，GiST和GIN实施。 将来，这可能会在SP-GiST中实现。 但是对于具有最小/最大逻辑的BRIN，原则上无法做到。
• 如果您过去用于构建功能索引，则将有效禁用HOT更新（仅堆元组）。 在PostgreSQL中更新记录时，实际上会创建一个新副本，这需要粘贴到表中的所有索引中，以便新值指向新元组。 这种优化已经实施了很长时间：如果更新不更改索引中未包含的字段并且同一页上有可用空间，则索引不会更新，并且在元组的旧版本中会放置指向新版本的指针。 这使您可以稍微减少更新的问题。 但是，如果您具有功能索引，则这种优化根本不起作用。 在PostgreSQL 11中，它开始工作。 如果您构建了功能索引并更新了不更改功能索引依赖项的元组，则HOT更新将起作用。

覆盖指数

该功能是三年前由PostgresPro实现的，而PostgreSQL一直都在尝试添加它。 覆盖索引意味着您可以直接在索引元组中向唯一索引添加额外的列。

怎么了 每个人都喜欢仅索引扫描，因为它们的工作迅速。 为此，构造了有条件的“覆盖”索引：



但是同时，您需要保持唯一性。 因此，正在建立两个指标，即窄和宽。
缺点是在应用真空，插入或更新表时，必须更新两个索引。 因此，插入索引是一项缓慢的操作。 覆盖索引将仅允许管理一个索引。

是的，他有一些限制。 更确切地说，可能无法立即理解其好处。 第一个创建索引中的列c和d不必是为其定义b树索引的标量类型。 也就是说，它们不一定具有更少的比较。 它可以是点或多边形。 唯一的是，该元组应小于2.7 Kb，因为索引中没有烘烤，但是您可以适应无法比较的值。

但是，在具有这些保证覆盖列的索引内，搜索时不会进行任何计算。 这应该通过位于索引上方的过滤器来完成。 一方面，为什么不在索引内计算它，另一方面，这是一个额外的函数调用。 但是，一切并没有看起来那么可怕。

好吧，此外，您可以将这些涵盖的列添加到主键中。

SP GiST

很少有人使用此索引，因为它非常具体。 尽管如此，仍然有可能不完全插入其中。 这是指有损-索引，压缩。 以多边形为例。 而是在索引中放置一个边界框，即包含所需多边形的最小矩形。 在这种情况下，我们将矩形表示为四维空间中的一个点，然后在四维空间中使用经典的quad3。

同样为SP-GiST引入了“前缀搜索”操作。 如果一行是另一行的前缀，则返回true。 他们不仅这样介绍了它，而且为了支持SP-GiST提出了这样的要求。

SELECT * FROM table WHERE c ^@ „abc“

在b树中，每行限制为2.7 Kb，但SP-GiST没有限制。 的确，PostgreSQL有一个限制：单个值不能超过1 GB。

性能表现

• 位图索引仅扫描已出现 。 它与经典索引仅扫描相同，但不能保证任何顺序。 因此，它仅适用于诸如count（*）之类的某些聚合，因为位图无法将字段从索引传输到执行程序。 他只能报告满足条件的记录的事实。
• 下一个创新是在应用真空过程中更新自由空间贴图 。 不幸的是，没有一个使用PostgreSQL的系统开发人员认为有必要在表末尾删除，否则会出现漏洞和未分配的空间。 为了跟踪这一点，我们实现了FSM，它允许我们不扩大表格，而是将元组插入空隙。 以前，这是在真空下完成的，但是最后。 现在，真空能够在此过程中做到这一点，在负载很重的系统中，它有助于使工作台尺寸保持受控。
• 在真空执行过程中可以跳过索引扫描 。 实际上，根据数据库理论，所有PostgreSQL索引都称为二级索引。 这意味着索引存储在表之外；指针从表指向表。 仅索引扫描允许您不要在指针上执行此跳转，而是直接从索引中获取。 但是，删除记录的真空不能仅仅因为索引中没有这样的数据而在索引中查看它们并决定是否删除它们。 因此，真空总是分两次进行。 首先，他浏览表格并找出需要删除的内容。 然后转到该表所附的索引，删除引用找到的记录，返回到表并删除它要去的内容。 并且不一定总是需要进入索引的阶段。
  
  如果自上次清理以来没有删除或更新，则您没有失效记录，则不需要删除它们。 在这种情况下，您无法转到索引。 还有其他一些细微之处，b树不会立即删除其页面，而是要分两次删除。 因此，如果您删除了表中的许多数据，则需要进行清理。 但是，如果要释放索引中的空间，请抽真空两次。
  
  会有人感到惊讶，该表中没有删除或更新的内容是什么？ 其实很多处理这个，只是不考虑。 这些是仅追加表，例如在其中添加了日志。 在它们中，去除非常罕见。 从而大大节省了真空/自动真空的时间，减少了磁盘上的负载，缓存的使用等。
• 同时进行竞争性交易 。 这不是创新，而是进步。 现在PostgreSQL检测到它将立即提交，并延迟了当前事务的提交，等待其余的提交。 请注意，如果您使用的是带有2至4个核心的小型服务器，则此功能几乎没有作用。
• postgres_fdw（外部数据包装器） 。 FDW是一种连接外部数据源的方式，使它看起来像真正的会议后。 postgres_fdw允许您将一个表从相邻实例连接到您的实例，它看起来几乎像一个真正的实例。 现在，更新和删除的限制之一已被删除。 PostgreSQL通常会猜测您需要发送原始数据。 执行联接请求的方法非常简单：我们在计算机上执行它，我们使用FDW从实例中拉出表，找出需要删除的id主键，然后应用更新和/或删除，即来回传递的数据。 现在可以做。 当然，如果表位于不同的计算机上，这并不是那么容易，但是FDW允许您让远程计算机执行操作，而我们只是在等待。
• toast_tuple_target 。 在某些情况下，数据略微超出了限制，在此之后有必要进行烘烤，但是同时烘烤这些值并不总是令人愉快的。 假设您有90个字节的限制，并且需要容纳100个字节。您必须开始10个字节的toast，分别添加它们，然后在选择此字段时，您需要转到toast索引，找出所需的数据在哪里，转到toast表，收集并给予。

现在，借助微调，您可以更改整个数据库或单独表的此行为，以使这样小的出口无需使用Toast。 但是，您必须了解自己在做什么，否则，将无法正常工作。

沃尔

• WAL（预写日志）是一个预写日志。 现在在initdb中设置WAL段的大小。 谢天谢地，不是在编译时。
• 逻辑也发生了变化。 以前，WAL段的集合是从倒数第二个检查点开始保存的，而现在是从最后一个检查点保存的。 这样可以大大减少所存储的数据量。 但是，如果您有一个1 TB的数据库，并且TPS = 1，即每秒一个请求，那么您将看不到差异。

备份与复制

• 截断出现在逻辑复制中 。 这是DML操作的最后一个，未在逻辑复制中反映出来。 现在反映出来。
• 有关准备的消息出现在逻辑复制中 。 现在您可以捕获准备事务，这是逻辑复制中的两阶段提交。 这是为群集的构建而实现的-异构，均质，分片且未着色的，多主群集等。
• pg_basebackup临时表和未记录表的异常 。 许多人抱怨pg_basebackup包含列出的表。 排除它们之外，我们减小了备份的大小。 但前提是您使用临时表和未记录的表，否则此选项对您将无用。
• 流复制中的校验和控制（用于表） 。 这使您可以了解副本所发生的情况。 到目前为止，该功能仅针对表实现。
• 复制插槽的位置得到了提升 。 与往常一样，只有在有WAL的情况下，您才可以向前回退。 此外，您需要非常了解自己在做什么以及为什么这么做。 在我看来，这更多是一种开发选项，但是将逻辑复制用于某些奇异应用程序的用户可以享受它。

对于dba

• 更改表，添加列，默认X不为null ，写入整个表。 为此需要支付少量费用：默认值单独存储。 如果选择元组并需要此列，则PostgreSQL被迫遵循其他编码路径以提取临时值，将其替换为元组并提供给您。 但是，可以忍受它。
• 真空/分析 。 以前，您只能对整个数据库或单个表应用真空或分析。 现在可以使用一个命令对多个表执行此操作。

并行执行

• b树索引的并行构造 。 在版本11中，可以将b树索引嵌入到多个工作程序中。 如果您有一台非常好的机器，许多磁盘和许多内核，那么您可以并行构建索引，这将保证性能显着提高。
• 使用共享的哈希表对执行程序进行哈希并行连接 。 , -. , . - , . .
• , union, create table as, select create materialized view!
• - (limit) . .

:

alter table usr reset (parallel_workers)
create index on usr(lower((so).occ)) — 2
alter table usr set (parallel_workers=2)
create index on usr(upper((so).occ)) — 1.8

parallel worker. . 16 4 ( ) 2 ., — 1,8 . , , . , .

:

explain analyze
select u1.* from usr u, usr1 u1 where
u.id=u1.id+0

, . , user — , . . , , .

, PostgreSQL 11 .



1425 , 1,5 . 1,4 . 2 . , 9.6 : 1 — 1 ., 2 1 . , 10 tuple. 11 . : user, batch, x-scan append .

:



. 211 , 702 . , 510 1473. , 2 .

parallel hash join. . — 4. , .

parallel index scan . batch . ? hash join, . user . , parallel hash, .

1 . , OLAP-, OLTP . OLTP , .

.

• . , . , «» «», index scan, . (highly skewed data), , . . , , .
• «», .

Window-

SQL:2011, .

, , . , , , , , .

websearch, . , . , .

# select websearch_to_tsquery('dog or cat');
----------------------
'dor' | 'cat'
# select websearch_to_tsquery('dog -cat');
----------------------
'dor' & !'cat'
# select websearch_to_tsquery('or cat');
----------------------
'cat'

— dog or cat — . Websearch . | , . “or cat”. , . websearch “or” . , -, .

Websearch — . : , . , .

Json(b)

10- , 11- . json json(b), tsvector. ( json(b)) - . , , , bull, numeric, string, . .

# select jsonb_to_tsvector
('{"a":"texts", "b":12}', '"string"');
-------------------
'text':1
# select jsonb_to_tsvector
('{"a":"texts", "b":12}', '["string", "numeric"]');
-------------------
'12':3 'text':1

json(b), . , , , .

PL/*

.

CREATE PROCEDURE transaction_test1()
LANGUAGE plpgsql
AS $$
BEGIN
FOR i IN 0..9 LOOP
INSERT INTO test1 (a) VALUES (i);
IF i % 2 = 0 THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
END LOOP;
END
$$;
CALL transaction_test1();

call, , . . . select, insert .

, , PostgreSQL . Perl, Python, TL PL/pgSQL. Perl sp begin, .

PL/pgSQL : , .

pgbench

pgbench ICSB bench — , , . if, , . case, - . --init-steps , , .

random-seed. zipfian- . / — , . - , , - , .

, , - .

PSQL

, PSQL, . exit quit.

• — copy, 2 ³² . copy : 2 ³² - . , 2 ³¹ 2 ³² copy . 64- , 2 ⁶⁴ .
• POSIX : NaN ⁰ = 1 1 ^NaN = 1.