PostgreSQL DBA Snippet: Largest Tables in a Database

18May/100

PostgreSQL DBA Snippet: Largest Tables in a Database

For my own reference - this little query will list all the tables in the current database with their respective physical size, including indexes and TOAST:

SELECT table_schema
       , TABLE_NAME
       , pg_size_pretty(pg_total_relation_size(table_schema || '.' || TABLE_NAME))
      , pg_total_relation_size(table_schema || '.' || TABLE_NAME)
FROM information_schema.TABLES
WHERE table_type = 'BASE TABLE' 
ORDER BY pg_total_relation_size DESC;