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
