Penguincoder , 1 year ago

Really ugly, but really works. Connect to psql and run:

<pre style="background-color:#ffffff;">
<span style="font-weight:bold;color:#a71d5d;">WITH</span><span style="color:#323232;"> RECURSIVE pg_inherit(inhrelid, inhparent) </span><span style="font-weight:bold;color:#a71d5d;">AS
</span><span style="color:#323232;">    (</span><span style="font-weight:bold;color:#a71d5d;">select</span><span style="color:#323232;"> inhrelid, inhparent
</span><span style="color:#323232;">    </span><span style="font-weight:bold;color:#a71d5d;">FROM</span><span style="color:#323232;"> pg_inherits
</span><span style="color:#323232;">    </span><span style="font-weight:bold;color:#a71d5d;">UNION
</span><span style="color:#323232;">    </span><span style="font-weight:bold;color:#a71d5d;">SELECT </span><span style="color:#0086b3;">child</span><span style="color:#323232;">.</span><span style="color:#0086b3;">inhrelid</span><span style="color:#323232;">, </span><span style="color:#0086b3;">parent</span><span style="color:#323232;">.</span><span style="color:#0086b3;">inhparent
</span><span style="color:#323232;">    </span><span style="font-weight:bold;color:#a71d5d;">FROM</span><span style="color:#323232;"> pg_inherit child, pg_inherits parent
</span><span style="color:#323232;">    </span><span style="font-weight:bold;color:#a71d5d;">WHERE </span><span style="color:#0086b3;">child</span><span style="color:#323232;">.</span><span style="color:#0086b3;">inhparent </span><span style="font-weight:bold;color:#a71d5d;">= </span><span style="color:#0086b3;">parent</span><span style="color:#323232;">.</span><span style="color:#0086b3;">inhrelid</span><span style="color:#323232;">),
</span><span style="color:#323232;">pg_inherit_short </span><span style="font-weight:bold;color:#a71d5d;">AS</span><span style="color:#323232;"> (</span><span style="font-weight:bold;color:#a71d5d;">SELECT * FROM</span><span style="color:#323232;"> pg_inherit </span><span style="font-weight:bold;color:#a71d5d;">WHERE</span><span style="color:#323232;"> inhparent NOT </span><span style="font-weight:bold;color:#a71d5d;">IN</span><span style="color:#323232;"> (</span><span style="font-weight:bold;color:#a71d5d;">SELECT</span><span style="color:#323232;"> inhrelid </span><span style="font-weight:bold;color:#a71d5d;">FROM</span><span style="color:#323232;"> pg_inherit))
</span><span style="font-weight:bold;color:#a71d5d;">SELECT</span><span style="color:#323232;"> table_schema
</span><span style="color:#323232;">    , TABLE_NAME
</span><span style="color:#323232;">    , row_estimate
</span><span style="color:#323232;">    , pg_size_pretty(total_bytes) </span><span style="font-weight:bold;color:#a71d5d;">AS</span><span style="color:#323232;"> total
</span><span style="color:#323232;">    , pg_size_pretty(index_bytes) </span><span style="font-weight:bold;color:#a71d5d;">AS</span><span style="color:#323232;"> INDEX
</span><span style="color:#323232;">    , pg_size_pretty(toast_bytes) </span><span style="font-weight:bold;color:#a71d5d;">AS</span><span style="color:#323232;"> toast
</span><span style="color:#323232;">    , pg_size_pretty(table_bytes) </span><span style="font-weight:bold;color:#a71d5d;">AS</span><span style="color:#323232;"> TABLE
</span><span style="color:#323232;">    , total_bytes::float8 </span><span style="font-weight:bold;color:#a71d5d;">/ </span><span style="color:#62a35c;">sum</span><span style="color:#323232;">(total_bytes) OVER () </span><span style="font-weight:bold;color:#a71d5d;">AS</span><span style="color:#323232;"> total_size_share
</span><span style="color:#323232;">  </span><span style="font-weight:bold;color:#a71d5d;">FROM</span><span style="color:#323232;"> (
</span><span style="color:#323232;">    </span><span style="font-weight:bold;color:#a71d5d;">SELECT *</span><span style="color:#323232;">, total_bytes</span><span style="font-weight:bold;color:#a71d5d;">-</span><span style="color:#323232;">index_bytes</span><span style="font-weight:bold;color:#a71d5d;">-</span><span style="color:#323232;">COALESCE(toast_bytes,</span><span style="color:#0086b3;">0</span><span style="color:#323232;">) </span><span style="font-weight:bold;color:#a71d5d;">AS</span><span style="color:#323232;"> table_bytes
</span><span style="color:#323232;">    </span><span style="font-weight:bold;color:#a71d5d;">FROM</span><span style="color:#323232;"> (
</span><span style="color:#323232;">         </span><span style="font-weight:bold;color:#a71d5d;">SELECT </span><span style="color:#0086b3;">c</span><span style="color:#323232;">.</span><span style="color:#0086b3;">oid
</span><span style="color:#323232;">              , nspname </span><span style="font-weight:bold;color:#a71d5d;">AS</span><span style="color:#323232;"> table_schema
</span><span style="color:#323232;">              , relname </span><span style="font-weight:bold;color:#a71d5d;">AS</span><span style="color:#323232;"> TABLE_NAME
</span><span style="color:#323232;">              , </span><span style="color:#62a35c;">SUM</span><span style="color:#323232;">(</span><span style="color:#0086b3;">c</span><span style="color:#323232;">.</span><span style="color:#0086b3;">reltuples</span><span style="color:#323232;">) OVER (partition BY parent) </span><span style="font-weight:bold;color:#a71d5d;">AS</span><span style="color:#323232;"> row_estimate
</span><span style="color:#323232;">              , </span><span style="color:#62a35c;">SUM</span><span style="color:#323232;">(pg_total_relation_size(</span><span style="color:#0086b3;">c</span><span style="color:#323232;">.</span><span style="color:#0086b3;">oid</span><span style="color:#323232;">)) OVER (partition BY parent) </span><span style="font-weight:bold;color:#a71d5d;">AS</span><span style="color:#323232;"> total_bytes
</span><span style="color:#323232;">              , </span><span style="color:#62a35c;">SUM</span><span style="color:#323232;">(pg_indexes_size(</span><span style="color:#0086b3;">c</span><span style="color:#323232;">.</span><span style="color:#0086b3;">oid</span><span style="color:#323232;">)) OVER (partition BY parent) </span><span style="font-weight:bold;color:#a71d5d;">AS</span><span style="color:#323232;"> index_bytes
</span><span style="color:#323232;">              , </span><span style="color:#62a35c;">SUM</span><span style="color:#323232;">(pg_total_relation_size(reltoastrelid)) OVER (partition BY parent) </span><span style="font-weight:bold;color:#a71d5d;">AS</span><span style="color:#323232;"> toast_bytes
</span><span style="color:#323232;">              , parent
</span><span style="color:#323232;">          </span><span style="font-weight:bold;color:#a71d5d;">FROM</span><span style="color:#323232;"> (
</span><span style="color:#323232;">                </span><span style="font-weight:bold;color:#a71d5d;">SELECT </span><span style="color:#0086b3;">pg_class</span><span style="color:#323232;">.</span><span style="color:#0086b3;">oid
</span><span style="color:#323232;">                    , reltuples
</span><span style="color:#323232;">                    , relname
</span><span style="color:#323232;">                    , relnamespace
</span><span style="color:#323232;">                    , </span><span style="color:#0086b3;">pg_class</span><span style="color:#323232;">.</span><span style="color:#0086b3;">reltoastrelid
</span><span style="color:#323232;">                    , COALESCE(inhparent, </span><span style="color:#0086b3;">pg_class</span><span style="color:#323232;">.</span><span style="color:#0086b3;">oid</span><span style="color:#323232;">) parent
</span><span style="color:#323232;">                </span><span style="font-weight:bold;color:#a71d5d;">FROM</span><span style="color:#323232;"> pg_class
</span><span style="color:#323232;">                    </span><span style="font-weight:bold;color:#a71d5d;">LEFT JOIN</span><span style="color:#323232;"> pg_inherit_short </span><span style="font-weight:bold;color:#a71d5d;">ON</span><span style="color:#323232;"> inhrelid </span><span style="font-weight:bold;color:#a71d5d;">= oid
</span><span style="color:#323232;">                </span><span style="font-weight:bold;color:#a71d5d;">WHERE</span><span style="color:#323232;"> relkind </span><span style="font-weight:bold;color:#a71d5d;">IN</span><span style="color:#323232;"> (</span><span style="color:#183691;">'r'</span><span style="color:#323232;">, </span><span style="color:#183691;">'p'</span><span style="color:#323232;">)
</span><span style="color:#323232;">             ) c
</span><span style="color:#323232;">             </span><span style="font-weight:bold;color:#a71d5d;">LEFT JOIN</span><span style="color:#323232;"> pg_namespace n </span><span style="font-weight:bold;color:#a71d5d;">ON </span><span style="color:#0086b3;">n</span><span style="color:#323232;">.</span><span style="color:#0086b3;">oid </span><span style="font-weight:bold;color:#a71d5d;">= </span><span style="color:#0086b3;">c</span><span style="color:#323232;">.</span><span style="color:#0086b3;">relnamespace
</span><span style="color:#323232;">  ) a
</span><span style="color:#323232;">  </span><span style="font-weight:bold;color:#a71d5d;">WHERE oid =</span><span style="color:#323232;"> parent
</span><span style="color:#323232;">) a
</span><span style="font-weight:bold;color:#a71d5d;">ORDER BY</span><span style="color:#323232;"> total_bytes </span><span style="font-weight:bold;color:#a71d5d;">DESC LIMIT </span><span style="color:#0086b3;">3</span><span style="color:#323232;">;
</span>

That will show the top 3 database tables sizes. I bet you number one is activity.