How can I find out the size of each column in a Redshift table?

Question

While trying out different compression settings in Redshift it would be very useful to know the size of each column. I know how to get the size of a table, but I want to know the size of each individual column in that table.

Answer 1

This query will give you the size (MB) of each column. What it does is that it counts the number of data blocks, where each block uses 1 MB, grouped by table and column.

SELECT
  TRIM(name) as table_name,
  TRIM(pg_attribute.attname) AS column_name,
  COUNT(1) AS size
FROM
  svv_diskusage JOIN pg_attribute ON
    svv_diskusage.col = pg_attribute.attnum-1 AND
    svv_diskusage.tbl = pg_attribute.attrelid
GROUP BY 1, 2

You can read more about the two tables involved in the query here: SVV_DISKUSAGE & pg_attribute.

Answer 2

A more accurate size of the table would include the hidden system columns deletexid, insertxid, oid (ROW ID), as well. One of my tables was using 752 blocks without including the hidden columns. When i added the hidden columns, it went upto 1063 blocks.

SELECT col, attname, COUNT(*) AS "mbs"
FROM stv_blocklist bl
JOIN stv_tbl_perm perm
  ON bl.tbl = perm.id AND bl.slice = perm.slice    
LEFT JOIN pg_attribute attr ON
  attr.attrelid = bl.tbl
  AND attr.attnum-1 = bl.col  
WHERE perm.name = '<TABLE-NAME>'  
GROUP BY col, attname
ORDER BY col;