{"id":1095,"date":"2024-02-17T11:16:27","date_gmt":"2024-02-17T16:16:27","guid":{"rendered":"https:\/\/jasonralph.org\/?p=1095"},"modified":"2024-02-20T10:29:10","modified_gmt":"2024-02-20T15:29:10","slug":"redshift-serverless-find-largest-tables","status":"publish","type":"post","link":"https:\/\/jasonralph.org\/?p=1095","title":{"rendered":"Redshift Serverless Find Largest Tables"},"content":{"rendered":"<p>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. <\/p>\n<p>1MB data blocks:<\/p>\n<pre class=\"theme:solarized-dark lang:default decode:true \" >\r\n(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; \r\n-[ RECORD 1 ]------------+----------------------------------------\r\ndatabase                 | redshift-sl-prod\r\nschema                   | qa\r\ntable_id                 | 1791319\r\ntable                    | transactions\r\nencoded                  | Y, AUTO(ENCODE)\r\ndiststyle                | AUTO(EVEN)\r\nsortkey1                 | AUTO(SORTKEY)\r\nmax_varchar              | 16383\r\nsortkey1_enc             | \r\nsortkey_num              | 0\r\nsize                     | 9102740\r\npct_used                 | 0.4444\r\nempty                    | 0\r\nunsorted                 | \r\nstats_off                | 0.00\r\ntbl_rows                 | 91509652251\r\nskew_sortkey1            | \r\nskew_rows                | \r\nestimated_visible_rows   | 91509653504\r\nrisk_event               | \r\nvacuum_sort_benefit      | \r\ncreate_time              | 2023-11-15 01:53:19.524329\r\nsize_in_1_mb_data_blocks | 9102740\r\n\r\nTime: 6995.168 ms (00:06.995)\r\n<\/pre>\n<p>Size In TB:<\/p>\n<pre class=\"theme:solarized-dark lang:default decode:true \" >\r\n(wg-redshift) admin@redshift-sl-prod=# select *, size\/(1024*1024) size_in_tb from svv_table_info order by pct_used desc limit 1; \r\n-[ RECORD 1 ]----------+----------------------------------------\r\ndatabase               | redshift-sl-prod\r\nschema                 | qa\r\ntable_id               | 1791319\r\ntable                  | transactions\r\nencoded                | Y, AUTO(ENCODE)\r\ndiststyle              | AUTO(EVEN)\r\nsortkey1               | AUTO(SORTKEY)\r\nmax_varchar            | 16383\r\nsortkey1_enc           | \r\nsortkey_num            | 0\r\nsize                   | 9102740\r\npct_used               | 0.4444\r\nempty                  | 0\r\nunsorted               | \r\nstats_off              | 0.00\r\ntbl_rows               | 91509652251\r\nskew_sortkey1          | \r\nskew_rows              | \r\nestimated_visible_rows | 91509653504\r\nrisk_event             | \r\nvacuum_sort_benefit    | \r\ncreate_time            | 2023-11-15 01:53:19.524329\r\nsize_in_tb             | 8\r\n\r\nTime: 6763.316 ms (00:06.763)\r\n<\/pre>\n<p>Specific Fields:<\/p>\n<pre class=\"theme:solarized-dark lang:default decode:true \" >\r\n(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;\r\n-[ RECORD 1 ]---------------------------------------\r\ndatabase   | redshift-sl-prod\r\nschema     | seamqa\r\ntable      | transactions\r\nsize_in_mb | 9102740\r\ntbl_rows   | 91509652251\r\nsize_in_tb | 8\r\n\r\nTime: 6202.359 ms (00:06.202)\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>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: (wg-redshift) admin@redshift-sl-prod=# select *, size size_in_1_MB_data_blocks [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[82,139,50,138,135,137,136],"class_list":["post-1095","post","type-post","status-publish","format-standard","hentry","category-general-code","tag-find","tag-largest","tag-redshift","tag-relations","tag-serverless","tag-table","tag-tables"],"_links":{"self":[{"href":"https:\/\/jasonralph.org\/index.php?rest_route=\/wp\/v2\/posts\/1095","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jasonralph.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jasonralph.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jasonralph.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jasonralph.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1095"}],"version-history":[{"count":8,"href":"https:\/\/jasonralph.org\/index.php?rest_route=\/wp\/v2\/posts\/1095\/revisions"}],"predecessor-version":[{"id":1103,"href":"https:\/\/jasonralph.org\/index.php?rest_route=\/wp\/v2\/posts\/1095\/revisions\/1103"}],"wp:attachment":[{"href":"https:\/\/jasonralph.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1095"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jasonralph.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1095"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jasonralph.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1095"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}