Like so many times before, today I was yet again in the situation where I wanted to know which tables/indexes take the most disk space in a particular PostgreSQL database.
My usual procedure in this case was to
dt+ in psql and scan the sizes by eye (this being on my development machine, trying to find out the biggest tables I could clean out to make room).
But once you’ve done that a few times and considering that
dt+ does nothing but query some PostgreSQL internal tables, I thought that I want this solved in an easier way that also is less error prone. In the end I just wanted the output of dt+ sorted by size.
The lead to some digging in the source code of psql itself (
src/bin/psql) where I quickly found the function that builds the query (
describe.c), so from now on, this is what I’m using when I need to get an overview over all relation sizes ordered by size in descending order:
Of course I could have come up with this without source code digging, but honestly, I didn’t know about relkind s, about pg_size_pretty and pg_relation_size (I would have thought that one to be stored in some system view), so figuring all of this out would have taken much more time than just reading the source code.
Now it’s here so I remember it next time I need it.