AnalyticDB for PostgreSQL allows you to divide a large table into partitions. When you use conditions to query data, the system scans only the partitions that meet the specified conditions. This prevents full table scans and improves query performance.
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.