Re: How to make partitioning scale better for larger numbers of partitions

On Fri, Jul 13, 2018 at 9:23 AM, Kato, Sho <kato-sho(at)jp(dot)fujitsu(dot)com> wrote:
>>I wondered if you compared to PG10 or to inheritence-partitioning (parent with relkind='r' and either trigger or rule or >INSERT/UPDATE directly into child) ?
>
> Thank you for your reply.
>
> I compared to PG11beta2 with non-partitioned table.
>
> Non-partitioned table has 1100 records in one table.
> Partitioned table has one record on each leaf partitions.
>

I don't think partitioning should be employed this way even for the
sake of comparison. Depending upon the size of each tuple, 1100 tuples
are inserted into a single table, they will probably occupy few
hundred pages. In a partitioned table with one tuple per partition
they will occupy 1100 pages at least. There is other space, locking
overheads to maintain 1100 tables. I think the right way to compare is
to have really large that which really requires 1100 partitions and
then compare performance by putting that data in 1100 partitions and
in an unpartitioned table. Even with that kind of data, you will see
some difference in performance, but that won't be as dramatic as you
report.

I might be missing something though.

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company