From: Andy Colson <andy@squeakycode.net>
To: Anibal David Acosta <aa@devshock.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: should i expected performance degradation over time
Date: Sat, 10 Sep 2011 12:20:41 -0500
Message-ID: <4E6B9C69.7000205@squeakycode.net> (raw)
In-Reply-To: <000001cc6fda$7e55fd20$7b01f760$@devshock.com>
References: <000001cc6fda$7e55fd20$7b01f760$@devshock.com>
On 09/10/2011 11:55 AM, Anibal David Acosta 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?
>
> I have postgres 9.0 on a windows machine with The autovacuum is ON
>
> I have some configuration tables
>
> And a couple of transactional table.
>
> 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.
>
> 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?
>
> Thanks!!
>
Yes. And no. Things have changed over that last few versions. In older version of PG I recall hearing about table bloat problems that were really bad, and backup/restore would fix it. (Vacuum full would probably also have fixed it).
"Vacuum full", in older versions was a last chance, bring a gun to a knife fight, nothing else has worked, fix table bloat solution. Its not dis-similar from backup/restore.
In newer versions of PG, autovacuum, vacuum and vacuum full are all much nicer and work better. I really doubt you'll need to resort to backup/restore to fix problems.
Just remember: the harder you hit a table, the less chance autovacuum will have to clean it up. So you might need manual vacuum. autovacuum will cancel itself if the table is getting hit, where-as manual vacuum wont.
Keeping on top of vacuum will keep your tables slim and trim. If things get out of hand, they'll balloon into problems. Vacuum full at that point should clean it up. But, if you ignore the problem for two years, and have super really bad table bloat, well, maybe backup/restore is best.
-Andy
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andy@squeakycode.net, aa@devshock.com
Subject: Re: should i expected performance degradation over time
In-Reply-To: <4E6B9C69.7000205@squeakycode.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox