Re: Autovacuum / full vacuum

От: Alvaro Herrera
Тема: Re: Autovacuum / full vacuum
Дата: ,
Msg-id: 20060117131958.GC2785@surnet.cl
(см: обсуждение, исходный текст)
Ответ на: Autovacuum / full vacuum  (Michael Riess)
Список: pgsql-performance

Скрыть дерево обсуждения

Autovacuum / full vacuum  (Michael Riess, )
 Re: Autovacuum / full vacuum  (Christopher Kings-Lynne, )
  Re: Autovacuum / full vacuum  (Michael Riess, )
   Re: Autovacuum / full vacuum  (Michael Stone, )
   Re: Autovacuum / full vacuum  ("Matthew T. O'Connor", )
    Re: Autovacuum / full vacuum  (Markus Schaber, )
    Re: Autovacuum / full vacuum  (Michael Riess, )
     Re: Autovacuum / full vacuum  (Tom Lane, )
     Re: Autovacuum / full vacuum  (Markus Schaber, )
    Re: Autovacuum / full vacuum  (Andrew Sullivan, )
  Re: Autovacuum / full vacuum  (Pandurangan R S, )
 Re: Autovacuum / full vacuum  (Alvaro Herrera <-ip.org>, )
 Re: Autovacuum / full vacuum  (Christopher Browne, )
  Re: Autovacuum / full vacuum  (Michael Riess, )
   Re: Autovacuum / full vacuum  (Andrew Sullivan, )
 Re: Autovacuum / full vacuum  ("Larry Rosenman", )
 Re: Autovacuum / full vacuum  (Christopher Browne, )
  Re: Autovacuum / full vacuum  (Michael Riess, )
   Re: Autovacuum / full vacuum  (Michael Stone, )
   Re: Autovacuum / full vacuum  (Andrew Sullivan, )
    Re: Autovacuum / full vacuum  (Scott Marlowe, )
     Re: Autovacuum / full vacuum  (Andrew Sullivan, )
      Re: Autovacuum / full vacuum  (Scott Marlowe, )
   Re: Autovacuum / full vacuum  (hubert depesz lubaczewski, )
   Re: Autovacuum / full vacuum  ("Jim C. Nasby", )
    Re: Autovacuum / full vacuum  (Michael Riess, )
     Re: Autovacuum / full vacuum  ("Jim C. Nasby", )
 Re: Autovacuum / full vacuum  (Andrew Sullivan, )
  Re: Autovacuum / full vacuum  (Michael Riess, )
   Re: Autovacuum / full vacuum  (Andrew Sullivan, )
 Re: Autovacuum / full vacuum  (Tom Lane, )
 Re: Autovacuum / full vacuum  (Chris Browne, )
  Re: Autovacuum / full vacuum  (Alvaro Herrera <-ip.org>, )
  Re: Autovacuum / full vacuum  (Andrew Sullivan, )
 Re: Autovacuum / full vacuum  (Chris Browne, )
  Re: Autovacuum / full vacuum  (Tom Lane, )
 Re: Autovacuum / full vacuum  ("Mindaugas", )
  Re: Autovacuum / full vacuum  (Alvaro Herrera <-ip.org>, )
 Re: Autovacuum / full vacuum  (Chris Browne, )
  Re: Autovacuum / full vacuum (off-topic?)  (Michael Crozier, )
   Re: Autovacuum / full vacuum (off-topic?)  ("Jim C. Nasby", )
    Re: Autovacuum / full vacuum (off-topic?)  (Michael Crozier, )
     Re: Autovacuum / full vacuum (off-topic?)  ("Jim C. Nasby", )
    Re: Autovacuum / full vacuum (off-topic?)  (Mark Kirkwood, )
     Re: Autovacuum / full vacuum (off-topic?)  (Bruce Momjian, )
     Re: Autovacuum / full vacuum (off-topic?)  ("Jim C. Nasby", )
      Re: Autovacuum / full vacuum (off-topic?)  ("Joshua D. Drake", )
       Re: Autovacuum / full vacuum (off-topic?)  ("Jim C. Nasby", )
        Re: Autovacuum / full vacuum (off-topic?)  ("Joshua D. Drake", )
         Re: Autovacuum / full vacuum (off-topic?)  ("Jim C. Nasby", )
 Re: Autovacuum / full vacuum (off-topic?)  (Chris Browne, )
  Re: Autovacuum / full vacuum (off-topic?)  (Michael Crozier, )
 Re: Autovacuum / full vacuum (off-topic?)  (<>, )
  Re: Autovacuum / full vacuum (off-topic?)  ("Jim C. Nasby", )

Michael Riess wrote:
> hi,
>
> I'm curious as to why autovacuum is not designed to do full vacuum.

Because a VACUUM FULL is too invasive.  Lazy vacuum is so light on the
system w.r.t. locks that it's generally not a problem to start one at
any time.  On the contrary, vacuum full could be a disaster on some
situations.

What's more, in general a lazy vacuum is enough to keep the dead space
within manageability, given a good autovacuum configuration and good FSM
configuration, so there's mostly no need for full vacuum.  (This is the
theory at least.)  For the situations where there is a need, we tell you
to issue it manually.

> So my question is: What's the use of an autovacuum daemon if I still
> have to use a cron job to do full vacuums? wouldn't it just be a minor
> job to enhance autovacuum to be able to perform full vacuums, if one
> really wants it to do that - even if some developers think that it's the
> wrong approach?

Yes, it is a minor job to "enhance" it to perform vacuum full.  The
problem is having a good approach to determining _when_ to issue a full
vacuum, and having a way to completely disallow it.  If you want to do
the development work, be my guest (but let us know your design first).
If you don't, I guess you would have to wait until it comes high enough
on someone's to-do list, maybe because you convinced him (or her, but we
don't have Postgres-ladies at the moment AFAIK) monetarily or something.

You can, of course, produce a patch and use it internally.  This is free
software, remember.

--
Alvaro Herrera                           Developer, http://www.PostgreSQL.org
"God is real, unless declared as int"


В списке pgsql-performance по дате сообщения:

От: Chris Browne
Дата:
Сообщение: Re: Autovacuum / full vacuum
От: Tom Lane
Дата:
Сообщение: Re: Autovacuum / full vacuum