Re: When will my database crash?

simon(at)2ndquadrant(dot)com ("Simon Riggs") wrote:
> Proactive, planned maintenance is more manageable than sudden,
> grinding failure when you're at home in bed. Make sure your manager
> is part of the call-out plan, thats usually a good way to make them
> care about maintenance.

And regular maintenance allows "maintenance sessions" to become
individually _much_ less costly. If the heavily-updated tables were
vacuumed daily or even hourly, it is quite likely that the issue would
go away, from whence comes the "pg_autovacuum" strategy.

> For most applications, you should be running VACUUM FULL at least
> monthly, since any more than that is effectively the same thing as
> "never", as your case shows.

So long as you vacuum heavily-updated tables often enough, run 'plain
VACUUM ANALYZE' once in a while, to catch the transaction ID rollover
issue, and have enough space in the free space map, VACUUM FULL
shouldn't be necessary.

At Afilias, we _never_ run VACUUM FULL in the production transactional
environment, or at least we haven't needed to since migrating to 7.4.
(On 7.2, we needed to do so periodically, as well as periodically
reindexing some tables.)
--
select 'cbbrowne' || '@' || 'ntlug.org';
http://www3.sympatico.ca/cbbrowne/sap.html
"A good system can't have a weak command language." -- Alan Perlis
[This explains why MS-DOS and Windows can't possibly be good
systems...]