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

Share this post:

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.

    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

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 as row_id,
	PG_SIZE_PRETTY(sum(PG_COLUMN_SIZE(r.*))) as row_size
FROM table_name as r
ORDER BY sum(pg_column_size(r.*)) DESC

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!