Postgres Table Size - How To Debug Your DB To Find The Biggest Tables, Rows and Cells

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.

Easy peasy!