X-Original-To: pgsql-admin@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id BD06F475C2B for ; Fri, 21 Feb 2003 22:02:39 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 30780-07 for ; Fri, 21 Feb 2003 22:02:12 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id E6ADA475E23 for ; Fri, 21 Feb 2003 21:41:10 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id h1M2fC5u002169; Fri, 21 Feb 2003 21:41:13 -0500 (EST) To: "Pascal PEYRE" Cc: pgsql-admin@postgresql.org Subject: Re: Vacuum Question In-reply-to: <000001c2d97d$b11814c0$e3026b83@intranet.cir.fr> References: <000001c2d97d$b11814c0$e3026b83@intranet.cir.fr> Comments: In-reply-to "Pascal PEYRE" message dated "Fri, 21 Feb 2003 08:48:58 +0100" Date: Fri, 21 Feb 2003 21:41:12 -0500 Message-ID: <2168.1045881672@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/355 X-Sequence-Number: 7553 "Pascal PEYRE" writes: > I have some big postgreSQL databases (4/5 GB at start) on this server. > Every night I erase data and I import a lot of new data. Exactly how do you erase the old data? If you're zapping the entire contents of tables, TRUNCATE might be a good answer. > For optimize my database I operate a vacuum "all" every night. Plain vacuum, or vacuum full? If it's a plain vacuum, do you have the free space map parameters (in postgresql.conf) set large enough to cover your database? > The problem is that the size of database is growing every day whereas > the volume of data stay remains stable. Exactly which tables are bloating? Look at the relpages column of pg_class (just after a vacuum, so that the values are up to date). Look to see which entries grow from day to day... regards, tom lane