X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 154279DC944 for ; Tue, 17 Jan 2006 09:17:41 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 12758-10 for ; Tue, 17 Jan 2006 09:17:44 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from alvh.no-ip.org (201-220-123-7.bk10-dsl.surnet.cl [201.220.123.7]) by postgresql.org (Postfix) with ESMTP id 7F0E49DC950 for ; Tue, 17 Jan 2006 09:17:38 -0400 (AST) Received: by alvh.no-ip.org (Postfix, from userid 1000) id DBE09C2DC59; Tue, 17 Jan 2006 10:19:58 -0300 (CLST) Date: Tue, 17 Jan 2006 10:19:58 -0300 From: Alvaro Herrera To: Michael Riess Cc: pgsql-performance@postgresql.org Subject: Re: Autovacuum / full vacuum Message-ID: <20060117131958.GC2785@surnet.cl> Mail-Followup-To: Michael Riess , pgsql-performance@postgresql.org References: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.11 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=1.954 required=5 tests=[AWL=0.035, DNS_FROM_RFC_ABUSE=0.479, DNS_FROM_RFC_POST=1.44] X-Spam-Score: 1.954 X-Spam-Level: * X-Archive-Number: 200601/224 X-Sequence-Number: 16702 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"