Discussion:
An Inesrt on a partitioned table become very slow
Tomer Praizler
2018-07-09 05:35:31 UTC
Permalink
Hey,

I use Postgres 10.1, and I have the following structure:
Parent table with columns:
range_value: double, timestamp: timestamp, device_id: integer, count:integer

which is partitioned with Partition key: LIST (range_value) which has a
value of 1 - 26.
So basically it has 26 child partitions.

Each one of those partitions it partitioned by range. essentially a daily
partition.

This is how the perant_table_with_range_value_1 is conofigured:

Partition of: perant_table FOR VALUES IN ('1')
Partition constraint: ((range_value IS NOT NULL) AND (range_value = ANY
(ARRAY['0'::double precision])))
Partition key: RANGE ("timestamp")

At the moment I have 128 days of partitions in each one of the ranged
partitioned.
Which means a total of 26 * 128 = 3328 total partitions.

My problem is that inserts became very slow.
I used to bulk insert 10000 rows in 6 seconds, now it takes 45 seconds.

Is this an expected behavior? is there something I missing?
Thank you!
Fabio Pardi
2018-07-09 09:16:24 UTC
Permalink
Hi Tomer,

On Postgres, partitioning does not scale well when partitions are in the order of 4+ figures.

https://www.postgresql.org/docs/10/static/ddl-partitioning.html

It is very likely that most of the time is spent by the query planner.

regards,

fabio pardi
Post by Tomer Praizler
Hey, 
range_value: double, timestamp: timestamp, device_id: integer, count:integer
which is partitioned with Partition key: LIST (range_value) which has a value of 1 - 26.
So basically it has 26 child partitions. 
Each one of those partitions it partitioned by range. essentially a daily partition.
Partition of: perant_table FOR VALUES IN ('1')
Partition constraint: ((range_value IS NOT NULL) AND (range_value = ANY (ARRAY['0'::double precision])))
Partition key: RANGE ("timestamp")
At the moment I have 128 days of partitions in each one of the ranged partitioned.
Which means a total of 26 * 128 = 3328 total partitions. 
My problem is that inserts became very slow. 
I used to bulk insert 10000 rows in 6 seconds, now it takes 45 seconds. 
Is this an expected behavior? is there something I missing? 
Thank you!
David Rowley
2018-07-09 10:31:02 UTC
Permalink
Post by Fabio Pardi
It is very likely that most of the time is spent by the query planner.
Unless the INSERTs have a SELECT clause, then the query planner's job
is pretty minimal for INSERTs. Most of the overhead will come from
ExecSetupPartitionTupleRouting(). This will become a little faster in
pg11 (once released), but only slightly, and a bunch of other things
means that partitioning just does not scale well to
that number of partitions. Most of those are in the planner. Some of
those are also faster in pg11 (partition pruning), but also there's
still quite a bit to do before partitioning will scale well to this
number of partitions.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Loading...