Content-Type: text/plain; charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 6.2 \(1499\))
Subject: Re: Savepoints in transactions for speed?
From: Steve Atkins <steve@blighty.com>
In-Reply-To: 
 <CANPAkgs_LvZV-fabTNN-ahiVcwSBAyOnArw1v+QbCePg7on6ZA@mail.gmail.com>
Date: Tue, 27 Nov 2012 15:26:26 -0800
Content-Transfer-Encoding: quoted-printable
Message-Id: <332684FA-4859-4B13-9EC7-94B75EE7BB99@blighty.com>
References: 
 <CANPAkgs_LvZV-fabTNN-ahiVcwSBAyOnArw1v+QbCePg7on6ZA@mail.gmail.com>
To: pgsql-performance@postgresql.org
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


On Nov 27, 2012, at 2:04 PM, Mike Blackwell <mike.blackwell@rrd.com> wrote:

> I need to delete about 1.5 million records from a table and reload it in =
one transaction.  The usual advice when loading with inserts seems to be gr=
oup them into transactions of around 1k records.  Committing at that point =
would leave the table in an inconsistent state.=20

I'd probably just do the whole thing in one transaction.=20

Do you have specific reasons you want to avoid a long transaction, or just =
relying on rules of thumb? Postgresql isn't going to run out of resources d=
oing a big transaction, in the way some other databases will.

Long running transactions will interfere with vacuuming, but inserting a co=
uple of million rows shouldn't take that long.

>  Would issuing a savepoint every 1k or so records negate whatever downsid=
e there is to keeping a transaction open for all 1.5 million records, or ju=
st add more overhead?


Savepoints are going to increase overhead and have no effect on the length =
of the transaction. If you want to catch errors and not have to redo the en=
tire transaction, they're great, but that's about it.

> The data to reload the table is coming from a Perl DBI connection to a di=
fferent database (not PostgreSQL) so I'm not sure the COPY alternative appl=
ies here.

COPY works nicely from perl:

$dbh->do("COPY foo FROM STDIN");
$dbh->pg_putcopydata("foo\tbar\tbaz\n");
$dbh->pg_putcopyend();

The details are in DBD::Pg. I use this a lot for doing big-ish (tens of mil=
lions of rows) bulk inserts. It's not as fast as you can get, but it's prob=
ably as fast as you can get with perl.

Cheers,
  Steve


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance