Manage and modify sharded databases and tables via ODC

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.

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.

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.

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.

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.

Demonstration: