Thread: Re: pgbench with partitioned tables

On Fri, Jan 31, 2025 at 2:27 PM Sami Imseih <samimseih@gmail.com> wrote:
>
> pgbench_accounts is the only table that should grow to
> the point where partitioning can benefit the tpcb-like
> benchmark.

pgbench_history is append-only, so I could see the argument for
partitioning that.

> IMO, If there is a good reason to allow the other pgbench
> tables to be partitioned, that may be better to think
> about. I am not sure there is though.

see this thread [1] proposing partitioning pgbench_history last year.

[1]
https://www.postgresql.org/message-id/flat/CAAKRu_Zo8ST-Qk8VQ4KFkbMQcqJsQQz5r%2BYRRbecS3avgkoZhw%40mail.gmail.com#ca9397c201ed483cb02f07dcaaa2773c

> > IMO, If there is a good reason to allow the other pgbench
> > tables to be partitioned, that may be better to think
> > about. I am not sure there is though.
>
> see this thread [1] proposing partitioning pgbench_history last year.
>
> [1]
https://www.postgresql.org/message-id/flat/CAAKRu_Zo8ST-Qk8VQ4KFkbMQcqJsQQz5r%2BYRRbecS3avgkoZhw%40mail.gmail.com#ca9397c201ed483cb02f07dcaaa2773c

I don't see the partitioning history being beneficial for the
built-in workloads, but it may make sense to partition the
history table if you intend to run a custom benchmark that
includes reading specific accounts from the history table.
Partitioning by date range as you suggest [1] makes sense as well.

Maybe It will be good to provide more flexibility around which
tables to partition and the partition key(s) for the pgbench schema so
to benchmark different partition strategies.

[1] https://www.postgresql.org/message-id/CAAKRu_Zo8ST-Qk8VQ4KFkbMQcqJsQQz5r%2BYRRbecS3avgkoZhw%40mail.gmail.com

Regards,

Sami