X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 15E535344B for ; Tue, 5 Apr 2005 07:42:04 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 29280-05 for ; Tue, 5 Apr 2005 06:42:02 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id B92F953433 for ; Tue, 5 Apr 2005 07:42:00 +0100 (BST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j356g0Ne010365; Tue, 5 Apr 2005 02:42:00 -0400 (EDT) To: "Ian Westmacott" Cc: pgsql-admin@postgresql.org Subject: Re: VACUUM and read-mostly tables In-reply-to: <000201c53993$b07bb440$76d81e42@hsd1.ma.comcast.net> References: <000201c53993$b07bb440$76d81e42@hsd1.ma.comcast.net> Comments: In-reply-to "Ian Westmacott" message dated "Mon, 04 Apr 2005 23:58:13 -0400" Date: Tue, 05 Apr 2005 02:42:00 -0400 Message-ID: <10364.1112683320@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/58 X-Sequence-Number: 17071 "Ian Westmacott" writes: > The problem is that we are writing rows every 1/15 second, 24x7. There > is no down time. I'm wondering if there is any way to avoid vacuuming > the old tables over and over. The documentation seems to indicate that > a full vacuum is needed to avoid XID wrap-around. Can vacuum freeze > help me? VACUUM FULL isn't really relevant. VACUUM FREEZE on a particular table should "fix" that table permanently, as long as you don't make any more changes to it. Keep in mind though that you still have to vacuum the system catalogs often enough to avoid wraparound in them. The real risk here is in overlooking any one table. You should probably use some kind of automated vacuum driver ... have you looked at pg_autovacuum? I believe Fujitsu is looking into what it'd take to make a variant Postgres with 64-bit XIDs. This'd probably imply also expanding CIDs, OIDs, and some other things, so the space penalty is not to be sneezed at ... but it might be worth it for installations like yours. regards, tom lane