You can improve the data storage and computing efficiency by specifying an appropriate table group and an appropriate shard count. This topic describes how to specify the table group and shard count in Hologres.

Query the metadata of table groups

  1. Query the default table group.
    SELECT * FROM hologres.hg_table_group_properties
    WHERE tablegroup_name IN (
      SELECT tablegroup_name FROM hologres.hg_table_group_properties
      WHERE property_key = 'is_default_tg' AND property_value = '1'
    );
    Result:
    Note In the returned result, the is_default_tg property indicates whether the table group is the default table group, and the shard_count property indicates the shard count of the table group.
    tablegroup_name |   property_key   | property_value
    -----------------+------------------+----------------
     test_tg_default | tg_version       | 1
     test_tg_default | table_num        | 1               
     test_tg_default | is_default_tg    | 1               
     test_tg_default | shard_count      | 3
     test_tg_default | replica_count    | 1
     test_tg_default | created_manually | 0
    (6 rows)
  2. Query table groups in the current database.
    SELECT tablegroup_name
    FROM hologres.hg_table_group_properties GROUP BY tablegroup_name;
    Result:
    tablegroup_name
    -----------------
     test_tg_default
    (1 row)
  3. Query the shard count of a table group.
    SELECT property_value AS shard_count
    FROM hologres.hg_table_group_properties
    WHERE property_key = 'shard_count' AND tablegroup_name ='<tg_name>';
    Result:
    shard_count
    -------------
     3
    (1 row)
  4. Query tables in a table group.
    SELECT table_namespace AS schema_name, table_name
    FROM hologres.hg_table_properties
    WHERE property_key = 'table_group' AND property_value = '<tg_name>';
    Result:
    schema_name | table_name
    -------------+------------
     public      | a
    (1 row)
  5. Query the table group to which a table belongs.
    SELECT property_value AS table_group_name
    FROM hologres.hg_table_properties
    WHERE property_key = 'table_group' AND table_name = '<table_name>';
    Result:
    table_group_name
    ------------------
     test_tg_default
    (1 row)

Create a table group

If you have new business or you want to change the shard count of a table group, you can create a table group. The following statement can be used:
Note
  • After you create a new table group, the original table and data belong to the original table group and are not automatically migrated to the new table group.
  • The original table group becomes ineffective only after the original table and data are migrated to the new table group or deleted.
CALL HG_CREATE_TABLE_GROUP ('<new_tg_name>', <shard_count>);
The following table describes the parameters.
Parameter Type Description
new_tg_name Text The name of the table group to be created.
shard_count INT4 The shard count for the table group.
Example:
-- Create a table group that is named tg_8 and has eight shards.
CALL HG_CREATE_TABLE_GROUP ('tg_8', 8);

Change the default table group

A default table group with a specific number of shards is created for each database in a Hologres instance. For more information, see Instance types. If the database contains multiple table groups, and you want to change the default table group to store newly created tables in the new table group, you can execute the following statement as required.

Note You can execute this statement to change the default table group only in Hologres V0.9 and later. If the version of your Hologres instance is earlier than V0.9, update the instance.
CALL HG_UPDATE_DATABASE_PROPERTY ('default_table_group', '<tg_name>');
The following table describes the parameter.
Parameter Type Description
tg_name TEXT The name of the default table group. After setting, its shard count is the number of shards of the specified table group.
Example:
-- In Hologres V0.9 or later, set a new table group as the default table group. By default, subsequently created tables are stored in this table group.
CALL HG_UPDATE_DATABASE_PROPERTY ('default_table_group', 'tg_8');

Create a table and add it to a specified table group

You can execute the following statement to create a table and add it to a specified table group:
BEGIN;
CREATE TABLE table_name (
    col type,
  ....
);
CALL SET_TABLE_PROPERTY('table_name', 'table_group', '<tg_name>');
COMMIT;
The following table describes the parameters.
Parameter Type Description
table_name TEXT The name of the table to be created.
tg_name TEXT The name of the default table group. After setting, its shard count is the number of shards of the specified table group.
Example:
-- Create a table named tbl1 and add it to the table group named tg_8.
BEGIN;
CREATE TABLE tbl1 (
    col1 text
);
CALL SET_TABLE_PROPERTY('tbl1', 'table_group', 'tg_8');
COMMIT;

Use the resharding feature to rebalance tables and data

In Hologres, shards are used to improve the concurrency of data processing. You can set the number of shards to an appropriate value to improve query or write performance. In most scenarios, the default number of shards for a Hologres instance can meet your requirements. You do not need to change the number of shards.

After a Hologres instance is scaled up, such as from 32 CPU cores to 128 CPU cores, the number of shards does not change for existing databases in the Hologres instance. If you want to improve the performance of a database, we recommend that you increase the number of shards for the database. If you create a database in the Hologres instance, the number of shards is the default number for the current instance type. For more information about instance types and shards, see Instance types.

After a Hologres instance is scaled up or down, the number of shards does not change for existing databases. You can execute statements to change the number of shards and import data again. Hologres provides the resharding feature. After you change the number of shards, you can use the resharding feature to rebalance the existing tables and data among shards, without the need to create tables and import data again. This simplifies the resharding procedure and achieves optimal performance.

  • Limits
    • The resharding feature is supported only in Hologres V0.10 and later. You can check the version of your Hologres instance on the instance details page in the Hologres console. If the version of your instance is earlier than V0.10, submit a ticket to update your instance.
    • Resharding is performed on a single table at a time. When you perform resharding on a table, you must stop writing data to the table by using Flink or Data Integration. You can still query data in the table.
    • Resharding consumes a number of resources. We recommend that you use the resharding feature during off-peak hours.
    • If binary logging is enabled for a table, you must disable binary logging before you perform resharding on the table. You can re-enable binary logging after resharding is complete. For more information, see Subscribe to Hologres binlogs.
  • Syntax
    You can execute the following statement to migrate some business tables to a new table group:
    Note
    • Before you migrate data to a table group, you must create one. For more information about how to create a table group, see Create a table group.
    • When you migrate tables, you must stop writing data to the tables. You can still query data in these tables.
    • After the tables in the original table group are migrated, the original table group is automatically deleted. If multiple table groups are created based on your business requirements, we recommend that you set the number of shards to an appropriate value for each table group.
    • When you migrate partitioned tables, you need to migrate only parent partitioned tables.
    -- Syntax in Hologres V1.1 and later
    CALL HG_MOVE_TABLE_TO_TABLE_GROUP('<table_name>','<new_table_group_name>');
    
    -- Syntax in Hologres V0.10
    CALL HG_UPDATE_TABLE_SHARD_COUNT('<table_name>','<new_table_group_name>');
  • Parameters
    Parameter Description Example
    table_name The name of the table that you want to migrate. You can execute the statement to migrate a single table at a time. If you want to migrate multiple tables, execute the statement multiple times. If the table that you want to migrate is a partitioned table, you need to migrate only the parent partitioned table. new_table
    table_group_name The name of the table group to which the table is to be migrated. new_tg

Delete a table group

You can execute the following statement to delete an empty table group. If a table exists in a table group, you cannot delete the table group.
CALL HG_DROP_TABLE_GROUP('<tg_name>');
Example:
-- Delete the table group named tg_8.
CALL HG_DROP_TABLE_GROUP('tg_8');

Best practices

In most cases, we recommend that you do not create a table group or change the shard count of the table group. If you need to create table groups based on your business requirements, you can make plans on the table groups based on the best practices. For more information, see Best practices for specifying table groups.