You can use the below SQL on redshift serverless to find the top largest tables. You can return the results in 1MB data blocks or convert to TB. You can obviously change the limit N to whatever you want to change the number of results returned.
1MB data blocks:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
(wg-redshift) admin@redshift-sl-prod=# select *, size size_in_1_MB_data_blocks from svv_table_info order by pct_used desc limit 1; -[ RECORD 1 ]------------+---------------------------------------- database | redshift-sl-prod schema | qa table_id | 1791319 table | transactions encoded | Y, AUTO(ENCODE) diststyle | AUTO(EVEN) sortkey1 | AUTO(SORTKEY) max_varchar | 16383 sortkey1_enc | sortkey_num | 0 size | 9102740 pct_used | 0.4444 empty | 0 unsorted | stats_off | 0.00 tbl_rows | 91509652251 skew_sortkey1 | skew_rows | estimated_visible_rows | 91509653504 risk_event | vacuum_sort_benefit | create_time | 2023-11-15 01:53:19.524329 size_in_1_mb_data_blocks | 9102740 Time: 6995.168 ms (00:06.995) |
Size In TB:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
(wg-redshift) admin@redshift-sl-prod=# select *, size/(1024*1024) size_in_tb from svv_table_info order by pct_used desc limit 1; -[ RECORD 1 ]----------+---------------------------------------- database | redshift-sl-prod schema | qa table_id | 1791319 table | transactions encoded | Y, AUTO(ENCODE) diststyle | AUTO(EVEN) sortkey1 | AUTO(SORTKEY) max_varchar | 16383 sortkey1_enc | sortkey_num | 0 size | 9102740 pct_used | 0.4444 empty | 0 unsorted | stats_off | 0.00 tbl_rows | 91509652251 skew_sortkey1 | skew_rows | estimated_visible_rows | 91509653504 risk_event | vacuum_sort_benefit | create_time | 2023-11-15 01:53:19.524329 size_in_tb | 8 Time: 6763.316 ms (00:06.763) |
Specific Fields:
|
1 2 3 4 5 6 7 8 9 10 |
(wg-redshift) admin@redshift-sl-prod=# select database, schema, "table", size size_in_mb, tbl_rows, size/(1024*1024) size_in_tb from svv_table_info order by pct_used desc limit 1; -[ RECORD 1 ]--------------------------------------- database | redshift-sl-prod schema | seamqa table | transactions size_in_mb | 9102740 tbl_rows | 91509652251 size_in_tb | 8 Time: 6202.359 ms (00:06.202) |