0

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

1 Answer 1

2
 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.