Working with Databases-Creating and Managing Partitioned Tables
Tags: opengauss, database
Published: 2023-03-13 10:49:36

**Background**
openGauss supports range partitioned tables.

In a range partitioned table, data within a certain range is mapped to each partition. The range is determined by the partition key specified when the partitioned table is created. This partitioning mode is most commonly used. The partition key is usually a date. For example, sales data is partitioned by month.

A partitioned table has the following advantages over an ordinary table:

**·** High query performance: You can specify partitions when querying partitioned tables, improving query efficiency.
**·** High availability: If a certain partition in a partitioned table is faulty, data in the other partitions is still available.
**·** Easy maintenance: To fix a partitioned table having a faulty partition, you only need to fix the partition.
**·** Balanced I/O: Partitions can be mapped to different disks to balance I/O and improve the overall system performance.

To convert an ordinary table to a partitioned table, you need to create a partitioned table and import data to it from the ordinary table. When you design tables, plan whether to use partitioned tables based on service requirements.

**Procedure**
Perform the following operations on range partitioned tables.

**·** Creating a tablespace

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

If the following information is displayed, the tablespace has been created:

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

**·** Creating a partitioned table

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

If the following information is displayed, the partitioned table has been created:

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

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

**·** Inserting data

Insert data from the **tpcds.customer_address** table to the **tpcds.web_returns_p2** table.

Suppose a backup table **tpcds.web_returns_p2** of the **tpcds.customer_address** table has been created in the database. You can run the following command to insert the data of the **tpcds.customer_address** table into the backup table **tpcds.web_returns_p2**:

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

**·** Modifying the row movement attributes of the partitioned table

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

**·** Deleting a partition

Run the following command to delete partition P8:

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

**·** Adding a partition

Run the following command to add partition P8 and set its range to [40000, MAXVALUE]:

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

**·** Renaming a partition

Run the following command to rename partition P8 to **P_9**:

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

**·** Run the following command to rename partition P_9 to P8:

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

**·** Modifying the tablespace of a partition

**·** Run the following command to change the tablespace of partition P6 to example3:

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

**·** Run the following command to change the tablespace of partition P4 to example4:

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

**·** Querying a partition

Run the following command to query partition P6:

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

**·** Deleting a partitioned table and its tablespaces

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