Re: Freebsd vs linux and hardware question

On Mon, Sep 22, 2003 at 11:45:04AM -0700, Josh Berkus wrote:
> Dror,
>
> > VACUUM seems to remove dead tuples in all the indices too. Does REINDEX
> > do something else?
>
> VACUUM removes the references to the dead tuples, but does not reclaim the
> space. It's like the difference between VACUUM and VACUUM FULL.

OK, the man page for REINDEX has changed. It used to say that you only
run REINDEX to fix indexes.

http://www.postgresql.org/docs/aw_pgsql_book/node274.html

The new page does mention reclaiming bad pages

http://developer.postgresql.org/docs/postgres/sql-reindex.html

So I'll double check now and see how often we need to reindex.

>
> In 7.2, you can decrease the frequency of VACUUM FULLs (even down to 1 per
> month) by proper adjusting of the Free Space Map. However, this is not
> extended to indexes until 7.4. As a result, if 75% of the rows in your
> index have been updated, then a substantial portion of the disk space used by
> your index is empty. I don't know the exact conversion, and suspect it
> varies by the size and nature of the updates.
>
> Once that empty space reaches 2/3 or more of the index file, I've found, the
> efficiency of the index drops substantially.
>
> --
> Josh Berkus
> Aglio Database Solutions
> San Francisco

--
Dror Matalon, President
Zapatec Inc
1700 MLK Way
Berkeley, CA 94709
http://www.zapatec.com