Skip to main content

KlustronDB Table Group Application Example

KlustronDBAbout 4 min

KlustronDB Table Group Application Example

Note:

Unless otherwise specified, the version numbers in the text can be replaced with the version numbers of any released version. For all released versions, see: Release_notes

Overview

Table Group (TABLE GROUP) is a feature of KlustronDB as a distributed database. A table group is an attribute of a table that affects the specific distribution of tables on Shards. This article introduces the purpose, syntax, and specific examples of table groups, demonstrating the advantages of using table groups. Finally, it shows the process of migrating an entire table group to another Shard through XPanel.

**01 **Table Group Introduction

In real-world scenarios, some related tables are often joined or frequently updated within the same transaction. Therefore, KlustronDB supports users placing these related tables in the same shard to achieve better query performance and transaction processing performance.

When these related tables are joined, the table joins can be pushed down to the storage shards for execution, thereby achieving better query performance; when updating related tables within the same transaction, it can avoid two-phase commits, thereby achieving better transaction processing performance.

A table in the table group cannot be moved to another shard individually. If it needs to be moved, the entire set of tables in the table group must be moved together.

02 Test Environment

Since the tests were conducted on virtual machines, the execution times in the article can only be used as a comparative reference between different scenarios under the same environment. The configuration information of the virtual machines related to the test cluster is as follows:

Node CategoryNameIPCPUMemory
Kluscomp instance
192.168.40.1522C Intel i9 2.4 GHz6G
Storage Master Nodeshard_1192.168.40.1512C Intel i9 2.4 GHz6G
Storage Master Nodeshard_2192.168.40.1532C Intel i9 2.4 GHz6G

03 Table Group Grammar

The syntax for creating a table group is as follows:

CREATE TABLEGROUP tablegroup_name
    [ OWNER  { new_owner | CURRENT_USER | SESSION_USER } ]
    [ WITH (SHARD = shardid) ]

When creating a table, you can specify the table group name.

CREATE TABLE table_name … WITH (TABLEGROUP tablegroup_name);

You can also specify the table group using the following statement after creating the table.

ALTER TABLE table_name SET (TABLEGROUP tablegroup_name);

04 Specific Case

First, log in to the Kluscomp instance 192.168.40.152 through the psql client, create users and data, and view shard information in the cluster.

psql -h 192.168.40.152 -p 47001 postgres
create user kunlun_test with password 'kunlun';
create database testdb owner kunlun_test;
grant all privileges on database testdb to kunlun_test;
alter user kunlun_test superuser;

Log in to testdb with user kunlun_test, and create test tables sales_order, product, and customer.

The specified customer and product are stored in shard_2, and the sales_order is stored in shard_1.

psql -h 192.168.40.152 -p 47001 -U kunlun_test testdb
CREATE TABLE customer
(
   customer_number      INT NOT NULL,
   customer_name        VARCHAR(128) NOT NULL,
   customer_street_address VARCHAR(256) NOT NULL,
   customer_zip_code    INT NOT NULL,
   customer_city        VARCHAR(32) NOT NULL,
   customer_state       VARCHAR(32) NOT NULL,
   PRIMARY KEY (customer_number)
) 
with(shard=1);
CREATE TABLE product
(
   product_code         INT NOT NULL,
   product_name         VARCHAR(128) NOT NULL,
   product_category     VARCHAR(256) NOT NULL,
   PRIMARY KEY (product_code)
)  
with (shard=1);

CREATE TABLE sales_order
(
   order_number         INT NOT NULL,
   customer_number      INT NOT NULL,
   product_code         INT NOT NULL,
   order_date           DATETIME NOT NULL,
   entry_date           DATETIME NOT NULL,
   order_amount         DECIMAL(18,2) NOT NULL,
   PRIMARY KEY (order_number)
) with (shard=2);

Insert test data

INSERT INTO customer
( customer_number
, customer_name
, customer_street_address
, customer_zip_code
, customer_city
, customer_state
 )
VALUES
  (1,'Big Customers', '7500 Louise Dr.', '17050','Mechanicsburg', 'PA')
, ( 2,'Small Stores', '2500 Woodland St.', '17055','Pittsburgh', 'PA')
, (3,'Medium Retailers', '1111 Ritter Rd.', '17055',  'Pittsburgh', 'PA')
,  (4,'Good Companies', '9500 Scott St.', '17050','Mechanicsburg', 'PA')
, (5,'Wonderful Shops', '3333 Rossmoyne Rd.', '17050','Mechanicsburg', 'PA')
, (6,'Loyal Clients', '7070 Ritter Rd.', '17055', 'Pittsburgh', 'PA')
;

Use the following two stored procedures to load test data for product and sales_order. Load 1000 rows for product and 100,000 rows for sales_order.

create or replace procedure generate_order_data()
AS $$
DECLARE
  v_customer_number integer;
  v_product_code integer;
  v_order_date date;
  v_amount integer;
  start_date date := to_date('2022-01-01','yyyy-mm-dd');
  i integer :=1;
BEGIN
	while i<=100000 loop
		v_customer_number := FLOOR(1+RANDOM()*6);
		v_product_code := FLOOR(1+RANDOM()*1000);
		v_order_date := to_date('2022-01-01','yyyy-mm-dd') + CAST(FLOOR(RANDOM()*365) AS INT);
		v_amount := FLOOR(1000+RANDOM()*9000);
		INSERT INTO sales_order VALUES (i,v_customer_number,v_product_code,v_order_date,v_order_date,v_amount);
		commit;
		i := i+1;
	end loop;
END; $$
LANGUAGE plpgsql;

create or replace procedure generate_product_data()
AS $$
DECLARE
  v_product_name varchar(128);
  i integer :=1;
BEGIN
	while i<=1000 loop
               case mod(i,3)
                   when 0 then
		        v_product_name := 'Hard Disk '||i;
		        INSERT INTO product VALUES (i,v_product_name,'Storage');
	         when 1 then
                          v_product_name := 'LCD '||i;
		         INSERT INTO product VALUES (i,v_product_name,'Monitor');
	         when 2 then
		      v_product_name := 'Paper'||i;
		       INSERT INTO product VALUES (i,v_product_name,'Paper');
		end case;
	      commit;
		i := i+1;
	end loop;
END; $$
LANGUAGE plpgsql;

Complete the data loading and create an index for sales_order, and finally gather statistics for all tables.

set statement_timeout=0;
call generate_product_data();
call generate_order_data();
create index sales_order_idx1 on sales_order(order_date);
analyze product;
analyze sales_order;
analyze customer;
\timing on

The business now wants to see the amount spent by Big Customers in the first half of 2022 on each product category.

The specific SQL is as follows:

select product_category,sum(order_amount ) from sales_order t1, product t2 ,customer t3 where 
t1.product_code = t2.product_code and 
t1.customer_number = t3.customer_number and
t1.order_date between to_date('2022-01-01','yyyy-mm-dd') and to_date('2022-06-30','yyyy-mm-dd') and customer_name='Big Customers' group by product_category;

View the detailed execution plan:

It was found that the filtered data was taken separately from Shard 1 and 2, and then the Join was performed separately after reaching the Kluscomp instance.

The corresponding execution time is 168 ms

First, create a table group sales_sum, which is located on Shard 2. And place the sales_order table into sales_sum.

CREATE TABLEGROUP sales_sum WITH (SHARD=2);
alter table sales_order set (tablegroup=sales_sum);

Recreate the product and customer tables, specifying the table group sales_tg when creating them.

drop table customer;
alter table product rename to product_old;
CREATE TABLE customer
(
   customer_number      INT NOT NULL,
   customer_name        VARCHAR(128) NOT NULL,
   customer_street_address VARCHAR(256) NOT NULL,
   customer_zip_code    INT NOT NULL,
   customer_city        VARCHAR(32) NOT NULL,
   customer_state       VARCHAR(32) NOT NULL,
   PRIMARY KEY (customer_number)
) 
with (tablegroup=sales_sum);

CREATE TABLE product
(
   product_code         INT NOT NULL,
   product_name         VARCHAR(128) NOT NULL,
   product_category     VARCHAR(256) NOT NULL,
   PRIMARY KEY (product_code)
)  
with (tablegroup=sales_sum);

Reload data for customer and product, and collect statistics.

INSERT INTO customer
( customer_number
, customer_name
, customer_street_address
, customer_zip_code
, customer_city
, customer_state
 )
VALUES
  (1,'Big Customers', '7500 Louise Dr.', '17050','Mechanicsburg', 'PA')
, ( 2,'Small Stores', '2500 Woodland St.', '17055','Pittsburgh', 'PA')
, (3,'Medium Retailers', '1111 Ritter Rd.', '17055',  'Pittsburgh', 'PA')
,  (4,'Good Companies', '9500 Scott St.', '17050','Mechanicsburg', 'PA')
, (5,'Wonderful Shops', '3333 Rossmoyne Rd.', '17050','Mechanicsburg', 'PA')
, (6,'Loyal Clients', '7070 Ritter Rd.', '17055', 'Pittsburgh', 'PA');

insert into product select * from product_old;
analyze product;
analyze customer;

Upon reviewing the execution plan of the previous SQL again, it was found that everything was completed on Shard 2.

The execution time has also become faster, improved by 49.4ms.

05 Table Group Migration

You can check the shard and group-related information where the table is located through the following methods.

Try to migrate the customer table separately to the shard with id 1 on XPanel.

Select the database as testdb, and then click OK.

First, select the customer table, check 'No' for 'Keep the original table', choose 'shard_2' as the target shard, and finally click OK.

Moving the customer table alone will cause an error; you cannot move only some tables within the sales_sum table group.

Try again to move all tables in the table group sales_sum; customer, product, and sales_order

All table tasks in the entire table group were moved successfully.

Verify that all tables are located in shard_2 (shard id=1) after the move

END

Copyright © KlustronDB
Copyright © 2026 KlustronDB