Hologres:Storage functions

PG_DATABASE_SIZE

Returns the total storage size of the current database. Use this function to get a high-level view of database-level storage consumption.

Syntax

SELECT PG_DATABASE_SIZE('database_name');

Parameters

Return value

Returns the sum of:

• The storage sizes of all tables in the database

• The sizes of WAL logs generated in the database

Example

Return the size of the current database in a human-readable format:

SELECT PG_SIZE_PRETTY(PG_DATABASE_SIZE(CURRENT_DATABASE()));

PG_RELATION_SIZE

Returns the total storage size of a table, covering both memory space and physical disk space. Use this function to identify which tables consume the most storage.

Syntax

SELECT PG_RELATION_SIZE('table_name');

Parameters

Return value

• Returns a STRING type value indicating the total size of the memory space and physical disk space the table currently occupies.

• Returns 0 if you query a parent partitioned table without specifying a child partition.

• Returns null if the table's storage size is 0.

Limitations

• Only internal tables are supported. To check a partitioned table, query a specific child partitioned table — not the parent partitioned table directly.

• Querying the storage size of a table with binary logging enabled requires Hologres V1.3.24 or later. To upgrade your instance or get help, see Upgrade instances or Obtain online support for Hologres.

Storage metrics collected from the Apsara Distributed File System directory include tables, metadata files, and temporary files generated by write, update, and delete operations. As a result, metric values are slightly higher than the values returned by PG_RELATION_SIZE.

Examples

The following examples use the sample data for general-purpose aggregate functions.

Query the storage size of a single table:

SELECT PG_SIZE_PRETTY(PG_RELATION_SIZE('example_table'));

Query the storage sizes of all tables in a database, sorted from largest to smallest:

SELECT table_schema || '.' || table_name AS table_full_name,
  PG_SIZE_PRETTY(PG_RELATION_SIZE(QUOTE_IDENT(table_schema) || '.' || QUOTE_IDENT(table_name))) AS table_size,
  PG_RELATION_SIZE(QUOTE_IDENT(table_schema) || '.' || QUOTE_IDENT(table_name)) AS order_size
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema', 'hologres')
ORDER BY order_size DESC;

HOLOGRES.HG_RELATION_SIZE

Returns the storage size of a table broken down by component: data, binary logs, or materialized views. Use this function when you need granular visibility into what is driving storage consumption — for example, to determine whether binary logs are the main contributor to table size.

Requires Hologres V2.1 or later. To upgrade your instance or get help, see Upgrade instances or Obtain online support for Hologres.

Syntax

SELECT HOLOGRES.HG_RELATION_SIZE('schema.table'[, 'type']);

Parameters

type valid values:

Examples

The following examples use the sample data for general-purpose aggregate functions.

Query the data storage size of a table:

SELECT HOLOGRES.HG_RELATION_SIZE('public.example_table', 'data');

Query the binary log storage size of a table:

SELECT HOLOGRES.HG_RELATION_SIZE('public.example_table', 'binlog');

Query the data storage sizes of all tables in a database, sorted from largest to smallest:

SELECT table_schema || '.' || table_name AS table_full_name,
  pg_size_pretty(hologres.hg_relation_size(quote_ident(table_schema) || '.' || quote_ident(table_name), 'data')) AS data_size,
  hologres.hg_relation_size(quote_ident(table_schema) || '.' || quote_ident(table_name), 'data') AS order_size
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema', 'hologres', 'hologres_statistic')
ORDER BY order_size DESC;