Thread: postgresql performance tuning

This didn't get through the first time around, so resending it again.
Sorry for any duplicate entries.

Hello,

I have a question on postgres's performance tuning, in particular, the
vacuum and reindex commands. Currently I do a vacuum (without full) on all
of my tables.  However, its noted in the docs (e.g.
http://developer.postgresql.org/docs/postgres/routine-reindex.html)
and on the lists here that indexes may still bloat after a while and hence
reindex is necessary.  How often do people reindex their tables out
there? I guess I'd have to update my cron scripts to do reindexing too
along with vacuuming but most probably at a much lower frequency than
vacuum.

But these scripts do these maintenance tasks at a fixed time (every few
hours, days, weeks, etc.) What I would like is to do these tasks on a need
basis.  So for vacuuming, by "need" I mean every few updates or some such
metric that characterizes my workload. Similarly, "need" for the reindex
command might mean every few updates or degree of bloat, etc.

I came across the pg_autovacuum daemon, which seems to do exactly what I
need for vacuums. However, it'd be great if there was a similar automatic
reindex utility, like say, a pg_autoreindex daemon. Are there any plans
for this feature?  If not, then would cron scripts be the next best
choice?

Thanks,
Ameet

Ameet Kini <akini@cs.wisc.edu> writes:
> I have a question on postgres's performance tuning, in particular, the
> vacuum and reindex commands. Currently I do a vacuum (without full) on all
> of my tables.  However, its noted in the docs (e.g.
> http://developer.postgresql.org/docs/postgres/routine-reindex.html)
> and on the lists here that indexes may still bloat after a while and hence
> reindex is necessary.  How often do people reindex their tables out
> there?

Never, unless you have actual evidence that your indexes are bloating.
It's only very specific use-patterns that have problems.

            regards, tom lane

On Dec 6, 2005, at 12:44 PM, Ameet Kini wrote:

> I have a question on postgres's performance tuning, in particular, the
> vacuum and reindex commands. Currently I do a vacuum (without full)
> on all
> of my tables.  However, its noted in the docs (e.g.
> http://developer.postgresql.org/docs/postgres/routine-reindex.html)
> and on the lists here that indexes may still bloat after a while
> and hence
> reindex is necessary.  How often do people reindex their tables out

Why would you be running a version older than 7.4?  Index bloat is
mostly a non-issue in recent releases of pg.

Ameet Kini schrieb:
>
> This didn't get through the first time around, so resending it again.
> Sorry for any duplicate entries.
>
> Hello,
>
> I have a question on postgres's performance tuning, in particular, the
> vacuum and reindex commands. Currently I do a vacuum (without full) on all
> of my tables.

I'm curious ... why no full vacuum? I bet that the full vacuum will
compact your (index) tables as much as a reindex would.

I guess the best advice is to increase FSM and to use autovacuum.