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 EAA2C537BA for ; Tue, 5 Apr 2005 19:26:56 +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 37770-02 for ; Tue, 5 Apr 2005 18:26:54 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 65411537A7 for ; Tue, 5 Apr 2005 19:26:54 +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 j35IQtPF026292; Tue, 5 Apr 2005 14:26:55 -0400 (EDT) To: Ian Westmacott Cc: pgsql-admin@postgresql.org Subject: Re: VACUUM and read-mostly tables In-reply-to: <1112723228.8115.110.camel@spectre.intellivid.com> References: <000201c53993$b07bb440$76d81e42@hsd1.ma.comcast.net> <10364.1112683320@sss.pgh.pa.us> <1112713990.8115.83.camel@spectre.intellivid.com> <13828.1112715280@sss.pgh.pa.us> <1112723228.8115.110.camel@spectre.intellivid.com> Comments: In-reply-to Ian Westmacott message dated "Tue, 05 Apr 2005 13:47:08 -0400" Date: Tue, 05 Apr 2005 14:26:54 -0400 Message-ID: <26291.1112725614@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/66 X-Sequence-Number: 17079 Ian Westmacott writes: > On Tue, 2005-04-05 at 11:34, Tom Lane wrote: >> Have you looked at whether you can slow down the rate of XID consumption >> (ie, by bundling operations into larger transactions)? That might be a >> more useful route to limiting the costs involved. > Yes, I'd like to explore that. Essentially what happens > now is that a number of rows are written to each of about > two dozen tables, each with a COPY FROM STDIN, and then > there are a few INSERTS and UPDATES to boot. As far as > we are concerned, all of this could be a single transaction. > My understanding is that all the inserts resulting from a > COPY are a single transaction, but is it possible to make > multiple COPYs a single transaction? Sure. Just wrap BEGIN/COMMIT around the whole mess. There are only a very small number of commands that can't be inside a transaction block. Keep in mind also that SELECT queries are also transactions; so you might need to think about bundling read operations too. regards, tom lane