From: | Dror Matalon <dror(at)zapatec(dot)com> |
---|---|
To: | sfpug(at)postgresql(dot)org |
Subject: | Re: Freebsd vs linux and hardware question |
Date: | 2003-09-22 19:14:01 |
Message-ID: | 20030922191401.GD97268@rlx11.zapatec.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | sfpug |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Aditya | 2003-09-22 19:14:38 | Re: Freebsd vs linux and hardware question |
Previous Message | Dror Matalon | 2003-09-22 19:02:08 | Re: Freebsd vs linux and hardware question |