- Notifications
You must be signed in to change notification settings - Fork67
Partitioning by composite key
Dmitry Ivanov edited this pageJun 6, 2017 ·3 revisions
Release 1.4 also supports composite keys (which areexpressions too).
/* create a table we're going to partition*/createtabletest (logdatedatenot null, commenttext);/* create sequence for automatic partition naming*/select create_naming_sequence('test');/* we have to manually create a composite partitioning key*/createtypetest_keyas (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(yearfromcurrent_date),1)::test_key, (extract(yearfromcurrent_date+'10 years'::interval),1)::test_key);
First of all, let's check the partition we've just created:
select parent, partition, parttype, range_min, range_maxfrom pathman_partition_list ; parent | partition | parttype | range_min | range_max--------+-----------+----------+-----------+----------- test | test_1 |2 | (2017,1) | (2027,1)(1 row)
We couldn't use neithercreate_range_partitions() norcreate_hash_partitions(), since the first one requires thatMAX &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(yearfromcurrent_date+ format('%s years', i)::interval),1)::test_key, (extract(yearfromcurrent_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_maxfrom pathman_partition_listwhere 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(yearfromcurrent_date+'30 years'::interval),1)::test_key, (extract(yearfromcurrent_date+'50 years'::interval),1)::test_key);ERROR: specified range [(2047,1), (2067,1)) overlaps with existing partitions
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):
explainselect*from testwhere 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 Scanon test_1 (cost=0.00..22.70 rows=1270 width=36)-> Seq Scanon test_2 (cost=0.00..22.70 rows=1270 width=36)-> Seq Scanon 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)
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
INSERTis disabled; - All other limitations that are inherent toexpressions;