PostgreSQL check DB, Tablespace, Table, Index Size

PostgreSQL有內建的function可以view各種的Size

--check the tablespace size

SELECT spcname,

pg_size_pretty(pg_tablespace_size(spcname)) as size,

pg_tablespace_size(spcname) as for_sort

FROM pg_tablespace

ORDER BY pg_tablespace_size(spcname) DESC;

--check the database size

SELECT datname,

pg_size_pretty(pg_database_size(datname)) as size,

pg_database_size(datname) as for_sort

FROM pg_database

ORDER BY pg_database_size(datname) DESC;

--check the object size

SELECT relname,reltuples,

pg_size_pretty(pg_relation_size(oid)) as size,

pg_relation_size(oid) as for_sort

FROM pg_class

WHERE relkind IN ('r') --public table

ORDER BY pg_relation_size(oid) DESC;

--check table include index size

SELECT relname,reltuples,

pg_size_pretty(pg_total_relation_size(oid)) as size,

pg_total_relation_size(oid) as for_sort

FROM pg_class

WHERE relkind IN ('r') --public table

ORDER BY pg_total_relation_size(oid) DESC;