From: Carlo <reg01@stonebanks.ca>
To: pgsql-performance@postgresql.org
Subject: One long transaction or multiple short transactions?
Date: Mon, 5 Oct 2015 23:10:49 -0400
Message-ID: <003d01d0ffe4$9ae8f890$d0bae9b0$@stonebanks.ca> (raw)
In-Reply-To: <002a01d0ffe4$2c630e60$85292b20$@stonebanks.ca>
References: <002a01d0ffe4$2c630e60$85292b20$@stonebanks.ca>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
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: pgsql-performance@postgresql.org
Cc: reg01@stonebanks.ca
Subject: Re: One long transaction or multiple short transactions?
In-Reply-To: <003d01d0ffe4$9ae8f890$d0bae9b0$@stonebanks.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox