Painful encounter with oversized Postgres (9.6)

I had not so nice encounter with an oversize PostgresDB. Somehow rather small DB ballooned to 256 GB (what was a partition size). I'm not PostgresDB expert. I can barely use it, but I've spent some time doing internet search and that might be useful for other (or for me in the future).

In the end I regained nearly 200 GB of space with simple command:

vacuum full

As it is suggested by this Stack Overflow question.

The key to find above suggestion was to understand that the problem was related to pg_toast table. I found that fact out using oid2name tool, which BTW in Ubuntu is in /usr/lib/postgresql/10/bin/.

/usr/lib/postgresql/10/bin/oid2name -H localhost -U postgres -d postgres -f 24806

From database "postgres":
  Filenode      Table Name
--------------------------
     24806  pg_toast_24729

I learnt about the tool from this document and learnt how to use from the man page.

It's also worth to remember that following query didn't help me. I think it's nice, so posting it here, anyway:

SELECT table_schema, 
    table_name, 
    pg_relation_filepath('"'||table_schema||'"."'||table_name||'"') 
FROM information_schema.tables 
WHERE 
    pg_relation_filepath('"'||table_schema||'"."'||table_name||'"') 
LIKE 'base/12404/248%';

The above query was built based on another good suggestion from Stack Overflow. This one shows all big tables.

SELECT
    table_schema,
    table_name,
    pg_relation_size('"'||table_schema||'"."'||table_name||'"')
FROM  information_schema.tables
ORDER BY 3
LIMIT 10