Hologres:User guide of table groups and shard counts

Recommended settings about table groups and shard counts

Permissions description

• If you want to create a table group, change the default table group, delete a table group, and use the resharding feature to migrate tables to a new table group, you must be assigned the superuser role of a Hologres instance. As a superuser, execute the following statement to assign the superuser role to the regular user:

  -- Replace Account ID with the Alibaba Cloud account ID of the user. If the user is a Resource Access Management (RAM) user, prefix the account ID with p4_. 
  ALTER USER "Account ID" SUPERUSER; 

• If you want to create a table and add it to a specified table group, you must be granted the permissions to create the table.

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

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

-- 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

After you create a database, a default table group with a specific number of shards is created for the database in a Hologres instance. For more information, see Instance types. You can execute the following statement 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.

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

-- In Hologres V0.9 and later, you can set a newly created table group to the default table group. After the setting takes effect, subsequently created tables are stored in the new default table group.
CALL HG_UPDATE_DATABASE_PROPERTY ('default_table_group', 'tg_8');

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;

-- 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 the 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.

For example, after a Hologres instance is scaled up 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, 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. You do not 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 view 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, manually upgrade your Hologres instance in the Hologres console or join the DingTalk group 32314975 for technical support. For more information about how to manually upgrade your Hologres instance in the Hologres console, see Instance upgrade.
  • The resharding operation is performed on a single table at a time. When you perform the resharding operation on a table, you must stop writing data to the table by using Realtime Compute for Apache Flink or Data Integration. You can still query data in the table. In Hologres V1.1 and later, you can specify set table readonly to implement automatic failover for real-time data writes.
  • Resharding consumes CPU resources and additional storage resources. We recommend that you perform the resharding operation during off-peak hours.
  • If binary logging is enabled for a table, you must disable binary logging before you perform the resharding operation on the table. You can enable binary logging again after resharding is complete. For more information, see Subscribe to Hologres binary logs.
  • You cannot perform the resharding operation 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 is complete.
  • You cannot perform the resharding operation on a table if the table depends on an object, such as a view. You must remove the relevant dependencies before you perform the resharding operation.
  • You can perform the resharding operation in only databases that use the simple permission model (SPM). Databases that use the standard PostgreSQL authorization model do not support 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 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 table.	new_table
  new_table_group_name	The name of the table group to which you want to migrate a table.	new_tg

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');

Query the allocation relationship between shards and workers

The shard count is proportional to the number of workers. For more information, see Relationship between shard counts and worker compute nodes. If the shard count is not proportional to the number of workers, worker resources cannot be evenly allocated and efficiently used. Hologres V1.3 allows you to query the allocation relationship among workers, table groups, and shard counts in the current database by using the worker_info system view. This helps resolve the issue of uneven resource allocation. For more information about the allocation of worker resources, see Query the shard allocation among workers.

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 plan the table groups based on the best practices. For more information, see Best practices for specifying table groups.

FAQ

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 an exception occurs when you perform one or more steps, the system may enter an unknown state. In this case, you can use the following solution for troubleshooting.