Re: How often do I need to reindex tables?

On Tue, Feb 27, 2007 at 11:26:02AM -0800, Dhaval Shah wrote:
> I am planning to use 8.2 and the average inserts/deletes and updates
> across all tables is moderate. That is, it is a moderate sized
> database with moderate usage of tables.
>
> Given that, how often do I need to reindex the tables? Do I need to do
> it everyday?

No, you should very rarely if ever need to do it.

If you're really concerned, I suggest monitoring average tuples per
index page; something like

SELECT relname, reltuples/relpages FROM pg_class WHERE relkind = 'i' AND
relpages > 1000;

That'll show tuples/page for all indexes over 8MB in size.

> Also with 8.2, I do not have to do vacuum anymore or that is what I
> understand. Does it do auto-vacuum?

You still need to enable autovacuum. See autovacuum_enable.
--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)