Graphql-engine: support for postgres table partitioning

[dsandip]

From Mounesh & co.

[ecthiender]

@dsandip @coco98 can you add more details please ? Is this in PG 11?

[ecthiender]

Postgres 10 - partitioned tables

HGE compatibility

1. queries work
2. insert mutations work as long as inserted data has a corresponding partition
3. update mutations work as long as the new data does not require moving the row to a new partition
4. delete mutations work
5. relationships to/from partitioned table does not work

Limitations:

1. Partitioned tables cannot have primary keys
2. Foreign keys (from other tables) cannot reference paritioned tables, nor are foreign key references from a partitioned table to another table
3. Inserts that does not have corresponding partition will fail
4. Updates that will move a row across partitions will fail

More Details:

Supports table partitioning, but each parition has to be manually created:

CREATE TABLE measurement (
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);

CREATE TABLE measurement_y2017 PARTITION OF measurement
  FOR VALUES FROM ('2017-01-01') TO ('2018-01-01');

CREATE TABLE measurement_y2018 PARTITION OF measurement 
  FOR VALUES FROM ('2018-01-01') TO ('2019-01-01');

Similarly indicies has to be manually managed:

CREATE INDEX ON measurement_y2017 (logdate);

CREATE INDEX ON measurement_y2018 (logdate);

Then:

1. select on the parent table is handled by the query planner to gather results from the partitions
2. insert on the parent table will automatically be inserted to the correct partition; if no corresponding partition exist, if will fail
   e.g - INSERT INTO measurement VALUES ('2016-10-09', 66, 23) will fail
3. update on the parent table works as long as new data does not require moving partitions
4. delete on the parent table works

Postgres 11 - partitioned tables

HGE compatibility

1. queries work
2. insert mutations work (as long as inserted data has a default partition created)
3. update mutations work (even across partitions)
4. delete mutations work
5. relationships work

Notes:

1. Partitioned tables can have primary keys (partition keys have to part of PKs)
2. Foreign keys work (referencing to another table, or another table referencing it)
3. Inserts that does not have a default partition will fail

More details:

CREATE TABLE city (
  id SERIAL PRIMARY KEY,
  name TEXT
);

CREATE TABLE measurement (
  logdate         date not null,
  peaktemp        int,
  unitsales       int,
  city_id         int REFERENCES city(id)
) PARTITION BY RANGE (logdate);

CREATE TABLE measurement_y2017 PARTITION OF measurement
  FOR VALUES FROM ('2017-01-01') TO ('2018-01-01');

CREATE TABLE measurement_y2018 PARTITION OF measurement
  FOR VALUES FROM ('2018-01-01') TO ('2019-01-01');

CREATE TABLE measurement_default PARTITION OF measurement DEFAULT;

CREATE INDEX ON measurement (logdate);

Index creation on partition tables are automatic (even for future partitions, pg will automagically create indicies).

[amesas]

+1 ;)

[SnirShechter]

Any update on this?

[tirumaraiselvan]

We can track partitioned tables via API (track_table) but it doesn't show in console (#5071). Furthermore, we need to do the following

1. Mention limitations of partitioned tables in PG 10 in docs. TBH, I am in two minds about this since these are PG limitations and not Hasura limitations.
2. Capture errors for runtime errors for partitioned tables (if we are not checking already), especially for PG 10.

[tirumaraiselvan]

Closing this now as console support is added in v1.2 onwards, we will create separate issues for above points (if required).