Hologres:Best practices for specifying table groups

SELECT * FROM hologres.hg_table_group_properties;

-- Sample result 
 tablegroup_name | property_key  | property_value
-----------------+---------------+----------------
 test_tg_default | is_default_tg | 1
 test_tg_default | shard_count   | 40
 test_tg_default | tg_version    | 1
 test_tg_default | table_num     | 1
(4 rows)

• Data amount

  If the shard count of the default table group meets the requirement of the data amount, you can use the default table group to directly create tables.

• Overall size

  The total data amount of all tables is controllable and predictable. The usage mode does not significantly change.

• Local join

  You need to perform efficient local join operations on tables in the default table group.

If the default table group cannot meet your requirements, you may need multiple table groups. Typically, multiple table groups may be required in your instance under the following conditions:

• Data amount

  The shard count of the existing table group is inappropriate for the estimated data amount in the current table. If the shard count is large and the data amount is small, excessive small files exist and more I/O overheads are consumed. If the shard count is small and the data amount is large, the concurrency of queries is reduced. This is the most common reason why you need multiple table groups.

• Independent load

  Existing table groups contain a large number of tables, and data needs to be simultaneously written to most of the tables. Consequently, the load of your instance is high. In addition, the table to be created requires high query and write throughput. In this case, multiple table groups can make data write and query of this table independent of other tables to some extent. For more information, see topics about how to isolate computing resources. Alternatively, if you troubleshoot the issue and determine that the existing table groups cannot meet the write and query requirements, multiple table groups are also required.

• Table correlation

  If a set of tables in your business have a unique data write or query pattern, have or will have local join requirements, and have few or no correlation with tables in existing table groups, you can create multiple independent table groups for the set of tables. Local join operations can be performed only on tables with the join key as the distribution key and in the same table group. In other words, you can create multiple table groups for a set of tables that have a strong correlation with each other but have few correlation and a low probability of local join with tables in existing table groups.

• Instance resource scaling

  If your instance has been scaled in or out more than five times, the original shard count may no longer meet the requirements. In this case, you can replace the default table group. The number of shards must be greater than the number of compute nodes and less than 60% of the total number of CPU cores.

If you need to plan multiple table groups, we recommend that you plan the roles and significance of the table groups and the table group to which each table belongs before stress testing and production. You can consider the following factors during planning:

• Data amount

  The shard count is determined by the amount of data to be stored in a table. Specifically, a table group that has a large shard count is appropriate for large tables, and a table group that has a small shard count is appropriate for small and medium tables.

• Required write performance

  The shard count has a positive correlation with data write performance. The write capability of a single shard has an upper limit. More shards indicate higher write concurrency and throughput. If you need to write data to a table at a high records per second (RPS), a larger shard count may be required. If the CPU utilization for a single core is 100%, a single shard in Hologres writes data at an RPS of 3,000 to 5,000 (1 KB per record). You can estimate the required shard count based on your required RPS. Each shard also needs to perform read operations such as data query. Therefore, the CPU utilization for data write cannot reach 100%. A shard that uses a 1/3 CPU core writes data at an RPS of 1,000 (1 KB per record). For example, if you want to write data at an RPS of 60,000 and the size of each record is 1KB, the shard count must be greater than 60. The shard count can be fine-tuned.

• Load of each table group

  When you create a table group, you must consider the number of tables to be added to this table group. If a large number of tables will be added to this table group in the future, and most of the tables are frequently queried, a small shard count may fail to support highly concurrent queries.