Re: should i expected performance degradation over time

On Sat, Sep 10, 2011 at 10:55 AM, Anibal David Acosta <aa(at)devshock(dot)com> wrote:
> Sometimes I read that postgres performance is degraded over the time and
> something people talk about backup and restore database solve the problem.
>
> It is really true?

Yes and no. If you let things get out of hand, a backup and restore
may be your best choice.

> I have postgres 9.0 on a windows machine with The autovacuum is ON

Good start

> Transactional table has about 4 millions of rows inserted per day.
>
> In the midnight all rows are moved to a historical table and in the
> historical table rows are about 2 months, any transaction older than 2
> months are deleted daily.

You should look into table partitioning then. but as long as vacuum
keeps up you're probably still ok. Look at the check_postgresql.pl
script by the same guy who wrote Bucardo. It'll keep you advised of
how much bloat your tables have.

> So, my question is, if Should I expect same performance over time (example:
> after 1 year) or should I expect a degradation and must implements come
> technics like backup restore every certain time?

If you maintain your db properly, performance should stay good. If
you ignore bloat issues you might have some issues.