GBase 8s Database Sharded Tables and Index Sharding Strategies (2)

In the realm of meticulous database management, sharding technology stands out for its exceptional data distribution and query optimization capabilities, becoming crucial for enhancing database performance. The GBase 8s database system offers robust sharding management functionalities. This article follows up on the previous one, detailing the management strategies for GBase 8s sharded tables, including initialization, modification, addition, deletion, merging, and detachment operations. It aims to assist database administrators and developers in efficiently managing sharded tables, ensuring high-performance data access.

GBase 8s Sharded Table Management

GBase 8s provides a series of operations for managing shards:

ALTER FRAGMENT ... INIT

Used to initialize a sharded table.

Example 1: Initialize a sharded table to a non-sharded table

drop table if  exists tab1;
CREATE TABLE tab1(col1 int, col2 date) FRAGMENT BY ROUND ROBIN IN datadbs1, datadbs2, datadbs3;
alter fragment on table tab1 init in datadbs1;

Example 2: Initialize a non-sharded table to a sharded table

alter fragment on table tab1 init FRAGMENT BY ROUND ROBIN IN datadbs1, datadbs2, datadbs3;

Example 3: Modify sharding strategy

alter fragment on table tab1 init FRAGMENT BY EXPRESSION
col1 >= 0 and col1 < 100 in  datadbs1,
col1 >= 100 and col1 < 200 in  datadbs2;

ALTER FRAGMENT ... ADD (or DROP)

Add a new shard to an existing sharded table or index (or remove it).

Example 1: Add a shard to an expression-sharded table

Drop table if exists tab1;
CREATE TABLE tab1 (col1 integer, col2 date)
       FRAGMENT BY EXPRESSION
        col1 >= 0 and col1 < 100 in  datadbs1,
        col1 >= 100 and col1 < 200 in  datadbs2;
alter fragment on table tab1 add 
col1 >= 200 and col1 < 300 in  datadbs3;

Example 2: Add a shard to a round-robin sharded table

Drop table if exists tab1;
CREATE TABLE tab1 (col1 integer, col2 date) 
FRAGMENT BY ROUND ROBIN IN datadbs1, datadbs2,datadbs3;

alter fragment on table tab1 add datadbs4;

Example 3: Delete a specified shard

alter fragment on table tab1 drop datadbs3;

Example 4: Delete a shard from an index

Drop table if exists tab1;
CREATE TABLE tab1 (col1 integer, col2 date)
       FRAGMENT BY EXPRESSION
        col1 >= 0 and col1 < 100 in  datadbs1,
        col1 >= 100 and col1 < 200 in  datadbs2,
          col1 >= 200 and col1 < 300 in  datadbs3;
CREATE INDEX idx_tab1 on tab1 (col1) 
FRAGMENT BY EXPRESSION 
col1 >= 0 and col1 < 100 in datadbs1, 
col1 >= 100 and col1 < 200 in datadbs2,
col1 >= 200 and col1 < 300 in  datadbs3;
alter fragment on INDEX idx_tab1 drop  datadbs2;
!oncheck -pt testdb:tab1 |grep DB
                  Table fragment partition datadbs1 in DBspace datadbs1
                  Table fragment partition datadbs2 in DBspace datadbs2
                  Table fragment partition datadbs3 in DBspace datadbs3
                  Index idx_tab1 fragment partition datadbs1 in DBspace datadbs1
                  Index idx_tab1 fragment partition datadbs3 in DBspace datadbs3

ALTER FRAGMENT ... MODIFY

Modify the expression of a sharded table or sharded index, or the dbspace corresponding to the shard.

Example 1: Modify the expression of a sharded table

Drop table if exists tab1;
CREATE TABLE tab1 (col1 integer, col2 date)
       FRAGMENT BY EXPRESSION
        col1 >= 0 and col1 < 100 in  datadbs1,
        col1 >= 100 and col1 < 200 in  datadbs2,
          col1 >= 200 and col1 < 300 in  datadbs3;
alter fragment on table tab1 modify datadbs3 to col1>=200 in datadbs3;

Example 2: Modify the dbspace of a sharded table

Drop table if exists tab1;
CREATE TABLE tab1 (col1 integer, col2 date)
       FRAGMENT BY EXPRESSION
        col1 >= 0 and col1 < 100 in  datadbs1,
        col1 >= 100 and col1 < 200 in  datadbs2,
          col1 >= 200 and col1 < 300 in  datadbs3;
alter fragment on table tab1 modify datadbs3 to  col1 >= 200 and col1 < 300 in datadbs4;
!oncheck -pt testdb:tab1 |grep DB
                   Table fragment partition datadbs1 in DBspace datadbs1
                  Table fragment partition datadbs2 in DBspace datadbs2
                   Table fragment partition datadbs4 in DBspace datadbs4

ALTER FRAGMENT ... ATTACH (or DETACH)

The operations ATTACH and DETACH allow merging tables with identical structures into a sharded table or detaching a shard into a standalone table. Here, we will introduce the detailed usage of the attach and detach operations for sharded tables.

• ALTER FRAGMENT ... ATTACH: This operation is used to merge a table as a shard into a sharded table.
• ALTER FRAGMENT ... DETACH: This operation is used to detach a shard from a sharded table into an individual table.

Example of Attaching Shards to a Sharded Table

The syntax is as follows:

Alter fragment on table tab1 ATTACH tab1 as partition p1, tab2 as partition p2;

or

ALTER FRAGMENT ON TABLE tb1 ATTACH tb1 AS (a <= 100), tb2 AS (a > 100);

Example 1: Merge Two Non-Sharded Tables with Identical Structures into a Sharded Table (The two tables can be in different dbspaces or the same dbspace)

Database testdb;
drop table if exists tab6_1;
drop table if exists tab6_2;
CREATE TABLE tab6_1 (col1 integer, col2 date) in   datadbs1;
Create index tab6_1_indx on tab6_1(col1);
CREATE TABLE tab6_2 (col1 integer, col2 date) in   datadbs2;
Create index tab6_2_indx on tab6_2(col1);
alter fragment on table tab6_1 ATTACH tab6_1 as partition p1, tab6_2 as partition p2;
!oncheck -pt testdb:tab6_1|grep DB
Your evaluation license will expire on 2025-03-30 00:00:00
                  Table fragment partition p1 in DBspace datadbs1
                  Table fragment partition p2 in DBspace datadbs2
                  Index tab6_1_indx fragment partition p1 in DBspace datadbs1
                  Index tab6_1_indx fragment partition p2 in DBspace datadbs2
--Alternatively, with expressions:
drop table if exists tab6_1;
drop table if exists tab6_2;
CREATE TABLE tab6_1 (col1 integer, col2 date) in   datadbs1;
Create index tab6_1_indx on tab6_1(col1);
CREATE TABLE tab6_2 (col1 integer, col2 date) in   datadbs2;
Create index tab6_2_indx on tab6_2(col1);
Alter fragment on table tab6_1 ATTACH tab6_1 as partition p11(col1 >= 0 and col1 < 200)  , tab6_2 as partition p22(col1 >= 100 and col1 < 200);
!oncheck -pt testdb:tab6_1|grep DB
                  Table fragment partition p11 in DBspace datadbs1
                  Table fragment partition p22 in DBspace datadbs2
                  Index tab6_1_indx fragment partition p11 in DBspace datadbs1
                  Index tab6_1_indx fragment partition p22 in DBspace datadbs2

Example 2: Attach a non-sharded table as a partition to a sharded table

Database testdb;
Drop table if exists tab7;
Drop table if exists tab8;
CREATE TABLE tab7 (col1 int, col2 date)
       FRAGMENT BY EXPRESSION
      Partition p1( col1 >= 0 and col1 < 100) in  datadbs1,
      Partition p2 (col1 >= 100 and col1 < 200) in  datadbs2;
 CREATE TABLE tab8 (col1 int, col2 date,check(col1>=200 and col1<300)) in datadbs3;
 Alter fragment on table tab7 attach tab8 as Partition p3 (col1>=200 and col1<300);
!oncheck -pt testdb:tab7 |grep DB
                   Table fragment partition p1 in DBspace datadbs1
                   Table fragment partition p2 in DBspace datadbs2
                   Table fragment partition p3 in DBspace datadbs3

Example 3: Use BEFORE and AFTER clauses to adjust the order of expressions

Database testdb;
Drop table if exists tab7;
Drop table if exists tab8;
CREATE TABLE tab7 (col1 int, col2 date)
FRAGMENT BY EXPRESSION
  col1 >= 0 and col1 < 100 in  datadbs1,
  col1 >= 100 and col1 < 200 in  datadbs2;
 CREATE TABLE tab8 (col1 int, col2 date) in datadbs3;
 Alter fragment on table tab7 attach tab8 as  (col1<0) before datadbs1;
!oncheck -pt testdb:tab7 |grep DB
                   Table fragment partition datadbs3 in DBspace datadbs3
                   Table fragment partition datadbs1 in DBspace datadbs1
                   Table fragment partition datadbs2 in DBspace datadbs2
--OR
Drop table if exists tab7;
Drop table if exists tab8;
CREATE TABLE tab7 (col1 int, col2 date)
       FRAGMENT BY EXPRESSION
       Partition p1( col1 >= 0 and col1 < 100) in  datadbs1,
       Partition p2 (col1 >= 100 and col1 < 200) in  datadbs2;
 CREATE TABLE tab8 (col1 int, col2 date) in datadbs3;
 Alter fragment on table tab7 attach tab8 as  Partition p3 (col1>=200 and col1<300) after p2;
!oncheck -pt testdb:tab7 |grep DB
                  Table fragment partition p1 in DBspace datadbs1
                  Table fragment partition p2 in DBspace datadbs2
                  Table fragment partition p3 in DBspace datadbs3

Points to Note:

• Use CHECK constraints in the target table to avoid unnecessary integrity checks during ATTACH.
• Foreign keys, primary keys, unique constraints, and serial type fields are not allowed in the target table.
• Index rebuilds can be avoided if:
• The data does not overlap.
• The new shard's index and the target table's index are built on the same column(s).
• The index has the same attributes as the target table's index (e.g., unique, duplicate).
• The new shard's index is not in any dbspace used by the target table's index.

Example of Detaching Shards from a Sharded Table

The syntax is as follows:

Alter fragment on table f1 DETACH dbspace2 f2

or

Alter fragment on table mytab1 DETACH partition p2 mytab2

If the index's sharding strategy differs from the table's sharding strategy, the index will need to be rebuilt. If they are the same, the index corresponding to the detached shard will be discarded. The database handles the index automatically, so no manual intervention is required.

--Index and Table Sharding Strategies Are the Same
CREATE TABLE tab9 (a int)   FRAGMENT BY EXPRESSION   
(a >=0 AND a < 5) IN datadbs1,
(a >=5 AND a <10) IN datadbs2,
(a >=10 AND a <15) IN datadbs3;
CREATE INDEX idx_tab9 ON tab9 (a);
!oncheck -pt testdb:tab9 |grep DB
                  Table fragment partition datadbs1 in DBspace datadbs1
                  Table fragment partition datadbs2 in DBspace datadbs2
                  Table fragment partition datadbs3 in DBspace datadbs3
                  Index idx_tab9 fragment partition datadbs1 in DBspace datadbs1
                  Index idx_tab9 fragment partition datadbs2 in DBspace datadbs2
                  Index idx_tab9 fragment partition datadbs3 in DBspace datadbs3
ALTER FRAGMENT ON TABLE tab9 DETACH datadbs3 tab10;
!oncheck -pt testdb:tab9 |grep DB
                  Table fragment partition datadbs1 in DBspace datadbs1
                  Table fragment partition datadbs2 in DBspace datadbs2
                  Index idx_tab9 fragment partition datadbs1 in DBspace datadbs1
                  Index idx_tab9 fragment partition datadbs2 in DBspace datadbs2

--Index and Table Sharding Strategies Differ
CREATE TABLE tab11 (a int, b int)  FRAGMENT BY EXPRESSION
  (a >=0 AND a < 5) IN datadbs1,
  (a >=5 AND a <10) IN datadbs2,
  (a >=10 AND a <15) IN datadbs3;

  CREATE INDEX idx_tab11 on tab11(a)   FRAGMENT BY EXPRESSION
  (a >=0 AND a< 10) IN datadbs3,
  (a >=5 AND a< 15) IN datadbs4;
!oncheck -pt testdb:tab11 |grep DB
Table fragment partition datadbs1 in DBspace datadbs1
                  Table fragment partition datadbs2 in DBspace datadbs2
                  Table fragment partition datadbs3 in DBspace datadbs3
                  Index idx_tab11 fragment partition datadbs3 in DBspace datadbs3
                  Index idx_tab11 fragment partition datadbs4 in DBspace datadbs4
ALTER FRAGMENT ON TABLE tab11 DETACH datadbs3 tab12 ;
!oncheck -pt testdb:tab11 |grep DB
Your evaluation license will expire on 2025-03-30 00:00:00
                  Table fragment partition datadbs1 in DBspace datadbs1
                  Table fragment partition datadbs2 in DBspace datadbs2
                  Index idx_tab11 fragment partition datadbs3 in DBspace datadbs3
                  Index idx_tab11 fragment partition datadbs4 in DBspace datadbs4

GBase 8s' sharded table management functionalities ensure high-performance operation for the database. Through this series of articles, we hope to help users deeply understand various aspects of shard management, master the formulation and adjustment of sharding strategies, and leverage the powerful performance of GBase databases in practical applications. As technology advances and business needs evolve, GBase 8s will continue to provide users with more comprehensive and efficient database solutions.