how to find size of database, schema, table in redshift

Question

Team,

my redshift version is:

PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.735 

how to find out database size, tablespace, schema size & table size ?

but below are not working in redshift ( for above version )

SELECT pg_database_size('db_name'); SELECT pg_size_pretty( pg_relation_size('table_name') ); 

Is there any alternate to find out like oracle ( from DBA_SEGMENTS )

for tble size, i have below query, but not sure about exact menaing of MBYTES. FOR 3rd row, MBYTES = 372. it means 372 MB ?

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 a.db_id, a.name;    database    |    schema    |      table       | mbytes |   rows ---------------+--------------+------------------+--------+----------       postgres | public       | company          |      8 |        1       postgres | public       | table_data1_1    |      7 |        1       postgres | proj_schema1 | table_data1    |    372 | 33867540       postgres | public       | table_data1_2    |     40 |  2000001  (4 rows) 

Answer 1

The above answers don't always give correct answers for table space used. AWS support have given this query to use:

SELECT   TRIM(pgdb.datname) AS Database,          TRIM(a.name) AS Table,          ((b.mbytes/part.total::decimal)*100)::decimal(5,2) AS pct_of_total,          b.mbytes,          b.unsorted_mbytes FROM     stv_tbl_perm a JOIN     pg_database AS pgdb   ON     pgdb.oid = a.db_id JOIN     ( SELECT   tbl,                     SUM( DECODE(unsorted, 1, 1, 0)) AS unsorted_mbytes,                     COUNT(*) AS mbytes            FROM     stv_blocklist            GROUP BY tbl ) AS b        ON a.id = b.tbl JOIN     ( SELECT SUM(capacity) AS total            FROM   stv_partitions            WHERE  part_begin = 0 ) AS part       ON 1 = 1 WHERE    a.slice = 0 ORDER BY 4 desc, db_id, name;