pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ian Westmacott <ianw@intellivid.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: VACUUM and read-mostly tables
Date: Tue, 05 Apr 2005 14:26:54 -0400
Message-ID: <26291.1112725614@sss.pgh.pa.us> (raw)
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>

Ian Westmacott <ianw@intellivid.com> 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



view thread (12+ messages)  latest in thread

Message-ID: <26291.1112725614@sss.pgh.pa.us>
Permalink:  ../26291.1112725614@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/26291.1112725614@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: tgl@sss.pgh.pa.us, ianw@intellivid.com
  Subject: Re: VACUUM and read-mostly tables
  In-Reply-To: <26291.1112725614@sss.pgh.pa.us>
  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> <26291.1112725614@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox