Skip to content

Partitioning by composite key

Jump to bottom
Dmitry Ivanov edited this page Jun 6, 2017 · 3 revisions

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;

Clone this wiki locally