I had a situation where I needed to find the top 100 largest tables in a certain tablespace on a postgres 9 database, in my case we archive tables into an archive1 tablespace. This query will find all the largest relations in the archive1 tablespace. Its important to swap out ‘archive1’ with whatever tablespace you are trying to list.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
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" 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') AND COALESCE(T.tablespace, I.tablespace, '') = 'archive1' ORDER BY pg_total_relation_size(C.oid) DESC LIMIT 100; relation | size (data) | tablespace ------------------------------------------------------------+------------------------------+------------ public.table1 | 172 GB (148 GB data) | archive1 public.table2 | 171 GB (147 GB data) | archive1 public.table3 | 32 GB (32 GB data) | archive1 --SNIP-- |
Hope this helps you out, took some time to get it to work.