Show size of tables and other database objects in Postgres

Question

I need to clear disk space, so I want to see which tables and other objects are using the most disk space in my Postgres databases.

Ideally, I'd like to see: database name, schema name, object type, object name and size in GB/MB. I'm imagining something like the du -h command or the ncdu utility but for Postgres.

Is there a command to do this in psql? I know about \l+ {database name} but this just gives me the size of the database, not the objects within it.

Answer 1

This is the hit list of the largest objects in your database:

SELECT oid::regclass,
       pg_relation_size(oid),
       relkind
FROM pg_class
ORDER BY 2 DESC;