作者
digoal
日期
2019-09-01
标签
PostgreSQL , tpcb , pgbench_account , 分区表
背景
https://www.postgresql.org/message-id/flat/alpine.DEB.2.21.1907230826190.7008@lancre
pgbench测试tpcb模型，支持分区表（pgbench_account）。
While doing some performance tests and reviewing patches, I needed to
create partitioned tables. Given the current syntax this is time
consumming.
The attached patch adds two options to create a partitioned "account"
table in pgbench.
It allows to answer quickly simple questions, eg "what is the overhead of
hash partitioning on a simple select on my laptop"? Answer:
```
# N=0..?
sh> pgench -i -s 1 --partition-number=$N --partition-type=hash
# then run
sh> pgench -S -M prepared -P 1 -T 10
# and look at latency:
# no parts = 0.071 ms
# 1 hash = 0.071 ms (did someone optimize this case?!)
# 2 hash ~ 0.126 ms (+ 0.055 ms)
# 50 hash ~ 0.155 ms
# 100 hash ~ 0.178 ms
# 150 hash ~ 0.232 ms
# 200 hash ~ 0.279 ms
# overhead ~ (0.050 + [0.0005-0.0008] * nparts) ms
```
Fabien.
``` --partitions=NUM Create a partitioned pgbench_accounts table with NUM partitions of nearly equal size for the scaled number of accounts. Default is 0, meaning no partitioning.
--partition-method=NAME Create a partitioned pgbench_accounts table with NAME method. Expected values are range or hash. This option requires that --partitions is set to non-zero. If unspecified, default is range. ```
支持range, hash分区。
参考
https://www.postgresql.org/message-id/flat/alpine.DEB.2.21.1907230826190.7008@lancre
https://www.postgresql.org/docs/devel/pgbench.html
https://www.depesz.com/2019/10/28/waiting-for-postgresql-13-pgbench-add-partitions-and-partition-method-options/
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
