Hologres:Manage table groups and shards

How Table Groups and shards work

A Hologres instance organizes data in a two-level hierarchy:

• Table Group -- A logical container that holds one or more tables. All tables in the same Table Group share the same shard count.

• Shard -- A unit of data distribution. Shards are spread across Workers (compute nodes) for parallel processing.

Every database has a default Table Group whose shard count matches the instance specifications. The default is sufficient for most workloads.

Configuration recommendations

Follow these guidelines:

• Use the default Table Group unless your workload requires a different shard count. Default shard counts by instance size are listed in Instance management.

• Large instances (> 256 CUs): Consider multiple Table Groups for load balancing:

  • Large data volumes -- create a separate Table Group with a higher shard count.

  • Many small tables -- create a separate Table Group with a lower shard count to reduce query startup overhead.

• Tables that join must share a Table Group.

• Do not create a Table Group per table. This adds unnecessary overhead and fragmentation.

• Align shard count with Workers. Set the shard count as a multiple of Workers for balanced resource use and easier scale-out.

SET hg_experimental_enable_shard_count_cap = off;

Permissions

Only a superuser can create, modify, or delete a Table Group, or move a table to a different Table Group (resharding).

To grant superuser privileges to a user:

-- Replace <Alibaba Cloud account ID> with the user's UID.
-- For a RAM user, add the prefix "p4_" to the account ID.
ALTER USER "<Alibaba Cloud account ID>" SUPERUSER;

Assigning a new table to a Table Group requires only table creation permissions.

Query Table Group metadata

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

 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)

SELECT tablegroup_name
FROM hologres.hg_table_group_properties GROUP BY tablegroup_name;

SELECT property_value AS shard_count
FROM hologres.hg_table_group_properties
WHERE property_key = 'shard_count' AND tablegroup_name = '<tg_name>';

SELECT table_namespace AS schema_name, table_name
FROM hologres.hg_table_properties
WHERE property_key = 'table_group' AND property_value = '<tg_name>';

SELECT property_value AS table_group_name
FROM hologres.hg_table_properties
WHERE property_key = 'table_group' AND table_name = '<table_name>';

Create a Table Group

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

Example:

-- Create a Table Group named tg_8 with 8 shards.
CALL HG_CREATE_TABLE_GROUP('tg_8', 8);

Change the default Table Group

Set a different default Table Group so new tables are automatically assigned to it.

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

Example:

-- Set tg_8 as the default Table Group.
CALL HG_UPDATE_DATABASE_PROPERTY('default_table_group', 'tg_8');

Assign a new table to a specific Table Group

Wrap the CREATE TABLE and SET_TABLE_PROPERTY calls in a transaction:

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

Example:

-- Create table tbl1 and assign it to Table Group tg_8.
BEGIN;
CREATE TABLE tbl1 (
    col1 text
);
CALL SET_TABLE_PROPERTY('tbl1', 'table_group', 'tg_8');
COMMIT;

Reshard a table

Scaling up an instance does not adjust the shard count for existing databases. To use the added capacity, create a new Table Group with a higher shard count and move your tables into it. New databases created after scale-up use the updated default. Overview of instance specifications.

Two methods are available:

-- For V1.1 and later:
CALL HG_MOVE_TABLE_TO_TABLE_GROUP('<table_name>', '<new_table_group_name>');

-- For V0.10 and later:
CALL HG_UPDATE_TABLE_SHARD_COUNT('<table_name>', '<new_table_group_name>');

-- 1. Remove the read-only flag from the original table.
CALL set_table_property('<schema_name>.<table_name>', 'readonly', 'false');

-- 2. Find the temporary table name.
-- For a 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;

-- For a 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;

-- 3. Drop the temporary table.
DROP TABLE IF EXISTS <schema_name>.<target_temp_table_name>;

-- 4. Clear the resharding progress record.
CALL hologres.hg_internal_clear_resharding_properties('<schema_name>.<table_name>');

Delete a Table Group

Remove all tables from the Table Group first, then run:

CALL HG_DROP_TABLE_GROUP('<tg_name>');

Example:

CALL HG_DROP_TABLE_GROUP('tg_8');

Check shard-to-worker distribution

Uneven shard distribution across Workers causes load imbalance and inefficient resource use.

From Hologres V1.3, use the worker_info system view to check shard-to-Worker mapping. Basic concepts covers the shard-to-node relationship. Query the shard allocation among workers provides the query syntax.

Best practices

The default Table Group suits most workloads. Custom configurations are covered in Best practices for setting table groups.

FAQ