Skip to content

Disk space used by tables and indexes in PostgreSQL

The SQL below can be used to check sizes of tables and their indexes in PostgreSQL database. It shows tables with indexes and indexes alone, which consume more than 10 MB of disk space.

select
    relname as "name",
    case
      relkind
        when 'r' then 'table + indexes'
        when 'i' then 'index'
        else 'unknown'
      end as "type",
    pg_size_pretty(pg_total_relation_size(c.oid)) as "size"
  from pg_class c
  left join pg_namespace n on (n.oid = c.relnamespace)
  where
    nspname = 'public'
    and (pg_total_relation_size(c.oid) > 10000000)
  order by 1 asc;

Results will look like:

name                           |type            |size   |
-------------------------------|----------------|-------|
address                        |table + indexes |10 MB  |
document                       |table + indexes |31 GB  |
document_pkey                  |index           |71 MB  |
document_typenumbertime_idx    |index           |100 MB |
person                         |table + indexes |49 MB  |