PolarDB partitioned table - scenarios and benefits - PolarDB

This topic describes the benefits and features of partitioned tables in .

Overview

In a database, a partitioned table is a table or index that is physically divided into smaller, more manageable pieces called partitions. Each partition is an independent object with its own name and optional storage attributes. From a database administrator's perspective, a partitioned table has multiple parts that can be managed together or separately. This gives administrators great flexibility in managing partitioned tables. However, from an application's perspective, a partitioned table is identical to a non-partitioned table. You do not need to modify SQL queries or Data Manipulation Language (DML) statements to access a partitioned table.

Each partition of a table must have the same logical attributes, such as column names, data types, and constraints. However, each partition can have separate physical attributes, such as enabled or disabled compression, physical storage settings, and tablespaces.

Partitioned tables are useful for many types of applications, especially those that manage large amounts of data. Online transactional processing (OLTP) databases often benefit from improvements in manageability and availability. Online analytical processing (OLAP) data warehouses benefit from improvements in performance and manageability.

Scenarios

Partition a table to improve performance when it exceeds the physical memory of the database server, for example, when a table is larger than 2 TB.
Use a partitioned table when a large table stores historical data and new data goes into the latest partition. For example, a table stores 12 months of data: the current month in an updatable partition and previous months in a read-only partition.

Benefits

Higher query performance
In some cases, query performance can significantly improve, especially when most frequently accessed rows in a table are in a single partition or a small number of partitions. Partitioning effectively replaces the upper levels of an index, making it more likely that the frequently used parts of the index fit in memory. When a query or update accesses a single partition or a small number of partitions, performance can improve because a sequential scan of that partition is used instead of an index. This avoids random-access reads scattered across the entire table.
Easier management
Partitioned objects have parts that can be managed together or separately. DDL statements can operate on partitions instead of the entire table or index. This lets you break down resource-intensive tasks, such as reindexing a table. You can move one table partition at a time. If a problem occurs, you only need to redo the partition move, not the table move. Also, if your partitioning design considers usage patterns, you can perform batch loads and deletes by adding or removing partitions. Using DROP TABLE to delete a single partition or running ALTER TABLE DETACH PARTITION is much faster than a batch operation. These commands also completely avoid the VACUUM overhead caused by a batch DELETE.
Reduced resource contention
In some OLTP systems, partitioning can reduce contention for shared resources. For example, DML is distributed across multiple partitions instead of one.
Improved availability
The unavailability of a partition does not mean the entire table is unavailable. The query optimizer automatically removes unreferenced partitions from the query plan. Therefore, queries are not affected when a partition is unavailable.
Lower storage costs
Infrequently used data can be moved to cheaper and slower storage media to save costs.

The benefits of partitioned tables are usually valuable only when the table is very large. Use a partitioned table when the size of a single table exceeds the physical memory of the database server.

Features of partitioned tables

Although the internal implementation of partitioned tables is more complex than that of standard tables, this complexity is transparent to users. The management and use of partitioned tables also differ from standard tables. A clear understanding of these features helps ensure that you use partitioned tables correctly and efficiently.

Example 1:

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);
CREATE TABLE measurement_y2006m02 PARTITION OF measurement
    FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');

CREATE TABLE measurement_y2006m03 PARTITION OF measurement
    FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');

...
CREATE TABLE measurement_y2007m11 PARTITION OF measurement
    FOR VALUES FROM ('2007-11-01') TO ('2007-12-01');

CREATE TABLE measurement_y2007m12 PARTITION OF measurement
    FOR VALUES FROM ('2007-12-01') TO ('2008-01-01')
    TABLESPACE fasttablespace;

CREATE TABLE measurement_y2008m01 PARTITION OF measurement
    FOR VALUES FROM ('2008-01-01') TO ('2008-02-01')
    WITH (parallel_workers = 4)
    TABLESPACE fasttablespace;

Partition key

A partition key is a column or a combination of columns that determines which partition each row in a partitioned table belongs to. A partitioned table must ensure that each row is explicitly assigned to a partition. uses the partition key to automatically direct insert, update, and delete operations to the correct partition.

In Example 1, logdate is the partition key of the measurement table. The boundary of each partition of the measurement table is determined by the value range of logdate.

Partitioning strategies

provides several partitioning strategies to control how the database places data into partitions:

Range Partitioning
The table is partitioned into "ranges" defined by the partition key. The value ranges assigned to different partitions do not overlap. For example, you can partition by date ranges or by ranges of identifiers for a specific business object. The bounds of each range are inclusive of the lower bound and exclusive of the upper bound. For example, if one partition's range is from 1 to 10 and the next partition's range is from 10 to 20, the value 10 belongs to the second partition, not the first. The measurement table in Example 1 is a range-partitioned table.
Interval range partitioning is an extension of range partitioning. For more information, see Interval range partitioning.
List Partitioning
Example 2:
```
CREATE TABLE department(deptno INT4 Primary Key,dname VARCHAR(50), location VARCHAR(100)) PARTITION BY LIST (deptno);
CREATE TABLE department_p1 partition of department for values in (10, 20);
CREATE TABLE department_p2 partition of department for values in (30, 40);
```
List partitioning divides a table into partitions by explicitly listing the key values that appear in each partition. The department table in Example 2 uses list partitioning. The partition key values are explicitly specified for each of its partitions. For example, department_p1 stores only rows where deptno is 10 or 20. department_p2 stores only rows where deptno is 30 or 40.
Hash Partitioning
Hash partitioning divides a table into partitions by specifying a modulus and a remainder for each partition. Each partition holds rows for which the hash value of the partition key divided by the specified modulus produces the specified remainder.
Example 3:
```
create table idxpart (i int) partition by hash (i);
create table idxpart0 partition of idxpart for values with (modulus 2, remainder 0);
create table idxpart1 partition of idxpart for values with (modulus 2, remainder 1);
```
The idxpart table in Example 3 uses hash partitioning. For example, idxpart0 stores rows where the hash value of i divided by 2 has a remainder of 0. idxpart1 stores rows where the hash value of i divided by 2 has a remainder of 1.

Multi-level partitioning

After a partitioned table is divided into partitions, these partitions can be further partitioned. Such a partitioned table is called a multi-level partitioned table.

does not currently limit the number of partitioning levels. However, you should avoid creating too many levels because this can make managing the partitioned table difficult and may also degrade query performance. A partitioning depth of three or fewer levels is generally recommended.

Different partitioning strategies can be used at different levels. For example, you can use range partitioning for the first level, hash partitioning for the second level, and list partitioning for the third level.

Example 4:

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);

CREATE TABLE measurement_y2006m03 PARTITION OF measurement
    FOR VALUES FROM ('2006-03-01') TO ('2006-04-01') PARTITION BY Hash (city_id);

CREATE TABLE measurement_y2006m03_hash1 PARTITION OF measurement_y2006m03
    for values with (modulus 2, remainder 0) PARTITION BY List (peaktemp);

CREATE TABLE measurement_y2006m03_hash1_l1 PARTITION OF measurement_y2006m03_hash1 for values in (10, 20);

Syntax

For information about the commands and descriptions related to each partition type, such as creating a partitioned table, adding partitions, merging partitions, splitting partitions, and deleting partitions, see Partitioned table commands.