Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1ZjIgF-0001O5-1q for pgsql-performance@arkaria.postgresql.org; Tue, 06 Oct 2015 03:12:44 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1ZjIgC-00070A-Iv for pgsql-performance@arkaria.postgresql.org; Tue, 06 Oct 2015 03:12:40 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ZjIeY-00058e-Uk for pgsql-performance@postgresql.org; Tue, 06 Oct 2015 03:10:59 +0000 Received: from sub5.mail.dreamhost.com ([208.113.200.129] helo=homiemail-a115.g.dreamhost.com) by makus.postgresql.org with esmtp (Exim 4.84) (envelope-from ) id 1ZjIeV-0000gT-60 for pgsql-performance@postgresql.org; Tue, 06 Oct 2015 03:10:57 +0000 Received: from homiemail-a115.g.dreamhost.com (localhost [127.0.0.1]) by homiemail-a115.g.dreamhost.com (Postfix) with ESMTP id 31DD55A1C for ; Mon, 5 Oct 2015 20:10:53 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha1; c=relaxed; d=stonebanks.ca; h=from:to :references:in-reply-to:subject:date:message-id:mime-version :content-type; s=stonebanks.ca; bh=46TREXKR0XQaT5vsAdu1O9zKYvc=; b= i7JJibJWq04UC83Hpl6nKBePNS+BDlt2d8oay504lKv8+oxkcdMe4WPa3/iZjO4V hD8dMpr3GQEd/d4AGi1Nl+AXGl4/0NcGPXhI18qpXlWSAx4q0RdYLCBW/iRiAHKI F1F2bZftfdjYTN+hsdF5Il3HuVE2JTIoNI7n0i7uVmA= Received: from CAPRICA (modemcable216.122-161-184.mc.videotron.ca [184.161.122.216]) (Authenticated sender: carlo@stonebanks.ca) by homiemail-a115.g.dreamhost.com (Postfix) with ESMTPA id E0856570E for ; Mon, 5 Oct 2015 20:10:52 -0700 (PDT) From: "Carlo" To: References: <002a01d0ffe4$2c630e60$85292b20$@stonebanks.ca> In-Reply-To: <002a01d0ffe4$2c630e60$85292b20$@stonebanks.ca> Subject: One long transaction or multiple short transactions? Date: Mon, 5 Oct 2015 23:10:49 -0400 Message-ID: <003d01d0ffe4$9ae8f890$d0bae9b0$@stonebanks.ca> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_003E_01D0FFC3.13D7A6B0" X-Mailer: Microsoft Outlook 14.0 Thread-Index: AQFD1GuMjlYKSpoTBolbgZAO1EVkoJ94H8Ow Content-Language: en-ca X-Pg-Spam-Score: -0.8 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multipart message in MIME format. ------=_NextPart_000_003E_01D0FFC3.13D7A6B0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit We have a system which is constantly importing flat file data feeds into normalized tables in a DB warehouse over 10-20 connections. Each data feed row results in a single transaction of multiple single row writes to multiple normalized tables. The more columns in the feed row, the more write operations, longer the transaction. Operators are noticing that splitting a single feed of say - 100 columns - into two consecutive feeds of 50 columns improves performance dramatically. I am wondering whether the multi-threaded and very busy import environment causes non-linear performance degradation for longer transactions. Would the operators be advised to rewrite the feeds to result in more smaller transactions rather than fewer, longer ones? Carlo ------=_NextPart_000_003E_01D0FFC3.13D7A6B0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

We have a = system which is constantly importing flat file data feeds into = normalized tables in a DB warehouse over 10-20 connections. Each data = feed row results in a single transaction of multiple single row writes = to multiple normalized tables.

 

The more = columns in the feed row, the more write operations, longer the = transaction.

 

Operators are noticing that splitting a single feed of = say – 100 columns – into two consecutive feeds of 50 columns = improves performance dramatically. I am wondering whether the = multi-threaded and very busy import environment causes non-linear = performance degradation for longer transactions. Would the operators be = advised to rewrite the feeds to result in more smaller transactions = rather than fewer, longer ones?

 

Carlo

------=_NextPart_000_003E_01D0FFC3.13D7A6B0--