Re: Catalog bloat (again)

--
Scott Mead via mobile
IPhone : +1-607-765-1395
Skype : scottm.openscg
Gtalk : scottm(at)openscg(dot)com

> On Jan 27, 2016, at 22:11, Joshua D. Drake <jd(at)commandprompt(dot)com> wrote:
>
>> On 01/27/2016 03:37 PM, Ivan Voras wrote:
>>
>>
>> On 28 January 2016 at 00:13, Bill Moran <wmoran(at)potentialtech(dot)com
>> <mailto:wmoran(at)potentialtech(dot)com>> wrote:
>>
>> On Wed, 27 Jan 2016 23:54:37 +0100
>> Ivan Voras <ivoras(at)gmail(dot)com <mailto:ivoras(at)gmail(dot)com>> wrote:
>>
>> > So, question #1: WTF? How could this happen, on a regularly vacuumed
>> > system? Shouldn't the space be reused, at least after a VACUUM? The issue
>> > here is not the absolute existence of the bloat space, it's that it's
>> > constantly growing for *system* tables.
>>
>> With a lot of activity, once a day probably isn't regular enough.
>>
>>
>> I sort of see what you are saying. I'm curious, though, what goes wrong
>> with the following list of expectations:
>>
>> 1. Day-to-day load is approximately the same
>> 2. So, at the end of the first day there will be some amount of bloat
>> 3. Vacuum will mark that space re-usable
>> 4. Within the next day, this space will actually be re-used
>> 5. ... so the bloat won't grow.
>>
>> Basically, I'm wondering why is it growing after vacuums, not why it
>> exists in the first place?
>
> If something is causing the autovacuum to be aborted you can have this problem.
It long-running transactions / idle in transaction / prepared xacts

Have you considered slowing down on temp tables? Typically, when bleeding, it's good to find the wound and stitch it up instead of just getting more towels....

>
> JD
>
>
> --
> Command Prompt, Inc. http://the.postgres.company/
> +1-503-667-4564
> PostgreSQL Centered full stack support, consulting and development.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general