AnalyticDB:Defining partitioned tables

When to use partitioning

Partitioning works best when all three of the following are true:

• The table is large. Tables with a large number of rows are good candidates for partitioning.

• Query performance is unsatisfactory. Partition only tables where queries produce slower response times than acceptable.

• Query predicates have clear access patterns. Check the WHERE clauses in your typical queries. If the same columns appear repeatedly, those columns are natural partition key candidates.

Supported partitioning types

Choose a partition key

The partition key determines whether partition pruning works effectively:

• Match your query predicates. Choose the column or columns that appear most frequently in WHERE clauses. Partition pruning is effective only when query conditions include the partition key.

• Align with your data retention strategy. If you regularly delete old data in bulk, design partitions so the data to be deleted lands in a single partition. Dropping a partition is far faster than running a DELETE statement.

• Primary key constraint. If the table has a primary key, the partition key must be a primary key column. Without a primary key, any column is eligible.

Create a range partitioned table

Specify START, END, and EVERY to auto-generate partitions within a range. By default, START is inclusive and END is exclusive.

Partition by date:

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

Partition by integer with a default partition:

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

The DEFAULT PARTITION catches rows that fall outside all defined ranges.

Create a list partitioned table

The partition key can be any column with a data type that supports value comparison. Declare a value description for each partition.

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

The following example creates a three-level partitioned table:

• Level 1: range-partitioned by year

• Level 2 (subpartition): range-partitioned by month

• Level 3 (subpartition): list-partitioned by region

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

Use ALTER TABLE ... ADD PARTITION to add a partition to an existing partitioned table.

If the table was created with a subpartition template, the new partition is automatically subpartitioned:

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

If no subpartition template was used, define subpartitions inline when adding the partition:

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

To subpartition an existing partition:

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

Partitions cannot be added to a table that has a default partition. To add partitions to such a table, split the default partition instead. See Split a partition.

Specify a partition name

As of AnalyticDB for PostgreSQL V6.3.10.9, use the WITH(tablename=<tablename>) clause to assign explicit names to partitions when creating a table:

CREATE TABLE partition_with_name_list (a int, b int, c int) DISTRIBUTED BY (a) PARTITION BY LIST (a)
(
    PARTITION p1 VALUES (1)  WITH (tablename='partition_with_name_list_p1'),
    PARTITION p2 VALUES (2)  WITH (tablename='partition_with_name_list_p2'),
    PARTITION p3 VALUES (3)  WITH (tablename='partition_with_name_list_p3'),
    PARTITION p4 VALUES (4)  WITH (tablename='partition_with_name_list_p4')
);

Rename a partition

As of AnalyticDB for PostgreSQL V6.3.10.9, rename a partition with:

ALTER TABLE partition_with_name_list_p1 RENAME TO partition_with_name_list_p1r;

Split a partition

Use ALTER TABLE ... SPLIT PARTITION to divide one partition into two.

Limits:

• If subpartitions exist, only the lowest-level subpartition can be split.

• The split value in the AT clause is assigned to the second partition.

Split an existing partition:

The following example splits a January 2017 partition at January 16. Data from January 1–15 goes into the first partition; data from January 16–31 goes into the second.

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

Split the default partition to add a new partition:

When a table has a default partition, split it to carve out a named partition. The default partition must be the second partition in the INTO clause.

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

Determine partition granularity

Partition granularity involves a trade-off between the amount of data in each partition and the total number of partitions. A finer granularity results in less data in each partition but a larger number of partitions.

Keep the number of partitions at 200 or fewer per table as a general guideline. A large number of partitions may reduce database performance — for example, the query optimizer takes longer to generate execution plans, and VACUUM operations take longer to complete.

Optimize partitioned table queries

AnalyticDB for PostgreSQL supports partition pruning: when a query includes conditions on the partition key, the optimizer scans only the matching partitions instead of the entire table.

Verify that pruning is active:

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

Expected output (pruning working correctly):

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

In this plan, Partitions selected: 1 (out of 468) confirms pruning is active. If you see a high number of partitions selected despite specific filter conditions, check whether the filter column matches the partition key.

Query partition definitions

Query all partitions in a table from the pg_partitions system view:

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

Related operations

For a complete list of partition management operations — including removing, truncating, and exchanging partitions — see Partitioning Large Tables.

FAQ

How do I choose the partition key?

If the table has a primary key, the partition key must be one of the primary key columns. Without a primary key, you can specify any column as the partition key. For best pruning results, choose the column that appears most frequently in WHERE clauses.