Skip to main content

KlustronDB Table Redistribution Function and Usage

KlustronDBAbout 4 min

KlustronDB Table Redistribution Function and Usage

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

Demonstrated the table redistribution functionality of KlustronDB through command line tools and XPanel, guiding customers on how to convert regular tables into partitioned tables. Simulated a case of table redistribution in an online application scenario, tested the impact of table redistribution on the online application, and verified data integrity.

In real business scenarios, it often happens that as business needs and the amount of data in a table change, the way the table was originally created may no longer be suitable. For example, in the sales_order table used in this article, at the initial stage of creation, because the business volume was not large, a non-partitioned table was created. However, as the company's business develops, the number of records in the table increases, so it needs to be converted into a partitioned table to facilitate future data archiving and speed up application access.

The specific environmental information is as follows:

Node TypeIPPort
Kluscomp instance192.168.40.15247002
Shard1 Primary192.168.40.15257003
Shard2 Primary192.168.26.15357005
XPanel192.168.40.15118080

01 Environment Preparation

Log in to the Kluscomp instance through the PG client and create users and databases.

psql -h 192.168.40.152 -p 47001 postgres
create user kunlun_test with password 'kunlun';
create database test_db with owner kunlun_test encoding utf8 template template0;
\q
psql -h 192.168.40.152 -p 47001 -U kunlun_test test_db

Create the sales_order table and load data through a stored procedure

create table sales_order
(
   order_number         INT NOT NULL AUTO_INCREMENT,
   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,order_date)
)  ;

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('2021-01-01','yyyy-mm-dd');
  i integer :=1;
BEGIN
	while i<=10000 loop
		v_customer_number := FLOOR(1+RANDOM()*6);
		v_product_code := FLOOR(1+RANDOM()*500);
		v_order_date := to_date('2021-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;
set statement_timeout=0;
call generate_order_data();
test_db=> select count(*) from sales_order;
 count 
-------
 10000
(1 row)

Create the target table sales_order_new, partitioned by the range of order time.

create table sales_order_new
(
   order_number         INT NOT NULL AUTO_INCREMENT,
   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,order_date)
) partition by range(order_date);
create table sales_order_p0 partition of sales_order_new
for values from ('2021-01-01') to ('2021-05-01');
create table sales_order_p1 partition of sales_order_new
for values from ('2021-05-01') to ('2021-09-01');
create table sales_order_p2 partition of sales_order_new
for values from ('2021-09-01') to ('2022-01-01');

02 Prepare a Python program kinsert.py to simulate an insertion application that runs continuously on the sales_order table.

import psycopg2.extras
import time

conn = psycopg2.connect(database='test_db',user='kunlun_test',
                 password='kunlun',host='192.168.40.152',port='47001')


cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)

# insert sales_order table
insert_sql = '''INSERT INTO sales_order VALUES (%s,
FLOOR(1+RANDOM()*6), FLOOR(1+RANDOM()*3),
to_date('2021-01-01','yyyy-mm-dd') + CAST(FLOOR(RANDOM()*365) AS INT),
to_date('2021-01-01','yyyy-mm-dd') + CAST(FLOOR(RANDOM()*365) AS INT),
FLOOR(1000+RANDOM()*9000));
;'''

#print("Press Enter to continue.")
input('Press any key and Enter to continue ~!')
i = 10001
while (i < 20001) :
  cursor.execute(insert_sql,[i])
  conn.commit()
  print("order_number:",i," is inserted.")
  i = i+1

cursor.close()
conn.close()

The application simulation starts inserting from order_number 10001. After the program runs, it will wait for input before executing the actual insertion logic. After starting to redistribute tasks in the XPanel interface, the program immediately resumes running.

[klbase@server-0 ~]$ python kinsert.py 
Press any key and Enter to continue ~!

03 Perform table redistribution operations on the XPanel interface

On the 'Cluster List Information' page, click 'Settings'

Click 'Table Redistribution' in the left sidebar

Select the 'target table cluster,' 'source table,' 'target table,' and the strategy for deleting the source table. If the strategy is set to 'automatic,' the source table will be retained by default for 7 days; if set to 'manual,' the user deletes the source table themselves.

_

Click 'Submit'. Meanwhile, switch to the terminal running the Python program kinsert.py and press Enter to continue inserting records.

[klbase@server-0 ~]$ python kinsert.py 
Press any key and Enter to continue ~!
order_number: 10001  is inserted.
order_number: 10002  is inserted.
order_number: 10003  is inserted.
order_number: 10004  is inserted.
order_number: 10005  is inserted.
order_number: 10006  is inserted.
order_number: 10007  is inserted.
order_number: 10008  is inserted.
order_number: 10009  is inserted.
order_number: 10010  is inserted.
order_number: 10011  is inserted.
order_number: 10012  is inserted.
order_number: 10013  is inserted.
order_number: 10014  is inserted.
order_number: 10015  is inserted.
order_number: 10016  is inserted.

Wait a moment and an insert error will occur, at which time the source table sales_order has been renamed.

order_number: 10281  is inserted.
order_number: 10282  is inserted.
order_number: 10283  is inserted.
order_number: 10284  is inserted.
order_number: 10285  is inserted.
order_number: 10286  is inserted.
order_number: 10287  is inserted.
order_number: 10288  is inserted.
order_number: 10289  is inserted.
order_number: 10290  is inserted.
order_number: 10291  is inserted.
order_number: 10292  is inserted.
order_number: 10293  is inserted.
order_number: 10294  is inserted.
order_number: 10295  is inserted.
order_number: 10296  is inserted.
order_number: 10297  is inserted.
Traceback (most recent call last):
  File "kinsert.py", line 22, in <module>
    cursor.execute(insert_sql,[i])
  File "/usr/local/lib64/python3.6/site-packages/psycopg2/extras.py", line 236, in execute
    return super().execute(query, vars)
psycopg2.errors.UndefinedTable: relation "sales_order" does not exist
LINE 1: INSERT INTO sales_order VALUES (10298,

At this time, the order_number inserted by the online application is 10297. When the table redistribution is executed, sales_order will be renamed, so the application fails to insert at this time.

_

03 Check the status of the target table and source table

Return to the PG client to check the status of the target table and the source table. It was found that the source table had become a partitioned table, and the target table was renamed to __sales_order$$tb_repartition_13 according to the 'delete source table' policy.

The naming rule is: _[source table name]$$tb_repartition[task number]. After 7 days, the system will automatically delete the table __sales_order$$tb_repartition_13.

_

_

Query the data in sales_order, the amount of data exactly matches the insertion point before the application error. After starting table redistribution, the online application still inserted 297 records.

test_db=> select count(*) from sales_order;
 count 
-------
 10297
(1 row)

END

Copyright © KlustronDB
Copyright © 2026 KlustronDB