From: hatman <joel.winteregg@gmail.com>
To: pgsql-performance@postgresql.org
Cc: Andreas Kostyrka <andreas@kostyrka.org>
Cc: Richard Huxton <dev@archonet.com>
Subject: Re: Insert performance
Date: 6 Mar 2007 07:38:25 -0800
Message-ID: <1173195505.228450.239060@c51g2000cwc.googlegroups.com> (raw)
In-Reply-To: <20070306122342.GO22981@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>
Hi Andreas,
Thanks for the info about COPY !!
On Mar 6, 1:23 pm, andr...@kostyrka.org (Andreas Kostyrka) wrote:
> * Richard Huxton <d...@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).
Ok, but what about constraints (foreign keys and SERIAL id) using a
copy statement ? do we need to handle auto-generated id (SERIAL)
manually ?
Thanks for your feedback.
Regards,
Joël
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: joel.winteregg@gmail.com, andreas@kostyrka.org, dev@archonet.com
Subject: Re: Insert performance
In-Reply-To: <1173195505.228450.239060@c51g2000cwc.googlegroups.com>
* 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