Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 624939FBC55 for ; Tue, 8 May 2007 08:06:15 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 88622-02 for ; Tue, 8 May 2007 08:06:07 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mail.mnc.ch (62-2-77-205.static.cablecom.ch [62.2.77.205]) by postgresql.org (Postfix) with ESMTP id 7CE0E9FBC50 for ; Tue, 8 May 2007 08:06:11 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by mail.mnc.ch (Postfix) with ESMTP id D3A98A82BD; Tue, 8 May 2007 13:06:09 +0200 (CEST) Received: from mail.mnc.ch ([127.0.0.1]) by localhost (mail.test.lan [127.0.0.1]) (amavisd-new, port 10025) with ESMTP id 10618-04; Tue, 8 May 2007 13:06:08 +0200 (CEST) Received: from meuh.mnc.lan (62-2-77-204.static.cablecom.ch [62.2.77.204]) by mail.mnc.ch (Postfix) with ESMTP id D7269A818B; Tue, 8 May 2007 13:06:08 +0200 (CEST) Received: by meuh.mnc.lan (Postfix, from userid 1000) id A3A211836AD; Tue, 8 May 2007 13:06:08 +0200 (CEST) To: Heikki Linnakangas Cc: pgsql-performance@postgresql.org Subject: Re: truncate a table instead of vaccum full when count(*) is 0 References: <46404BEA.5060009@enterprisedb.com> X-Hashcash: 1:20:070508:heikki@enterprisedb.com::o7Q70DfRgpnZQoKv:000000000000000000000000000000000000000n6d X-Hashcash: 1:20:070508:pgsql-performance@postgresql.org::7v5JLFJgtPKXt7QY:000000000000000000000000000000x00 From: Guillaume Cottenceau Date: 08 May 2007 13:06:07 +0200 In-Reply-To: <46404BEA.5060009@enterprisedb.com> Message-ID: <871whro9r4.fsf@meuh.mnc.lan> Lines: 35 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.4 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: amavisd-new at mnc.ch X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/110 X-Sequence-Number: 24500 Heikki Linnakangas writes: > Pomarede Nicolas wrote: > > But for the data (dead rows), even running a vacuum analyze every > > day is not enough, and doesn't truncate some empty pages at the end, > > so the data size remains in the order of 200-300 MB, when only a few > > effective rows are there. > > For a table like that you should run VACUUM much more often than once > a day. Turn on autovacuum, or set up a cron script etc. to run it > every 15 minutes or so. Heikki, is there theoretical need for frequent VACUUM when max_fsm_pages is large enough to hold references of dead rows? VACUUM documentation says: "tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done". Free Space Map documentation says: "the shared free space map tracks the locations of unused space in the database. An undersized free space map may cause the database to consume increasing amounts of disk space over time, because free space that is not in the map cannot be re-used". I am not sure of the relationship between these two statements. Are these deleted/obsoleted tuples stored in the FSM and actually the occupied space is reused before a VACUUM is performed, or is something else happening? Maybe the FSM is only storing a reference to diskspages containing only dead rows, and that's the difference I've been missing? -- Guillaume Cottenceau, MNC Mobile News Channel SA, an Alcatel-Lucent Company Av. de la Gare 10, 1003 Lausanne, Switzerland - direct +41 21 317 50 36