Re: INSERT times - same storage space but more fields -> much slower inserts

On Tue, 14 Apr 2009, Stephen Frost wrote:
> What does your test harness currently look like, and what would you like
> to see to test the binary-format COPY?  I'd be happy to write up the
> code necessary to implement binary-format COPY for this.

If anyone needs this code in Java, we have a version at
http://www.intermine.org/

Download source code: http://www.intermine.org/wiki/SVNCheckout

Javadoc: http://www.intermine.org/api/

The code is contained in the org.intermine.sql.writebatch package, in the
intermine/objectstore/main/src/org/intermine/sql/writebatch directory in
the source.

The public interface is org.intermine.sql.writebatch.Batch.

The Postgres-specific binary COPY code is in
org.intermine.sql.writebatch.BatchWriterPostgresCopyImpl.

The implementation unfortunately relies on a very old modified version of
the Postgres JDBC driver, which is in the intermine/objectstore/main/lib
directory.

The code is released under the LGPL, and we would appreciate notification
if it is used.

The code implements quite a sophisticated system for writing rows to
database tables very quickly. It batches together multiple writes into
COPY statements, and writes them in the background in another thread,
while fully honouring flush calls. When it is using the database
connection is well-defined. I hope someone can find it useful.

Matthew

--
 -. .-.   .-. .-.   .-. .-.   .-. .-.   .-. .-.   .-. .-.   .-.
 ||X|||\ /|||X|||\ /|||X|||\ /|||X|||\ /|||X|||\ /|||X|||\ /|||
 |/ \|||X|||/ \|||X|||/ \|||X|||/ \|||X|||/ \|||X|||/ \|||X|||/
 '   `-' `-'   `-' `-'   `-' `-'   `-' `-'   `-' `-'   `-' `-'