Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

On 07/23/2017 12:03 PM, Joshua D. Drake wrote:

> As you can see even with aggressive vacuuming, over a period of 36 hours 
> life gets increasingly miserable.
> 
> The largest table is:
> 
> postgres=# select 
> pg_size_pretty(pg_total_relation_size('bmsql_order_line'));
>   pg_size_pretty
> ----------------
>   148 GB
> (1 row)
> 

[snip]

> With the PK being
> 
> postgres=# select 
> pg_size_pretty(pg_relation_size('bmsql_order_line_pkey'));
>   pg_size_pretty
> ----------------
>   48 GB
> (1 row)
> 
> I tried to see how much data we are dealing with here:

-hackers,

I cleaned up the table with VACUUM FULL and ended up with the following:

postgres=# select 
pg_size_pretty(pg_total_relation_size('bmsql_order_line')); pg_size_pretty
---------------- 118 GB
(1 row)

postgres=# select pg_size_pretty(pg_relation_size('bmsql_order_line_pkey')); pg_size_pretty
---------------- 27 GB
(1 row)

Does this suggest that we don't have a cleanup problem but a 
fragmentation problem (or both at least for the index)? Having an index 
that is almost twice the uncleaned up size isn't that uncommon.

Thanks in advance,

JD


-- 
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc

PostgreSQL Centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://pgconf.us
*****     Unless otherwise stated, opinions are my own.   *****