Updated on 2025-05-29 GMT+08:00

Table Partitioning

Table partitioning logically divides a large table or index into smaller and easier-to-manage logical units (partitions), minimizing the impact on table query and modification statements. Users can quickly locate a partition where data is located by using a partition key. In this way, users do not need to scan all large tables in the database and can concurrently perform DDL and DML operations on different partitions. Table partitioning provides users with the following capabilities:

  • Improved query efficiency in large data scenarios: Data in a table is logically partitioned by partition key. During query, only the subset of the related partition is accessed, instead of the entire table. This partition pruning technology can significantly improve query performance and provide performance gains in orders of magnitude.
  • Reduced impact caused by concurrent O&M and query operations: Partitioned tables can significantly reduce the mutual impact of DML and DDL statements in concurrent scenarios. This advantage is particularly obvious in scenarios with a large amount of data and partitioning by time. For example, the import of new data partitions and real-time point query operations, as well as O&M operations such as data cleaning and partition merging of old data partitions, can be performed independently without interfering with each other.
  • Flexible data O&M management in large data scenarios: Partitioned tables physically isolate data across different partitions. Each partition can be independently set with physical attributes, such as enabling or disabling compression, physical storage settings, and tablespace. In addition, partitioned tables support partition-level data management operations, such as data loading, index creation and rebuild, backup, and restoration, without the need to operate the entire table, greatly reducing the operation time.