Postgres 10 and partitions
The problem with lots of partitions#
Gaining lots of knowledge on Postgres 10 especially about partioning
, we observed a strange
behavior on UPDATE
and DELETE
lately while the number of partitions was growing
a lot > 1500
.
To recap this is the table structure
CREATE TABLE IF NOT EXISTS projection_table
(
id varchar(200) NOT NULL,
provider_id varchar(60) NOT NULL,
input varchar(4096) NOT NULL,
label varchar(100),
modified_at timestamp NOT NULL
) PARTITION BY LIST (provider_id);
Each partition has 2 more indexes which speed up the queries when the right partition was already determined:
CREATE TABLE IF NOT EXISTS proj_prov_100
PARTITION OF projection_table
FOR VALUES IN ('/providers/100');
CREATE INDEX IF NOT EXISTS idx_proj_prov_100_mod_at
ON proj_prov_100 (modified_at DESC);
CREATE INDEX IF NOT EXISTS idx_proj_prov_100_cid
ON proj_prov_100 (card_id);
SELECT
works fine as it denotes the result of the EXPLAIN
. It finds the
right partition:
db=> EXPLAIN SELECT * FROM projection_table WHERE provider_id = '/providers/100' AND id ='123';
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Append (cost=0.41..8.43 rows=1 width=379)
-> Index Scan using idx_proj_prov_100_cid on proj_prov_100 (cost=0.41..8.43 rows=1 width=379)
Index Cond: ((id)::text = '123'::text)
Filter: ((provider_id)::text = '/providers/100'::text)
(432 rows)
Time: 1025.446 ms
On the other hand, a DELETE
and UPDATE
took forever. Even the EXPLAIN
of the command did not
return several minutes.
db=> EXPLAIN DELETE FROM
projection_table
WHERE provider_id = '/providers/100' and id ='123';
...
Solution#
After playing around with the queries, we found out that directly
accessing the partition tables on, especially on DELETE
speeded things up
drastically.
As we knew the pattern for the names of the partitions and their indices,
we rewrote the Queries by accessing the partitions directly instead of querying the parent table. This, for example, show the following
UPSERT
query, which has to implemented by hand, as we could not use
the ON CONFLICT
variant.
BEGIN WORK;
DELETE FROM proj_prov_100
WHERE card_id = '123';
INSERT INTO proj_prov_100
VALUES (...);
COMMIT WORK;
We are looking forward to taking advantage of the PG11 improvement on paritioning and its support by AWS RDS.