Redshift Disk Space Monitoring

This is part 2 of a series on Amazon Redshift maintenance:

1. Vacuuming for Table Performance
2. Monitoring Disk Space
3. Monitoring Query Performance
4. Column Compression

Why monitor disk space?

Redshift requires free space on your cluster to create temporary tables during query execution. When space becomes tight, your query performance can take a hit.

Besides the performance hit, vacuuming operations also require free space during the rebalancing operation.

You'll also want to keep an eye on disk space for capacity planning purposes.

SQL to analyze disk space usage for the cluster

Besides looking up disk space usage through CloudWatch, you can also query Redshift directly via SQL:

select
    cast(use.usename as varchar(50)) as Owner, 
    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
left join pg_user use on (pgc.relowner = use.usesysid)
join pg_namespace as pgn on pgn.oid = pgc.relnamespace
    and pgn.nspowner > 1
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; 

Next Generation SQL Data Analysis Write SQL, visualize data, and share your results. Onboarding forward-thinking teams now:

No spam, ever! Unsubscribe any time. Learn more about the product.