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;Written by Matt Keranen
Related protips
7 Responses
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) | |
Thanks for sharing. How could we add the average row size (table size / num. of rows)?
@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.
@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 :/
@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
Thank for sharing.
I've got following error
column ts.spclocation does not exist
LINE 7: COALESCE(TS.spclocation, XS.spclocation, '') AS "locatio..
At some point a Postgres version moved or renamed the pg_tablespace.spclocation column.
Have a fresh tip? Share with Coderwall community!