Re: DB size

po 26. 4. 2021 v 22:25 odesílatel <luis(dot)roberto(at)siscobra(dot)com(dot)br> napsal:
>
> Hi!
>
> I've looked around, but could only find very old answers to this question, and maybe it changed since then...
>
> I'm struggling to identify the cause of the difference in size between the sum of all relations (via pg_total_relation_size) and pg_database_size:
>
> SELECT sum(pg_total_relation_size(relid)),
> pg_size_pretty(sum(pg_total_relation_size(relid)))
> FROM pg_catalog.pg_stat_all_tables
>
>
> sum |pg_size_pretty|
> ------------+--------------+
> 518549716992|483 GB |
>
>
> SELECT pg_database_size('mydb'),
> pg_size_pretty(pg_database_size('mydb'))
>
>
> pg_database_size|pg_size_pretty|
> ----------------+--------------+
> 869150909087|809 GB |
>
> There are three databases in the cluster, apart from 'mydb' (one of them is the 'postgres' database). These other two size about 8MB each.

Do you have any indexes in mydb database?

>
> We run pg_repack weekly and recently had crashes related to disk running out of space, so my guess is something got 'lost' during repack.
>
> What can I do to recover the wasted space?
>
> Thanks!
>
> Luis R. Weck
>
>