PostgreSQL 14新特性--减少索引膨胀_腾讯云代理,腾讯云代理商,腾讯云优惠折扣-深圳航网科技有限公司

公司新闻

行业新闻

专业知识

PostgreSQL 14新特性--减少索引膨胀

| 2019-05-17

PostgreSQL 14新特性--减少索引膨胀

PG12中索引的存储更加高效，PG13添加索引条目去重功能进一步提升存储效率。PG14将带来“自底向上”的索引条目去除功能，旨在减少不必要的页面分裂、索引膨胀和更新大量索引带来的碎片。

为什么会出现索引膨胀

对于B-tree索引，表中每个行版本都有一个未死的索引条目（对所有人可见）。执行vacuum删除死记录时，也会删除对应的索引条目。和表一样，同样会在索引页中创建空的空间。这样的空间可以重用，但是如果没有新元组插入该页，这样的空间会保持为空。

这种膨胀在某种程度上是不可避免的，也是正常的。但如果膨胀太多，索引效率就会降低：

1) 对于索引范围扫描，必须扫描更多的页

2) RAM中缓存了索引页，意味着缓冲膨胀，就是浪费了RAM

3) 每个页中更少的索引条目意味着更少的“fan out”，索引树的层级将更高

如果频繁更新相同行，就会发生这种情况。VACUUM清理老元组前，表和索引会维护相同行的很多版本。如果索引页填满，将令人很烦：然后PG会将索引页分裂成2个。这是一个昂贵的操作，VACUUM执行完清理，我们最终会得到2个臃肿的页面而不是一个。

当前用于改善索引膨胀和性能的特性

HOT元组

HOT元组的创建可能是PG对抗索引中不必要条目的强大武器。使用此功能UPDATE创建产生的元组不会被索引条目引用，它还会引用元组的老版本。通过这种方法，不需要创建新的索引条目，可以避免索引膨胀。HOT参考：

https://www.cybertec-postgresql.com/en/hot-updates-in-postgresql-for-better-performance/

杀死索引条目

当索引扫描遇到一个指向死元组的条目时，标记该条目“killed”。后续索引扫描会在VACUUM删除他们之前跳过这些条目。此外，PG可以在索引页面已满时删除这样的条目，以避免页分裂。

PG14如何进一步减少索引膨胀

自下而上的索引元组删除比之前方法更进一步：他在索引页分裂即将发生前就删除指向死元组的索引条目。这可以减少索引条目的数量并避免昂贵的分裂，以及稍后VACUUM清理参数的膨胀。

在某种程度上，这执行了之前VACUUM的部分公众，在这点上可以避免索引膨胀。

案例

为了演示新功能效果，使用pgbench分别在PG13和14上执行操作：

测试表：

CREATE TABLE testtab (
   id        bigint
      CONSTRAINT testtab_pkey PRIMARY KEY,
   unchanged integer,
   changed   integer
);

INSERT INTO testtab
   SELECT i, i, 0
   FROM generate_series(1, 10000) AS i;

CREATE INDEX testtab_unchanged_idx ON testtab (unchanged);
CREATE INDEX testtab_changed_idx ON testtab (changed);

Pgbench名为bench.sql的脚本：

\set id random_gaussian(1, 10000, 10)
UPDATE testtab SET changed = changed + 1 WHERE id = :id;
UPDATE testtab SET changed = changed + 1 WHERE id = :id;
UPDATE testtab SET changed = changed + 1 WHERE id = :id;
UPDATE testtab SET changed = changed + 1 WHERE id = :id;
UPDATE testtab SET changed = changed + 1 WHERE id = :id;
UPDATE testtab SET changed = changed + 1 WHERE id = :id;
UPDATE testtab SET changed = changed + 1 WHERE id = :id;
UPDATE testtab SET changed = changed + 1 WHERE id = :id;
UPDATE testtab SET changed = changed + 1 WHERE id = :id;
UPDATE testtab SET changed = changed + 1 WHERE id = :id;

我运行脚本 60000 次（6 个客户端 10000 次迭代），如下所示：

pgbench -n -c 6 -f bench.sql -t 10000 test

比较测试结果

我们使用pgstattuple扩展来获取psql 的索引统计信息：

SELECT i.indexrelid::regclass AS index,
       s.index_size,
       s.avg_leaf_density
FROM pg_index AS i
   CROSS JOIN LATERAL pgstatindex(i.indexrelid) AS s
WHERE indrelid = 'testtab'::regclass;

这是我们在 v13 中得到的：

         index         │ index_size │ avg_leaf_density
═══════════════════════╪════════════╪══════════════════
 testtab_pkey          │     319488 │             66.6
 testtab_unchanged_idx │    4022272 │             5.33
 testtab_changed_idx   │    4505600 │            13.57
(3 rows)

对于 v14，结果是：

         index         │ index_size │ avg_leaf_density
═══════════════════════╪════════════╪══════════════════
 testtab_pkey          │     245760 │            87.91
 testtab_unchanged_idx │     532480 │            39.23
 testtab_changed_idx   │    4038656 │            14.23
(3 rows)

改进最大的时testtab_unchanged_idx。在13中，索引膨胀严重，而在14中仅有60%的膨胀（这对索引来说还不错）。

在这里我们看到了新功能的最大影响。UPDATE不扫扫描那个索引，因此没有killed的索引条目，“自底向上的删除”可以删除足够的这样的条目避免分裂。

也可以衡量testtab_pkey。由于UPDATE扫描该索引，死的索引元组被killed，新特性在分裂前删除这些元组。与13相比，效果不太明显，因为13已经很好地避免索引膨胀了。

索引testtab_changed_idx无法从新特性中获益。因为这进解决了UPDATE不修改索引值的情况。如果想知道为什么testtab_unchanged_idx叶子密度比13低：删除了索引重复数据。

Pg_upgrade后我们可以使用这项功能吗？

索引的存储格式没有变，所以pg_upgrade PG12及之后版本创建的索引后会自动公众。但之前版本创建的索引，需要REINDEX后获益。记住，pg_upgrade仅拷贝索引文件，不会更改内部索引版本。

总结

PG14继续改进B-tree索引。这个特性虽不是革命性的，但有望为许多公众负载提供改进的性能，尤其是那些有大量更新的工作负载。

编辑：航网科技来源：腾讯云本文版权归原作者所有转载请注明出处

关注我们

微信扫一扫关注我们