All Products
Search
Document Center

Hologres:Query the storage sizes of tables and databases

Last Updated:Feb 20, 2024

Hologres is compatible with PostgreSQL. You can use PostgreSQL functions to query the storage size of a table or database in Hologres. This topic describes how to query the storage sizes of tables and databases by executing SQL statements.

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;