How can I get a list of tables in my CartoDB account by size?

Question

The CartoDB dashboard only sorts tables by date modified or created. I would like a list of tables sorted by size.

Answer 1

 SELECT cdb_usertables,
        pg_size_pretty(pg_total_relation_size(cdb_usertables))
 FROM CDB_UserTables()
 ORDER BY pg_total_relation_size(cdb_usertables) DESC

The CDB_UserTables() function is mentioned in another question regarding getting a list of all tables. It returns one column named after the function.

pg_total_relation_size() "accepts the OID or name of a table or toast table, and returns the size in bytes of the data and all associated indexes and toast tables."

"pg_size_pretty can be used to format the result of one of the other functions in a human-readable way, using kB, MB, GB or TB as appropriate."

Source: http://www.postgresql.org/docs/8.1/static/functions-admin.html

EDIT: This no longer works, because CDB_UserTables() function returns table names that don't exist, and also because the table names are not returned as text. CartoDB support proposes using the table CDB_TableMetadata instead.

SELECT tabname, pg_size_pretty(pg_total_relation_size(tabname))
FROM CDB_TableMetadata
ORDER BY pg_total_relation_size(tabname) DESC