TABLEGROUP Functionality and Usage Guide
TABLEGROUP Functionality and Usage Guide
Why is TABLEGROUP needed::
In practice, some tables are frequently involved in table joins or are frequently updated within the same transaction. To optimize query performance and transaction processing, Klustron enables users to consistently place these tables within the same shard. This results in better query performance and the ability to avoid two-phase commits for updates within the same transaction. Klustron achieves this through the TABLEGROUP functionality, allowing users to group related tables together. This enables pushing table joins within the group down to storage shards, enhancing performance. Tables within a TABLEGROUP cannot be moved individually to another shard; if migration is required, the entire tablegroup needs to be moved. Multiple table groups (clusters) can exist in the user's application system, along with tables not associated with any group.
Klustron provides comprehensive syntax to create and manage TABLEGROUPs.
CREATETABLEGROUP — You can specify the name of the table group, its owner, and the shard it resides in.
You can specify the name of the table group, its owner, and the shard it resides in. If no shard is specified, the computing node will automatically assign one
Outline:
{{{
CREATETABLEGROUP name
[ OWNER { new_owner | CURRENT_USER | SESSION_USER } ]
[ WITH (SHARD = shardid) ]
}}}
View:
-- The system table pg_tablegroup displays all table groups.
select * From pg_tablegroup ;
-- Access the view information_schema.tablegroup to retrieve all bound tables within a group.
select * from information_schema.tablegroup;
-- The "show create table" command displays the associated partition.
show create table t1;
Examples:
Binding Table Groups:
{{{
-- Create a table group
CREATETABLEGROUP tg1 WITH (SHARD=1);
-- When creating a table, use the WITH keyword to specify the table group
CREATE TABLE t1(a int) WITH(TABLEGROUP=tg1);
-- After creating a table, use ALTER to bind it to a table group
create table t2(a int) with (shard=1);
ALTER TABLE t2 SET (TABLEGROUP=tg1);
-- If the shard attribute of the table conflicts with the shard attribute of the table group, an error is thrown; only non-partitioned tables can be bound to a table group.
create table t3(a int) with(shard=2, tablegroup=tg1);
ERROR: Specified shard not matched with the tablegroup.
-- A table belongs to only one group, and multiple specifications of the group will be based on the last one specified.
show create table t1;
CREATETABLEGROUP tg2 WITH (SHARD=1);
ALTER TABLE t1 SET (TABLEGROUP=tg2);
show create table t1;
}}}
Modifying Shards:
{{{
-- Modify the shard where the table group resides (used to modify the metadata of the table group after moving the table group);
-- The metadata of all tables belonging to this table group will also be synchronized and modified.
select * From pg_tablegroup ;
ALTER TABLEGROUP tg2 SET(SHARD=2);
select * From pg_tablegroup ;
show create table t1;
}}}
Unbinding:
{{{
-- Remove a table from a table group by resetting the table's TABLEGROUP property
ALTER TABLE t1 RESET(TABLEGROUP);
show create table t1;
}}}
Deleting:
{{{
-- Only the owner can delete a table group;
-- All dependent tables will automatically be unbound from the table group, not deleted.
ALTER TABLE t2 SET (TABLEGROUP=tg1);
select * From pg_tablegroup ;
show create table t2;
DROP TABLEGROUP tg1;
show create table t2;
}}}
Rollback
Supports remap_shardid type DDL events, used to adjust the shard attributes of tables and table groups during cluster rollback.
pg_dump Export
