postgres queries to check disk usage

pg_disk_usage.sql

-- pg equivalent of DESCRIBE TABLE

select column_name, data_type, character_maximum_length
from INFORMATION_SCHEMA.COLUMNS where table_name = 'my_table_name';

-- General Table Size Information, Performance Snippets
-- Disk usage, Works with PostgreSQL>=9.2
-- This will report size information for all tables, in both raw bytes and "pretty" form.

SELECT *, pg_size_pretty(total_bytes) AS total
    , pg_size_pretty(index_bytes) AS INDEX
    , pg_size_pretty(toast_bytes) AS toast
    , pg_size_pretty(table_bytes) AS TABLE
  FROM (
  SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
      SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
              , c.reltuples AS row_estimate
              , pg_total_relation_size(c.oid) AS total_bytes
              , pg_indexes_size(c.oid) AS index_bytes
              , pg_total_relation_size(reltoastrelid) AS toast_bytes
          FROM pg_class c
          LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE relkind = 'r'
  ) a
) a;

-- Finding the largest databases in your cluster
-- Works with PostgreSQL>=8.2
-- Databases to which the user cannot connect are sorted as if they were infinite size.

SELECT d.datname AS Name,  pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
        ELSE 'No Access'
    END AS SIZE
FROM pg_catalog.pg_database d
    ORDER BY
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_database_size(d.datname)
        ELSE NULL
    END DESC -- nulls first
    LIMIT 20

-- Finding the size of your biggest relations
-- Works with PostgreSQL>=8.1

-- Relations are objects in the database such as tables and indexes, and this query shows the size of all the individual parts.
-- Tables which have both regular -- and TOAST pieces will be broken out into separate components;
-- an example showing how you might include those into the main total is available in the documentation,
-- and as of PostgreSQL 9.0 it is possible to include it automatically by using pg_table_size here instead of pg_relation_size:

-- Note that all of the queries below this point on this page show you the sizes for only those objects
-- which are in the database you are currently connected to.

SELECT nspname || '.' || 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 20;


--Finding the total size of your biggest tables
--This version of the query uses pg_total_relation_size,
-- which sums total disk space used by the table including indexes
-- and toasted data rather than breaking out the individual pieces:

SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
  ORDER BY pg_total_relation_size(C.oid) DESC
  LIMIT 20;

--Sizes before 8.1
--The pg_relation_size functions were introduced in PostgreSQL 8.1.
--In earlier versions, the following query can be used instead, returning the size in megabytes:

SELECT
  relname, (relpages * 8) / 1024 AS size_mb
  FROM pg_class ORDER BY relpages DESC LIMIT 20;

--You'll need to account for TOAST yourself here. Bear in mind also that relpages is only up-to-date as of the last VACUUM or ANALYZE on the particular table.

--Sizes in 8.4 and later
--In 8.4, pg_relation_size was changed to use the regclass type, which means that pg_relation_size(data_type_name) no longer works.