I was looking into some SQLBot issues where Slack reports were taking a long time, and noticed the database got HUUUUUUUUGE when I took a database backup. Hmm . . . something's not right.
How does one narrow down an issue like this?
Let's start with figuring out which tables are the fattest.
How To Find Table Size In Postgres
To find the biggest tables in your Postgres DB, you can run a query like this, it will bring back the 10 biggest tables in descending order of size.
SELECT relname as table, PG_SIZE_PRETTY(PG_TOTAL_RELATION_SIZE(relid)) as total_size FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC, pg_relation_size(relid) DESC LIMIT 10;
Ack! It looks like one of our tables got way out whack and got too portly. What now? Let's find the offending rows.
How To Find Row Size In Postgres
To find the biggest rows in a given table, you can use the PG_COLUMN_SIZE function and group by the id of the row. Here's an example.
SELECT r.id as row_id, PG_SIZE_PRETTY(sum(PG_COLUMN_SIZE(r.*))) as row_size FROM table_name as r GROUP BY r.id ORDER BY sum(pg_column_size(r.*)) DESC LIMIT 10
This will output something like this:
From there we can look at the offending row to see which field in it has the bloated data.