AnalyticDB:Use partitioned tables of AnalyticDB for PostgreSQL V7.0

Use partitioned tables

If a table is partitioned, the system scans only the partitions that meet specified query conditions, instead of scanning the full table. This improves query performance. For more information, see Partitioned tables of AnalyticDB for PostgreSQL V6.0.

Create a range partitioned table

You can create a range partitioned table and then create a new table as a partition of the partitioned table. Example:

-- Create a partitioned table.
CREATE TABLE sales (id int, date date, amt decimal(10,2))
DISTRIBUTED BY (id)
PARTITION BY RANGE (date);

-- Add a partition.
CREATE TABLE sales_20160101 PARTITION OF sales
FOR VALUES FROM('2016-01-01') TO ('2016-01-02');

You can use AnalyticDB for PostgreSQL to generate partitions by specifying a START value, an END value, and an EVERY clause that defines the partition increment value. By default, START values are inclusive and END values are exclusive. Example:

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

You can create a range partitioned table that uses a column of a numeric data type as the partition key. Example:

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

You can create a list partitioned table and then create a new table as a partition of the partitioned table. Example:

-- Create a partitioned table.
CREATE TABLE rank (id int, rank int, year int, gender 
char(1), count int) 
DISTRIBUTED BY (id)
PARTITION BY LIST (gender);

-- Add a partition.
CREATE TABLE rank_girls PARTITION OF rank FOR VALUES IN ('F');

A list partitioned table uses a column of a specific data type as the partition key. For list partitions, you must declare a partition specification for each partition (list value) that you want to create. Example:

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 hash partitioned table

Hash partitioning determines the partitions into which data is distributed based on the hash values of the partition key values. You can create a hash partitioned table and then create new tables as partitions of the partitioned table. When you add each partition for a hash partitioned table, you can use the MODULUS clause to specify the total number of partitions into which the table is divided and the REMAINDER clause to specify the partition that stores specific data. Each partition holds the rows whose hash values of the partition key values produce the specified remainder when they are divided by the specified modulus.

-- Create a partitioned table.
CREATE TABLE orders (id int, order_date date, customer_id int, amount decimal)
DISTRIBUTED BY (id)
PARTITION BY HASH (customer_id);

-- Add partitions.
CREATE TABLE orders_p0 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE orders_p1 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE orders_p2 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE orders_p3 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 3);

AnalyticDB for PostgreSQL uses built-in hash functions to compute the hash values of partition key values and allows you to use the satisfies_hash_partition system function to query whether a specific value is stored in a specific partition. The following statement is used to query whether the data whose customer_id is 2 is stored in a partition of the public.orders table with MODULUS set to 4 and REMAINDER set to 0:

SELECT satisfies_hash_partition('public.orders'::regclass, 4, 0, variadic ARRAY[2]); 

Create an expression partitioned table

AnalyticDB for PostgreSQL V7.0 supports expression partitioning, which is highly flexible and easy to use.

-- Create a partitioned table. 
CREATE TABLE sales (id int, sale_time timestamp, amt decimal(10,2))
DISTRIBUTED BY (id)
PARTITION BY LIST (date_trunc('month', sale_time));

-- Add a partition.
CREATE TABLE sales_202401 PARTITION OF sales FOR VALUES IN('2024-01-01'::timestamp);

Create a multi-level partitioned table

You can create a table that has multi-level partitions. The following sample statement shows how to create a three-level partitioning table. Data in range partitions is partitioned by month, and data in list partitions is partitioned by region.

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

Optimize partitioned table queries

• Partition granularity

  For a table partitioned by time, the granularity can be a day, week, or month. A finer granularity produces a smaller amount of data in each partition but a larger number of partitions. The number of partitions is not measured by an absolute standard. Typically, the number of partitions is considered quite large at around 200. A large number of partitions have a significant impact on database performance. For example, the query optimizer may take a longer time to generate execution plans, and maintenance operations such as VACUUM may slow down.

  In a multi-level partitioned table, the number of partitioning files may increase sharply. For example, if a table is partitioned by month and city in a scenario that contains 24 months and 100 cities, the total number of table partitions is 2,400. If the table is a column-oriented table that stores each column in a physical table and the table contains 100 columns, the system must manage more than 100,000 files for the table. You must estimate the number of partitions that are needed when you determine the partitioning method.

• Partition pruning

  AnalyticDB for PostgreSQL supports partition pruning for partitioned tables. For more information, see Partition pruning.

Maintain partitioned tables

In partitioned tables, partitions can be managed by using various SQL statements. Examples:

• Create a partition

  If a default partition exists, you can split the default partition instead of creating partitions. For more information, see the "Split a partition" section of this topic.

  CREATE TABLE sales_p2 PARTITION OF sales FOR VALUES FROM('2017-02-01') TO('2017-02-28');

  If a partitioned table has at least one partition, you can also use the ALTER TABLE ADD PARTITION syntax to create partitions.

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

• Attach a partition

  You can attach an existing table that has the same table structure as a partitioned table as a partition of the partitioned table.

  ALTER TABLE sales ATTACH PARTITION sales_p2 FOR VALUES FROM('2017-02-01') TO('2017-02-28');

• Detach a partition

  You can detach a partition of a partitioned table. If you detach a partition, the child partitioned table that corresponds to the partition is disassociated from the partitioned table but not deleted.

  ALTER TABLE sales DETACH PARTITION sales_p2;

• Delete a partition and its subpartitions

  When you delete a partition, the child partitioned table that corresponds to the partition is deleted.

  ALTER TABLE sales DROP PARTITION p2;

• Rename a partition

  ALTER TABLE sales RENAME PARTITION p2 TO Feb17;

• Clear a partition

  ALTER TABLE sales TRUNCATE PARTITION p1;

• Exchange a partition

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

• Split a partition

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

  If a partitioned table has a default partition, you can create partitions only by splitting the default partition.

  ALTER TABLE sales SPLIT DEFAULT PARTITION START('2017-03-01') END('2017-03-31') into(partition p3, default partition);