public inbox for [email protected]  

help / color / mirror / Atom feed

From: Dimitri Fontaine <[email protected]>
To: [email protected]
Cc: Josh Berkus <[email protected]>
Cc: Greg Smith <[email protected]>
Subject: Re: VLDB Features
Date: Wed, 12 Dec 2007 11:19:02 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>

Hi,

Le mercredi 12 décembre 2007, Josh Berkus a écrit :
> > I'm curious what you feel is missing that pgloader doesn't fill that
> > requirement:  http://pgfoundry.org/projects/pgloader/
>
> Because pgloader is implemented in middleware, it carries a very high
> overhead if you have bad rows.  As little as 1% bad rows will slow down
> loading by 20% due to retries.

Not that much, in fact, I'd say.
pgloader allows its user to configure how large a COPY buffer to use (global 
parameter as of now, could easily be a per-section configuration knob, just 
didn't see any need for this yet).
It's the 'copy_every' parameter as seen on the man page here:
  http://pgloader.projects.postgresql.org/#toc4

pgloader will obviously prepare a in-memory buffer of copy_every tuples to 
give to COPY, and in case of error will cut it and retry. Classic dichotomy 
approach, from initial implementation by Jan Wieck.

So you can easily balance the error recovery costs against the COPY bulk size.

Note also that the overall loading time with pgloader is not scaling the same 
as the COPY buffer size, the optimal choice depends on the dataset --- and 
the data massaging pgloader has to make on it ---, and I've experienced best 
results with 10000 and 15000 tuples buffers so far.

FYI, now the pgloader topic is on the table, the next items I think I'm gonna 
develop for it are configurable behavior on errors tuples (load to another 
table when pk error, e.g.), and some limited ddl-partioning support.

I'm playing with the idea for pgloader to be able to read some partitioning 
schemes (parsing CHECK constraint on inherited tables) and load directly into 
the right partitions.
That would of course be done only when configured this way, and if constraints 
are misread it would only result in a lot more rejected rows than expected, 
and you still can retry using your insert trigger instead of pgloader buggy 
smartness.

Comments welcome, regards,
-- 
dim


Attachments:

  [application/pgp-signature] signature.asc (189B, ../[email protected]/2-signature.asc)
  download

view thread (39+ messages)  latest in thread

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 using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: VLDB Features
  In-Reply-To: <[email protected]>

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

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