1. 查看当前库sehcma大小,并按schema大小排序
SELECT schema_name, pg_size_pretty(sum(table_size)::bigint) as "disk space", round((sum(table_size) / pg_database_size(current_database())) * 100,2) as "percent(%)" FROM ( SELECT pg_catalog.pg_namespace.nspname as schema_name, pg_total_relation_size(pg_catalog.pg_class.oid) as table_size FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid ) t GROUP BY schema_name ORDER BY "percent(%)" desc;
schema_name | disk space | percent(%) --------------------------------------------------+------------+------------ here_eu_20q2_f_de82ee69_adr_db | 670 GB | 14.49 here_eu_19q4_f_65b7e619_adr_db | 636 GB | 13.74 here_eu_20q1_f_2b691ad6_adr_db | 634 GB | 13.70 pg_toast | 569 GB | 12.30 here_eu_19q3_f_03d551cd_adr_db | 537 GB | 11.61 here_na_20q1_f_17dde91e_adr_db | 526 GB | 11.38 here_na_20q2_f_77bf295a_adr_db | 517 GB | 11.18 here_na_19q4_f_77962109_adr_db_20191125_021513 | 493 GB | 10.65 here_na_19q3_f_4cecd456_adr_db | 257 GB | 5.55 here_eu_20q2_f_de82ee69_adr_work | 115 GB | 2.49 here_eu_20q1_f_2b691ad6_adr_work | 104 GB | 2.24 here_na_19q4_f_77962109_adr_db | 103 GB | 2.22 here_eu_19q4_f_65b7e619_adr_work | 95 GB | 2.05 here_sa_20q2_f_4fd08908_adr_db | 60 GB | 1.30 here_sa_20q1_f_10c809ef_adr_db | 58 GB | 1.24 here_sa_19q4_f_52015438_adr_db | 56 GB | 1.20 here_sa_19q3_f_58fa5e4b_adr_db | 52 GB | 1.13 here_na_20q2_f_77bf295a_adr_work | 50 GB | 1.08 here_na_19q4_f_77962109_adr_work | 48 GB | 1.04 here_na_20q1_f_17dde91e_adr_work | 44 GB | 0.95 here_na_19q3_f_4cecd456_adr_work | 43 GB | 0.94 here_na_19q4_f_77962109_adr_work_20191125_021513 | 44 GB | 0.94 here_eu_19q3_f_03d551cd_adr_work | 28 GB | 0.60 here_anz_20q2_f_8beb527d_adr_db | 23 GB | 0.51 here_anz_20q1_f_e3fd4896_adr_db | 23 GB | 0.49 here_anz_19q3_f_ce373b15_adr_db | 22 GB | 0.48 here_mea_20q2_f_4c01689d_adr_db | 20 GB | 0.43 here_mea_20q1_f_9c9b1589_adr_db | 19 GB | 0.42
3. 查看当前库中所有表大小,并按降序排列
SELECT table_catalog AS database_name, table_schema AS schema_name, table_name, pg_size_pretty(relation_size) AS table_size FROM ( SELECT table_catalog, table_schema, table_name, pg_total_relation_size(('"' || table_schema || '"."' || table_name || '"')) AS relation_size FROM information_schema.tables WHERE table_schema not in ('pg_catalog', 'public', 'public_rb', 'topology', 'tiger', 'tiger_data', 'information_schema') ORDER BY relation_size DESC ) AS all_tables WHERE relation_size >= 1073741824;
database_name | schema_name | table_name | table_size -----------------+--------------------------------------------------+-------------------------------------------+------------ TnGeo-Here-Data | here_na_20q2_f_77bf295a_adr_db | usa_base_address_point_view | 46 GB TnGeo-Here-Data | here_na_20q1_f_17dde91e_adr_db | usa_base_address_point_view | 46 GB TnGeo-Here-Data | here_na_20q2_f_77bf295a_adr_db_200615_052043 | usa_shape_point | 38 GB TnGeo-Here-Data | here_na_20q2_f_77bf295a_adr_db | usa_shape_point | 38 GB TnGeo-Here-Data | here_na_20q1_f_17dde91e_adr_db | usa_shape_point | 38 GB TnGeo-Here-Data | here_na_19q4_f_77962109_adr_db_20200609_053311 | usa_shape_point | 38 GB TnGeo-Here-Data | here_na_19q3_f_4cecd456_adr_db | usa_shape_point | 37 GB TnGeo-Here-Data | here_na_20q2_f_77bf295a_adr_work | usa_addr_compact | 36 GB TnGeo-Here-Data | here_na_20q2_f_77bf295a_adr_work_200615_052043 | usa_addr_compact | 35 GB TnGeo-Here-Data | here_na_19q4_f_77962109_adr_work | usa_addr_compact | 35 GB TnGeo-Here-Data | here_na_19q4_f_77962109_adr_work_20200609_053311 | usa_addr_compact | 35 GB TnGeo-Here-Data | here_na_20q1_f_17dde91e_adr_db | usa_base_door_range_link_view | 34 GB TnGeo-Here-Data | here_na_20q2_f_77bf295a_adr_db | usa_base_door_range_link_view | 34 GB TnGeo-Here-Data | here_na_20q1_f_17dde91e_adr_work | usa_addr_compact | 33 GB TnGeo-Here-Data | here_na_19q3_f_4cecd456_adr_work | usa_addr_compact | 33 GB TnGeo-Here-Data | here_na_20q1_f_17dde91e_adr_db | usa_base_door_range_link_view_v1 | 32 GB TnGeo-Here-Data | here_na_20q2_f_77bf295a_adr_db | usa_base_door_range_link_view_v1 | 32 GB TnGeo-Here-Data | here_na_20q2_f_77bf295a_adr_db | usa_base_address_point_view_v1 | 30 GB TnGeo-Here-Data | here_na_20q1_f_17dde91e_adr_db | usa_base_address_point_view_v1 | 29 GB TnGeo-Here-Data | here_na_19q4_f_77962109_adr_db | usa_shape_point | 28 GB TnGeo-Here-Data | here_na_20q2_f_77bf295a_adr_db_200615_052043 | usa_address_point | 25 GB TnGeo-Here-Data | here_na_20q2_f_77bf295a_adr_db | usa_address_point | 25 GB TnGeo-Here-Data | here_na_20q1_f_17dde91e_adr_db | usa_address_point | 25 GB TnGeo-Here-Data | here_na_19q4_f_77962109_adr_db_20200609_053311 | usa_address_point | 24 GB TnGeo-Here-Data | here_na_19q3_f_4cecd456_adr_db | usa_address_point | 24 GB TnGeo-Here-Data | here_na_20q2_f_77bf295a_adr_db | usa_addr_point_range_timstamp_view | 20 GB TnGeo-Here-Data | here_na_20q2_f_77bf295a_adr_db | usa_addr_full_timstamp_view | 20 GB TnGeo-Here-Data | here_na_20q1_f_17dde91e_adr_db | usa_addr_point_range_timstamp_view | 20 GB TnGeo-Here-Data | here_na_20q1_f_17dde91e_adr_db | usa_addr_full_timstamp_view | 20 GB TnGeo-Here-Data | here_na_20q2_f_77bf295a_adr_db_200615_052043 | usa_final_address_point_range_view_static | 18 GB TnGeo-Here-Data | here_na_20q2_f_77bf295a_adr_db | usa_final_address_point_range_view_static | 18 GB TnGeo-Here-Data | here_na_20q1_f_17dde91e_adr_db | usa_final_address_point_range_view_static | 18 GB TnGeo-Here-Data | here_na_19q4_f_77962109_adr_db_20200609_053311 | usa_final_address_point_range_view_static | 18 GB TnGeo-Here-Data | here_na_19q3_f_4cecd456_adr_db | usa_final_address_point_range_view_static | 18 GB TnGeo-Here-Data | here_na_20q2_f_77bf295a_adr_db_200615_052043 | usa_address_point_name | 16 GB TnGeo-Here-Data | here_na_20q2_f_77bf295a_adr_db | usa_address_point_name | 16 GB TnGeo-Here-Data | here_na_20q1_f_17dde91e_adr_db | usa_address_point_name | 16 GB TnGeo-Here-Data | here_na_19q4_f_77962109_adr_db_20200609_053311 | usa_address_point_name | 16 GB TnGeo-Here-Data | here_na_19q3_f_4cecd456_adr_db | usa_address_point_name | 16 GB TnGeo-Here-Data | here_na_20q2_f_77bf295a_adr_db | usa_door_range | 16 GB TnGeo-Here-Data | here_na_20q2_f_77bf295a_adr_db_200615_052043 | usa_door_range | 16 GB TnGeo-Here-Data | here_na_20q1_f_17dde91e_adr_db | usa_door_range | 15 GB TnGeo-Here-Data | here_na_19q4_f_77962109_adr_db_20200609_053311 | usa_door_range | 15 GB TnGeo-Here-Data | here_na_19q3_f_4cecd456_adr_db | usa_door_range | 15 GB TnGeo-Here-Data | here_na_20q2_f_77bf295a_adr_db_200615_052043 | usa_final_address_full_view | 14 GB TnGeo-Here-Data | here_na_20q2_f_77bf295a_adr_db | usa_final_address_full_view | 14 GB TnGeo-Here-Data | here_na_20q1_f_17dde91e_adr_db | usa_final_address_full_view | 14 GB TnGeo-Here-Data | here_na_19q4_f_77962109_adr_db_20200609_053311 | usa_final_address_full_view | 14 GB TnGeo-Here-Data | here_na_19q3_f_4cecd456_adr_db | usa_final_address_full_view | 14 GB TnGeo-Here-Data | here_eu_20q2_f_de82ee69_adr_work | gbr_admin_center_compact | 13 GB TnGeo-Here-Data | here_eu_20q1_f_2b691ad6_adr_work | gbr_admin_center_compact | 12 GB TnGeo-Here-Data | here_eu_19q4_f_65b7e619_adr_work | gbr_admin_center_compact | 12 GB TnGeo-Here-Data | here_eu_19q3_f_03d551cd_adr_work | gbr_admin_center_compact | 12 GB TnGeo-Here-Data | here_eu_20q2_f_de82ee69_adr_work | fra_addr_compact | 12 GB TnGeo-Here-Data | here_na_19q4_f_77962109_adr_db | usa_address_point_name | 11 GB TnGeo-Here-Data | here_na_19q4_f_77962109_adr_db | usa_door_range | 11 GB TnGeo-Here-Data | here_eu_20q1_f_2b691ad6_adr_work | fra_addr_compact | 11 GB TnGeo-Here-Data | here_eu_19q4_f_65b7e619_adr_work | fra_addr_compact | 11 GB TnGeo-Here-Data | here_na_20q2_f_77bf295a_adr_db | usa_street_link_geohash | 11 GB TnGeo-Here-Data | here_na_20q2_f_77bf295a_adr_db_200615_052043 | usa_street_link_geohash | 11 GB TnGeo-Here-Data | here_na_20q1_f_17dde91e_adr_db | usa_street_link_geohash | 11 GB TnGeo-Here-Data | here_na_19q4_f_77962109_adr_db_20200609_053311 | usa_street_link_geohash | 11 GB
===================来自一泽涟漪的博客,转载请标明出处 www.cnblogs.com/ilifeilong===================
