Here are some SQL examples to get postgres database,table index sizes, all tested for 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_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

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_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 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"."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

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
----------------------+---------+---------------
 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

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
billing=# \l+                                               List of databases
     Name     |   Owner   | Encoding  | Collate | Ctype|Access privileges|  Size  | Tablespace |
--------------+-----------+-----------+---------+------+-----------------+--------+------------+
 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 |

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