Amazon RedShift : How to find Database size

Question

There have been many google results which answer this. However, none seemed to work for me. Hence I am creating this question and answering it for my own future reference as well as for any one else who might reach this thread via Google.

Answer 1

Here is the Query:

select sum(mbytes)/1024, database from (
select trim(pgdb.datname) as Database,
trim(a.name) as Table, b.mbytes
from stv_tbl_perm a
join pg_database as pgdb on pgdb.oid = a.db_id
join (select tbl, count(*) as mbytes
from stv_blocklist group by tbl) b on a.id=b.tbl
where a.slice=0
order by db_id, name)
group by database;

Output:

?column? |   database
----------+---------------
       62 | db1
       33 | db2
       33 | db3
        2 | db4
       37 | db5
       34 | db6
       35 | db7
       59 | db8
        2 | db9
       26 | db10
        2 | db11
       72 | db12
       36 | db13
       41 | db14

Note: Above numbers are in GB

Answer 2

When looking for disk space usage for tables, use this query:

select
    trim(pgdb.datname) as Database,
    trim(pgn.nspname) as Schema,
    trim(a.name) as Table,
    b.mbytes,
    a.rows
from (
    select db_id, id, name, sum(rows) as rows
    from stv_tbl_perm a
    group by db_id, id, name
) as a
join pg_class as pgc on pgc.oid = a.id
join pg_namespace as pgn on pgn.oid = pgc.relnamespace
join pg_database as pgdb on pgdb.oid = a.db_id
join (
    select tbl, count(*) as mbytes
    from stv_blocklist
    group by tbl
) b on a.id = b.tbl
order by mbytes desc, a.db_id, a.name;