DBA SQL Tips

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 :

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;

Table Wise Query info 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 mbytes desc

Slice Wise Query in GB

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

# Ask Any Doubts Queries in below Comments #

Amazon Redshift Database Disk usage Query | Storage | Table wise | Node | Slice