Overview and Advantages of KlustronDB
Overview and Advantages of KlustronDB
KlustronDB is a distributed database system compatible with both MySQL and PostgreSQL, serving as an enhanced version of MySQL and PostgreSQL. By utilizing the computing and storage resources of multiple servers, KlustronDB can manage data scales and access loads far beyond those of MySQL and PostgreSQL, and can continuously grow as needed. At the same time, the way application software interacts with KlustronDB is exactly the same as with MySQL and PostgreSQL databases, while still providing complete transaction ACID compliance, automatic fault recovery, and data consistency guarantees. Supporting such a simple, elegant usage and robustness are a large number of advanced technologies designed and developed by the KlustronDB R&D team, including automatic data partitioning, horizontal elastic scalability, distributed transaction processing, distributed parallel query processing, fully consistent master-slave replication, distributed transaction fault recovery, global consistency views (Global MVCC), and more.
KlustronDB addresses a series of technical challenges related to massive data storage, management, analysis, and utilization, supporting high-concurrency, high-load online transaction processing (OLTP), and providing extreme performance with high throughput and low latency. Based on the relational data model, KlustronDB provides one-stop support for GIS, JSON, text, and vector data management and query retrieval, greatly simplifying application system architecture design and development complexity, significantly reducing the complexity of backend system operations and maintenance and hardware resource overhead, and offering standard interoperability and compatibility with upper-level application systems and other data processing components, helping users achieve a pluggable, standardized, component-based system architecture.
Quick Start with KlustronDB
For application system developers, the usage of KlustronDB is exactly the same as MySQL and PostgreSQL databases. KlustronDB supports JDBC, ODBC, Hibernate, MyBatis, and client connection libraries for all common programming languages. Software written in all these languages can connect to KlustronDB and correctly execute all standard-compliant SQL statements, as well as MySQL and PostgreSQL proprietary DML SQL statements. Therefore, applications originally using MySQL and PostgreSQL can use KlustronDB without any modifications.
Connect and initialize
First, you can use the psql or mysql programs, JDBC/ODBC, and various programming language libraries for PostgreSQL and MySQL to connect to KlustronDB. The methods and parameters are the same as connecting to and using PostgreSQL and MySQL. During the installation of a KlustronDB cluster through XPanel, users can set the default username and password for KlustronDB Kluscomp instances. If not set, the default is abc/abc.
$ ./psql -h192.168.12.34 -p23456 -Uabc postgres
用户 abc 的口令：
After each KlustronDB cluster is installed, the database name created by default in the Kluscomp instance is also postgres, which is a tribute by the KlustronDB development team to PostgreSQL. Then users can create more databases for various business systems. For example, in this case, we create KunlunDB and use it for function demonstration.
KlustronDB supports and extends the MySQL-style SHOW series of commands, and you can use the SHOW DATABASES statement to query which databases exist in the current cluster. You can also query metadata tables like pg_database to obtain more detailed system metadata information.
All system tables in PostgreSQL catalogs still exist in KlustronDB and the vast majority are still valid for use, and KlustronDB has added more system tables for KlustronDB-related functions. Users cannot directly modify the data in these system catalog tables, and executing DDL will indirectly modify data in some of these tables.
postgres=# show databases;
Database
-----------
postgres
template1
template0
(3 行记录)
In the list above, postgres is the default created and available database. Do not use template0/1, as these two are templates used to create more databases.
MySQL users need to note that each database connection can only operate on and access data in one database, and cannot access data in other databases. Therefore, you must exit the current connection and reconnect to the KunlunDB database to execute DDL/DML SQL statements within it. This behavior is inherited from PostgreSQL, which allows for the most thorough data isolation and security, and is particularly important in a multi-tenant mode.
The 'database' that MySQL users are familiar with corresponds to the 'schema' in PostgreSQL and KlustronDB, which is essentially a namespace. MySQL simplifies the concept of a database by making it completely equivalent to a schema. This approach is insufficient for larger application systems. In PostgreSQL and KlustronDB, a database can contain multiple schemas, which will be introduced shortly.
postgres=# create database KunlunDB;
CREATE DATABASE
postgres=# quit
Exit the connection to the postgres database, then connect to the KunlunDB database. Note that KlustronDB follows PostgreSQL's case rules for all kinds of names --- case-sensitive and converted to lowercase upon creation. Therefore, although the database name is specified as KunlunDB, you actually need to use kunlundb to find this database.
$ ./psql -h192.168.12.34 -p23456 -Uabc KunlunDB
用户 abc 的口令：
psql: 错误: FATAL: database "KunlunDB" has disappeared from pg_database
描述: Database OID 1188114 now seems to belong to "kunlundb".
$ ./psql -h192.168.12.34 -p23456 -Uabc kunlundb
用户 abc 的口令：
Use MySQL style SHOW SCHEMAS to list all schemas in the current database. Among these schemas, public is the schema created by default for user use in each database, while the others are system schemas that contain metadata tables and views of the KlustronDB system. It is not recommended for users to create database objects in these system schemas, and users are also not allowed to directly modify any data in these system schemas.
kunlundb=# show schemas;
Schemas
--------------------
pg_toast
pg_temp_1
pg_toast_temp_1
pg_catalog
public
information_schema
(6 行记录)
For KlustronDB and PostgreSQL, a schema is a namespace. Within the same database, there can be multiple schemas, and these schemas can have database objects with the same name (such as tables, indexes, procedures, functions, views, materialized views, sequences, etc.) without name conflicts. For example, schema_1.table_1 and schema_2.table_1 are two different tables, and when querying, you should use the full name, such as select * from schema_1.table_1. If the full name is not specified, such as select * from table_1, the table name will be searched for first in the current schema.
kunlundb=# create schema ecom;
CREATE SCHEMA
kunlundb=# use ecom;
USE
Every time you connect to KlustronDB, the default current schema is public. As long as the current database user has the required permissions, you can freely switch to any schema within the current database in a single database connection, and at the same time access data in any of the schemas. To switch the current schema to another schema, use the USE statement as described above, which is a syntax added by KlustronDB for MySQL compatibility.
When connecting with MySQL, you can specify the current schema after connecting. For example, ./mysql -h192.168.12.34 -P23457 -uabc -pabc kunlundb.ecom will connect to the ecom schema within the kunlundb database. At the same time, for MySQL compatibility, by default in KlustronDB's MySQL connections, executing CREATE/DROP DATABASE dbname and SHOW DATABASES is equivalent to executing CREATE/DROP SCHEMA dbname and SHOW SCHEMAS, thereby meeting MySQL users' expectations regarding databases --- allowing switching back and forth, and being able to access all databases within the same connection.
Of course, you can also use PostgreSQL's method to set the search path (SET SEARCH_PATH). KlustronDB follows exactly the same name resolution rules as PostgreSQL, so during an actual search, it first looks for the name in the current schema, and if not found, it searches in the subsequent schemas according to the schema sequence of the search_path variable.
Note that each Kluscomp instance in a KlustronDB cluster will listen on a pair of identical ports, namely a PostgreSQL port (23456 in this example) and a MySQL protocol port (23457 in this example). This pair of port numbers is automatically assigned by the relevant modules of KlustronDB during installation.
Starting from CREATE TABLE
In KlustronDB, the data of a table is stored in its storage shards. If it is a partitioned table, KlustronDB will roughly distribute its shards evenly across all shards in the cluster; if it is a single table, it will be assigned to a specific shard.
kunlundb=# use ecom;
USE
kunlundb=# CREATE TABLE products (
kunlundb(# product_id INT PRIMARY KEY AUTO_INCREMENT,
kunlundb(# product_name VARCHAR(100) NOT NULL,
kunlundb(# category VARCHAR(50) NOT NULL,
kunlundb(# price DECIMAL(10, 2) NOT NULL,
kunlundb(# stock_quantity INT NOT NULL,
kunlundb(# created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
kunlundb(# );
CREATE TABLE
Fully automatic and customizable hash partitioning
1. Fully automatic Hash partitioning
Starting from KlustronDB version 1.4, KlustronDB supports default automatic hash partitioning, which means that when auto_hash_partitions is a positive number, there is no need to specify the partitioning method in detail; by default, the primary key is used as the hash partition key, dividing the table into auto_hash_partitions partitions.
Therefore, although the above CREATE TABLE statement does not specify any partitioning method or parameters, executing SHOW CREATE TABLE shows that the above products table is actually created as a HASH partitioned table with 4 partitions.
Users must always read and write data directly using the products table. KlustronDB will automatically handle the mapping of data rows, write them to the appropriate partitions, or query them from the appropriate partitions, automatically avoiding access to partitions where the target data row does not exist. Data rows must only be read and written using the main table; directly inserting data rows into table partitions is prohibited, because doing so is not only cumbersome but also prone to errors, which can result in data rows being inserted into the wrong partitions and, in some cases (such as querying based on partition conditions), becoming unqueryable.
kunlundb=# show create table products;
Table | Create Table
----------+-------------------------------------------------------------------------------------------------------------------------------
products | CREATE TABLE products ( +
| product_id integer NOT NULL, +
| product_name character varying(100) COLLATE utf8mb4_0900_bin NOT NULL, +
| category character varying(50) COLLATE utf8mb4_0900_bin NOT NULL, +
| price numeric(10,2) NOT NULL, +
| stock_quantity integer NOT NULL, +
| created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, +
| CONSTRAINT products_pkey PRIMARY KEY (product_id) +
| ) PARTITION BY HASH (product_id) WITH (engine=innodb); +
| +
| ALTER TABLE products ALTER COLUMN product_id ADD AUTO_INCREMENT ( +
| SEQUENCE NAME products_product_id_seq +
| START WITH 1 +
| INCREMENT BY 1 +
| MINVALUE 1 +
| NO MAXVALUE +
| CACHE 1 +
| SHARD 1); +
| +
| CREATE TABLE products_auto_p0 PARTITION OF products FOR VALUES WITH (modulus 4, remainder 0) WITH (engine=innodb, shard='2');+
| +
| CREATE TABLE products_auto_p1 PARTITION OF products FOR VALUES WITH (modulus 4, remainder 1) WITH (engine=innodb, shard='1');+
| +
| CREATE TABLE products_auto_p2 PARTITION OF products FOR VALUES WITH (modulus 4, remainder 2) WITH (engine=innodb, shard='2');+
| +
| CREATE TABLE products_auto_p3 PARTITION OF products FOR VALUES WITH (modulus 4, remainder 3) WITH (engine=innodb, shard='1')
(1 行记录)
kunlundb=# show auto_hash_partitions;
auto_hash_partitions
----------------------
4
(1 行记录)
-- 使用总表做读写，不要直接读写表分区
kunlundb-# insert into products(product_name, category, price, stock_quantity) values
kunlundb-# ('iphone', 'cell phones', 500, 10000),
kunlundb-# ('xiaomi', 'cell phones', 450, 15000),
kunlundb-# ('huawei', 'cell phones', 550, 15000),
kunlundb-# ('oppo', 'cell phones', 450, 10000),
kunlundb-# ('vivo', 'cell phones', 400, 10000);
-- 使用总表做读写，不要直接读写表分区
kunlundb=# select*from products;
product_id | product_name | category | price | stock_quantity | created_at
------------+--------------+-------------+--------+----------------+---------------------
1 | iphone | cell phones | 500.00 | 10000 | 2026-03-29 09:38:44
3 | huawei | cell phones | 550.00 | 15000 | 2026-03-29 09:38:44
5 | vivo | cell phones | 400.00 | 10000 | 2026-03-29 09:38:44
2 | xiaomi | cell phones | 450.00 | 15000 | 2026-03-29 09:38:44
4 | oppo | cell phones | 450.00 | 10000 | 2026-03-29 09:38:44
You can use the following statement to query which table partition each row in the products table is located in, and which shard that partition is located on.
kunlundb=# select p.*, p.tableoid, c.relname, n.id as shard_id from products p, pg_shard n, pg_class c where p.tableoid=c.oid and c.relshardid=n.id;
product_id | product_name | category | price | stock_quantity | created_at | tableoid | relname | shard_id
------------+--------------+-------------+--------+----------------+---------------------+----------+------------------+----------
1 | iphone | cell phones | 500.00 | 10000 | 2026-03-29 09:38:44 | 1188124 | products_auto_p0 | 2
3 | huawei | cell phones | 550.00 | 15000 | 2026-03-29 09:38:44 | 1188130 | products_auto_p1 | 1
5 | vivo | cell phones | 400.00 | 10000 | 2026-03-29 09:38:44 | 1188130 | products_auto_p1 | 1
2 | xiaomi | cell phones | 450.00 | 15000 | 2026-03-29 09:38:44 | 1188136 | products_auto_p2 | 2
4 | oppo | cell phones | 450.00 | 10000 | 2026-03-29 09:38:44 | 1188142 | products_auto_p3 | 1
You can explicitly modify auto_hash_partitions before executing CREATE TABLE in order to create a table with the appropriate number of partitions.
The four partitions of this products table products_auto_p0/1/2/3 are placed in the two shards numbered 1 and 2, and the InnoDB storage engine is used in the Klustore instances to store the data. You can explicitly set the engine attribute in CREATE TABLE to specify using the rocksdb storage engine, or (rarely necessary) explicitly specify the shard attribute to store it in a specific shard, as detailed below.
2. Customizable Automatic Hash Partitioning
KlustronDB also supports a more flexible automatic hash partitioning method, allowing the selection of partition columns and partitioning methods.
kunlundb=# create table logistics(
kunlundb=# dest_addr varchar(300), order_id bigint,
kunlundb=# request_ts timestamp, pickup_ts timestamp, cust_sign_ts timestamp,
kunlundb=# status enum('requested', 'picked_up', 'customer_signed') default 'requested')
kunlundb=# partition by hash(order_id) partitions 6; -- 定制 HASH 分区列和分区个数
CREATE TABLE
kunlundb=# show create table logistics;
Table | Create Table
-----------+---------------------------------------------------------------------------------------------------------------------------------
logistics | CREATE TABLE logistics ( +
| dest_addr character varying(300) COLLATE utf8mb4_0900_bin, +
| order_id bigint NOT NULL, +
| request_ts timestamp without time zone, +
| pickup_ts timestamp without time zone, +
| cust_sign_ts timestamp without time zone, +
| status enum_logistics_status DEFAULT 'requested'::enum_logistics_status, +
| __sys_auto_rowid__ bigint DEFAULT "nextval"('logistics___sys_auto_rowid___seq'::regclass) NOT NULL, +
| CONSTRAINT logistics_pkey PRIMARY KEY (__sys_auto_rowid__, order_id) +
| ) PARTITION BY HASH (order_id); +
| +
| CREATE TABLE logistics_auto_p0 PARTITION OF logistics FOR VALUES WITH (modulus 6, remainder 0) WITH (engine=innodb, shard='1');+
| +
| CREATE TABLE logistics_auto_p1 PARTITION OF logistics FOR VALUES WITH (modulus 6, remainder 1) WITH (engine=innodb, shard='2');+
| +
| CREATE TABLE logistics_auto_p2 PARTITION OF logistics FOR VALUES WITH (modulus 6, remainder 2) WITH (engine=innodb, shard='1');+
| +
| CREATE TABLE logistics_auto_p3 PARTITION OF logistics FOR VALUES WITH (modulus 6, remainder 3) WITH (engine=innodb, shard='2');+
| +
| CREATE TABLE logistics_auto_p4 PARTITION OF logistics FOR VALUES WITH (modulus 6, remainder 4) WITH (engine=innodb, shard='2');+
| +
| CREATE TABLE logistics_auto_p5 PARTITION OF logistics FOR VALUES WITH (modulus 6, remainder 5) WITH (engine=innodb, shard='1')
(1 行记录)
Note in the above statement, since order_id is set as a partition column, even though the column is not set with the NOT NULL constraint in the CREATE TABLE statement, the system automatically adds the NOT NULL constraint for it and includes the column in the system-defined primary key.
Custom data storage solution
KlustronDB supports rich and flexible data storage solutions, and also allows users to flexibly and finely customize data storage solutions. This section provides a guide to related features and usage. In addition to the customization capabilities of storage options listed in this section, KlustronDB also supports more storage option customization features.
Select storage engine
You can explicitly set the engine=rocksdb property in the CREATE TABLE statement to specify using the rocksdb storage engine to store table shard data. You can also set the column_family implementation to use a specific column family, so that RocksDB can achieve better performance. Additionally, you can set the default_storage_engine configuration variable (default is 'innodb', it can also be set to 'rocksdb'), so that you can use the engine specified by default_storage_engine in the CREATE TABLE statement without setting the engine property.
Currently, KlustronDB supports two transaction storage engines, innodb and rocksdb, for storing data. RocksDB performs well in append-only write scenarios (such as time-series data and operational logs), and its compression ratio can exceed InnoDB by more than 10 times, significantly reducing storage space. There are also some additional precautions that must be strictly followed when using RocksDB.
kunlundb=# CREATE TABLE orders (
kunlundb(# order_id INT PRIMARY KEY AUTO_INCREMENT,
kunlundb(# customer_id INT NOT NULL,
kunlundb(# product_id INT NOT NULL,
kunlundb(# quantity INT NOT NULL,
kunlundb(# order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
kunlundb(# status enum('pending','paied','delivered','completed') NOT NULL DEFAULT 'pending',
kunlundb(# total_amount DECIMAL(10, 2) NOT NULL
kunlundb(# ) with (engine='rocksdb', column_family='cf_orders');
CREATE TABLE
kunlundb=# show create table orders;
Table | Create Table
--------+-----------------------------------------------------------------------------------------------------------------------------------------------------
orders | CREATE TABLE orders ( +
| order_id integer NOT NULL, +
| customer_id integer NOT NULL, +
| product_id integer NOT NULL, +
| quantity integer NOT NULL, +
| order_date timestamp without time zone DEFAULT CURRENT_TIMESTAMP, +
| status enum_orders_status DEFAULT 'pending'::enum_orders_status NOT NULL, +
| total_amount numeric(10,2) NOT NULL, +
| CONSTRAINT orders_pkey PRIMARY KEY (order_id) +
| ) PARTITION BY HASH (order_id) WITH (engine=rocksdb, column_family=cf_orders); +
| +
| ALTER TABLE orders ALTER COLUMN order_id ADD AUTO_INCREMENT ( +
| SEQUENCE NAME orders_order_id_seq +
| START WITH 1 +
| INCREMENT BY 1 +
| MINVALUE 1 +
| NO MAXVALUE +
| CACHE 1 +
| SHARD 1); +
| +
| CREATE TABLE orders_auto_p0 PARTITION OF orders FOR VALUES WITH (modulus 4, remainder 0) WITH (engine=rocksdb, column_family=cf_orders, shard='1');+
| +
| CREATE TABLE orders_auto_p1 PARTITION OF orders FOR VALUES WITH (modulus 4, remainder 1) WITH (engine=rocksdb, column_family=cf_orders, shard='1');+
| +
| CREATE TABLE orders_auto_p2 PARTITION OF orders FOR VALUES WITH (modulus 4, remainder 2) WITH (engine=rocksdb, column_family=cf_orders, shard='2');+
| +
| CREATE TABLE orders_auto_p3 PARTITION OF orders FOR VALUES WITH (modulus 4, remainder 3) WITH (engine=rocksdb, column_family=cf_orders, shard='2')
(1 行记录)
In the above statement, it can be seen that KlustronDB supports MySQL's enum data type and its syntax, and even more conveniently than PostgreSQL, KlustronDB will automatically create this enum type, namely enum_orders_status, and use this type for the column.
Automatically add primary key
If a user table has neither a primary key nor a unique index, starting from version 1.4, KlustronDB will automatically add a primary key to the user table to achieve better performance. The specific method is to add an auto-increment 64-bit integer column __sys_auto_rowid__ to create the primary key. The aforementioned automatic partitioning feature will use this automatically added primary key column as the partition column for automatic HASH partitioning, and this combination achieves a very surprising effect.
kunlundb=# create table syslogs(msg text, rcv_ts timestamp, level enum ('error', 'warning', 'info')) with (engine='rocksdb', column_family='cf_syslogs');
CREATE TABLE
kunlundb=# show create table syslogs;
Table | Create Table
---------+--------------------------------------------------------------------------------------------------------------------------------------------------------
syslogs | CREATE TABLE syslogs ( +
| msg text COLLATE utf8mb4_0900_bin, +
| rcv_ts timestamp without time zone, +
| level enum_syslogs_level, +
| __sys_auto_rowid__ bigint DEFAULT "nextval"('syslogs___sys_auto_rowid___seq'::regclass) NOT NULL, +
| CONSTRAINT syslogs_pkey PRIMARY KEY (__sys_auto_rowid__) +
| ) PARTITION BY HASH (__sys_auto_rowid__) WITH (engine=rocksdb, column_family=cf_syslogs); +
| +
| CREATE TABLE syslogs_auto_p0 PARTITION OF syslogs FOR VALUES WITH (modulus 4, remainder 0) WITH (engine=rocksdb, column_family=cf_syslogs, shard='1');+
| +
| CREATE TABLE syslogs_auto_p1 PARTITION OF syslogs FOR VALUES WITH (modulus 4, remainder 1) WITH (engine=rocksdb, column_family=cf_syslogs, shard='2');+
| +
| CREATE TABLE syslogs_auto_p2 PARTITION OF syslogs FOR VALUES WITH (modulus 4, remainder 2) WITH (engine=rocksdb, column_family=cf_syslogs, shard='2');+
| +
| CREATE TABLE syslogs_auto_p3 PARTITION OF syslogs FOR VALUES WITH (modulus 4, remainder 3) WITH (engine=rocksdb, column_family=cf_syslogs, shard='1')
(1 行记录)
Unless explicitly specified, the __sys_auto_rowid__ column will not appear in the results of a SELECT, and it does not need to appear in an INSERT statement, although its field value can be explicitly set; in an UPDATE/DELETE statement, if you need to specify particular rows to update or delete, you can use the __sys_auto_rowid__ column to identify the rows, and you can also update this field as long as it does not conflict with the column values of other rows.
kunlundb=# insert into syslogs(msg) values('hello world!');
INSERT 0 1
kunlundb=# insert into syslogs(msg, rcv_ts) values('exciting things is about to happen', current_timestamp);
INSERT 0 1
kunlundb=# select*from syslogs;
msg | rcv_ts | level
------------------------------------+---------------------+-------
hello world! | |
exciting things is about to happen | 2026-03-29 11:39:51 |
(2 行记录)
kunlundb=# select __sys_auto_rowid__, *from syslogs;
__sys_auto_rowid__ | msg | rcv_ts | level
--------------------+------------------------------------+---------------------+-------
1 | hello world! | |
2 | exciting things is about to happen | 2026-03-29 11:39:51 |
(2 行记录)
kunlundb=# update syslogs set rcv_ts=current_timestamp where __sys_auto_rowid__ = 1;
kunlundb=# select __sys_auto_rowid__, *from syslogs;
__sys_auto_rowid__ | msg | rcv_ts | level
--------------------+------------------------------------+---------------------+-------
1 | hello world! | 2026-03-29 11:41:11 |
2 | exciting things is about to happen | 2026-03-29 11:39:51 |
(2 行记录)
You can set enable_auto_generate_pk to FALSE to disable this feature, but it is strongly not recommended to turn it off.
Custom partitioning method
If you are unsure how to partition a table, you can use the default fully automatic HASH partitioning mentioned above, which means no partition table setup or operation is required. If you want to explicitly place each partition of a table on a specific shard, you must use explicit partitioning. However, it is strongly not recommended to explicitly assign table partitions to specific shards. Typically, users' needs for explicitly arranging storage shards are met by KlustronDB's TABLE GROUP feature.
KlustronDB supports all three partitioning methods of PostgreSQL and their corresponding SQL syntax. In addition to the aforementioned HASH method, there are also RANGE and LIST methods. All three partitioning methods allow explicit specification of partition columns and partition parameters. The syntax is exactly the same as PostgreSQL, but KlustronDB-specific WITH options can be added to explicitly set the storage scheme, as shown in the example below.
Based on an in-depth understanding of the operation of application system business, an application system architect can accurately define the partitioning of key business data tables, which can optimize the performance of the application system and demonstrate the professionalism and value of the architect.
At the end of the partition table statement below, add the WITH option to specify the shard where the table partition is located, as well as the storage engine used.
1. Range Partition
When a partition column lookup method needs to support both equality lookups and range lookups, the Range partitioning method should be used. Another advantage of Range partitioning is that in some scenarios, such as partitioning by date, timestamp, or incrementing columns, as more data rows are added, the range of the partition column values also expands, allowing more partitions to be added as needed during the cluster's operation.
CREATE TABLE order_info (
id bigserial,
order_no varchar(32),
create_time timestamp NOT NULL,
amount decimal(10,2),
primary key(id, create_time)
) PARTITION BY RANGE (create_time); -- 按时间范围分区
-- 2. 创建子分区（按月分区）
-- 分区1：2025-01月数据
CREATE TABLE order_info_202501 PARTITION OF order_info
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01') with (shard=1);
-- 分区2：2025-02月数据
CREATE TABLE order_info_202502 PARTITION OF order_info
FOR VALUES FROM ('2025-02-01') TO ('2025-03-01') with (shard=2, engine='innodb');
kunlundb=# show create table order_info;
Table | Create Table
------------+----------------------------------------------------------------------------------------------------------------------------
order_info | CREATE TABLE order_info(
| id bigint DEFAULT "nextval"('order_info_id_seq'::regclass) NOT NULL,
| order_no character varying(32) COLLATE utf8mb4_0900_bin,
| create_time timestamp without time zone NOT NULL,
| amount numeric(10,2),
| CONSTRAINT order_info_pkey PRIMARY KEY (id, create_time)
| ) PARTITION BY RANGE (create_time);
| CREATE TABLE order_info_202501 PARTITION OF order_info FOR VALUES FROM ('2025-01-01 00:00:00') TO ('2025-02-01 00:00:00') WITH (engine=innodb, shard='1');
| CREATE TABLE order_info_202502 PARTITION OF order_info FOR VALUES FROM ('2025-02-01 00:00:00') TO ('2025-03-01 00:00:00') WITH (engine=innodb, shard='2');
(1 行记录)
Note, if the partition column is not used as the primary key column, then this partition column must be NOT NULL, because all partition columns of a table must appear in the primary key and unique indexes. If the primary key is automatically added by KlustronDB, then this primary key will also automatically follow this rule, meaning KlustronDB will automatically include the partition column in the primary key, as shown in the example below.
2. List Partition
When the number of different values in the partition column is small and only equality searches are needed, it is recommended to use List partitioning. List partitioning also has the advantage of adding partitions on demand later. For example, when using List partitioning based on columns such as the country, province, city, or district where the business is launched, as more businesses are opened, additional List partitions can be added as needed during cluster operation to store data from newly launched regions.
CREATE TABLE user_info (
name varchar(32),
region varchar(16) NOT NULL -- 按地区分区
) PARTITION BY LIST (region);
-- 2. 创建子分区
-- 华东地区
CREATE TABLE user_info_east PARTITION OF user_info FOR VALUES IN ('上海', '江苏', '浙江', '安徽');
-- 华南地区
CREATE TABLE user_info_south PARTITION OF user_info FOR VALUES IN ('广东', '广西', '福建');
-- 默认分区（可选，存不匹配的数据）
CREATE TABLE user_info_default PARTITION OF user_info DEFAULT;
kunlundb=# show create table user_info;
Table | Create Table
-----------+------------------------------------------------------------------------------------------------------------------------------------
user_info | CREATE TABLE user_info ( +
| name character varying(32) COLLATE utf8mb4_0900_bin, +
| region character varying(16) COLLATE utf8mb4_0900_bin NOT NULL, +
| __sys_auto_rowid__ bigint DEFAULT "nextval"('user_info___sys_auto_rowid___seq'::regclass) NOT NULL, +
| CONSTRAINT user_info_pkey PRIMARY KEY (__sys_auto_rowid__, region) +
| ) PARTITION BY LIST (region); +
| +
| CREATE TABLE user_info_east PARTITION OF user_info FOR VALUES IN ('上海', '江苏', '浙江', '安徽') WITH (engine=innodb, shard='1');+
| +
| CREATE TABLE user_info_south PARTITION OF user_info FOR VALUES IN ('广东', '广西', '福建') WITH (engine=rocksdb, shard='2'); +
| +
| CREATE TABLE user_info_default PARTITION OF user_info DEFAULT WITH (engine=innodb, shard='2')
Note that the two partitions above, user_info_east and user_info_south, are set to use different storage engines. KlustronDB supports this kind of flexibility. This can be very useful in some scenarios, such as mixed queries of hot and cold data. KlustronDB supports read-only queries of cold data (historical data that is no longer updated) in parquet and ORC formats. If 冷热数据混合查询 is needed, that is, if a single SELECT statement queries both cold and hot data to produce query results, then KlustronDB's unique Parquet and ORC storage engines can be used to store cold data for high compression storage, and those shards storing cold data can use low-spec, low-cost servers; meanwhile, hot data still uses InnoDB or RocksDB engines stored on high-performance servers, allowing support for historical data queries while keeping costs manageable.
3. Hash Partition
When the number of different values in the partition column is large and only equality lookups are needed, it is recommended to use hash partitioning.
KlustronDB also inherits PostgreSQL's Hash partitioning syntax, as shown in the example below. This syntax is more cumbersome and complex than KlustronDB's proprietary automatic Hash partitioning syntax, but it does not have any unique advantages, so its use is not recommended. It is recommended to directly use KlustronDB's fully automatic Hash partitioning and customizable automatic Hash partitioning syntax.
-- PostgreSQL的Hash分区语法示例。该语法比KlustronDB的全自动/可定制的两种Hash分区语法繁琐，因此建议使用KlustronDB的语法。
CREATE TABLE product (
id bigint PRIMARY KEY,
prod_name varchar(64),
price decimal(10,2)
) PARTITION BY HASH (id); -- 按 id 哈希分散
-- 2. 创建固定数量的哈希分区（必须手动指定分区数）
CREATE TABLE product_p1 PARTITION OF product FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE product_p2 PARTITION OF product FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE product_p3 PARTITION OF product FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE product_p4 PARTITION OF product FOR VALUES WITH (MODULUS 4, REMAINDER 3);
Transaction processing
KlustronDB's transaction processing capabilities can achieve the same ACID guarantees as mainstream centralized relational databases, and the usage of transactions is the same as in MySQL, slightly different from PostgreSQL, and more enriched.
DDL statements and transactions
First of all, like MySQL, DDL statements cannot appear in explicit transactions and are executed as a transaction on their own. PostgreSQL users should note that a DDL statement appearing in an explicit transaction will implicitly commit that transaction before execution and then execute the DDL in a separate transaction. Therefore, in the following example, a PostgreSQL transaction executed in KlustronDB is split into three transactions.
BEGIN
INSERT INTO ...
SELECT * FROM ...
CREATE TABLE ... -- 此语句提交当前事务Txn1 然后在独立的事务Txn2 中运行并提交
INSERT INTO ... -- autocommit=off 时，新的事务Txn3隐式启动
SELECT * FROM ...
COMMIT --- 第 Txn3个事务提交
Autocommit and implicit transaction start and commit
Similar to MySQL, KlustronDB also has the autocommit variable, which allows switching whether to enable auto-commit; it also supports implicit transaction start and implicit commit.
After executing set autocommit=true in a database connection, each CRUD SQL statement becomes its own transaction (i.e., a statement transaction). When executing the statement, the KlustronDB system automatically starts a transaction internally, and after execution, it automatically commits the transaction. Therefore, when executing this setting statement, KlustronDB will also automatically commit any active transaction currently running in the connection (if any);
After executing set autocommit=false, if there is no active transaction in the current database connection, KlustronDB will implicitly start a transaction the first time a CRUD statement is executed in this connection, and it will execute subsequent received CRUD SQL statements within it until a statement that commits this transaction is encountered (there are multiple types, see the next section), then the transaction is committed.
After set autocommit=true is executed, the subsequent SQL statements for insert, delete, update, and query received in the current connection will be automatically committed.
PostgreSQL does not have autocommit or implicit transaction start and commit; all SQL statements for insert, delete, update, and query that are not in an explicit transaction are automatically committed. Therefore, PostgreSQL users can gain more flexibility in handling transactions when using KlustronDB.
Statement that implicitly commits the currently running transaction
Similar to MySQL, executing the following statement in a database connection will commit any active transaction (if any).
- DDL statement
- set autocommti=true
- commit
Error Handling and Implicit Rollback
When a statement execution fails, PostgreSQL automatically rolls back the transaction it belongs to. Similar to PostgreSQL, KlustronDB also handles it this way by default. This is different from MySQL. MySQL allows the client (application side) to decide whether to roll back the current transaction in this case. To support this behavior of MySQL, a enable_stmt_subxact variable was added in KlustronDB. Setting it to true allows MySQL-like behavior, that is, after a statement execution fails, the current transaction is retained, and the application side decides whether to roll back this transaction. The application can choose to ignore the error and continue running, eventually committing the transaction, or it can decide to roll back the transaction immediately or after executing subsequent statements.
Global Multi-Version Concurrency Control (Global MVCC)
When a SELECT statement queries data across multiple shards, in order to maintain the consistency of the query results and avoid reading changes from some branches of a distributed transaction that is being committed while missing changes from other branches, KlustronDB supports Global MVCC, which realizes a globally consistent snapshot across a KlustronDB cluster. This feature comes with a slight performance cost, so it is not enabled by default. When installing a KlustronDB cluster, you can set whether to enable Global MVCC. After enabling it, you can see that the configuration variable enable_global_mvcc is TRUE.
KlustronDB Cluster
The following statement queries which shards exist in the KlustronDB cluster where the current Kluscomp instance is located, which nodes are in each shard, and their detailed information. Users are prohibited from modifying any such metadata information, otherwise the system will not function properly.
kunlundb=# select*from pg_shard;
name | id | master_node_id | num_nodes | space_volumn | num_tablets | db_cluster_id | when_created
---------+----+----------------+-----------+--------------+-------------+---------------+-------------------------------
shard_2 | 1 | 2 | 3 | 311296 | 76 | 1 | 2026-02-26 13:02:43.465621+08
shard_1 | 2 | 6 | 3 | 258048 | 63 | 1 | 2026-02-26 13:02:43.465621+08
(2 行记录)
kunlundb=# select*from pg_shard_node;
id | port | shard_id | svr_node_id | ro_weight | ping | latency | user_name | hostaddr | passwd | when_created | extra
----+-------+----------+-------------+-----------+------+---------+-----------+---------------+---------+-------------------------------+-------
2 | 22067 | 1 | 0 | 10 | 0 | 0 | pgx | 192.168.0.136 | *** | 2026-02-26 13:02:43.465621+08 |
5 | 22064 | 2 | 0 | 10 | 0 | 1500519 | pgx | 192.168.0.132 | *** | 2026-02-26 13:02:43.465621+08 |
6 | 22064 | 2 | 0 | 10 | 0 | 0 | pgx | 192.168.0.136 | *** | 2026-02-26 13:02:43.465621+08 |
1 | 22067 | 1 | 0 | 10 | 0 | 1303656 | pgx | 192.168.0.132 | *** | 2026-02-26 13:02:43.465621+08 |
4 | 22064 | 2 | 0 | 10 | 0 | 3 | pgx | 192.168.0.125 | *** | 2026-02-26 13:02:43.465621+08 |
3 | 22067 | 1 | 0 | 10 | 0 | 109135 | pgx | 192.168.0.125 | *** | 2026-02-26 13:02:43.465621+08 |
(6 行记录)
The following SQL statement queries the current Kluscomp instance as well as the global metadata information of the Metashard cluster.
kunlundb=# select*from pg_cluster_meta;
comp_node_id | cluster_id | cluster_master_id | ha_mode | cluster_name | comp_node_name | meta_ha_mode
--------------+------------+-------------------+---------+---------------------------+----------------+--------------
1 | 1 | 2 | 2 | cluster_1772081642_000001 | comp1 | 2
(1 行记录)
kunlundb=# select*from pg_cluster_meta_nodes;
server_id | cluster_id | is_master | port | user_name | hostaddr | passwd
-----------+------------+-----------+-------+-----------+---------------+---------
1 | 1 | f | 22061 | pgx | 192.168.0.125 | ***
2 | 1 | t | 22061 | pgx | 192.168.0.132 | ***
3 | 1 | f | 22061 | pgx | 192.168.0.136 | ***
(3 行记录)
The actual situation in the storage shard
Next, let's take a look at how data is stored in each storage shard. It should be emphasized that users are prohibited from directly connecting to the nodes of a storage shard to read or write data or execute DDL to create, delete, or modify KlustronDB tables; otherwise, it will cause KlustronDB to operate abnormally, because the effects of statements executed in the shard will not be visible in KlustronDB's Kluscomp instances.
- databases
Each schema under a KlustronDB database corresponds to a database in the storage shard. When connected to the Klustore instance, kunlundb_$$_ecom and kunlundb_$$_public correspond to two schemas, ecom and public, of the database kunlundb. These databases exist in all storage shards of the cluster.
mysql> show databases;
+---------------------+
| Database |
+---------------------+
| information_schema |
| kunlun_sysdb |
| kunlundb_$$_ecom |
| kunlundb_$$_public |
| mysql |
| performance_schema |
| postgres_$$_public |
| postgres_$$_tpcc |
| postgres_$$_tpcc2 |
| sys |
+---------------------+
10 rows in set (0.08 sec)
- tables
Then we enter kunlundb_$$_ecom, where we can see that part of it is table partitions, and the other part is in another shard, which will not be shown here. It can be seen that each table partition exists as a single table in the shard, and its table definition perfectly corresponds to the definition of the corresponding main table in the KlustronDB cluster.
mysql> use kunlundb_$$_ecom;
Database changed
mysql> show tables;
+----------------------------+
| Tables_in_kunlundb_$$_ecom |
+----------------------------+
| order_info_202502 |
| orders_auto_p3 |
| products_auto_p0 |
| products_auto_p2 |
| syslogs_auto_p1 |
| user_info_default |
| user_info_south |
+----------------------------+
7 rows in set (0.04 sec)
mysql> show create table order_info_202502;
+--------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------------+------------------------------------------------------------------+
| order_info_202502 | CREATE TABLE `order_info_202502` (
`id` bigint NOT NULL,
`order_no` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin DEFAULT NULL,
`create_time` datetime NOT NULL,
`amount` decimal(10,2) DEFAULT NULL,
PRIMARY KEY (`id`,`create_time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin |
+---------------------------------------------------------------------------------------+
1 row in set (0.04 sec)
mysql> show create table user_info_south;
+-----------------+----------------------------------------------------------------------------+
| Table | Create Table |
+-----------------+----------------------------------------------------------------------------+
| user_info_south | CREATE TABLE `user_info_south` (
`name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin DEFAULT NULL,
`region` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NOT NULL,
`__sys_auto_rowid__` bigint NOT NULL,
PRIMARY KEY (`__sys_auto_rowid__`,`region`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin |
+-----------------+-----------------------------------------------------------------------------+
1 row in set (0.06 sec)
Summary
At this point, you have learned the basic methods of using KlustronDB and gained a preliminary understanding of it. It can be seen that KlustronDB maintains a high degree of compatibility with PostgreSQL and MySQL, is easy to use, and also has its unique horizontal scaling capabilities. KlustronDB also has many more powerful and useful features waiting for you to discover and use. We hope KlustronDB can create value for you; and we look forward to your feedback, which will help us continuously improve the quality of KlustronDB.
Why should we iterate from old system architectures to KlustronDB
1. Major flaws in middleware for database and table partitioning
The technical challenges of massive data storage management and analysis utilization include:
- The limited computing and storage resources of a single server are in great conflict with the continuously growing scale of data management and access load;
- Various hardware and software failures can cause computer server node failures and network failures, making it a great challenge and difficulty to maintain long-term reliability and continuity of data read and write services, as well as data durability and consistency.
- Under data read and write loads with elastic fluctuations, maintaining continuously stable high throughput and low latency is extremely technically challenging, but it is crucial for a smooth terminal user experience, and therefore it must be achieved.
- The compatibility of the application ecosystem has great value, and applications that are compatible with existing software using MySQL and PostgreSQL, two world-class databases, have enormous value.
- It is necessary to use multiple databases to manage relational data, JSON, GIS, text, and vector data, which creates a heavy operational burden and makes application system development complex.
To address Type 1 challenges, MySQL users previously relied on database sharding middleware or implemented sharding logic within the application system. These “crude methods” have a series of serious shortcomings and cannot cope with Type 2, 3, and 4 challenges. Using these 'crude methods,' users essentially need to implement data management functions case by case in the business system, including even transaction processing and fault tolerance functions. For the vast majority of application software development teams, this is an impossible task. The reliability, stability, and maintainability of the application software system will face serious problems, development difficulty greatly increases, development cycles become uncontrollable, project delay risks increase significantly, and manpower costs required for the project increase substantially. At the same time, automatic elastic scaling cannot be achieved, as the data splitting logic is deeply coupled with the application software.
Using KlustronDB can completely, thoroughly, and reliably address all of the aforementioned challenges! KlustronDB fully encapsulates the complexities of data sharding storage and querying, transaction processing, globally consistent views, disaster recovery, fault tolerance, and more, so that application software developers only need to implement business logic in exactly the same way as with a centralized database. Regardless of how much data needs to be stored and managed or the level of online access load, users (DBAs, application software developers, and architects) can fully delegate data management tasks to KlustronDB. DBAs only need to add or remove database server hardware as needed, and KlustronDB will automatically perform elastic scaling to handle these fluctuating loads. This greatly improves the work efficiency of application software programmers, significantly reduces the workload and technical difficulty of application system development, ensures the quality, stability, and reliability of application software systems, and greatly lowers project development cycles and costs. At the same time, KlustronDB supports importing full and incremental data from all common relational databases, making it convenient for users to migrate to KlustronDB or from KlustronDB to other databases at any time.
KlustronDB allows programmers to focus on developing and implementing application logic and functional requirements, without having to take on data management functions in the application system at all, greatly improving their work efficiency as well as the reliability and user experience of their business systems and products, reducing IT system hardware, software, and personnel costs for the company, and ensuring that the launch time of its online business systems is controllable and predictable.
Next, let's specifically analyze the issues of using application-layer database sharding or using database sharding middleware. The problems with data row routing middleware such as mysql_proxy, mysql_router, mycat, etc., are:
1.1 They do not support complete distributed query processing
Legal SQL statements sent by user programs to these middlewares, as long as they involve some advanced SQL features, such as multi-table joins, subqueries, CTEs, window functions, aggregation, etc., are usually unable to be processed by such middlewares; this leads to a situation where a large number of tools in the SQL ecosystem, such as low-code tools, ORM middleware (for example, Hibernate), machine learning algorithms, and various data analysis tools and algorithms, cannot interact or collaborate with these middlewares.
If database sharding at the application layer is used, application software programmers need to query data fragments from the target data storage shards in the business code, and then assemble the final result within the business code. These operations constitute a query processing and execution in the application layer for a specific SQL statement. If the 'query statement' needs to be modified, a large amount of application code must be changed, making the maintenance cost of the application software very high.
This task could originally have been done by directly sending SQL statements to the distributed database to get results. However, without a distributed database, it is only possible to implement a query processing function for each specific SQL statement, so the workload is naturally very huge. In particular, such query processing code may need to be repeatedly modified due to changes and iterations in business logic requirements, making this development workload much larger and more complex than directly modifying SQL statements.
1.2 They do not support distributed transaction processing for reliable disaster recovery
Many application programmers do not realize what business risks are involved if distributed transactions are not executed with two-phase commit, remaining in an 'unconscious' state; a few application programmers are aware of this potential risk but are unable to solve it, so they just get by.
A few programmers recognize the problem and are able to solve it, but they can only solve it on a case-by-case basis. For example, to achieve reliable transfer functionality, it is necessary to design a set of technology to implement disaster recovery capability for the transfer scenario at the business layer. Encountering other scenarios requires redesigning and implementing a new set of algorithms.
This has led to a sharp increase in the technical threshold and workload for application development, with significant risks and uncertainties in product reliability and stability; there is a high risk of project delays and high development costs. Although some middleware uses MySQL's XA feature for two-phase commits, it cannot reliably ensure the consistency of user data (ACID properties) in the event of node crashes, network disconnections, timeouts, and other exceptions.
A common problem mentioned above is that application software developers need to know exactly which storage shard each of their tables is stored in, in order to implement correct data management and query functions. This creates a further binding between data management and business logic, which goes against the original intention of database systems—to fully encapsulate data management and allow application software developers to completely ignore any details of data storage management.
1.3 They cannot achieve automatic horizontal scaling.
Expanding capacity requires the DBA to complete it manually and requires service to be paused for a period of time (for example, several hours). Service suspension will seriously affect business continuity and user experience.
2. Application of Database Sharding in the Stone Age
There is another, more primitive method in the industry to address the problem of excessive data storage scale and access load — application-layer data partitioning. This approach is considered more primitive because, in addition to all the aforementioned 'headache-inducing' problems, it also has a series of serious issues, to the extent that we can say the products and services of these companies are still in the Stone Age. Surprisingly, there are still quite a few companies like this in the Stone Age.
Unique issues of application-layer sharding include:
2.1 Hardcoding of Subtable Logic
In this way, similar functionality would need to be implemented for each table, which results in a high development burden and complexity. Especially if multiple applications or web services need to use the same set of data tables (which is a common situation), it is also necessary to ensure that all these programs follow the same sharding rules for each table. The development workload and complexity would increase exponentially, not just linearly multiplied.
Even if you are smarter and use configuration files like the middleware mentioned above with big heads, there are still problems — you need to implement the sharding logic, which significantly increases the workload of business development. And in the end, you have only created a mediocre middleware. It is called mediocre because only your company/team uses it, and it may only be applicable to your specific business scenarios. This further tightly binds and depends data management on application logic, which is a very poor system design.
2.2 Horizontal scaling is even more difficult than a 'headache'
In the case of hard-coded database sharding logic, elastic scaling is almost impossible to achieve, because you need to modify the business code to implement new data partitioning rules in order to scale, which is simply a nightmare for developers and DBAs.
Therefore, we decided to develop a truly distributed database product to completely rescue the users mentioned above who are 'overwhelmed' and those who are 'stuck in the Stone Age,' bringing them into the current technological era so they can experience the charm of cutting-edge modern technology.
From now on, they will no longer have to rack their brains designing and implementing distributed data management and query programs; they can simply send SQL statements to initiate and commit distributed transactions, as well as execute distributed queries and directly obtain the query results.
In this way, data management is truly separated from application software, abstracting data management from application logic—this was precisely the original intention of the pioneers of database theory and technology 50 years ago, and the lesson they bought with countless person-months and dollars:
Use an independent database system for data management, separating application software development from general data management logic, to achieve maximum software reuse and simplify application development, greatly improve developers' work efficiency and the reliability of their business logic and products, lower the technical threshold of users' business systems while greatly enhancing their reliability, reduce company development costs, and ensure that the launch time of online business systems is controllable and predictable.
3. The Integration Capability of KlustronDB
KlustronDB can simultaneously manage relational data, JSON, vectors, spatial (GIS), and text data, allowing all types of data to be managed within a single database system. This avoids cross-database transactions and queries that occur when using multiple database systems simultaneously, greatly simplifying application system architecture and implementation. Otherwise, application architectures that span multiple databases would suffer from significant reliability, stability, complexity, and cost drawbacks similar to the sharded database architectures mentioned above, resulting in long-term risks and costs for the maintenance of the application system.
KlustronDB supports the wide range of plugins available in the PostgreSQL community, leveraging and enhancing their capabilities. Among these plugins, the KlustronDB team has extended the PostGIS and PGVector plugins, while other plugins can be compiled and built directly using the community version source code and KlustronDB header files for use. KlustronDB's PostGIS and PGVector plugins provide GIS and vector data management capabilities, making it a distributed PostGIS and PGVector. KlustronDB can manage and query JSON data and provide text data storage and retrieval, especially combined text and vector search capabilities. Users can query target data in a single SQL statement using relational data predicate conditions, JSON content fragments, spatial location relationships, vector distances, and text keywords. For more details, see Chapter 5.
Below, read the main content of this chapter to gain a detailed understanding of KlustronDB's architecture, basic concepts, and technical advantages, making a simple preparation for using KlustronDB.
1. KlustronDB System Architecture
2. KlustronDB Core Capabilities
3. KlustronDB Technical Advantages
5. Multi-Data Model Integration