Amazon Redshift Database is bit tricky compared to other conventional database(s) as the disk usage is based on Nodes. Nodes are further divided into slices. Typically a node has 160 GB equally distributed among slices but can vastly vary depending Compute Cluster opted. Below are queries to help find out the disk usage :
Table Wise Query info MB
Slice Wise Query in GB
# Ask Any Doubts Queries in below Comments #
Database Disk Size usage Query in GB
select sum(capacity)/1024 as capacity_gbytes, sum(used)/1024 as used_gbytes, (sum(capacity) - sum(used))/1024 as free_gbytes from stv_partitions where part_begin=0;
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
SELECT node, stv_blocklist.slice, ROUND((COUNT(*) / 1024.0),2) as "Size in GB" FROM stv_blocklist INNER JOIN SVV_TABLE_INFO on SVV_TABLE_INFO.table_id = stv_blocklist.tbl INNER JOIN stv_slices on stv_slices.slice = stv_blocklist.slice WHERE database = current_database() GROUP BY node, stv_blocklist.slice order by node asc
No comments:
Post a Comment