AnalyticDB for PostgreSQL:Define table partitioning

Supported partitioning types

• Range partitioning: Data is divided based on a numeric range, such as date.
• List partitioning: Data is divided based on a list of values, such as city attributes.
• Multi-level partitioning: Data is divided based on a numeric range and a list of values.

Create a range partitioned table

You can have AnalyticDB for PostgreSQL automatically generate partitions by specifying a START value, an END value, and an EVERY clause that defines the interval within the range. By default, the START value is inclusive and the END value is exclusive.

Create a table that is range-partitioned by date. Sample statement:

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 a table that is range-partitioned by number. For example, a column of the INT data type can be used as the partition key. Sample statement:

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

When you create a list partitioned table, you can set the partition key to any column whose data type allows value comparison, and you must declare a description for each specified value of the partition key.

Create a list partitioned table. Sample statement:

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 );

Create a multi-level partitioned table

AnalyticDB for PostgreSQL allows you to create a table that has multi-level partitions. The following example demonstrates how to create a three-level partitioned table. Data in level-1 partitions is range-partitioned by year, data in level-2 partitions is range-partitioned by month, and data in level-3 partitions is list-partitioned by region. The level-2 and level-3 partitions are called subpartitions.

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

Add a partition

You can execute an ALTER TABLE statement to add a partition to a partitioned table. If a subpartition template is used when the partitioned table is created, the added partition is also subpartitioned accordingly. The following example demonstrates how to add a partition:

ALTER TABLE sales ADD PARTITION 
            START (date '2017-02-01') INCLUSIVE 
            END (date '2017-03-01') EXCLUSIVE;

If no subpartition template is used when the partitioned table is created, you can define subpartitions when you add a partition. The following example demonstrates how to add a partition and define its subpartitions:

ALTER TABLE sales ADD PARTITION 
            START (date '2017-02-01') INCLUSIVE 
            END (date '2017-03-01') EXCLUSIVE
      ( SUBPARTITION usa VALUES ('usa'), 
        SUBPARTITION asia VALUES ('asia'), 
        SUBPARTITION europe VALUES ('europe') );

You can also use an ALTER TABLE statement to subpartition an existing partition. Sample statement:

ALTER TABLE sales ALTER PARTITION FOR (RANK(12))
      ADD PARTITION africa VALUES ('africa');

Split a partition

You can execute an ALTER TABLE statement to split a partition into two partitions. Partition splitting is subject to the following limits:

• If subpartitions exist, only the lowest level of subpartitions can be split.
• The split value specified in the AT clause of the partition splitting statement is assigned to the second partition.

For example, assume that a partition that contains the data of January 2017 is split into two partitions. The first partition contains the data of January 1 to January 15 and the second partition contains the data of January 16 to January 31. Sample statement:

ALTER TABLE sales SPLIT PARTITION FOR ('2017-01-01')
AT ('2017-01-16')
INTO (PARTITION jan171to15, PARTITION jan1716to31);

If your partitioned table has a default partition, you can add a partition by splitting the default partition. In the INTO clause, you must specify the default partition as the second partition. Sample statement:

ALTER TABLE sales SPLIT DEFAULT PARTITION 
START ('2017-01-01') INCLUSIVE 
END ('2017-02-01') EXCLUSIVE 
INTO (PARTITION jan17, default partition);

Determine the partition granularity

When you use partitioned tables, you need to determine the partition granularity. For example, to partition a table by time, you may choose a granularity of day, week, or month. A finer granularity results in less data in each partition but a larger number of partitions. The number of partitions is not measured by an absolute standard. We recommend that you assign no more than 200 partitions in each table. A large number of partitions may reduce database performance. For example, the query optimizer takes a long time to generate execution plans or the VACUUM operation takes a long time to complete.

Optimize partitioned table queries

AnalyticDB for PostgreSQL supports partition pruning for partitioned tables to improve query performance. When partition pruning is enabled, the system scans only the required partitions based on query conditions, instead of scanning the entire table. Example:

EXPLAIN 
  SELECT * FROM sales 
  WHERE year = 2008 
    AND month = 1 
    AND day = 3 
    AND region = 'usa';

In the preceding example, the query conditions fall on the level-3 partition usa in the level-2 partition 1 in the level-1 partition 2008. Therefore, only the data in the level-3 partition usa is scanned during the query. The following execution plan shows that only one of the 468 level-3 partitions needs to be scanned.

Gather Motion 4:1  (slice1; segments: 4)  (cost=0.00..431.00 rows=1 width=24)
  ->  Sequence  (cost=0.00..431.00 rows=1 width=24)
        ->  Partition Selector for sales (dynamic scan id: 1)  (cost=10.00..100.00 rows=25 width=4)
              Filter: year = 2008 AND month = 1 AND region = 'usa'::text
              Partitions selected:  1 (out of 468)
        ->  Dynamic Table Scan on sales (dynamic scan id: 1)  (cost=0.00..431.00 rows=1 width=24)
              Filter: year = 2008 AND month = 1 AND day = 3 AND region = 'usa'::text

Query partition definitions

You can execute the following SQL statement to query the definitions of all partitions in a table:

SELECT 
  partitionboundary, 
  partitiontablename, 
  partitionname,
  partitionlevel, 
  partitionrank
FROM pg_partitions 
WHERE tablename='sales';

Maintain partitioned tables

You can manage partitions in partitioned tables. For example, you can add, remove, rename, truncate, exchange, and split partitions. For more information, see Partitioning Large Tables.