Amazon Redshift – How to get the sizes of all tables

· by

This is another useful script, in the series of Redshift related tools and scripts that I find very useful. The following query gives you all tables and their row count and disk size on a given database in Redshift cluster. You can tweak it and customize more as per your requirements.

Important attributes to remember here are:

  • pg_database.datname is the databasename
  • stv_tbl_perm.name is table name. Put appropriate filters if you want to see the size of just one table
  • pg_namespace.nspname is the schema name. Put appropriate filters if you want to see the details of one particular schema

SELECT CAST(use2.usename AS VARCHAR(50)) AS OWNER
 ,TRIM(pgdb.datname) AS DATABASE
 ,TRIM(pgn.nspname) AS SCHEMA
 ,TRIM(a.NAME) AS TABLE
 ,(b.mbytes) / 1024 AS Gigabytes
 ,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 use2 ON (pgc.relowner = use2.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
WHERE pgdb.datname = 'mydb'
ORDER BY mbytes DESC
 ,a.db_id
 ,a.NAME;

 

 

Leave a comment