Upgraded Postgres to 9.4 recently, want to check if my handy SQLs still works for pg9.4, Here is the one to get postgres database,table index sizes, actually it these SQLs still work since postgres 9.1, 9.2, and 9.3.

 

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_2014 | 271028134404 | 252 GB
billing_2013 | 138678444548 | 129 GB
chimera | 110959308564 | 103 GB
dcache | 8466196 | 8268 kB
replicas | 7589652 | 7412 kB
template1 | 6967060 | 6804 kB
postgres | 6967060 | 6804 kB
pgbench | 6967060 | 6804 kB
billing_2015 | 6849028 | 6689 kB
template0 | 6840836 | 6681 k

To get table and index sizes

Show tables and indexes 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')
order by pg_relation_size(c.oid) desc limit 30;
 nspname |          relname          |  size   
---------+---------------------------+---------
 public  | t_acl                     | 11 GB
 public  | t_dirs                    | 9817 MB
 public  | t_inodes                  | 9276 MB
 public  | t_tags                    | 7218 MB
 public  | t_dirs_pkey               | 6068 MB
 public  | t_acl_pkey                | 5849 MB
 public  | i_t_acl_rs_id             | 5090 MB
 public  | t_locationinfo            | 4966 MB
 public  | t_tags_pkey               | 4885 MB
 public  | i_dirs_iparent            | 4666 MB
 public  | i_dirs_ipnfsid            | 4666 MB
 public  | t_level_2                 | 4260 MB
 public  | t_locationinfo_pkey       | 3589 MB
 public  | i_tags_itagid             | 3243 MB
 public  | t_inodes_pkey             | 2741 MB
 public  | t_inodes_checksum         | 2544 MB
 public  | t_access_latency          | 2306 MB
 public  | t_retention_policy        | 2306 MB
 public  | i_locationinfo_ipnfsid    | 2238 MB
 public  | t_access_latency_pkey     | 1779 MB
 public  | t_inodes_checksum_pkey    | 1779 MB
 public  | t_retention_policy_pkey   | 1779 MB
 public  | t_level_2_pkey            | 1779 MB
 public  | t_storageinfo             | 454 MB
 public  | t_locationinfo_trash      | 331 MB
 public  | t_storageinfo_pkey        | 317 MB
 public  | t_locationinfo_trash_pkey | 278 MB
 public  | t_level_1                 | 10 MB
 public  | t_locationinfo_tmp        | 3400 kB
 public  | t_level_1_pkey            | 3264 kB

To get table and index sizes in one row

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"."t_dirs"             | 9817 MB    | 15 GB        | 25 GB
 "public"."t_acl"              | 11 GB      | 11 GB        | 22 GB
 "public"."t_tags"             | 7218 MB    | 8128 MB      | 15 GB
 "public"."t_inodes"           | 9276 MB    | 2741 MB      | 12 GB
 "public"."t_locationinfo"     | 4966 MB    | 5827 MB      | 11 GB
 "public"."t_level_2"          | 4260 MB    | 1779 MB      | 6039 MB
 "public"."t_inodes_checksum"  | 2544 MB    | 1779 MB      | 4323 MB
 "public"."t_access_latency"   | 2306 MB    | 1779 MB      | 4084 MB
 "public"."t_retention_policy" | 2306 MB    | 1779 MB      | 4084 MB
 "public"."t_storageinfo"      | 454 MB     | 317 MB       | 771 MB

Get table, index sizes, including 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
--------------------+---------+---------------
 t_dirs             | 25 GB   | 15 GB
 t_acl              | 22 GB   | 11 GB
 t_tags             | 15 GB   | 8128 MB
 t_inodes           | 12 GB   | 2741 MB
 t_locationinfo     | 11 GB   | 5827 MB
 t_level_2          | 6039 MB | 1779 MB
 t_inodes_checksum  | 4323 MB | 1779 MB
 t_access_latency   | 4084 MB | 1779 MB
 t_retention_policy | 4084 MB | 1779 MB
 t_storageinfo      | 771 MB  | 317 MB

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

Quick and eEstimated number for DB, table sizes

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

For dbs, to get DB size
chimera=# \l+
                                                                 List of databases
     Name     |   Owner   | Encoding  | Collate | Ctype |   Access privileges   |  Size   | Tablespace |                Description                 
--------------+-----------+-----------+---------+-------+-----------------------+---------+------------+--------------------------------------------
 billing_2013 | srmdcache | SQL_ASCII | C       | C     |                       | 129 GB  | pg_default |
 billing_2014 | srmdcache | SQL_ASCII | C       | C     |                       | 252 GB  | pg_default |
 billing_2015 | srmdcache | SQL_ASCII | C       | C     |                       | 6689 kB | pg_default |

To get estimated number of table size

\d+

What about column? What to check how much space a column takes in a table? see  How to estimate the size of one column size in postgres table