Hologres:User guide for table groups and shard counts

Recommendations for setting table groups and shard counts

Permissions

• To create a table group, modify the default table group, delete a table group, or migrate a table to another table group (resharding), you must have Superuser permissions for the instance. A Superuser can run the following statement to grant Superuser permissions to a regular user.

  -- Replace "Alibaba Cloud account ID" with the actual username. If you use a RAM user, add the prefix "p4_" to the account ID.
  ALTER USER "Alibaba Cloud account ID" SUPERUSER; 

• To place a new table into a specified table group, the user account must have permission to create tables.

Get table group metadata

1. View 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 result, is_default_tg indicates whether the table group is the default, and shard_count 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. View the 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. View 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. View the 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. View 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

CALL HG_CREATE_TABLE_GROUP ('<new_tg_name>', <shard_count>);

-- Create a new table group named tg_8 with a shard count of 8.
CALL HG_CREATE_TABLE_GROUP ('tg_8', 8);

Modify the default table group

After you create a database, a default table group with a default shard count is created in the instance. For more information, see Instance management. If a database has multiple table groups and you want to change the default one, you can use the following statement. After the change, new tables are stored in the new default table group.

CALL HG_UPDATE_DATABASE_PROPERTY ('default_table_group', '<tg_name>');

Description:

Example:

-- Set the new table group as the default. New tables will use this table group by default (for V0.9 and later).
CALL HG_UPDATE_DATABASE_PROPERTY ('default_table_group', 'tg_8');

Place a new table into a specified table group

BEGIN;
CREATE TABLE table_name (
    col type,
  ....
);
CALL SET_TABLE_PROPERTY('table_name', 'table_group', '<tg_name>');
COMMIT;

-- Create a table named tbl1 and associate it with the table group named tg_8.
BEGIN;
CREATE TABLE tbl1 (
    col1 text
);
CALL SET_TABLE_PROPERTY('tbl1', 'table_group', 'tg_8');
COMMIT;

Migrate a table to a new table group (resharding)

In Hologres, shards improve data processing concurrency. A proper shard count helps improve query and write performance. In most cases, the default shard count for a Hologres instance is sufficient, and you do not need to modify it manually.

After an instance is scaled out, for example, from 32 cores to 128 cores, the shard count of existing databases does not change by default. We recommend that you increase the shard count for the databases to achieve better performance. If you create a new database in the instance, the default shard count for the current instance type is used. For more information about instance types and shards, see Instance management.

After an instance is scaled out or scaled in, the shard count of its existing databases does not automatically increase or decrease. You must use statements to set the shard count and then re-import the data.

• For standard tables and physical partitioned tables, you can use either the `REBUILD` syntax or a built-in stored procedure.

• For logical partitioned tables, we recommend that you use the `REBUILD` syntax. This syntax can automatically perform sequential execution by partition.

• Limits

  • The resharding feature is supported only in Hologres V0.10 and later. You can view your Hologres instance version on the instance details page in the Hologres console. If your instance version is earlier than V0.10, see Common upgrade preparation errors or join the Hologres DingTalk group to provide feedback. For more information, see Obtain online support for Hologres.

  • Currently, resharding is performed at the table level. When you reshard a table, you must stop writing data to it from sources such as Flink and Data Integration. Queries on the table are not affected. In Hologres V1.1 and later, you can run set table readonly to enable automatic failover for real-time write tasks.

  • Resharding consumes CPU resources and increases storage usage during the process. We recommend that you perform resharding during off-peak hours.

  • If binary logging is enabled for a table, disable it before resharding. Enable binary logging again after resharding is complete. For more information, see Subscribe to Hologres binary logs.

  • Resharding is not supported for tables that contain columns with `SERIAL` auto-increment sequences or `DEFAULT` values. An error is reported if you reshard a table with a `SERIAL` column. The `DEFAULT` property is lost after a table with a `DEFAULT` column is resharded.

  • A table cannot have other dependencies, such as views, during resharding. If dependencies exist, you must delete them before resharding. Otherwise, the error "ERROR: resharding table xxx can not executed because other objects depend on it." is reported.

  • In Hologres V2.2.29 and later and V3.0.36 and later, you can reshard tables that have view dependencies. To do this, run the set hg_experimental_hg_insert_overwrite_enable_view=on; command to enable the GUC parameter.

  • Resharding is applicable only to the simple permission model, not the expert permission model. For more information about Hologres permission models, see Hologres permission model.

  • Resharding does not retain the dynamic partitioning property of a table.

  • Only resharding operations in Hologres V2.0 and later retain column `COMMENTS`. If your instance version is earlier than V2.0, you must back up the column `COMMENTS` beforehand and manually set them after resharding is complete.

• Syntax example

  • If your instance is V2.0.24 or later, we recommend that you use HoloWeb to perform resharding. For more information, see Table Resharding.

  • If your instance version is earlier than V2.0.24, you can use the following commands to migrate business tables to a new table group. If the instance version is earlier than V0.10, you must upgrade it first.

  Note

  • Before migration, you must have a new table group. If you do not have one, create one first. For more information, see Create a table group.

  • When you migrate a table, you must stop writing data to it. Queries are not affected.

  • After all tables in the original table group are migrated, the original table group is automatically deleted. If your business requires multiple table groups, set an appropriate shard count for each one.

  • When you migrate partitioned tables, you only need to perform the operation on the parent table.

  • If your instance is a compute group type and you need to migrate a table from Table Group A to the target Table Group B, you must use the primary compute group W of Table Group B to perform the migration. In addition, compute group W must have access permissions to Table Group A. This means compute group W must be loaded as a follower compute group of Table Group A. For more information about how to grant table group permissions to a compute group, see Authorize a compute group to access data.

  -- Command syntax for V1.1 and later
  CALL HG_MOVE_TABLE_TO_TABLE_GROUP('<table_name>','<new_table_group_name>');
  
  -- Command syntax for 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 to migrate. Only one table can be migrated at a time. To migrate multiple tables, run the command multiple times. For a partitioned table, you only need to operate on the parent table.	new_table
  table_group_name	The name of the new table group.	new_tg

• Exception handling

  During table resharding, the table is first set to read-only and then migrated to the new table group using a temporary table. Because resharding tasks involve data import and may take a long time, various exceptions can occur, such as out-of-memory (OOM) errors or manual task termination. In these cases, the original table remains in a read-only state, and a temporary table named <initial_table_name>_xxxxxxxx appears in the database. In such cases, handle the situation as follows:

  • If the version of your Hologres instance is earlier than V2.0.24, upgrade the instance or submit a ticket. For more information about how to upgrade an instance, see Instance upgrades.

  • If your Hologres instance version is V2.0.24 or later:

    • If you are performing resharding in HoloWeb, click Continue or Cancel. For more information, see Table Resharding.

    • If you are resharding by running the preceding commands, follow these steps:

      1. To continue resharding the table, resolve the issue that caused the interruption, and then run the `HG_MOVE_TABLE_TO_TABLE_GROUP` command on the table again.

      2. To cancel resharding and restore the initial state, run the following commands in order:

         -- Cancel the readonly property of the original table.
         CALL set_table_property('<schema_name>.<table_name>','readonly','false');
         
         -- Clear the temporary table.
           -- Get the name of the temporary table (partitioned table).
         SELECT schema_name,target_temp_table_name FROM hologres.hg_resharding_properties WHERE reshard_table_name = <schema_name>.<table_name> AND is_parent_table IS TRUE;
           -- Get the name of the temporary table (non-partitioned table).
         SELECT schema_name,target_temp_table_name FROM hologres.hg_resharding_properties WHERE reshard_table_name = <schema_name>.<table_name> AND is_parent_table IS FALSE AND is_sub_table IS FALSE;
           -- Clear the temporary table.
         DROP TABLE IF EXISTS <schema_name>.<target_temp_table_name>;
         
         -- Clear the system table. The system table records the resharding progress of the current table. If you want to cancel the task, you must clear the corresponding data from the system table.
         CALL hologres.hg_internal_clear_resharding_properties('<schema_name>.<table_name>'); 

Delete a table group

CALL HG_DROP_TABLE_GROUP('<tg_name>');

-- Delete the table group named tg_8.
CALL HG_DROP_TABLE_GROUP('tg_8');

View the skew relationship between shards and workers

An allocation relationship exists between the shard count and the number of workers. For more information, see Relationship between shards and worker compute nodes. If shards are not evenly allocated to workers, worker resource skew can occur, which leads to uneven loads and inefficient resource use. Therefore, Hologres V1.3 provides the worker_info system view to query the relationship among workers, table groups, and shards in the current database. This view helps you troubleshoot skew issues. For more information about how to check for worker resource skew, see View worker skew relationship.

Best practices

Table groups are an advanced feature. In most cases, we do not recommend creating new table groups or modifying shard counts for your business. If you have different business requirements, you can plan them based on our best practices. For more information, see Best practices for setting table groups.

FAQ

Resharding is a complex process that involves multiple steps, such as creating temporary tables, modifying the read-only state of the original table, writing to the target table, changing table names, and recording the synchronization status. If an exception occurs during any step, the system state may become uncertain. You can use the following methods to troubleshoot issues.