Hologres:Table Index Diagnosis

Diagnosis Item

Description

Recommendation

Number of tables and views per database

Shows the total number of internal tables, partitioned child tables, foreign tables, and views in each database of the selected instance.

Prioritize the administration of databases that contain many tables.

Number of internal tables per table group

Shows the number of internal tables (including partitioned child tables) in each table group.

• Hologres does not recommend that you have more than 10,000 internal tables in a single table group. An excessive number of tables can slow down Data Definition Language (DDL) execution and affect version upgrades. Use the Resharding feature to manage your table groups and ensure that they remain in a healthy state. For details, see Resharding.

• Avoid assigning one table per table group. This practice leads to an excessive number of table groups and inefficient worker resource allocation. If a table group contains only one table, consider merging it with other table groups.

Number and storage size of partitioned tables

Shows the number of partitioned child tables and their storage size for each partitioned table in the selected instance.

• For partitioned tables with more than 10,000 child partitions, use Tiered Storage of Hot and Cold Data to move infrequently accessed partitions to cold storage and reduce storage costs.

• Delete partitioned tables that have zero storage or zero partitions. Empty tables still consume metadata space.

Tables with zero storage

Shows tables with zero storage in both hot and cold tiers.

Promptly delete tables with zero storage to reduce metadata memory usage.

Tables using Segment format

Lists all tables in the selected instance that use Segment format storage. Hologres V2.0 and later no longer support Segment format tables. You must manually convert these tables to the Optimized Row Columnar (ORC) format. The ORC format uses a more efficient compression algorithm, which reduces storage costs and can improve performance. If you do not convert these tables, you cannot upgrade your instance to V2.0 or later. For conversion steps, see Change the data storage format of a column-oriented table.

None.

Row-oriented tables

Lists all row-oriented tables in the selected instance.

• Row-oriented tables are intended only for key-value queries based on primary keys. They are unsuitable for Online Analytical Processing (OLAP) queries and can degrade performance. Use them with caution. For details, see Table storage formats: column store, row store, and hybrid store.

• Check the slow query log for queries that involve a specific row-oriented table over the past day. If any query uses an engine type other than SDK or FixedQE, modify the query or change the table to a column-oriented or hybrid format.

• Example:

  -- View queries involving a specific table over the past day
  select usename,status,query_id,datname,command_tag,duration,message,query_start,query_end,query from hologres.hg_query_log where 
  query like '%<table_name>%'
  and query_start >= now() - interval '1 day';

Hybrid store tables

Lists all hybrid store tables in the selected instance.

Hybrid store tables are suitable for all scenarios but use double the amount of storage. Use them with caution. For details, see Table storage formats: column store, row store, and hybrid store. You can use the following SQL statement to find queries that target a specific hybrid store table.

-- View queries involving a specific table over the past day
select usename,status,query_id,datname,command_tag,duration,message,query_start,query_end,query from hologres.hg_query_log where 
query like '%<table_name>%'
and query_start >= now() - interval '1 day';

Row-oriented tables without a primary key (PK)

Row-oriented tables are designed for primary key–based queries and require a primary key (PK) to function effectively.

• Add a PK to row-oriented tables that lack one.

• Convert the table to a column-oriented format.

Row-oriented tables with mismatched distribution key and clustering key

The distribution key and clustering key of a row-oriented table must use the same field. Mismatched keys can add query overhead and reduce performance.

• Recreate the table and ensure that the distribution key and clustering key use the same field.

• Convert the table to a column-oriented format.

Column-oriented tables with more than 300 columns

Avoid creating column-oriented tables with more than 300 columns. Many columns increases performance overhead. For details, see Best practices.

Hybrid store tables with more than 300 columns

Avoid creating hybrid store tables with more than 300 columns. Many columns increases performance overhead. For details, see Best practices.

Row-oriented tables with more than 300 columns

Avoid creating row-oriented tables with more than 300 columns. Many columns increases performance overhead. For details, see Best practices.

Tables with more than three columns in the distribution key

The distribution key determines how data is distributed. In join scenarios, a well-chosen distribution key enables local joins and reduces data shuffling. However, using more than three columns can limit its effectiveness. Keep the distribution key to three columns or fewer. For details, see Distribution Key.

Tables with more than three columns in the clustering key

The clustering key sorts data within files and is commonly used for range filtering. It follows a left-matching rule. Avoid using more than three columns, because this limits index hit scenarios. For details, see Clustering Key.

Tables with more than three columns in the segment key

The segment key defines file boundaries and is often used with monotonic fields, such as timestamps, for filtering. It follows a left-matching rule. Avoid using more than three columns, because this limits index hit scenarios. For details, see Event Time Column (Segment Key).

Tables with nullable clustering key columns

The clustering key sorts data within files and is commonly used for range filtering. Avoid setting clustering key fields to nullable, because this disables acceleration. For details, see Clustering Key.

Recreate the table and set the clustering key field to NOT NULL.

Tables with nullable segment key columns

The segment key defines file boundaries and is often used with monotonic fields, such as timestamps, for filtering. Avoid setting segment key fields to nullable, because this disables acceleration. For details, see Event Time Column (Segment Key).

Recreate the table and set the segment key field to NOT NULL.

Tables with TTL less than 7 days

Time to Live (TTL) is used to manage the data lifecycle. Data is purged after its TTL expires. The exact deletion time is not guaranteed, but the deletion occurs after the expiration. A shorter TTL results in faster data deletion. We recommend that you set the TTL to at least 7 days to prevent issues such as duplicate data or accidental deletion. For details, see SQL command reference.

• Run the following command to increase the table’s TTL:

  -- Set TTL to 100 years (effectively permanent)
  begin; 
  call set_table_property('<tablename>', 'time_to_live_in_seconds', '8640000');
  commit; 

• For partitioned tables, use dynamic partitioning to manage child table lifecycles.

All tables with Binlog enabled

Enabling Binlog adds storage overhead. We recommend that you enable Binlog for row-oriented and hybrid store tables. Enabling Binlog for column-oriented tables incurs significant performance overhead. For details, see Subscribe to Hologres Binlog.

• If a column-oriented table has Binlog enabled, convert it to a row-oriented or hybrid format.

• Disable Binlog for tables where it is not required.

Tables with Binlog TTL greater than 7 days

Hologres Binlog records Data Manipulation Language (DML) operations. A longer Binlog TTL retains more records and increases storage overhead. We recommend that you keep the Binlog TTL within 7 days.

For tables with a long Binlog TTL, run the following statement to reduce it:

-- Set Binlog TTL to 7 days
begin; 
call set_table_property('<table_name>', 'binlog.ttl', '604800'); 
commit;