Amazon Redshift creates the SVV_DISKUSAGE system view by joining the STV_TBL_PERM and STV_BLOCKLIST tables. The SVV_DISKUSAGE view contains information about data allocation for the tables in a database.
Use aggregate queries with SVV_DISKUSAGE, as the following examples show, to determine the number of disk blocks allocated per database, table, slice, or column. Each data block uses 1 MB. You can also use STV_PARTITIONS to view summary information about disk utilization.
SVV_DISKUSAGE is visible only to superusers. For more information, see Visibility of Data in System Tables and Views.
|Column Name||Data Type||Description|
|slice||integer||Data slice allocated to the table.|
|col||integer||Zero-based index for the column. Every table you create has three hidden columns appended to it: INSERT_XID, DELETE_XID, and ROW_ID (OID). A table with 3 user-defined columns contains 6 actual columns, and the user-defined columns are internally numbered as 0, 1, and 2. The INSERT_XID, DELETE_XID, and ROW_ID columns are numbered 3, 4, and 5, respectively, in this example.|
|blocknum||integer||ID for the data block.|
|num_values||integer||Number of values contained on the block.|
|minvalue||bigint||Minimum value contained on the block.|
|maxvalue||bigint||Maximum value contained on the block.|
|sb_pos||integer||Internal identifier for the position of the super block on disk.|
|pinned||integer||Whether or not the block is pinned into memory as part of pre-load. 0 = false; 1 = true. Default is false.|
|on_disk||integer||Whether or not the block is automatically stored on disk. 0 = false; 1 = true. Default is false.|
|modified||integer||Whether or not the block has been modified. 0 = false; 1 = true. Default is false.|
|hdr_modified||integer||Whether or not the block header has been modified. 0 = false; 1 = true. Default is false.|
|unsorted||integer||Whether or not a block is unsorted. 0 = false; 1 = true. Default is true.|
|tombstone||integer||For internal use.|
|preferred_diskno||integer||Disk number that the block should be on, unless the disk has failed. Once the disk has been fixed, the block will move back to this disk.|
|temporary||integer||Whether or not the block contains temporary data, such as from a temporary table or intermediate query results. 0 = false; 1 = true. Default is false.|
|newblock||integer||Indicates whether or not a block is new (true) or was never committed to disk (false). 0 = false; 1 = true.|
SVV_DISKUSAGE contains one row per allocated disk block, so a query that selects all the rows potentially returns a very large number of rows. We recommend using only aggregate queries with SVV_DISKUSAGE.
Return the highest number of blocks ever allocated to column 6 in the USERS table (the EMAIL column):
select db_id, trim(name) as tablename, max(blocknum) from svv_diskusage where name='users' and col=6 group by db_id, name; db_id | tablename | max --------+-----------+----- 175857 | users | 2 (1 row)
The following query returns similar results for all of the columns in a large 10-column table called SALESNEW. (The last three rows, for columns 10 through 12, are for the hidden metadata columns.)
select db_id, trim(name) as tablename, col, tbl, max(blocknum) from svv_diskusage where name='salesnew' group by db_id, name, col, tbl order by db_id, name, col, tbl; db_id | tablename | col | tbl | max --------+------------+-----+--------+----- 175857 | salesnew | 0 | 187605 | 154 175857 | salesnew | 1 | 187605 | 154 175857 | salesnew | 2 | 187605 | 154 175857 | salesnew | 3 | 187605 | 154 175857 | salesnew | 4 | 187605 | 154 175857 | salesnew | 5 | 187605 | 79 175857 | salesnew | 6 | 187605 | 79 175857 | salesnew | 7 | 187605 | 302 175857 | salesnew | 8 | 187605 | 302 175857 | salesnew | 9 | 187605 | 302 175857 | salesnew | 10 | 187605 | 3 175857 | salesnew | 11 | 187605 | 2 175857 | salesnew | 12 | 187605 | 296 (13 rows)