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
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.
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.
Top comments (0)