[Postgres] Useful Management Queries

postgres_management.md

Size of the DB

SELECT pg_size_pretty(pg_database_size('postgres'));

Size of the table with indexes

select pg_size_pretty(pg_total_relation_size('tablename'));

Size of the index

select pg_size_pretty(pg_total_relation_size('indexname'));

List biggest relations on the DB with size

SELECT relname AS "relation", pg_size_pretty(pg_relation_size(C.oid)) AS "size"
  FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  ORDER BY pg_relation_size(C.oid) DESC
  LIMIT 10;