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

本文涉及的产品
云原生数据库 PolarDB MySQL 版,通用型 2核8GB 50GB
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
简介: PostgreSQL 14新特性--减少索引膨胀

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索引。这个特性虽不是革命性的,但有望为许多公众负载提供改进的性能,尤其是那些有大量更新的工作负载。


            原文


            https://www.cybertec-postgresql.com/en/index-bloat-reduced-in-postgresql-v14/

            相关实践学习
            使用PolarDB和ECS搭建门户网站
            本场景主要介绍如何基于PolarDB和ECS实现搭建门户网站。
            阿里云数据库产品家族及特性
            阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
            目录
            相关文章
            |
            4月前
            |
            存储 监控 关系型数据库
            B-tree不是万能药:PostgreSQL索引失效的7种高频场景与破解方案
            在PostgreSQL优化实践中,B-tree索引虽承担了80%以上的查询加速任务,但因多种原因可能导致索引失效,引发性能骤降。本文深入剖析7种高频失效场景,包括隐式类型转换、函数包裹列、前导通配符等,并通过实战案例揭示问题本质,提供生产验证的解决方案。同时,总结索引使用决策矩阵与关键原则,助你让索引真正发挥作用。
            257 0
            |
            监控 关系型数据库 数据库
            PostgreSQL的索引优化策略?
            【8月更文挑战第26天】PostgreSQL的索引优化策略?
            394 1
            |
            8月前
            |
            SQL 关系型数据库 OLAP
            云原生数据仓库AnalyticDB PostgreSQL同一个SQL可以实现向量索引、全文索引GIN、普通索引BTREE混合查询,简化业务实现逻辑、提升查询性能
            本文档介绍了如何在AnalyticDB for PostgreSQL中创建表、向量索引及混合检索的实现步骤。主要内容包括:创建`articles`表并设置向量存储格式,创建ANN向量索引,为表增加`username`和`time`列,建立BTREE索引和GIN全文检索索引,并展示了查询结果。参考文档提供了详细的SQL语句和配置说明。
            188 2
            |
            9月前
            |
            JSON 关系型数据库 PostgreSQL
            PostgreSQL 9种索引的原理和应用场景
            PostgreSQL 支持九种主要索引类型,包括 B-Tree、Hash、GiST、SP-GiST、GIN、BRIN、Bitmap、Partial 和 Unique 索引。每种索引适用于不同场景,如 B-Tree 适合范围查询和排序,Hash 仅用于等值查询,GiST 支持全文搜索和几何数据查询,GIN 适用于多值列和 JSON 数据,BRIN 适合非常大的表,Bitmap 适用于低基数列,Partial 只对部分数据创建索引,Unique 确保列值唯一。
            |
            SQL 关系型数据库 MySQL
            SQL Server、MySQL、PostgreSQL:主流数据库SQL语法异同比较——深入探讨数据类型、分页查询、表创建与数据插入、函数和索引等关键语法差异,为跨数据库开发提供实用指导
            【8月更文挑战第31天】SQL Server、MySQL和PostgreSQL是当今最流行的关系型数据库管理系统,均使用SQL作为查询语言,但在语法和功能实现上存在差异。本文将比较它们在数据类型、分页查询、创建和插入数据以及函数和索引等方面的异同,帮助开发者更好地理解和使用这些数据库。尽管它们共用SQL语言,但每个系统都有独特的语法规则,了解这些差异有助于提升开发效率和项目成功率。
            1401 0
            |
            关系型数据库 数据库 PostgreSQL
            PostgreSQL索引维护看完这篇就够了
            PostgreSQL索引维护看完这篇就够了
            944 0
            |
            SQL 关系型数据库 数据库
            RDS PostgreSQL索引推荐原理及最佳实践
            前言很多开发人员都知道索引对于数据库的查询性能至关重要,一个好的索引能使数据库的性能提升成千上万倍。但给数据库加索引是一项相对专业的工作,需要对数据库的运行原理有一定了解。同时,加了索引有没有性能提升、性能提升了多少,这些都是加索引前就想知道的。这项繁杂的工作有没有更好的方案呢?有!就是今天重磅推出...
            302 1
            RDS PostgreSQL索引推荐原理及最佳实践
            |
            1月前
            |
            安全 关系型数据库 MySQL
            MySQL安全最佳实践:保护你的数据库
            本文深入探讨了MySQL数据库的安全防护体系,涵盖认证安全、访问控制、网络安全、数据加密、审计监控、备份恢复、操作系统安全、应急响应等多个方面。通过具体配置示例,为企业提供了一套全面的安全实践方案,帮助强化数据库安全,防止数据泄露和未授权访问,保障企业数据资产安全。
            |
            16天前
            |
            缓存 关系型数据库 BI
            使用MYSQL Report分析数据库性能(下)
            使用MYSQL Report分析数据库性能
            54 3
            |
            22天前
            |
            关系型数据库 MySQL 数据库
            自建数据库如何迁移至RDS MySQL实例
            数据库迁移是一项复杂且耗时的工程,需考虑数据安全、完整性及业务中断影响。使用阿里云数据传输服务DTS,可快速、平滑完成迁移任务,将应用停机时间降至分钟级。您还可通过全量备份自建数据库并恢复至RDS MySQL实例,实现间接迁移上云。

            热门文章

            最新文章

            推荐镜像

            更多