Date: Tue, 6 Mar 2007 13:49:37 +0100
From: Andreas Kostyrka <andreas@kostyrka.org>
To: Richard Huxton <dev@archonet.com>
Cc: =?iso-8859-1?Q?jo=EBl?= Winteregg <joel.winteregg@gmail.com>,
	pgsql-performance@postgresql.org
Subject: Re: Insert performance
Message-ID: <20070306124937.GP22981@andi-lap.la.revver.com>
References: <1172596221.393824.41130@j27g2000cwj.googlegroups.com>
	<45ED1996.7050008@archonet.com>
	<1173167607.4917.8.camel@hatman> <45ED217D.30404@archonet.com>
	<1173172748.4917.23.camel@hatman> <45ED37D1.3000503@archonet.com>
	<20070306122342.GO22981@andi-lap.la.revver.com>
	<45ED5D9A.2010702@archonet.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <45ED5D9A.2010702@archonet.com>
User-Agent: mutt-ng/devel-r804 (Debian)

* Richard Huxton <dev@archonet.com> [070306 13:47]:
> Andreas Kostyrka wrote:
> >* Richard Huxton <dev@archonet.com> [070306 12:22]:
> >>>>2. You can do a COPY from libpq - is it really not possible?
> >>>>
> >>>Not really but i have been testing it and inserts are flying (about
> >>>100000 inserts/sec) !!
> >>What's the problem with the COPY? Could you COPY into one table then insert from that to your target table?
> >Well, there are some issues. First your client needs to support it.
> >E.g. psycopg2 supports only some specific CSV formatting in it's
> >methods. (plus I had sometimes random psycopg2 crashes, but guarding against
> >these is cheap compared to the speedup from COPY versus INSERT)
> >Plus you need to be sure that your data will apply cleanly (which in
> >my app was not the case), or you need to code a fallback that
> >localizes the row that doesn't work.
> >And the worst thing is, that it ignores RULES on the tables, which
> >sucks if you use them ;) (e.g. table partitioning).
> 
> Ah, but two things deal with these issues:
> 1. Joel is using libpq
> 2. COPY into a holding table, tidy data and INSERT ... SELECT

Clearly COPY is the way for bulk loading data, BUT you asked, so I
wanted to point out some problems and brittle points with COPY.

(and the copy into the holding table doesn't solve completly the
problem with the dirty inconsistent data)

Andreas