Hologres:Table storage functions

Description: Queries the storage size of the current database.

SELECT PG_DATABASE_SIZE('database_name');

Parameters:

database_name: required. The name of the current database.

Return value:

The sum of the sizes of all tables in the current database and the sizes of write-ahead logging (WAL) logs generated in the current database is returned.

Example:

SELECT PG_SIZE_PRETTY(PG_DATABASE_SIZE(CURRENT_DATABASE())); 

Description: Queries the storage size of a table.

SELECT PG_RELATION_SIZE('table_name');

The PG_RELATION_SIZE function is used to collect the storage sizes of tables. Metrics are used to collect the storage sizes of the files stored in an Apsara Distributed File System directory on an instance. These files include tables, metadata files, and temporary files generated by write, update, and delete operations. As a result, the storage sizes of files collected by using the metrics are slightly greater than the storage sizes of tables queried by using the PG_RELATION_SIZE function.

Limits:

• Only Hologres V1.3.24 and later allow you to use this function to query the storage size of a Hologres table for which binary logging is enabled.

  Note

  If the version of your Hologres instance is earlier than V1.3.24, manually upgrade your Hologres instance or join the Hologres DingTalk group to contact technical support. For more information about how to upgrade an instance, see Upgrade instances. For more information about how to obtain online support, see Obtain online support for Hologres.

• You can query the storage sizes of only internal tables. You cannot directly query the storage size of a parent partitioned table. Instead, you can query the storage size of a child partitioned table.

Parameters:

table_name: the name of the table whose storage size you want to query.

Return value:

• A value of the STRING type is returned. The return value indicates the total size of the memory space and physical disk space that the table currently occupies.

• If you directly query the storage size of a parent partitioned table, without specifying a child partitioned table, the value 0 is returned.

• If the storage size of a table is 0, a null value is returned.

Examples:

Sample data of general-purpose aggregate functions is used in the following examples:

• Query the storage size of a single table.

  -- Query the storage size of a single table.
  SELECT PG_SIZE_PRETTY(PG_RELATION_SIZE('example_table'));

• Query the storage sizes of all the tables in a database.

  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;

Description: Queries the storage details of a table, such as the storage sizes of data and binary logs.

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

Limits:

Only Hologres V2.1 and later support this function.

Parameters:

• schema.table: In this parameter, schema specifies the name of the schema to which the table belongs and table specifies the table name.

• type: optional. Valid values:

  • data: The storage size of data in the table is queried.

  • binlog: The storage size of binary logs of the table is queried.

  • mv: The storage size of the materialized view is queried if a materialized view is created for the table.

  • all: The total storage size of the table is queried.

Examples:

Sample data of general-purpose aggregate functions is used in the following examples:

• Example 1: Query the storage size of data in a table.

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

• Example 2: Query the storage size of binary logs of a table.

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

• Example 3: Query the storage size of all tables in a database.

  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;