AnalyticDB:How to check the data volume of tables and databases?

Function reference

Each function measures a different scope:

Query the total size of a table

Returns the size of a table including both data and indexes.

Replace <schemaname> and <tablename> with your actual schema and table names.

select pg_size_pretty(pg_total_relation_size('<schemaname>.<tablename>'));

Example output:

 pg_size_pretty
----------------
 42 MB
(1 row)

Query the data size of a table

Returns the data size of a table, excluding indexes.

select pg_size_pretty(pg_relation_size('<schemaname>.<tablename>'));

Example output:

 pg_size_pretty
----------------
 38 MB
(1 row)

Query the total size of all partitions in a partitioned table

Returns the combined size of all partitions in a partitioned table, including indexes.

select schemaname,tablename,round(sum(pg_total_relation_size(schemaname || '.' || partitiontablename))/1024/1024) "MB" from pg_partitions where schemaname='<schemaname>' and tablename='<tablename>' group by 1,2;

Example output:

 schemaname | tablename | MB
------------+-----------+-----
 public     | orders    | 210
(1 row)

Query the total size of all tables under a schema

Returns the combined size of all tables in a schema, including indexes.

select schemaname ,round(sum(pg_total_relation_size(schemaname||'.'||tablename))/1024/1024) "Size_MB" from pg_tables where schemaname='<schemaname>' group by 1;

Example output:

 schemaname | Size_MB
------------+---------
 public     |    1024
(1 row)

Query the size of each database

Returns the size of every database in the instance.

select datname,pg_size_pretty(pg_database_size(datname)) from pg_database;

Example output:

  datname  | pg_size_pretty
-----------+----------------
 mydb      | 8537 MB
 postgres  | 7665 kB
(2 rows)