Re: REINDEX disk space requirements

On Fri, Nov 6, 2009 at 12:11 PM, Alvaro Herrera <alvherre(at)commandprompt(dot)com>
wrote:

> Immediately after the deletions, or is there some delay? Keep in mind
> that rows cannot be reclaimed until the oldest transaction that was open
> when they were deleted is finished.

The VACUUM waits until after the DELETE has been committed. But when you
refer to the oldest transaction, do you mean any transaction at all?
Currently it's guaranteed that no other transaction is running when VACUUM
starts, but we were thinking of changing that behavior. It would then be
possible for an INSERT in a separate transaction to start running ~10
seconds before the DELETE is done, and continue running for ~10 seconds
after the VACUUM starts.

Is that the problem you were referring to? How does VACUUM behave in that
situation? It sounds like it returns without reclaiming anything?

On Fri, Nov 6, 2009 at 12:20 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Out of disk space is 100% guaranteed here, because it'll take about
> twice the index size to do a REINDEX --- there's a sort file that's
> roughly the size of the index, plus the new index itself, and we
> don't risk deleting the old index until the transaction commits.
>

Aha; I assumed it would require free space equal to the size of the index.
If it needs double, that explains it pretty clearly. That's exactly what I
was looking for; thank you!

David