PostgreSQL表分区不同实现,性能基准测试[翻译]

原文链接

original by Alexander Korotkov@Postgres Professional company 翻译: 小次郎@飞象

最近(2016 3月份) pg_pathman 更新提供了更改查询删除查询支持. 由于使用了PostgreSQL查询计划挂钩,更新和删除规划在对单个分区查询时,速度会有改善 其他方式的查询,依旧使用缓慢的继承查询规划 当然,UPDATE,DELETE操作只涉及一个一个分区的情况似乎是最常见,最需要优化的。

此外,分享一些基准测试。 这一基准测试是一年的记账,按天来做表分区,总计约1百万(1M)条数据 当然,这只是个测试示列,因为在实际中由于数据量太小,没有人会分成这么多分区

但它仍然很高兴见到分区开销。下列操作的性能进行比较:

  • 选择单行 (使用时间戳) SELECT one;
  • 选择某一天的全部数据(查询单个分区的全部数据) SELECT one_partation
  • 随机插入单行 (使用随机时间戳) INSERT
  • 随机更新单行 (使用随机时间戳筛选) UPDATE

以下的分区方法进行了比较︰

单表,没有使用分区 pg_partman 扩展 pg_pathman 扩展

在2xIntel Xeon cpu X5675 @ 3.07GHz,24 GB 内存的服务器上,数据库参数配置fsync=off 使用10个线程可以得到如下的结果。

我可以得到以下结论

  • pg_pathman 显著优于 pg_partman,主要是由于pg_pathman 使用查询计划钩子,而pg_partman 使用内置的继承机制.

  • 当选择查询或更新单个行时,pg_pathman 和普通表几乎是一样快,插入单个行的差异是稍大一点,因为触发器用来的

  • 在选择整个分区查询时,pg_partman 和 pg_pathman 时选择整个分区之间的区别不是那样数十倍(在查询单行时)。 因为这时,查询规划时间只占整个语句执行时间的一小部分。

  • 随机INSERT时,pg_pathman 的执行速度仍然远远超过与pg_partman,是由于其他两者在在父关系上使用了触发器,但是,pg_pathman 使用了快速的C函数来实现。

  • 选择整个分区表分区由 pg_pathman 的时候是略高于从平原表中选择相同的行。 这是因为在扫描整个表分区时,使用了顺序扫描,而索引扫描用于选择表的一部分。 当数据量很大,缓存不足以存下时,这种差异预计会更大。

用于基准测试的 sql 脚本,请参阅此依据

create_*.sql 创建日记帐表使用分区的各种方法。 pg_bench的脚本: select_one.sql、 select_day.sql、 insert.sql 和 update.sql .

P.S. 这篇文章不是对 pg_partman 的批评。 在很长时间以来,是一个很强大的模块,并且使用了很久.

pg_pathman新的扩展模块。

pg_pathman github 地址

译者注: 表分区模块

相关文章

来源:http://www.postgres.cn/docs/11/ 4.1.1. 标识符和关键词 SQL标识符和关键词必须以一个...
来源:http://www.postgres.cn/docs/11/ 8.1. 数字类型 数字类型由2、4或8字节的整数以及4或8...
来源:http://www.postgres.cn/docs/11/ 5.1. 表基础 SQL并不保证表中行的顺序。当一个表被读...
来源:http://www.postgres.cn/docs/11/ 6.4. 从修改的行中返回数据 有时在修改行的操作过程中...
来源:http://www.postgres.cn/docs/11/ 13.2.1. 读已提交隔离级别 读已提交是PostgreSQL中的...
来源:http://www.postgres.cn/docs/11/ 9.7. 模式匹配 PostgreSQL提供了三种独立的实现模式匹...