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

Cong Li - Jul 29 - - Dev Community

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Example 3: Delete a specified shard

alter fragment on table tab1 drop datadbs3;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

or

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

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

or

Alter fragment on table mytab1 DETACH partition p2 mytab2
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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.

Image
logical operator C++
Image
The Reasons Why Adding A Head Injury Lawyer To Your Life's Journey Will Make The Difference
Image
Implementing Webpack from Scratch, But in Rust - [4] Implement Plugin System

webpack, rust
Image
Start Your NFT Gaming Platform with These 10 Simple Ideas

webdev, programming, productivity, devops
Image
10 No-Fuss Methods To Figuring The Leg And Arm Injury Attorneys You're Looking For
Image
How To Get More Value With Your Gas And Heating Engineer
Image
Cheap Gas Engineer Near Me Explained In Fewer Than 140 Characters
Image
14 Misconceptions Common To Bifold Door Repairs
Image
Ubuntu Üzerine SQLite3 kullanılarak Zabbix Proxy Kurulumu, Yapılandırması ve Verilerin Proxy üzerinden Server'da Gösterilmesi

zabbix, proxy
Image
Advanced WAF Not Fully Based on Rules or Signatures

cybersecurity, opensource, webdev, beginners
Image
Building a Token Blacklisting System with Redis Cloud in Node.js
Image
Why Nobody Cares About Internal Injury Attorney
Image
Top Benefits of Choosing a Limo in Las Vegas for Your Next Event

limoinlasvegas
Image
The Biggest Issue With Bifold Door Repair Near Me, And How You Can Fix It
Image
Langkah Naikkan Prosentase Menang Judi Online
Image
Magia Mebli: Jak Stworzyć Wnętrze, Które Zachwyca
Image
Dubai Transit Visa for Kenyans: Process, Fees & Documents

travel
Image
Teknik Claim Bonus Di Situs Judi Online Paling dipercaya
Image
My Highlights from the Umbraco US Festival 2024

umbraco, techtalks, dotnet
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Terabox Video Player