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.

© 2021