You can improve the data storage and computing efficiency by using an appropriate table group and an appropriate shard count. This topic describes how to manage a table group and specify a 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 an existing table group, you can create a table group by using the following syntax.
Note
  • After a new table group is generated, the original tables and data still belong to the original table group and are not automatically migrated to the new table group.
  • The original table group becomes ineffective only after its tables 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 subsequently created tables in the new default table group, you can execute the following statement.

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 new default table group. The shard count of the table group determines the number of shards in the table group.
Example:
-- In Hologres V0.9 and later, you can change the default table group. After the setting takes effect, subsequently created tables are stored in the specified 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 statements 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. The shard count of the table group determines the number of shards in the 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 migrate tables to a new table group

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

After a Hologres instance is scaled up, such as from 32 CPU cores to 128 CPU cores, the shard count 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 shard count for the database. If you create a database in the Hologres instance, the default shard count for the current instance type applies. For more information about instance types and shards, see Instance types.

After a Hologres instance is scaled up or down, the shard count does not change for existing databases. You can execute statements to change the shard count and import data again. Hologres provides the resharding feature. After you change the shard count, 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 specific 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.
    • You cannot perform resharding on a table if the table contains an auto-increment field of the SERIAL data type or the table is created with the DEFAULT constraint. If the table contains an auto-increment field of the SERIAL data type, an error occurs during resharding. If the table is created with the DEFAULT constraint, the DEFAULT constraint becomes ineffective after resharding.
    • You cannot perform resharding on a table if the table depends on an object, such as a view. You must remove the relevant dependencies before you perform resharding.
  • Syntax
    You can execute one of the following statements to migrate some business tables to a new table group.
    Note
    • You must create a table group before you can migrate tables to the table group. 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 all tables in the original table group are migrated, the original table group is automatically deleted. If you need to create multiple table groups based on your business requirements, we recommend that you set the shard count 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 one of the preceding statements 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.

Troubleshooting related to resharding

Resharding is a complex process that involves multiple steps, such as the creation of temporary tables, changes in the Read Only state of source tables, data writes to destination tables, changes in table names, and recording of the synchronization status. If one or more steps encounter exceptions and the system enters an unknown state, you can use the following solution for troubleshooting.

If the internal error: Get rundown is not allowed in recovering state error message is returned, the table that is being updated is in the Read Only state. In this case, you cannot perform an insert, update, or delete operation on the table unless the table is not in the Read Only state.
  1. Execute the following statement to query the table that is in the Read Only table:
    select * from hologres.hg_table_properties where property_key ='readonly' and property_value='true';
  2. Execute the following statement to disable Read Only for the table:
    call set_table_property('<table_name>','readonly','false');
    table_name specifies the name of the table for which you want to disable Read Only.