Known limitations

This a non-exhaustive list of known limitations of pg_pathman:

Sub-partitions are not supported

Currently you can't create multilevel partitioning scheme.

INSERT ... ON CONFLICT is not supported

create table test(id int not null, val float8);
create unique index on test (id);
select create_range_partitions('test', 'id', 1, 10, 3);

insert into test values (0, 0) on conflict (id) do update set val=1;
ERROR:  ON CONFLICT clause is not supported with partitioned tables

Global primary keys are not supported

create table test(id int primary key, date timestamp not null);
select create_range_partitions('test', 'date', now(), '1 day'::interval, 4);

insert into test values(1, now());
insert into test values(1, now() + '1 day');

table test;
 id |            date
----+----------------------------
  1 | 2017-10-03 16:33:24.953012
  1 | 2017-10-04 16:33:31.476003
(2 rows)

Foreign keys pointing to partitioned tables are not supported

create table test(id int not null);
create unique index on test (id);
select create_range_partitions('test', 'id', 1, 10, 3);
insert into test values (1);

create table simple(id int references test(id));
insert into simple values (1);
ERROR:  insert or update on table "simple" violates foreign key constraint "simple_id_fkey"
DETAIL:  Key (id)=(1) is not present in table "test".

DIY logical replication is not supported (?)

This includes Londiste, pg_logical etc. Instead, take a look at pg_shardman.

Some optimizations may not be available on PostgreSQL 9.5

Due to significant API changes, these optimizations are not available on 9.5:

• Faster partition pruning in SELECT ... subqueries under UPDATE and DELETE statements;
• Faster partition pruning in SELECT ... FOR SHARE/UPDATE/etc.

See this page for more information.

UPDATE and DELETE queries on a partitioned table joined with another partitioned table

We've noticed that certain queries cannot be planned by pg_pathman:

• DELETE FROM partitioned_table_1 USING partitioned_table_2 ...;
• UPDATE partitioned_table_1 FROM partitioned_table_2 ...;

This is due to some limitations of PostgreSQL's planner: we can't hook inheritance planner used for DELETE and UPDATE queries on partitioned tables. As a result, we can't plan and execute queries that are going to modify several partitions:

create table a(val int not null);
select create_range_partitions('a', 'val', 1, 10, 3);
create table b(val int not null);
select create_range_partitions('b', 'val', 1, 10, 3);

delete from a using b where a.val = b.val;
ERROR:  DELETE and UPDATE queries with a join of partitioned tables are not supported

However, query will work as expected if it's obvious to the planner that it wants to modify only 1 (or 0) partition:

explain (costs off)
delete from a using b where a.val = b.val and a.val < 10;
               QUERY PLAN
-----------------------------------------
 Delete on a_1
   ->  Merge Join
         Merge Cond: (a_1.val = b_1.val)
         ->  Sort
               Sort Key: a_1.val
               ->  Seq Scan on a_1
                     Filter: (val < 10)
         ->  Sort
               Sort Key: b_1.val
               ->  Append
                     ->  Seq Scan on b_1
                     ->  Seq Scan on b_2
                     ->  Seq Scan on b_3
(13 rows)

As a workaround, you can use WITH statement to hide the joined partitioned table:

explain (costs off) 
with q as (select * from b)
delete from a using q where a.val = q.val;
              QUERY PLAN               
---------------------------------------
 Delete on a
   Delete on a
   Delete on a_1
   Delete on a_2
   Delete on a_3
   CTE q
     ->  Append
           ->  Seq Scan on b_1
           ->  Seq Scan on b_2
           ->  Seq Scan on b_3
   ->  Hash Join
         Hash Cond: (q.val = a.val)
         ->  CTE Scan on q
         ->  Hash
               ->  Seq Scan on a
   ->  Merge Join
         Merge Cond: (a_1.val = q.val)
         ->  Sort
               Sort Key: a_1.val
               ->  Seq Scan on a_1
         ->  Sort
               Sort Key: q.val
               ->  CTE Scan on q
   ->  Merge Join
         Merge Cond: (a_2.val = q.val)
         ->  Sort
               Sort Key: a_2.val
               ->  Seq Scan on a_2
         ->  Sort
               Sort Key: q.val
               ->  CTE Scan on q
   ->  Merge Join
         Merge Cond: (a_3.val = q.val)
         ->  Sort
               Sort Key: a_3.val
               ->  Seq Scan on a_3
         ->  Sort
               Sort Key: q.val
               ->  CTE Scan on q
(39 rows)