Re: REINDEX CONCURRENTLY 2.0

On 04/03/2017 07:57 AM, Michael Paquier wrote:
> On Fri, Mar 31, 2017 at 5:12 PM, Andreas Karlsson <andreas(at)proxel(dot)se> wrote:
>> On 03/31/2017 08:27 AM, Michael Paquier wrote:
>>> - Do a per-index rebuild and not a per-relation rebuild for concurrent
>>> indexing. Doing a per-relation reindex has the disadvantage that many
>>> objects need to be created at the same time, and in the case of
>>> REINDEX CONCURRENTLY time of the operation is not what matters, it is
>>> how intrusive the operation is. Relations with many indexes would also
>>> result in much object locks taken at each step.
>>
>> I am personally worried about the amount time spent waiting for long running
>> transactions if you reindex per index rather than per relation. Because when
>> you for one index wait on long running transactions nothing prevents new
>> long transaction from starting, which we will have to wait for while
>> reindexing the next index. If your database has many long running
>> transactions more time will be spent waiting than the time spent working.
>
> Yup, I am not saying that one approach or the other are bad, both are
> worth considering. That's a deal between waiting and manual potential
> cleanup in the event of a failure.

Agreed, and which is worse probably depends heavily on your schema and
workload.

> I am marking this patch as returned with feedback, this won't get in
> PG10. If I am freed from the SCRAM-related open items I'll try to give
> another shot at implementing this feature before the first CF of PG11.

Thanks! I also think I will have time to work on this before the first CF.

Andreas