X-Original-To: pgsql-jdbc-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 948C8D1B1F8; Sat, 5 Jun 2004 17:12:38 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 66133-09; Sat, 5 Jun 2004 20:12:31 +0000 (GMT) Received: from noao.edu (noao.edu [140.252.1.54]) by svr1.postgresql.org (Postfix) with ESMTP id A36ECD1B1D1; Sat, 5 Jun 2004 17:12:28 -0300 (ADT) X-TFF-CGPSA-Version: 1.2.7 X-TFF-CGPSA-Filter: Scanned Received: from weaver.tuc.noao.edu ([140.252.38.8] verified) by noao.edu (CommuniGate Pro SMTP 4.1.8) with ESMTP-TLS id 12292472; Sat, 05 Jun 2004 13:12:29 -0700 Received: from weaver.tuc.noao.edu (localhost.localdomain [127.0.0.1]) by weaver.tuc.noao.edu (8.12.8/8.12.8) with ESMTP id i55KCTQ5016107; Sat, 5 Jun 2004 13:12:29 -0700 Received: (from swampler@localhost) by weaver.tuc.noao.edu (8.12.8/8.12.8/Submit) id i55KCTfs016105; Sat, 5 Jun 2004 13:12:29 -0700 X-Authentication-Warning: weaver.tuc.noao.edu: swampler set sender to swampler@noao.edu using -f Subject: Using a COPY...FROM through JDBC? From: Steve Wampler Reply-To: swampler@noao.edu To: Postgres-JDBC , Postgres-performance Content-Type: text/plain Content-Transfer-Encoding: 7bit Organization: National Solar Observatory Message-Id: <1086466349.29063.76.camel@weaver.tuc.noao.edu> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 Date: Sat, 05 Jun 2004 13:12:29 -0700 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/22 X-Sequence-Number: 10224 I've got a simple database (no indices, 6 columns) that I need to write data quickly into through JDBC connections from multiple such connections simultaneously in a distributed environment. (This is going to be a message logging service for software generated messages.) Using a PreparedStatement, I can get about 400/s inserted. If I (on the java side) buffer up the entries and dump them in large transaction blocks I can push this up to about 1200/s. I'd like to go faster. One approach that I think might be promising would be to try using a COPY command instead of an INSERT, but I don't have a file for input, I have a Java collection, so COPY isn't quite right. Is there anyway to efficiently use COPY without having to create a file (remember that the java apps are distributed on a LAN and aren't running on the DB server.) Is this a dead end because of the way COPY is implemented to only use a file? Is there something else I can do? Ultimately, this will end up on a machine running 1+0 RAID, so I expect that will give me some performance boost as well, but I'd like to push it up as best I can with my current hardware setup. Thanks for any advice! -Steve -- Steve Wampler -- swampler@noao.edu The gods that smiled on your birth are now laughing out loud.