Re: REINDEX disk space requirements

David Schnur <dnschnur(at)gmail(dot)com> writes:
> One large installation we're working with is seeing 'out of disk space'
> errors when performing the REINDEX. I don't have precise numbers at the
> moment, but here's what I know:

> - Total DB size is ~100 GB
> - Size of the main table is ~60 GB (~1B rows)
> - Size of the main table PK index is ~20 GB
> - Free space on disk is ~35 GB

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.

Possibly you could drop and recreate the index instead of using REINDEX,
if you're going to have the table locked anyway. But it seems to me
that you're likely to need more disk pretty soon, unless this DB is
more static than most. Maybe just spring for more hardware now.

regards, tom lane