Last Updated: February 25, 2016
·
5.609K
· datasaur

PostgreSQL data and index size by table

SELECT N.nspname || '.' || C.relname AS "relation",
    CASE WHEN reltype = 0
        THEN pg_size_pretty(pg_total_relation_size(C.oid)) || ' (index)'
        ELSE pg_size_pretty(pg_total_relation_size(C.oid)) || ' (' ||  pg_size_pretty(pg_relation_size(C.oid)) || ' data)'
    END AS "size (data)",
    COALESCE(T.tablespace, I.tablespace, '') AS "tablespace",
    COALESCE(TS.spclocation, XS.spclocation, '') AS "location"
FROM pg_class C
LEFT JOIN pg_namespace N ON  (N.oid = C.relnamespace)
LEFT JOIN pg_tables T ON (T.tablename = C.relname)
LEFT JOIN pg_indexes I ON (I.indexname = C.relname)
LEFT JOIN pg_tablespace TS ON TS.spcname = T.tablespace
LEFT JOIN pg_tablespace XS ON XS.spcname = I.tablespace
WHERE nspname NOT IN ('pg_catalog','pg_toast','information_schema')
ORDER BY pg_total_relation_size(C.oid) DESC;

7 Responses
Add your response

Great tip. Prints in descending order of size the largest tables:

relation | size (data) | tablespace | location
-----------------+-------------------+-----------+----------
public.facts | 247 MB (154 MB data) | |
public.sessions | 177 MB (111 MB data) | |
public.follows | 110 MB (42 MB data) | |
public.user_events | 58 MB (47 MB data) | |
public.skills | 41 MB (31 MB data) | |

over 1 year ago ·

Thanks for sharing. How could we add the average row size (table size / num. of rows)?

over 1 year ago ·

@dserodio Exact row counts in PostgreSQL come from SELECT COUNT(*) which can be slow as a full table scan, but a close estimate can be found in pg_stat_user_tables.n_live_tup

Joining pg_stat_user_tables.relname and schemaname to pg_tables.tablename and schemaname would allow you to calculate average row size, and average size by index and tablespace.

over 1 year ago ·

@hybriddba Hello, i did not understand why joining with pgtable would allow me to get the average rowsize. I did the query and noticed that pgtables do not add any relevant information :/

over 1 year ago ·

@bcap Used as an example on joining pg_stat_user_tables to the original query, then divide pg_total_relation_size(C.oid) by pg_stat_user_tables.n_live_tup

over 1 year ago ·

Thank for sharing.
I've got following error

column ts.spclocation does not exist
LINE 7: COALESCE(TS.spclocation, XS.spclocation, '') AS "locatio..

over 1 year ago ·

At some point a Postgres version moved or renamed the pg_tablespace.spclocation column.

over 1 year ago ·