Re: Dump / restore for optimization?

Shridhar Daithankar <shridhar(at)frodo(dot)hserus(dot)net> writes:
> lec wrote:
>> I always have to dump & restore to make a database (which is in use for
>> a few months) efficient again. I have scheduled nightly vacuum analyze,
>> but only dump & restore can make the database efficient again. Has
>> anyone experience this?

> You could try several things if you want to avoid dump/restore

> - Try vacuum full instead of vacuum analyze
> - Postgresql versions pre-7.4 had index bloat with vacuum. 7.4.x fixed this. So
> try with that.
> - Run autovacuum daemon if possible.

The most likely bet is that he needs larger FSM settings in
postgresql.conf. I'd try larger FSM and more frequent regular
vacuums, rather than vacuum full. (autovacuum is good but
not necessary.)

regards, tom lane