利用PostgreSQL 14的自底向上索引元组删除技术，有效防止B树索引膨胀

发表时间: 2021-10-29 14:00

作者：金涛

准备PG12和PG14两个环境，主要参数保持一致，并执行如下SQL初始化测试表：

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);

vacuum ANALYZE testtab;

PG12查看表索引大小：

postgres=# \dt+ testtab

List of relations

--------+---------+-------+----------+--------+-------------

(1 row)

postgres=# \di+ testtab*

List of relations

--------+-----------------------+-------+----------+---------+--------+-------------

(3 rows)

PG14查看表索引大小：

postgres=# \di+ testtab*

List of relations

--------+-----------------------+-------+----------+---------+-------------+---------------+--------+-------------

(3 rows)

新建pgbench.sql文件并写入如下SQL:

\set id random_gaussian(1, 10000, 10)

UPDATE testtab SET changed = changed + 1 WHERE id = :id;

分别在PG12和PG14运行如下pgbench基准测试：

time pgbench -M prepared -n -c 6 -f pgbench.sql -t 10000 postgres

查看索引及表大小：

PG12

postgres=# \di+ testtab*

List of relations

--------+-----------------------+-------+----------+---------+---------+-------------

(3 rows)

PG14

postgres=# \di+ testtab*

List of relations

--------+-----------------------+-------+----------+---------+-------------+---------------+---------+-------------

(3 rows)

可以看到明显PG14中的索引比PG12大小小的多得多。升级！升级！升级！

利用PostgreSQL 14的自底向上索引元组删除技术，有效防止B树索引膨胀

热门阅读

推荐阅读