Background information
In today's data-driven era, enterprises and organizations face unprecedented challenges in data storage and processing as data volumes grow exponentially and user bases expand rapidly. Database/table sharding technology, as an effective horizontal data partitioning strategy, has become a critical component of modern database architecture design.
Database/table sharding technology improves data processing capability and query performance by distributing data across multiple database instances or tables. This technology proves particularly valuable for large-scale data processing, high-concurrency access scenarios, and complex data model management. It effectively alleviates performance bottlenecks of single databases while enhancing overall system availability and fault tolerance.
In financial and internet sectors, core systems typically adopt unitized architectures where database/table sharding is prevalent. While sharding solves business problems, managing numerous shards becomes a major pain point for DBAs and developers. ODC's sharding support not only reduces management burdens but also lays the foundation for future sharded data archiving to OceanBase clusters.
Scenarios
Database/table sharding may encounter these operational challenges:
Structural changes require repeating DDL across all shards
When table structural modifications are needed, DBAs must execute identical DDL statements on each shard, multiplying workloads and increasing error risks.
Complex sharding rules demand manual memorization
Different business tables may follow distinct sharding rules (user ID hashing, date-based partitioning, etc.). Without centralized management, maintaining these becomes impractical.
Error-prone initial shard creation
New business launches require creating hundreds of tables according to predefined topologies - a process often done through manually written shell scripts or PL scripts that are difficult to maintain.
Schema inconsistency risks
Structural discrepancies among shards may impact business operations. Manually detecting these inconsistencies is unrealistic for DBAs.
To address these pain points, ODC V4.3.2 introduced logical database functionality with multiple operational solutions.
Automated shard discovery
When your system already contains numerous sharded tables, ODC enables one-click identification and management without tedious reconfiguration.
Prerequisites
- The project contains 2 physical databases: odc_0 and odc_1.
- odc_0 contains 3 sharded tables: test_0, test_2 and test_4.
- odc_1 contains 3 sharded tables: test_6, test_8 and test_10.
- All tables have identical structures.
Demonstration:
With just two simple steps, ODC automatically identifies the logical table and generates the topology expression odc_[0-1].test_[0-10:2], where:
odc_[0-1] indicates tables are evenly distributed between odc_0 and odc_1
test_[0-10:2] shows the shard numbering uses 0 as lower bound, 10 as upper bound, with step 2 (0,2,4,6,8,10). Users can expand the logical table to view physical tables under each database and their expressions.
Create Sharded Databases/Tables with various topologies
Logical tables can be treated like physical tables - users can view their structure in SQL window and create them similarly.
Prerequisites
The logical database has been configured.
Demonstration:
Creating a logical table
Viewing task results
The only difference from creating physical tables is specifying the logical table expression to determine its topology distribution. ODC supports various sharding topology expressions for flexible logical table creation.
Logical table structure consistency inspection
When physical shards develop structural inconsistencies, how can DBAs quickly identify them? ODC provides both scheduled background inspections and manual structural consistency checks.
Prerequisites
- At t1: odc_test_0, odc_test_1, odc_test_2 and odc_test_3 have identical structure and are recognized as one logical table.
- At t2: odc_test_0's structure changes (new column c2 added).
- At t3: Click to check logical table structure.
Demonstration:
Smart grouping-based structural changes
For logical table structural modifications, simply initiate a logical database change ticket like physical database changes. ODC will automatically apply changes to all physical shards based on the logical table's topology. The execution logic follows: Serial execution between multiple logical SQL statements, and smart parallel execution within single logical SQL. For MySQL, groups physical tables by their data source host, and executes serially within each group and in parallel between groups. For OceanBase, groups physical tables by their cluster, and executes serially within each group and in parallel between groups. This minimizes database pressure while maintaining structure consistency across physical shards.
Prerequisites
The logical table test already exists.
Demonstration:
Creating logical table change task
Viewing logical table change task
Emergency handling and exception recovery
During logical database changes, maintaining structure consistency across physical databases is critical. While successful changes are straightforward, ODC provides special handling for partial failures. For SQL in execution, ODC supports termination. And for failed SQL, ODC will pause entire task for manual resolution before continuing. These features enable rapid emergency response during logical database changes.
Prerequisites
Column c2 already exists in the logical table test.
Demonstration:
