Re: Bloat and Slow Vacuum Time on Toast

Ken Caruso <ken(at)ipl31(dot)net> wrote:
> Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov wrote:
>
>> Ken Caruso <ken(at)ipl31(dot)net> wrote:
>>
>>
>> > SELECT SUM(pg_relation_size(pg_class.oid))
>> > FROM pg_class ;
>>
>> Perhaps you want pg_total_relation_size()?

> The number returned when using pg_total_relation size makes more
> sense: 346GB. However it looked like pg_class has toast and
> indexes listed individually, so I assumed that using
> pg_total_relation_size would count values twice, because the total
> relation size of table would include toast and indexes, and then
> they would get counted again when I ran pg_total_relation_size on
> the toast and indexes themselves. Is that an incorrect assumption
> on my part?

No that's a good point. On one of our production databases,
externally:

ccsa(at)SOCRATES:~> du -sh /var/pgsql/data/cc/
2.2T /var/pgsql/data/cc/

internally:

cir=# select
pg_size_pretty(sum(pg_relation_size(pg_class.oid))::bigint) from
pg_class;
pg_size_pretty
----------------
2176 GB
(1 row)

cir=# select
pg_size_pretty(sum(pg_total_relation_size(pg_class.oid))::bigint)
from pg_class;
pg_size_pretty
----------------
4223 GB
(1 row)

cir=# select
pg_size_pretty(sum(pg_total_relation_size(pg_class.oid))::bigint)
from pg_class where relkind = 'r';
pg_size_pretty
----------------
2176 GB
(1 row)

You must have a lot of something outside of relations showing as
active in pg_class taking up space. WAL files? Server logs? Temp
files left over from crashes?

-Kevin