AnalyticDB:Partitioned tables

Partition types

AnalyticDB for PostgreSQL supports three partition types.

The following figure shows a multi-level partitioned table with date-based range partitions and region-based list subpartitions.

When to partition

Evaluate the following criteria before partitioning a table:

• Data volume: Partitioning benefits tables with large data volumes, such as a fact table with more than 10 million rows. The exact threshold depends on your workload and query performance requirements.

• Partition key: The table must have a suitable column for use as a partition key, such as a date (day or month) or a categorical value (region or status).

• Data lifecycle: Dropping an old partition is faster and less resource-intensive than running a DELETE statement against millions of rows.

• Query patterns: Partitioning improves performance only when queries include the partition key in their filter conditions. Queries that omit the partition key must scan all partitions, which may take longer than scanning an unpartitioned table.

Create a range-partitioned table

A range-partitioned table divides data by a continuous range of values. Specify a START value, an END value, and an EVERY clause to define the partition interval. By default, START values are inclusive and END values are exclusive.

CREATE TABLE sales (id int, date date, amt decimal(10,2))
DISTRIBUTED BY (id)
PARTITION BY RANGE (date)
( START (date '2016-01-01') INCLUSIVE
   END (date '2017-01-01') EXCLUSIVE
   EVERY (INTERVAL '1 day') );

CREATE TABLE rank (id int, rank int, year int, gender char(1), count int)
DISTRIBUTED BY (id)
PARTITION BY RANGE (year)
( START (2006) END (2016) EVERY (1),
  DEFAULT PARTITION extra );

Create a list-partitioned table

A list-partitioned table divides data by discrete values. The partition key accepts any data type that supports equality comparisons. Declare a partition specification for each distinct value.

CREATE TABLE rank (id int, rank int, year int, gender
char(1), count int )
DISTRIBUTED BY (id)
PARTITION BY LIST (gender)
( PARTITION girls VALUES ('F'),
  PARTITION boys VALUES ('M'),
  DEFAULT PARTITION other );

This statement creates two named partitions based on gender values, plus a default partition for any values not explicitly listed.

Create a multi-level partitioned table

A multi-level partitioned table combines two partitioning strategies. The following example creates a three-level partitioned table that first partitions by month (range) and then subpartitions by region (list).

CREATE TABLE sales
  (id int, year int, month int, day int, region text)
DISTRIBUTED BY (id)
PARTITION BY RANGE (month)
  SUBPARTITION BY LIST (region)
    SUBPARTITION TEMPLATE (
    SUBPARTITION usa VALUES ('usa'),
    SUBPARTITION europe VALUES ('europe'),
    SUBPARTITION asia VALUES ('asia'),
    DEFAULT SUBPARTITION other_regions)
(START (1) END (13) EVERY (1),
DEFAULT PARTITION other_months );

This statement creates 12 monthly range partitions (January through December), each containing four list subpartitions (usa, europe, asia, and other_regions), plus a default partition for months outside the 1-12 range.

Optimize partition performance

Maintain partitioned tables

Manage partitions with the following ALTER TABLE operations.

ALTER TABLE test_partition_range ADD partition p2 start ('2017-02-01') end ('2017-02-28');

ALTER TABLE test_partition_range DROP partition p2;

ALTER TABLE test_partition_range RENAME PARTITION p2 TO Feb17;

ALTER TABLE test_range_partition TRUNCATE PARTITION p1;

ALTER TABLE test_range_partition EXCHANGE PARTITION p2 WITH  TABLE {cos_table_name} ;

-- Split the p2 partition into two partitions with the boundary value set to 2017-02-20.
ALTER TABLE test_partition_range SPLIT partition p2 at ('2017-02-20') into (partition p2, partition p3);