Hi,
I want to discuss performance improvements of INSERTs to a partitioned table.
When an application inserts records into a table partitioned into thousands, find_all_inheritors() locks all of the partitions.
Updating partition key locks in the same way.
So, Execution time becomes longer as the number of partition increases.
* nparts 8
testdb=# explain analyze insert into test.accounts_history(aid, delta, mtime) values(8, 5000, current_timestamp);
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Insert on accounts_history (cost=0.00..0.02 rows=1 width=20) (actual time=0.281..0.281 rows=0 loops=1)
-> Result (cost=0.00..0.02 rows=1 width=20) (actual time=0.079..0.080 rows=1 loops=1)
Planning Time: 0.080 ms
Execution Time: 0.362 ms
(4 rows)
* nparts 8192
testdb=# explain analyze insert into test.accounts_history(aid, delta, mtime) values(8192, 5000, current_timestamp);
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Insert on accounts_history (cost=0.00..0.02 rows=1 width=20) (actual time=0.058..0.059 rows=0 loops=1)
-> Result (cost=0.00..0.02 rows=1 width=20) (actual time=0.032..0.034 rows=1 loops=1)
Planning Time: 0.032 ms
Execution Time: 12.508 ms
(4 rows)
Locking only the target partitions like the patch previously proposed by David[1], the performance will improve greatly.
* nparts 8192 (patched)
testdb=# explain analyze insert into test.accounts_history(aid, delta, mtime) values(8192, 5000, current_timestamp);
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Insert on accounts_history (cost=0.00..0.02 rows=1 width=20) (actual time=0.415..0.416 rows=0 loops=1)
-> Result (cost=0.00..0.02 rows=1 width=20) (actual time=0.140..0.141 rows=1 loops=1)
Planning Time: 0.120 ms
Execution Time: 1.694 ms
(4 rows)
However, I am concerned that "unsafe" is included in the name of this patch.
If locking only target partitions and locking all of partitions are executed at the same time, a problem may occurs.
But, I'm not sure what kind of problem will occur.
Is it enough to lock only the target partitions?
If a problem occurs in above case, I think it is safer to divide the steps to acquire the lock into two.
In first step, locking only the parent table in share or exclusive mode.
In second step, locking only the target partitions after locking the parent table.
Thoughts?
[1]: https://www.postgresql.org/message-id/CAKJS1f_1RJyFquuCKRFHTdcXqoPX-PYqAd7nz=GVBwvGh4a6xA@mail.gmail.com
regards,
Sho Kato
