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 |
Make DBA Life Easy 