Partitioning by composite key

Release 1.4 also supports composite keys (which are expressions too).

Prerequisites

/* create a table we're going to partition */
create table test (logdate date not null, comment text);

/* create sequence for automatic partition naming */
select create_naming_sequence('test');

/* we have to manually create a composite partitioning key */
create type test_key as (year float8, month float8);

/* register a RANGE-partitioned table 'test' */
select add_to_pathman_config('test',
                             '( extract(year from logdate),
                                extract(month from logdate) )::test_key',
                             NULL);

/* add one partition [(year, month), (year + 10, month)) */
select add_range_partition('test',
                           (extract(year from current_date), 1)::test_key,
                           (extract(year from current_date + '10 years'::interval), 1)::test_key);

Partitions

First of all, let's check the partition we've just created:

select parent, partition, parttype, range_min, range_max from pathman_partition_list ;
 parent | partition | parttype | range_min | range_max
--------+-----------+----------+-----------+-----------
 test   | test_1    |        2 | (2017,1)  | (2027,1)
(1 row)

We couldn't use neither create_range_partitions() nor create_hash_partitions(), since the first one requires that MAX & MIN aggregates and +/- operators be defined for the expression's type, while the second one cannot work without a hash-function (which we didn't assign).

This means that we have to create new partitions manually. Let's add a few more:

/* [(10, 1), (20, 1)), [(20, 1), (30,1)) ... */
select add_range_partition('test',
                           (extract(year from current_date +
                                         format('%s years', i)::interval),
                            1)::test_key,
                           (extract(year from current_date +
                                         format('%s years', i + 10)::interval),
                            1)::test_key)
from generate_series(10, 200, 10) as g(i);

That's way better!

select parent, partition, range_min, range_max
from pathman_partition_list
where parent = 'test'::regclass;
 parent | partition | range_min | range_max
--------+-----------+-----------+-----------
 test   | test_1    | (2017,1)  | (2027,1)
 test   | test_2    | (2027,1)  | (2037,1)
 test   | test_3    | (2037,1)  | (2047,1)
 test   | test_4    | (2047,1)  | (2057,1)
 test   | test_5    | (2057,1)  | (2067,1)
 test   | test_6    | (2067,1)  | (2077,1)
 test   | test_7    | (2077,1)  | (2087,1)
 test   | test_8    | (2087,1)  | (2097,1)
 test   | test_9    | (2097,1)  | (2107,1)
 test   | test_10   | (2107,1)  | (2117,1)
 test   | test_11   | (2117,1)  | (2127,1)
 test   | test_12   | (2127,1)  | (2137,1)
 test   | test_13   | (2137,1)  | (2147,1)
 test   | test_14   | (2147,1)  | (2157,1)
 test   | test_15   | (2157,1)  | (2167,1)
 test   | test_16   | (2167,1)  | (2177,1)
 test   | test_17   | (2177,1)  | (2187,1)
 test   | test_18   | (2187,1)  | (2197,1)
 test   | test_19   | (2197,1)  | (2207,1)
 test   | test_20   | (2207,1)  | (2217,1)
 test   | test_21   | (2217,1)  | (2227,1)
(21 rows)

Note that overlap checks will still work:

/* this command will fail */
select add_range_partition('test',
                           (extract(year from current_date + '30 years'::interval), 1)::test_key,
                           (extract(year from current_date + '50 years'::interval), 1)::test_key);
ERROR:  specified range [(2047,1), (2067,1)) overlaps with existing partitions

Sample queries

The expression itself looks quite ugly:

ROW(date_part('year'::text, logdate), date_part('month'::text, logdate))::test_key

Unfortunately, we can't do much about it. Let's select all dates prior to (2040, 1):

explain select * from test
where ROW(date_part('year'::text, logdate),
          date_part('month'::text, logdate))::test_key < (2040, 1)::test_key;
                                                                                                             QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..83.98 rows=2963 width=36)
   ->  Seq Scan on test_1  (cost=0.00..22.70 rows=1270 width=36)
   ->  Seq Scan on test_2  (cost=0.00..22.70 rows=1270 width=36)
   ->  Seq Scan on test_3  (cost=0.00..38.58 rows=423 width=36)
         Filter: (ROW(date_part('year'::text, (logdate)::timestamp without time zone), date_part('month'::text (logdate)::timestamp without time zone))::test_key < ROW('2040'::double precision, '1'::double precision)::test_key)
(5 rows)

Limitations

The harshest limitations:

• The following functions won't work with composite keys by default:
  • create_range_partitions()
  • create_hash_partitions()
  • append_range_partition()
  • prepend_range_partition()
  • generate_range_bounds()
• Automatic partition creation on INSERT is disabled;
• All other limitations that are inherent to expressions;