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

On 2018/07/16 13:16, Tsunakawa-san wrote:
>Thanks. And what does the comparison look like between the unpartitioned case and various partition counts? What's the performance characteristics in terms of the latency and partition count? I thought that's what you tried to reveal first?

In unpartitioned table, latency of SELECT/UPDATE statement is close to O(n), where n is number of records.
Latency of INSERT statements is close to O(1).

In partitioned table, up to 400 partitions, latency of SELECT/INSERT statement is close to O(log n), where n is the number of partitions.
Between 400 and 6400 partitions, latency is close to O(n).
Up to 400 partitions, latency of UPDATE statement is close to O(n).
Between 400 and 6400 partitions, latency of UPDATE statement seems to O(n^2).

Details are as follows.

unpartitioned table result (prepared mode)
------------------------------------------

scale | latency_avg | tps_ex | update_latency | select_latency | insert_latency
-------+-------------+-------------+----------------+----------------+----------------
100 | 0.24 | 4174.395738 | 0.056 | 0.051 | 0.04
200 | 0.258 | 3873.099014 | 0.065 | 0.059 | 0.04
400 | 0.29 | 3453.171112 | 0.081 | 0.072 | 0.041
800 | 0.355 | 2814.936942 | 0.112 | 0.105 | 0.041
1600 | 0.493 | 2027.702689 | 0.18 | 0.174 | 0.042
3200 | 0.761 | 1313.532458 | 0.314 | 0.307 | 0.043
6400 | 1.214 | 824.001431 | 0.54 | 0.531 | 0.043

partitioned talble result (prepared mode)
-----------------------------------------

num_part | latency_avg | tps_ex | update_latency | select_latency | insert_latency
----------+-------------+-------------+----------------+----------------+----------------
100 | 0.937 | 1067.473258 | 0.557 | 0.087 | 0.123
200 | 1.65 | 606.244552 | 1.115 | 0.121 | 0.188
400 | 3.295 | 303.491681 | 2.446 | 0.19 | 0.322
800 | 8.102 | 123.422456 | 6.553 | 0.337 | 0.637
1600 | 36.996 | 27.030027 | 31.528 | 1.621 | 2.455
3200 | 147.998 | 6.756922 | 136.136 | 4.21 | 4.94
6400 | 666.947 | 1.499383 | 640.879 | 7.631 | 9.642

regards,
-----Original Message-----
From: Tsunakawa, Takayuki [mailto:tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com]
Sent: Monday, July 16, 2018 1:16 PM
To: Kato, Sho/加藤 翔 <kato-sho(at)jp(dot)fujitsu(dot)com>
Cc: 'Amit Langote' <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>; PostgreSQL mailing lists <pgsql-hackers(at)postgresql(dot)org>
Subject: RE: How to make partitioning scale better for larger numbers of partitions

From: Kato, Sho [mailto:kato-sho(at)jp(dot)fujitsu(dot)com]
> I did pgbench -M prepared and perf record.
>
> UPDATE latency in prepared mode is 95% shorter than in simple mode.
> SELECT latency in prepared mode is 54% shorter than in simple mode.
> INSERT latency in prepared mode is 8% shorter than in simple mode.

Thanks. And what does the comparison look like between the unpartitioned case and various partition counts? What's the performance characteristics in terms of the latency and partition count? I thought that's what you tried to reveal first?

Regards
Takayuki Tsunakawa