Overview of Partitioned Tables

What Is a Partitioned Table?

In database administration, as business workloads grow, the data volume of a single table can increase significantly. This expansion often leads to degraded query performance and affects normal workload operations. For example, larger datasets require scanning more data for the same query, which slows execution.

TaurusDB inherits the partitioned table capabilities from the open-source community and provides an enhanced solution. TaurusDB allows different tablespace attributes to be defined for each partition, making data management more flexible. Partitioned tables not only support physical isolation and performance optimization but also divide large tables into multiple partitions so that queries can be written to target specific partitions, ensuring stable query performance. Partitioning is also an effective method for managing cold data and reducing its impact on query efficiency. Cold data can be archived into specific partitions and queries can be directed only to hot partitions, avoiding unnecessary scanning of cold data.

Figure 1 Structure of a partitioned table

Subpartitioning

A table can be divided into partitions based on one or more column values. This is known as partitioning. Subpartitioning, also known as composite partitioning, further subdivides each partition based on other column values.

Subpartitioning divides a table into smaller, more manageable segments, improving storage efficiency and query performance. Its key advantages include:

1. Flexible data management: Subpartitioning allows partition policies to be defined based on business requirements and data characteristics, enabling on-demand storage and querying.
2. Improved query efficiency: Because data is divided into smaller segments, queries can scan only the relevant partitions, reducing unnecessary scans and improving the query speed.
3. Convenient backup and restoration: Backup and restoration can be performed for specific partitions rather than the entire table, saving time and storage resources.

Subpartitioned tables are typically designed across multiple dimensions. When a partition grows large, it can be further subdivided along another dimension. For example, in a sales table, partitions may be divided by region, whereas subpartitions may be divided by year. In this way, when querying data for a specific year in a specific region, you only need to access the corresponding subpartition, achieving high query performance.

The following figure shows the structure of a subpartitioned table.

Figure 2 Structure of a subpartitioned table

Advantages of Partitioned Tables

• Efficient data management: Operations such as data loading, index creation and rebuilding, backup, and restoration can be performed at the partition level rather than on the entire table, significantly reducing management time.
• Improved query performance: Queries can access only the target partition instead of the full table. Partition pruning can deliver order-of-magnitude performance improvements by eliminating unnecessary I/O.
• Partition independence: Maintenance operations on partitions are isolated. While some partitions undergo maintenance, others can continue to support concurrent queries and DML operations. Query, DML, and DDL operations all support parallel execution.
• Enhanced system availability: Partitioning key tables and indexes reduces maintenance windows and improves the availability of mission-critical workloads.
• Seamless integration with existing applications: Applications leverage the full benefits of partitioning transparently, without requiring SQL rewrites.
• Simplified data lifecycle management: Partitioned tables provide more efficient lifecycle management, making data maintenance easier.

Feature Enhancements

Compared with native MySQL, TaurusDB provides enhanced partitioning capabilities.