Partitioned Tables（1）
Tags: opengauss
Published: 2023-03-07 04:51:53

If a table contains a large amount of data, data query and operation efficiency will be severely affected. openGauss can logically divide a table into multiple shards to avoid processing a large amount of data at a time and then improve the processing efficiency.

openGauss supports the following types of partitioned tables:

**·** Range partitioned table: One or more columns are divided into multiple ranges. A partition is created for each range to store data. For example, sales data can be partitioned by month.
**·** List partitioned table: Partitions are created based on values in a column. For example, sales data is divided by sales store.
**·** Interval partitioned table: It is a special type of range partitions. The interval value definition is added. If no matched partition is found when a record is inserted, partitions can be automatically created based on the interval.
**·** Hash partitioned table: The modulus and remainder are specified for each partition based on a column of the table, and the records to be inserted into the table are allocated to the corresponding partitions.

In addition to creating a partitioned table, you can perform the following operations:

**·** Querying a partitioned table: Data is queried by partition name or value in a partition.
**·** Importing data: Data is imported directly or from an existing table.
**·** Modifying a partitioned table: Partitions are added, deleted,split, or combined, or partition names are changed.
**·** Deleting a partitioned table: The operation is the same as that of deleting a common table.

**Classification of Range Partitioned Tables**

Range partitioned tables are classified into the following types:

**·** VALUES LESS THAN: specifies the partition range based on the upper limit of each partition. Upper limit of the previous partition ≤ Range of the partition ≤ Upper limit of the current partition.
**·** START END: Partitioning is performed in the following methods.
**·** Providing the start point and end point of the partition
**·** Providing only the start point of the partition
**·** Providing only the end point of the partition
**·** Providing the interval value within the range after the start point and end point of the partition are provided
**·** Comprehensively using the preceding methods

**Syntax for Creating a VALUES LESS THAN Range Partitioned Table**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8qhgwfztkthgyb65ff8r.png)

**Parameters for Creating a VALUES LESS THAN Range Partitioned Table**

**· partition_table_name**

Specifies the name of the partitioned table.

**· column_name**

Specifies the name of the column to be created in the new table.

**· data_type**

Specifies the data type of the column.

**· partition_key**

Specifies the name of the partition key.

In this case, a maximum of four partition keys are supported.

**· partition_name**

Specifies the name of a range partition.

**· VALUES LESS THAN**

Specifies that the value in the partition must be less than the upper limit value.

**· partition_value**

Specifies the upper limit of a range partition, and the value depends on the type specified by partition_key.

**· MAXVALUE**

Specifies the upper limit of the last range partition.

**Example of Creating a VALUES LESS THAN Range Partitioned Table**

Example 1: Create the **sales_table** range partitioned table. The table has four partitions and the data type of their partition keys is DATE. The range of the partition is as follows: **sales_date** < 2021-04-01, 2021-04-01 ≤ **sales_date** < 2021-07-01, 2021-07-01 ≤ **sales_date** < 2021-10-01, and 2021-10-01 ≤ **sales_date** < MAXVALUE.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xlv8reosgl8wmdwl80dj.png)

**Syntax for Querying a Partitioned Table**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/euc2nzuv7i7852uysn86.png)

**Parameters for Querying a Partitioned Table**

**· partition_table_name**

Specifies the name of the partitioned table.

**· partition_name**

Specifies the partition name.

**· partition_value**

Specifies the value of the partition. The partition where the value specified by the **PARTITION FOR** clause is located is the partition to be queried.

**Syntax for Querying a Partitioned Table**

Example 2: Query the **sales_table** partitioned table created in example 1.

-- Query data in the sales_table table.
openGauss=# SELECT * FROM sales_table;
 order_no |      goods_name      |     sales_date      | sales_volume |     sale
s_store
----------+----------------------+---------------------+--------------+---------
-------------
        1 | jacket               | 2021-01-10 00:00:00 |            3 | Alaska

        2 | hat                  | 2021-05-06 00:00:00 |            5 | Clolorado

        3 | shirt                | 2021-09-17 00:00:00 |            7 | Florida

        4 | coat                 | 2021-10-21 00:00:00 |            9 | Hawaii

(4 rows)

-- Query the data of the fourth quarter in the sales_table table. sales_table PARTITION (season4) is used to reference the partition where the data of the fourth quarter is located.
openGauss=# SELECT * FROM sales_table PARTITION (season4);
 order_no |      goods_name      |     sales_date      | sales_volume |     sales_store
----------+----------------------+---------------------+--------------+----------------------
        4 | coat                 | 2021-10-21 00:00:00 |            9 | Hawaii
(1 row)

-- Query the data of the first quarter in the sales_table table. sales_table PARTITION FOR ('2021-3-21 00:00:00') is used to reference the partition where the data of the first quarter is located. '2021-3-21 00:00:00' is located in the partition where the data of the first quarter is located.
openGauss=# SELECT * FROM sales_table PARTITION FOR ('2021-3-21 00:00:00');
 order_no |      goods_name      |     sales_date      | sales_volume |     sales_store
----------+----------------------+---------------------+--------------+----------------------
        1 | jacket               | 2021-01-10 00:00:00 |            3 | Alaska
(1 row)

**Syntax for Creating a START END Range Partitioned Table**
A START END range partitioned table can be created by different methods, and these methods can be combined within a partitioned table.

Method 1: By executing **START(partition_value) END (partition_value | MAXVALUE)**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/75jiw99ps0n173q4j9b3.png)

**·** Method 2: By executing **START(partition_value)**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/k24qf7wpuuwjo5d8lfci.png)

**·** Method 3: By executing **END(partition_value | MAXVALUE)**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/n2ijlv6xgj33j8tppknl.png)

**·** Method 4: By executing **START(partition_value) END (partition_value) EVERY (interval_value)**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4kjw6ysvi1a8gd9uk8wp.png)

**Parameters for Creating a START END Range Partitioned Table**

**· partition_table_name**

Specifies the name of the partitioned table.

**· column_name**

Specifies the name of the column to be created in the new table.

**· data_type**

Specifies the data type of the column.

**· partition_key**

Specifies the name of the partition key.

In this case, only one partition key is supported.

**· partition_name**

Specifies the name or prefix of the range partition.

· If the definition is in the **START(partition_value) END (partition_value) EVERY (interval_value)** clause and the value of **partition_name** is **p1**, the partition names are **p1_1**, **p1_2**, and so on.

For example, if **PARTITION p1 START(1) END(4) EVERY(1)** is defined, the generated partitions are [1, 2), [2, 3), and [3, 4), and their names are **p1_1**, **p1_2**, and **p1_3**. In this case, **p1 **is a name prefix.

· If the defined statement is in the first place and has **START** specified, the range (MINVALUE, **START**) will be automatically used as the first actual partition, and its name will be **p1_0**. The other partitions are then named **p1_1**, **p1_2**, and so on.

For example, if **PARTITION p1 START(1)**, **PARTITION p2 START(2)** is defined, generated partitions are (MINVALUE, 1), [1, 2), and [2, MAXVALUE), and their names will be **p1_0**, **p1_1**, and **p2**. In this case, **p1** is a name prefix and **p2** is a partition name. **MINVALUE** indicates the minimum value.

**·** In other cases, this parameter specifies the range partition name.

**· VALUES LESS THAN**

Specifies that the value in the partition must be less than the upper limit value.

**· partition_value**

Specifies the endpoint value (start or end point) of the range partition. The value depends on the type specified by **partition_key**.

**· interval_value**

Splits the range specified by **[START, END)** and specifies the width of each partition after splitting. If the value of **(END-START)** cannot be exactly divided by the value of **EVERY**, only the width of the last partition is less than the value of **EVERY**.

**· MAXVALUE**

Specifies the upper limit of the last range partition.

**Syntax for Creating a List Partitioned Table**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6upgpecn2crm7thmpiu4.png)

**Parameters for Creating a List Partitioned Table**

**· partition_table_name**

Specifies the name of the partitioned table.

**· column_name**

Specifies the name of the column to be created in the new table.

**· data_type**

Specifies the data type of the column.

**· partition_key**

Specifies the name of the partition key.

The list partitioning policy supports only one column of partition keys.

**· partition_name**

Specifies the name of a range partition.

**· list_values_clause**

There are one or more key values of the corresponding partition. Use commas (,) to separate multiple key values.

**· VALUES (DEFAULT)**

If the added data contains key values that are not listed in **list_values_clause**, the data is stored in the partition corresponding to **VALUES (DEFAULT)**.

**· MAXVALUE**

Specifies the upper limit of the last range partition.