Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps
 (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <pgsql-admin-owner+archive@lists.postgresql.org>)
	id 1muUS5-00044h-MV
	for pgsql-admin@arkaria.postgresql.org; Tue, 07 Dec 2021 06:59:49 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-admin-owner+archive@lists.postgresql.org>)
	id 1muUS4-0007JM-5e
	for pgsql-admin@arkaria.postgresql.org; Tue, 07 Dec 2021 06:59:48 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <rui@crazybean.net>)
	id 1muUS3-0007JD-Qj
	for pgsql-admin@lists.postgresql.org; Tue, 07 Dec 2021 06:59:47 +0000
Received: from impout007aa.msg.chrl.nc.charter.net ([47.43.20.31]
 helo=impout007.msg.chrl.nc.charter.net)
	by magus.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <rui@crazybean.net>)
	id 1muURz-0003NL-1z
	for pgsql-admin@lists.postgresql.org; Tue, 07 Dec 2021 06:59:47 +0000
Received: from cerberus.crazybean.net ([71.11.29.254])
	by cmsmtp with ESMTP
	id uURwmPXuHhBsjuURwm4xQY; Tue, 07 Dec 2021 06:59:40 +0000
Authentication-Results: crazybean.net; none
X-Authority-Analysis: v=2.4 cv=BISgEXcG c=1 sm=1 tr=0 ts=61af065c
 a=GkUsAQdcAzAk0MT19QAIRQ==:117 a=GkUsAQdcAzAk0MT19QAIRQ==:17
 a=xqWC_Br6kY4A:10 a=IOMw9HtfNCkA:10 a=VVlED5B4AAAA:8 a=4SHaxXrHVHuSNhPgahcA:9
 a=CjuIK1q_8ugA:10 a=Bt0lIpEwqn8C4BjF8IwA:9 a=kVCzYhEB8wzHmqS4:21
 a=_W_S_7VecoQA:10
Received: from smtpclient.apple (<unknown>
 [2001:470:e117:4:75ff:9d6c:302:4ed8])
	by cerberus.crazybean.net (OpenSMTPD) with ESMTPSA id e07fcd6b
 (TLSv1.2:ECDHE-RSA-AES256-GCM-SHA384:256:NO);
	Tue, 7 Dec 2021 01:59:39 -0500 (EST)
From: Rui DeSousa <rui@crazybean.net>
Message-Id: <5681E7B8-DCF4-48B6-B32F-6895670BB000@crazybean.net>
Content-Type: multipart/alternative;
	boundary="Apple-Mail=_1EFDEB1A-A7F7-4010-B36B-7C6827F6781E"
Mime-Version: 1.0 (Mac OS X Mail 15.0 \(3693.20.0.1.32\))
Subject: Re: Transaction Size in PostgreSQL
Date: Tue, 7 Dec 2021 01:59:34 -0500
In-Reply-To: <17b19213-1588-dca9-b009-8913b8d1401f@gmx.net>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
To: Thomas Kellerer <shammat@gmx.net>
References: 
 <CAGH8HirGbc_=EU3GYBWQYnofWU2y_ygjP=1gzp-j6wmE3CSMtQ@mail.gmail.com>
 <CAKFQuwajBNigQZ=mU3_hG_-3uNcAzk6j_hPRSH=YDRU_ZKcZUA@mail.gmail.com>
 <CAGH8HiqXBToqe_EVqCxPaV1ZP+vKA9hQDvLEFE-UYRkR+RzwEw@mail.gmail.com>
 <17b19213-1588-dca9-b009-8913b8d1401f@gmx.net>
X-Mailer: Apple Mail (2.3693.20.0.1.32)
X-CMAE-Envelope: 
 MS4xfG6679F8ZnlEoeoQGYLb+EMgBu5i3Zax7vXERASdY9bi3IiJE6evoDv7cBRWWXpxttOx8sYKF0d1xlBTYK3XqghiJcbox9w1r8ySbf1dcdBZ7ja9Ah/j
 y9iMFXH15/5cYg+oNiLpkMt4v5WKefXOeoBjEiaHN5V4IlRXOWYzzZ+RkkrPqFXlMq8toAl86NZq+KmSblRrhT3B06LAKfKcPoVRennz3RyQL+ENXby8yFOP
List-Id: <pgsql-admin.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-admin@lists.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-admin>
Archived-At: 
 <https://www.postgresql.org/message-id/5681E7B8-DCF4-48B6-B32F-6895670BB000%40crazybean.net>
Precedence: bulk


--Apple-Mail=_1EFDEB1A-A7F7-4010-B36B-7C6827F6781E
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=us-ascii


> On Dec 7, 2021, at 1:41 AM, Thomas Kellerer <shammat@gmx.net> wrote:
>=20
> "Massive data changes" in a single transaction aren't really a =
problem.
> In my experience a single transaction changing a lot of rows is more
> efficient than multiple transactions changing fewer rows.

That might work on a idle system but I would have to disagree with that =
statement.  In theory it may be more efficient but it practice large =
long running transactions cause replication lag issues as well as =
vacuuming issues by holding on to older xmin value.  Basically, they are =
bad for high transaction systems. =20






--Apple-Mail=_1EFDEB1A-A7F7-4010-B36B-7C6827F6781E
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
	charset=us-ascii

<html><head><meta http-equiv=3D"Content-Type" content=3D"text/html; =
charset=3Dus-ascii"></head><body style=3D"word-wrap: break-word; =
-webkit-nbsp-mode: space; line-break: after-white-space;" class=3D""><br =
class=3D""><div><blockquote type=3D"cite" class=3D""><div class=3D"">On =
Dec 7, 2021, at 1:41 AM, Thomas Kellerer &lt;<a =
href=3D"mailto:shammat@gmx.net" class=3D"">shammat@gmx.net</a>&gt; =
wrote:</div><br class=3D"Apple-interchange-newline"><div class=3D""><span =
style=3D"caret-color: rgb(0, 0, 0); font-family: Helvetica; font-size: =
12px; font-style: normal; font-variant-caps: normal; font-weight: =
normal; letter-spacing: normal; text-align: start; text-indent: 0px; =
text-transform: none; white-space: normal; word-spacing: 0px; =
-webkit-text-stroke-width: 0px; text-decoration: none; float: none; =
display: inline !important;" class=3D"">"Massive data changes" in a =
single transaction aren't really a problem.</span><br =
style=3D"caret-color: rgb(0, 0, 0); font-family: Helvetica; font-size: =
12px; font-style: normal; font-variant-caps: normal; font-weight: =
normal; letter-spacing: normal; text-align: start; text-indent: 0px; =
text-transform: none; white-space: normal; word-spacing: 0px; =
-webkit-text-stroke-width: 0px; text-decoration: none;" class=3D""><span =
style=3D"caret-color: rgb(0, 0, 0); font-family: Helvetica; font-size: =
12px; font-style: normal; font-variant-caps: normal; font-weight: =
normal; letter-spacing: normal; text-align: start; text-indent: 0px; =
text-transform: none; white-space: normal; word-spacing: 0px; =
-webkit-text-stroke-width: 0px; text-decoration: none; float: none; =
display: inline !important;" class=3D"">In my experience a single =
transaction changing a lot of rows is more</span><br style=3D"caret-color:=
 rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: =
normal; font-variant-caps: normal; font-weight: normal; letter-spacing: =
normal; text-align: start; text-indent: 0px; text-transform: none; =
white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; =
text-decoration: none;" class=3D""><span style=3D"caret-color: rgb(0, 0, =
0); font-family: Helvetica; font-size: 12px; font-style: normal; =
font-variant-caps: normal; font-weight: normal; letter-spacing: normal; =
text-align: start; text-indent: 0px; text-transform: none; white-space: =
normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; =
text-decoration: none; float: none; display: inline !important;" =
class=3D"">efficient than multiple transactions changing fewer =
rows.</span></div></blockquote></div><br class=3D""><div class=3D"">That =
might work on a idle system but I would have to disagree with that =
statement. &nbsp;In theory it may be more efficient but it practice =
large long running transactions cause replication lag issues as well as =
vacuuming issues by holding on to older xmin value. &nbsp;Basically, =
they are bad for high transaction systems. &nbsp;</div><div class=3D""><br=
 class=3D""></div><div class=3D""><br class=3D""></div><div class=3D""><br=
 class=3D""></div><div class=3D""><br class=3D""></div><div class=3D""><br=
 class=3D""></div></body></html>=

--Apple-Mail=_1EFDEB1A-A7F7-4010-B36B-7C6827F6781E--