On Wed, Nov 7, 2012 at 3:15 PM, Lists <lists@xxxxxxxxxxxxxxxxxx> wrote: > On 11/07/2012 12:42 PM, Tom Lane wrote: >> >> So you've turned off autovacuum, and are carefully not vacuuming the >> system catalogs. That's your problem all right. Is there a >> particularly good reason why this script isn't a one-liner "VACUUM"? > > > Back in the 8.x days, we experienced "vacuum full analyze" occasionally > causing other processes to hang/timeout. That was your first mistake. By 8.0 the need for vacuum full was almost zero. Except for instances where bloat got out of hand, vacuum full should generally be avoided after 8.0. Regular vacuum should be plenty, whether it's run by autovacuum daemon or a cron job. Until 8.3 autovacuum was single threaded so therefore often had trouble keeping up with bloat. While vacuum full is a blocking operation plain vacuums are not, so unless you REALLY need a vacuum full they should be avoided. > In an attempt to minimize the > impact of the locking, we updated the script to vacuum one table at a time, > which seemed to work well throughout the 8.x series. I'd happily accept that > this conclusion may have simply have been wrong, but it worked well enough > that nobody complained and life was good. Yeah you still had blocking but it was probably less noticeable. > After switching to 9.x, we read > that the "full" vacuum was less useful and so the script was changed to > "vacuum analyze $table" rather than "vacuum full analyze $table". Yeah at that point you'd have been better off tuning autovacuum to be more aggressive and let it do the job. Generally the time to call vacuum by hand is right after you've done something like delete half the rows in a large table. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
