Re: REINDEX disk space requirements

An alternative to adding more hardware is to partition the table. This
may be your best solution for the long term too. The benefits are

1. Elimination of frequent vacuums
2. Instant space reclamation via partition deletes.

We had a similar situation as yours...we bit the bullet and
implemented partitioning..and that was the best decision we ever made.

On Fri, Nov 6, 2009 at 9:20 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>