System tables for dynamic table

The hologres.hg_dynamic_table_properties system table

This table stores the metadata of each Dynamic Table, including its refresh configuration and query definition.

General properties

Parameters for incremental refresh

Parameters for full refresh

The hologres.hg_dynamic_table_dependencies system table

This table stores the data lineage of Dynamic Tables — specifically, which base tables each Dynamic Table depends on.

Usage notes:

• A Dynamic Table can depend on multiple base tables, so multiple rows may appear for a single Dynamic Table.

• The state table used in incremental refresh is also recorded as a base table. By default, it is in the hologres_streaming_mv system schema. Ignore rows with this schema in practice.

• Use system tables such as pg_class to further differentiate base table types.

Example: query data lineage for a specific Dynamic Table

SELECT
  table_namespace,
  table_name,
  dependency
FROM hologres.hg_dynamic_table_dependencies
WHERE dynamic_table_name = '<your_dynamic_table>'
  AND table_namespace != 'hologres_streaming_mv'
ORDER BY dependency;

The hologres.hg_dynamic_table_refresh_activity system table

This system table is not supported in Hologres instances of V3.1 and later. Use hologres.hg_dynamic_table_refresh_history to view completed refresh tasks.

This table records currently running Dynamic Table refresh tasks.

The hologres.hg_dynamic_table_refresh_history system table

This table stores the history of Dynamic Table refresh tasks. Data is retained for one month by default.

Example: find failed refreshes in the past week

SELECT
  dynamic_table_name,
  refresh_start,
  refresh_end,
  duration,
  refresh_mode,
  status
FROM hologres.hg_dynamic_table_refresh_history
WHERE status = 'FAILED'
  AND refresh_start >= NOW() - INTERVAL '7 days'
ORDER BY refresh_start DESC;

Example: check refresh latency for a specific Dynamic Table

SELECT
  refresh_start,
  refresh_end,
  duration,
  refresh_latency,
  status
FROM hologres.hg_dynamic_table_refresh_history
WHERE dynamic_table_name = '<your_dynamic_table>'
ORDER BY refresh_start DESC
LIMIT 20;

What's next

• O&M for Dynamic Table refresh tasks

• View the schema and data lineage of a Dynamic Table