1) Database Size in PostgreSQL.

testdb=> SELECT pg_size_pretty ( pg_database_size ('testdb') );

pg_size_pretty
----------------
136 GB
(1 row)

testdb=>select pg_database.datname,
      pg_size_pretty(pg_database_size(datname)) AS DBSIZE
      from pg_database;
datname    | dbsize
-----------+---------
template0  | 7873 kB
rdsadmin   | 8125 kB
template1  | 8037 kB
postgres   | 8037 kB
testdb     | 136 GB
(5 rows)

2) Tablespace size in PostgreSQL.

testdb=> select pg_size_pretty ( pg_tablespace_size ('pg_default'));

pg_size_pretty
----------------
136 GB
(1 row)

testdb=> select pg_tablespace.spcname,
pg_size_pretty(pg_tablespace_size(spcname)) AS "TBLSPACESIZE" 
 from pg_tablespace;

  spcname   | TBLSPACESIZE
------------+--------------
 pg_default | 136 GB
 pg_global  | 774 kB
(2 rows)

3)Table size in PostgreSQL.

—  Specify the disk space used by the table OID or table name, excluding the index (but containing TOAST, free space mapping, and visual mapping).

testdb=> select pg_size_pretty( pg_table_size('testuser.test'));
pg_size_pretty
----------------
10 GB
(1 row)

testdb=> select pg_size_pretty( pg_relation_size('testuser.test'));
pg_size_pretty
----------------
10 GB
(1 row)

— Specify the total disk space used by the table OID or table name, including all indexes and TOAST data.

testdb=> select pg_size_pretty( pg_total_relation_size('testuser.test'));
pg_size_pretty
----------------
16 GB
(1 row)

4)Index size in PostgreSQL.

— total disk space associated with table index of specified table OID or table name.

testdb=> select pg_size_pretty( pg_indexes_size('testuser.test'));
pg_size_pretty
----------------
5377 MB
(1 row)

-- get all index names of table testuser.test
testdb=>\d testuser.test
...
..
.

-- get size of index
testdb=> select pg_size_pretty( pg_relation_size('testuser.ix_test_id'));
pg_size_pretty
----------------
942 MB
(1 row)

5) Top 10 biggest tables.

testdb=> select schemaname,tablename, 
 pg_size_pretty( pg_table_size(schemaname||'.'||tablename)) TABSIZE, 
 pg_table_size(schemaname||'.'||tablename) TABSIZEINBYTES
from pg_tables order by 4 desc limit +10;

schemaname  | tablename         | tabsize | tabsizeinbytes
------------+-------------------+---------+---------------
testuser    | cdr               | 44 GB   | 47675359232
testuser    | test              | 10 GB   | 11257372672
...
..
.
(10 rows)

6) Top 10 tables with biggest indexes.

testdb=> select schemaname,tablename,
pg_size_pretty( pg_indexes_size(schemaname||'.'||tablename)) IDXSIZE,
pg_indexes_size(schemaname||'.'||tablename) IDXSIZEINBYTES 
from pg_tables order by 4 desc limit +10;

schemaname | tablename | idxsize  | idxsizeinbytes
-----------+-----------+----------+----------------
testuser   | cdr       | 29 GB    | 31294144512
testuser   | test      | 5379 MB  | 5640585216
...
..
.
(10 rows)

7) Top 10 biggest indexes.

testdb=> select schemaname,tablename,indexname,
pg_size_pretty(pg_relation_size(schemaname||'.'||indexname)) idxsize,
pg_relation_size(schemaname||'.'||indexname) indexsizeinbytes 
from pg_indexes 
order by 5 desc limit +10;

schemaname  | tablename | indexname | idxsize | indexsizeinbytes
------------+-----------+-----------+---------+------------------
testuser    | cdr       | pk_cdr    | 4045 MB | 4241883136
testuser    | test      | ix_pk     | 2777 MB | 2912100352
...
..
.
(10 rows)

8) Top 10 biggest tables ( including indexes, TOAST…).

testdb=> select schemaname,tablename,
pg_size_pretty( pg_total_relation_size(schemaname||'.'||tablename)) relsize,
pg_total_relation_size(schemaname||'.'||tablename) relsizeinbytes 
from pg_tables order by 4 desc limit +10;

schemaname  | tablename | relsize | relsizeinbytes
------------+-----------+---------+----------------
testuser    | cdr       | 74GB    | 78969503744
testuser    | test      | 16 GB   | 16900882432
testuser    | test1     | 13 GB   | 13817872384
...
..
.
(10 rows)

Functions of database object

Function name	Return type	describe
pg_column_size(any)	int	The number of bytes required to store a specified value (possibly compressed)
pg_database_size(oid)	bigint	Specify the disk space used by the OID database
pg_database_size(name)	bigint	Disk space for specified name database
pg_indexes_size(regclass)	bigint	Use total disk space associated with table index of specified table OID or table name
pg_relation_size(relation regclass, fork text)	bigint	Specify the table or index of OID or name by specifying fork(‘main‘, Disk space used by’fsm’or’vm’
pg_relation_size(relation regclass)	bigint	Abbreviation for pg_relation_size(…,’main’)
pg_size_pretty(bigint)	text	Converts a size in bytes expressed as a 64-bit integer into a human-readable format with size units
pg_size_pretty(numeric)	text	Converting a byte-based value into a human readable unit of size
pg_table_size(regclass)	bigint	Specify the disk space used by the table OID or table name, excluding the index (but containing TOAST, free space mapping, and visual mapping)
pg_tablespace_size(oid)	bigint	Specify the disk space used by the table space of the OID
pg_tablespace_size(name)	bigint	Disk space used by the specified name table space
pg_total_relation_size(regclass)	bigint	Specify the total disk space used by the table OID or table name, including all indexes and TOAST data