DB‎ > ‎Postgres‎ > ‎

Get PostgreSQL db, table and index sizes

Thank you for visiting this page, this page has been update in another link How to get postgresql db table and index sizes

To get DB sizes.

SELECT pg_database.datname, pg_database_size(pg_database.datname), pg_size_pretty(pg_database_size(pg_database.datname)) FROM pg_database ORDER BY pg_database_size DESC;
   datname    | pg_database_size | pg_size_pretty
--------------+------------------+----------------
 billing_2012 |     123630289720 | 115 GB
 billing      |     114415821624 | 107 GB
 billing_2011 |      76438053688 | 71 GB
 pgbench      |          6308664 | 6161 kB
 template1    |          6308664 | 6161 kB
 postgres     |          6308664 | 6161 kB
 template0    |          6201860 | 6057 kB

To get table and index sizes, tables and indexes are in separate rows

select nspname,relname,pg_size_pretty(pg_relation_size(c.oid)) as "size" from pg_class c left join pg_namespace n on ( n.oid=c.relnamespace) where nspname not in ('pg_catalog','information_schema')
chimera->  order by pg_relation_size(c.oid) desc limit 30;
 nspname |            relname             |  size  
---------+--------------------------------+---------
 public  | t_dirs                         | 4187 MB
 public  | t_locationinfo                 | 3874 MB
 public  | t_inodes                       | 3209 MB
 public  | t_level_2                      | 2859 MB
 public  | t_dirs_pkey                    | 2766 MB
 public  | t_locationinfo_pkey            | 2726 MB
 public  | t_acl                          | 2017 MB
 public  | i_dirs_iparent                 | 1856 MB
 public  | i_dirs_ipnfsid                 | 1833 MB
 public  | i_locationinfo_ipnfsid         | 1773 MB
 public  | t_inodes_checksum              | 1700 MB

To get table and index sizes, put table and indexes in one row, indexes number is a total number for one table

 SELECT                                                                                                                                                                  
    table_name,
    pg_size_pretty(table_size) AS table_size,
    pg_size_pretty(indexes_size) AS indexes_size,
    pg_size_pretty(total_size) AS total_size
FROM (
    SELECT
        table_name,
        pg_table_size(table_name) AS table_size,
        pg_indexes_size(table_name) AS indexes_size,
        pg_total_relation_size(table_name) AS total_size
    FROM (
        SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
        FROM information_schema.tables
    ) AS all_tables
    ORDER BY total_size DESC
) AS pretty_sizes limit 10;

           table_name            | table_size | indexes_size | total_size
---------------------------------+------------+--------------+------------
 "public"."doorinfo"             | 41 GB      | 12 GB        | 53 GB
 "public"."billinginfo"          | 30 GB      | 20 GB        | 50 GB
 "public"."billinginfo_tmp"      | 1328 MB    | 1174 MB      | 2502 MB
 "public"."storageinfo"          | 526 MB     | 379 MB       | 904 MB
 "public"."billinginfo_stats"    | 36 MB      | 21 MB        | 57 MB
 "public"."doorinfo_stats"       | 12 MB      | 8144 kB      | 20 MB
 "public"."storageinfo_stats"    | 7568 kB    | 10 MB        | 18 MB
 "pg_catalog"."pg_statistic"     | 1856 kB    | 40 kB        | 1896 kB
 "public"."billinginfo_tm_daily" | 1440 kB    | 16 kB        | 1456 kB
 "public"."billinginfo_rd_daily" | 1080 kB    | 16 kB        | 1096 kB

Another way, which includs external size

 SELECT
   relname as "Table",
   pg_size_pretty(pg_total_relation_size(relid)) As "Size",
   pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
   FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC limit 10;
        Table         |  Size   | External Size
----------------------+---------+---------------
 doorinfo             | 53 GB   | 12 GB
 billinginfo          | 50 GB   | 20 GB
 billinginfo_tmp      | 2502 MB | 1174 MB
 storageinfo          | 904 MB  | 379 MB
 billinginfo_stats    | 57 MB   | 21 MB
 doorinfo_stats       | 20 MB   | 8184 kB
 storageinfo_stats    | 18 MB   | 11 MB
 billinginfo_tm_daily | 1456 kB | 48 kB
 billinginfo_rd_daily | 1096 kB | 48 kB
 billinginfo_wr_daily | 888 kB  | 48 kB

External Size – The size that related objects of this table like indices take, so called toast data

If you only want to have a quick and estimated numbers, here are them

For dbs, to get DB size
billing=# \l+
                                                                 List of databases
     Name     |   Owner   | Encoding  | Collate | Ctype |   Access privileges   |  Size   | Tablespace |                Description                
--------------+-----------+-----------+---------+-------+-----------------------+---------+------------+--------------------------------------------
 billing      | srmdcache | SQL_ASCII | C       | C     |                       | 107 GB  | pg_default |
 billing_2011 | srmdcache | SQL_ASCII | C       | C     |                       | 71 GB   | pg_default |
 billing_2012 | srmdcache | SQL_ASCII | C       | C     |                       | 115 GB  | pg_default |

For tables
\d+





Comments