Declarative partitioning improvements

Background

Since version 10 PostgreSQL implements declarative partitioning.

It already has many important features:

• various forms of partitioning: RANGE/ LIST / HASH;
• ATTACH PARTITION/ DETACH PARTITION commands;
• partition pruning - runtime query optimization.

There are also 2 alternative projects pg_pathman and pg_partman, which were developed in parallel with declarative partitioning.

They contain different sets of features, but most importantly, they implement functions that simplify partitioning administration.

During this project, I want to bring these features to the PostgreSQL core.

Proposed improvements

1. Automatic partitioning (static)

The previous discussion ended up with some syntax proposal. See Creating partitions automatically at least on HASH?

This is a relatively simple task. Still, we must agree about the new syntax and make it extensible for future improvements.

2. Automatic partitioning (dynamic)

This issue has at least two subtle moments to discuss:

• We need to store information for dynamic generation of partition bounds somewhere in the catalog.
• We need to implement specific locking to handle concurrent insertions. At first glance, it looks similar to existing concurrent operations that require several passes.

3. Callback for partition creation event handling

This feature is present in pg_pathman and is widely used to rename newly created partitions and purge old data. Probably the best solution would be to extend event triggers, but for the start, we can reuse pg_pathman approach with a user-defined callback. See set_init_callback() for more details.

Syntax discussion

Other DBMS

Overview of existing partitioning syntax in other DMBS. Examples here are sketchy and intended to show the most important or dbms specific syntax details. Subpartition syntax is also omitted. Follow the links to read full syntax documentation.

1) Greenplum

• Doesn't cover HASH partitioning.
• List

PARTITION BY LIST (gender)
( PARTITION girls VALUES ('F'), 
 PARTITION boys VALUES ('M'), 
 DEFAULT PARTITION other );

• Range. Allows specifying INCLUSIVE|EXCLUSIVE bounds explicitly.

PARTITION BY RANGE (date)
( START (date '2016-01-01') INCLUSIVE
  END (date '2017-01-01') EXCLUSIVE
  EVERY (INTERVAL '1 day') );

2) MySQL

PARTITION BY 
{ HASH(expr) [ PARTITIONS num]
| RANGE (expr) 
| LIST (expr)
[(partition_definition [, partition_definition] ...)]

partition_definition:
PARTITION partition_name
[VALUES
{LESS THAN {(expr | value_list) | MAXVALUE}
|  IN (value_list)}]

3) Oracle. The full syntax is very sophisticated. The basic syntax looks more or less like MySQL syntax above.

Dynamic range partitioning is called "interval partitioning":

PARTITION BY RANGE(expr)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION P_DEC2018 VALUES LESS THAN (TO_DATE('01-JAN-2019','DD-MON-YYYY')));

4) MsSQL has a very specific syntax, so I wouldn't even consider this way. Table definition uses a partition scheme, which in its turn requires a partition function.

Proposal (is subject to change)

The current proposal is based mostly on Greenplum syntax.

Static

• for all types: HASH, LIST and RANGE
• LIST and RANGE also support default partition

CREATE TABLE numbers(int number)
PARTITION BY HASH (number)
USING (partition_desc)

where partition_desc is

MODULUS n
| VALUES IN (value_list), [DEFAULT PARTITION part_name]
| START ([datatype] 'start_value')
  END ([datatype] 'end_value')
  EVERY (partition_step), [DEFAULT PARTITION part_name]

where partition_step is

[datatype] [number | INTERVAL] 'interval_value'

examples:

CREATE TABLE numbers(int number)
PARTITION BY HASH (number)
USING (MODULUS 3)

CREATE TABLE letters(char letter)
PARTITION BY LIST (letter)
USING
(VALUES IN (('a', 'b'), (‘c’,’d’)),
DEFAULT PARTITION other_letters);

CREATE TABLE years(int year)
PARTITION BY RANGE (year)
USING
(START (2006) END (2016) EVERY (1),
DEFAULT PARTITION other_years);

Dynamic

• only for RANGE partitioning. If END bound is not set, apply dynamic partitioning instead of static.

CREATE TABLE years(int year)
PARTITION BY RANGE (year)
USING
(START (2006) EVERY (1));

• To handle decreasing values, set negative 'partition_step'.

• The first partition is created along with the parent table to save the partbound value in the catalog, other partitions are created on the fly. “partition_step” is stored in new column pg_class.relpartstep for the parent table.

• If we have too many partitions. partbound computation can take time. We need to find rightmost / leftmost partition and add partition_step. Caching is needed.