Hologres:Get and analyze table statistics

Scenario 1: View access trends for all internal tables

This query returns weekly trends for all internal tables, including storage size, file count, daily read and write counts, and row count. Use it to identify changes in data volume and access patterns over time.

SELECT
  db_name,
  schema_name,
  table_name,
  collect_time::date AS collect_date,
  hot_storage_size,
  cold_storage_size,
  hot_file_count,
  cold_file_count,
  read_sql_count_1d,
  write_sql_count_1d,
  row_count
FROM
  hologres.hg_table_info
WHERE
  collect_time > (current_date - interval '1 week')::timestamptz
  AND type = 'TABLE'
ORDER BY collect_date DESC;

Scenario 2: Find the top 10 tables by storage with access information

This query returns the 10 largest tables by total storage, along with their weekly read and write activity. Use it to identify high-storage tables that may be candidates for cleanup or optimization.

SELECT
  db_name,
  schema_name,
  table_name,
  hot_storage_size + cold_storage_size AS total_storage_size,
  row_count,
  sum(read_sql_count_1d) AS total_read_count,
  sum(write_sql_count_1d) AS total_write_count
FROM
  hologres.hg_table_info
WHERE
  collect_time > (current_date - interval '1 week')::timestamptz
  AND type = 'TABLE'
  AND (
    cold_storage_size IS NOT NULL
    OR hot_storage_size IS NOT NULL
  )
GROUP BY db_name, schema_name, table_name, total_storage_size, row_count
ORDER BY total_storage_size DESC
LIMIT 10;

Scenario 3: Track weekly trends for the top 10 tables by storage

This query first identifies the top 10 tables by storage based on yesterday's snapshot, then retrieves their daily storage, row count, and access activity for the past week. Use it to monitor how your largest tables grow and are accessed over time.

WITH top10_table AS (
  SELECT
    db_name,
    schema_name,
    table_name,
    hot_storage_size + cold_storage_size AS total_storage_size
  FROM
    hologres.hg_table_info
  WHERE
    collect_time >= (current_date - interval '1 day')::timestamptz  -- Yesterday
    AND collect_time < current_date
    AND type = 'TABLE'
    AND (cold_storage_size IS NOT NULL OR hot_storage_size IS NOT NULL)
  ORDER BY total_storage_size DESC
  LIMIT 10
)
SELECT
  base.db_name,
  base.schema_name,
  base.table_name,
  base.hot_storage_size + cold_storage_size AS total_storage_size,
  base.row_count,
  base.read_sql_count_1d,
  base.write_sql_count_1d,
  base.collect_time::date AS collect_date
FROM
  hologres.hg_table_info AS base
LEFT JOIN
  top10_table
ON
  base.db_name = top10_table.db_name
  AND base.schema_name = top10_table.schema_name
  AND base.table_name = top10_table.table_name
WHERE
  collect_time > (current_date - interval '1 week')::timestamptz
  AND type = 'TABLE'
  AND (cold_storage_size IS NOT NULL OR hot_storage_size IS NOT NULL)
ORDER BY total_storage_size DESC, collect_date DESC;

Scenario 4: Track weekly trends for the 10 smallest tables by storage

This query identifies the 10 tables with the least storage based on yesterday's snapshot, then retrieves their weekly access and storage trends. Use it to find tables that may be unused or safe to archive.

WITH top10_table AS (
  SELECT
    db_name,
    schema_name,
    table_name,
    hot_storage_size + cold_storage_size AS total_storage_size
  FROM
    hologres.hg_table_info
  WHERE
    collect_time >= (current_date - interval '1 day')::timestamptz  -- Yesterday
    AND collect_time < current_date
    AND type = 'TABLE'
  ORDER BY total_storage_size ASC
  LIMIT 10
)
SELECT
  base.db_name,
  base.schema_name,
  base.table_name,
  base.hot_storage_size + cold_storage_size AS total_storage_size,
  base.row_count,
  base.read_sql_count_1d,
  base.write_sql_count_1d,
  base.collect_time::date AS collect_date
FROM
  hologres.hg_table_info AS base
RIGHT JOIN
  top10_table
ON
  base.db_name = top10_table.db_name
  AND base.schema_name = top10_table.schema_name
  AND base.table_name = top10_table.table_name
WHERE
  collect_time > (current_date - interval '1 week')::timestamptz
  AND type = 'TABLE'
ORDER BY total_storage_size ASC, collect_date DESC;

Scenario 5: Find tables with small file problems

This query returns each table's total storage, file count, and average file size, sorted by average file size in ascending order. Tables at the top of the results—with a low average file size—likely have a small file accumulation problem, which increases storage overhead and can degrade query performance. The query also joins with hg_table_group_properties to show the shard count for each table group, which helps assess whether the file count is proportionate to the table configuration.

Shard count is shown only for table groups in the current database. The shard_count column is empty for tables in other databases.

SELECT
  db_name,
  schema_name,
  table_name,
  cold_storage_size + hot_storage_size AS total_storage_size,
  cold_file_count + hot_file_count AS total_file_count,
  (cold_storage_size + hot_storage_size) / (cold_file_count + hot_file_count) AS avg_file_size,
  tmp_table_info.table_meta ->> 'table_group' AS table_group,
  tg_info.shard_count
FROM
  hologres.hg_table_info tmp_table_info
  LEFT JOIN (
    SELECT
      tablegroup_name,
      property_value AS shard_count
    FROM
      hologres.hg_table_group_properties
    WHERE
      property_key = 'shard_count'
  ) tg_info ON tmp_table_info.table_meta ->> 'table_group' = tg_info.tablegroup_name
WHERE
  collect_time > (current_date - interval '1 day')::timestamptz
  AND type = 'TABLE'
  AND (
    cold_storage_size IS NOT NULL
    OR hot_storage_size IS NOT NULL
  )
  AND (
    cold_file_count IS NOT NULL
    OR hot_file_count IS NOT NULL
  )
  AND cold_file_count + hot_file_count <> 0
ORDER BY avg_file_size;

Scenario 6: View row count changes on the last modification day

This query returns the last modification time of each table and the net change in row count compared to the day before the modification. Use this to audit data change events across your tables.

If your instance has many tables, add filters to the tmp_table_info CTE to avoid long query times. Examples are provided as inline comments.

WITH tmp_table_info AS (
  SELECT
    db_name,
    schema_name,
    table_name,
    row_count,
    collect_time,
    last_modify_time
  FROM
    hologres.hg_table_info
  WHERE
    last_modify_time IS NOT NULL
    AND type = 'TABLE'
    -- Add filters here if needed, for example:
    -- AND collect_time > (current_date - interval '14 day')::timestamptz
    -- AND table_name LIKE ''
)
SELECT
  end_data.db_name,
  end_data.schema_name,
  end_data.table_name,
  (end_data.row_count - start_data.row_count) AS modify_row_count,
  end_data.row_count AS current_rows,
  end_data.last_modify_time
FROM
  (
    SELECT
      db_name,
      schema_name,
      table_name,
      row_count,
      last_modify_time
    FROM
      tmp_table_info
    WHERE
      collect_time > (current_date - interval '1 day')::timestamptz
  ) end_data
  LEFT JOIN (
    SELECT
      db_name,
      schema_name,
      table_name,
      row_count,
      collect_time
    FROM
      tmp_table_info
  ) start_data ON (
    end_data.db_name = start_data.db_name
    AND end_data.schema_name = start_data.schema_name
    AND end_data.table_name = start_data.table_name
    AND end_data.last_modify_time::date = (start_data.collect_time + interval '1 day')::date
  );