Re: [COMMITTERS] pgsql: Add pages deleted from pending list to FSM

On Sat, Sep 19, 2015 at 5:20 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> On Fri, Sep 18, 2015 at 6:27 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>
>> [ moving thread to -hackers ]
>>
>> Fujii Masao <masao(dot)fujii(at)gmail(dot)com> writes:
>> > So autoanalyze still doesn't call IndexFreeSpaceMapVacuum().
>> > That is, only backend can clean the list in INSERT-only workload.
>> > I don't think that this is desirable. Because the backend will
>> > periodically take a big performance penalty.
>
>
> Calling IndexFreeSpaceMapVacuum is only need to reuse the space freed up by
> cleaning the pending list.
>
> The list is still getting cleaned and truncated by autoanalyze, it is just
> that the space is not getting marked for reuse.
>
> When I wrote this, my thought was that a table vacuum is going to call
> IndexFreeSpaceMapVacuum() from another place in its code and so should not
> call it here as well. I neglected to consider the autoanalyze case.
>
>
>>
>> > So I'm thinking that even autoanalyze should call
>> > IndexFreeSpaceMapVacuum()
>> > to clean the list in a background, in order to avoid such spikes in
>> > INSERT response time. Thought?
>
>
> But it already is cleaning the list. If that space is not marked as
> reusable, that causes index bloat, but doesn't cause latency spikes.

Yep, you're right. The problem here is an index bloat.

>> It seems quite bizarre for auto-analyze to do that. auto-vacuum, sure,
>> but I do not think this should get plugged into ANALYZE.
>
>
> It may be odd that autoanalyze cleans the list at all (which this patch
> doesn't change), but given that it does clean the list I don't see why it
> would be bizarre to make the cleaned-up space re-usable.

Agreed. It's odd to clean up the list but not mark the pages as re-usable.

Regards,

--
Fujii Masao