How to find out sizes of PostgreSQL databases and tables

-- list databases and their sizes
SELECT datname, pg_database_size(datname)/1024/1024 AS MegaBytes FROM pg_database ORDER BY MegaBytes DESC; 

-- list tables, their sizes in MB (WITH and WITHOUT size of their toasted tables and indexes), and their index overhead
SELECT
 schemaname, tablename,
 (SELECT pg_total_relation_size(schemaname||'.'||tablename) AS Bytes)/1024/1024 AS MegaBytes_table_plus_all_indexes,
 (SELECT pg_relation_size(schemaname||'.'||tablename) AS Bytes)/1024/1024 AS MegaBytes_table_only,
 ROUND((
  (
   (SELECT pg_total_relation_size(schemaname||'.'||tablename))
   - (SELECT pg_relation_size(schemaname||'.'||tablename))
  )::NUMERIC / (
   SELECT pg_total_relation_size(schemaname||'.'||tablename)
  )
 )*100,2)||'%' AS overhead_all_indexes 
FROM pg_tables
ORDER BY MegaBytes_table_plus_all_indexes DESC;

-- indexes and their sizes, with tables and their sizes
SELECT
 schemaname, tablename,
 (SELECT pg_total_relation_size(schemaname||'.'||tablename))/1024/1024 AS megabytes_table_plus_all_indexes,
 (SELECT pg_relation_size(schemaname||'.'||tablename))/1024/1024 AS megabytes_table_only,
 indexname,
 (
  (SELECT pg_total_relation_size(schemaname||'.'||tablename))
  - (SELECT pg_relation_size(schemaname||'.'||tablename))
 )/1024/1024 AS megabytes_all_indexes,
 ROUND((
  (
   (SELECT pg_total_relation_size(schemaname||'.'||tablename))
   - (SELECT pg_relation_size(schemaname||'.'||tablename))
  )::NUMERIC / (
   SELECT pg_total_relation_size(schemaname||'.'||tablename)
  )
 )*100,2)||'%' AS percentage_all_indexes, 
 (SELECT pg_total_relation_size(schemaname||'.'||"indexname"))/1024/1024 AS megabytes_index,
 ROUND((
  (
   SELECT pg_total_relation_size(schemaname||'.'||"indexname")
  )::NUMERIC / (
   SELECT pg_total_relation_size(schemaname||'.'||tablename)
  )
 )*100,2)||'%' AS percentage_index
FROM pg_indexes
-- HACK: there is probably bug in function pg_total_relation_size - does not work, if name contains uppercase characters
WHERE indexname = LOWER(indexname)
ORDER BY megabytes_all_indexes DESC, schemaname, tablename, megabytes_index DESC;

SQL scripts above inspired by these articles:

http://www.thegeekstuff.com/2009/05/15-advanced-postgresql-commands-with-examples

http://www.appdesign.com/blog/2009/05/07/list-of-postres-tables-with-their-sizes-and-indexes/

http://www.postgresonline.com/journal/archives/110-Determining-size-of-database,-schema,-tables,-and-geometry.html

http://archives.postgresql.org/pgsql-admin/2008-07/msg00197.php

http://archives.postgresql.org/pgsql-sql/2005-03/msg00351.php

http://blog.marcua.net/post/65207426/determining-postgres-page-size