:Query the storage sizes of tables and databases

Limits

• You can query the storage sizes of only internal tables. You cannot directly query the storage size of a parent table. If you try to directly query the storage size of a parent table, the value 0 is returned. You can query the storage size of a child table. If the storage size of a table is 0, a null value is returned.

• In Hologres V1.3.24 and later, you can use the pg_relation_size function to query the storage size of a Hologres table for which binary logging is enabled.

• 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 the Pangu directory on an instance. These files include tables, metadata files, and temporary files generated by write, update, and delete operations. Therefore, the storage sizes of files collected based on the metrics are slightly greater than the storage sizes of tables queried by using the pg_relation_size function.

Query the storage size of a table

• Syntax

  SELECT pg_relation_size('table_name');-- The unit of the return value is byte.

• Parameters

  Parameter	Description
  table_name	The name of the table whose storage size you want to query in the current database.

• Return value: The unit of the return value is byte, and the type of the return value is STRING. The return value indicates the total size of the memory and physical disk space that the table occupies at the moment.

  If you need higher readability, use the pg_size_pretty function. Sample statements:

  -- Query the storage size of a single table in the current database.
  SELECT pg_size_pretty(pg_relation_size('table_name'));
  
  -- Query the storage sizes of all the tables in the current 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;

Query the storage size of a schema

• Description

  You can query the storage sizes of all the tables in a schema by executing an SQL statement.

• Syntax

  SELECT table_schema, pg_size_pretty(SUM(pg_relation_size(quote_ident(table_schema) || '.' || quote_ident(table_name))::decimal)) AS schema_size
  FROM information_schema.tables 
  WHERE table_schema = '<schema_name>'
  GROUP BY table_schema;

• Parameters

  Parameter	Description
  schema_name	The name of the schema to which the current table belongs.

• Return value: The unit of the return value is byte.

Query the storage size of a database

• Description

  You can query the storage size of the current database and the sizes of the internal tables in the database.

• Syntax

  SELECT pg_database_size(current_database()); -- The unit of the return value is byte.

• Parameters

  Parameter	Description
  current_database	The current database. You can directly execute the preceding statement to query the storage size of the current database.

• Return value: The unit of the return value is byte. The return value indicates the sizes of all the Hologres tables and the sizes of write-ahead logs generated in the current database.

  If you need higher readability, use the pg_size_pretty function. Sample statement:

  SELECT pg_size_pretty(pg_database_size(current_database())); -- The unit of the return value may be KB or MB.

Query the storage details of a table

In Hologres V2.1 and later, you can use the hologres.hg_relation_size function to query storage details of a table, including the storage sizes of data and binary logs.

• Limits

  Only Hologres V2.1 and later support the hologres.hg_relation_size function.

• Syntax

  SELECT hologres.hg_relation_size('<schema.table>','[data|binlog|mv|all]') ;

• Parameters

  Parameter	Description
  schema.table	In this parameter, schema specifies the name of the schema to which the table belongs and table specifies the table name.
  [data|binlog|mv|all]	Optional. data: the storage size of data in the table. binlog: the storage size of binary logs of the table. mv: the storage size of the materialized view if the materialized view is created for the table. all: the total storage size of the table.

• Examples

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

    SELECT hologres.hg_relation_size('<schema.table>','data') ;

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

    SELECT hologres.hg_relation_size('<schema.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;