From: Dimitri Fontaine <dfontaine@hi-media.com>
Organization: Hi-Media
To: pgsql-hackers@postgresql.org
Subject: Re: VLDB Features
Date: Wed, 12 Dec 2007 11:19:02 +0100
User-Agent: KMail/1.9.7
Cc: Josh Berkus <josh@agliodbs.com>,
 Greg Smith <gsmith@gregsmith.com>
References: <1197371508.4255.1327.camel@ebony.site>
	<Pine.GSO.4.64.0712111845220.21402@westnet.com>
	<200712112036.06273.josh@agliodbs.com>
In-Reply-To: <200712112036.06273.josh@agliodbs.com>
MIME-Version: 1.0
Content-Type: multipart/signed; boundary="nextPart3089888.KruB4fYodW";
	protocol="application/pgp-signature"; micalg=pgp-sha1
Content-Transfer-Encoding: 7bit
Message-Id: <200712121119.05192.dfontaine@hi-media.com>

--nextPart3089888.KruB4fYodW
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi,

Le mercredi 12 d=E9cembre 2007, Josh Berkus a =E9crit=A0:
> > 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 (globa=
l=20
parameter as of now, could easily be a per-section configuration knob, just=
=20
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=
=20
give to COPY, and in case of error will cut it and retry. Classic dichotomy=
=20
approach, from initial implementation by Jan Wieck.

So you can easily balance the error recovery costs against the COPY bulk si=
ze.

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

=46YI, now the pgloader topic is on the table, the next items I think I'm g=
onna=20
develop for it are configurable behavior on errors tuples (load to another=
=20
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=
=20
schemes (parsing CHECK constraint on inherited tables) and load directly in=
to=20
the right partitions.
That would of course be done only when configured this way, and if constrai=
nts=20
are misread it would only result in a lot more rejected rows than expected,=
=20
and you still can retry using your insert trigger instead of pgloader buggy=
=20
smartness.

Comments welcome, regards,
=2D-=20
dim

--nextPart3089888.KruB4fYodW
Content-Type: application/pgp-signature; name=signature.asc 
Content-Description: This is a digitally signed message part.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQBHX7WZlBXRlnbh1bkRAsbUAJ9A8oXjoIOR+ouiRjhxXEaYhAqv3ACfU3Il
4etiuHU01fijLWo+QINnn7Y=
=Okk4
-----END PGP SIGNATURE-----

--nextPart3089888.KruB4fYodW--