RE: Speeding up INSERTs and UPDATEs to partitioned tables

Thanks David!

I did benchmark with pgbench, and see a speedup for INSERT / UPDATE scenarios.
I used range partition.

Benchmark results are as follows.

1. 11beta2 result

part_num | tps_ex | latency_avg | update_latency | select_latency | insert_latency
----------+------------+-------------+----------------+----------------+----------------
100 | 479.456278 | 2.086 | 1.382 | 0.365 | 0.168
200 | 169.155411 | 5.912 | 4.628 | 0.737 | 0.299
400 | 24.857495 | 40.23 | 36.606 | 2.252 | 0.881
800 | 6.718104 | 148.853 | 141.471 | 5.253 | 1.433
1600 | 1.934908 | 516.825 | 489.982 | 21.102 | 3.701
3200 | 0.456967 | 2188.362 | 2101.247 | 72.784 | 8.833
6400 | 0.116643 | 8573.224 | 8286.79 | 257.904 | 14.949

2. 11beta2 + patch1 + patch2

patch1: Allow direct lookups of AppendRelInfo by child relid
commit 7d872c91a3f9d49b56117557cdbb0c3d4c620687
patch2: 0001-Speed-up-INSERT-and-UPDATE-on-partitioned-tables.patch

part_num | tps_ex | latency_avg | update_latency | select_latency | insert_latency
----------+-------------+-------------+----------------+----------------+----------------
100 | 1224.430344 | 0.817 | 0.551 | 0.085 | 0.048
200 | 689.567511 | 1.45 | 1.12 | 0.119 | 0.05
400 | 347.876616 | 2.875 | 2.419 | 0.185 | 0.052
800 | 140.489269 | 7.118 | 6.393 | 0.329 | 0.059
1600 | 29.681672 | 33.691 | 31.272 | 1.517 | 0.147
3200 | 7.021957 | 142.412 | 136.4 | 4.033 | 0.214
6400 | 1.462949 | 683.557 | 669.187 | 7.677 | 0.264

benchmark script:

\set aid random(1, 100 * 1)
\set delta random(-5000, 5000)
BEGIN;
UPDATE test.accounts SET abalance = abalance + :delta WHERE aid = :aid;
SELECT abalance FROM test.accounts WHERE aid = :aid;
INSERT INTO test.accounts_history (aid, delta, mtime) VALUES (:aid, :delta, CURRENT_TIMESTAMP);
END;

partition key is aid.

-----Original Message-----
From: David Rowley [mailto:david(dot)rowley(at)2ndquadrant(dot)com]
Sent: Thursday, July 05, 2018 6:19 PM
To: Kato, Sho/加藤 翔 <kato-sho(at)jp(dot)fujitsu(dot)com>
Cc: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: Speeding up INSERTs and UPDATEs to partitioned tables

On 5 July 2018 at 18:39, Kato, Sho <kato-sho(at)jp(dot)fujitsu(dot)com> wrote:
> postgres=# create table a(i int) partition by range(i); CREATE TABLE
> postgres=# create table a_1 partition of a for values from(1) to
> (200); CREATE TABLE postgres=# create table a_2 partition of a for
> values from(200) to (400); server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
> The connection to the server was lost. Attempting reset: Failed.

Hi,

Thanks for testing. I'm unable to reproduce this on beta2 or master as of f61988d16.

Did you try make clean then building again? The 0001 patch does change PartitionDescData, so if you've not rebuilt all .c files which use that then that might explain your crash.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services