Redshift Disk Space Monitoring
This is part 2 of a series on Amazon Redshift maintenance:
- Vacuuming for Table Performance
- Monitoring Disk Space
- Monitoring Query Performance
- 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.