Message-ID: <06613990-9967-9304-ddba-e1fed931af80@gmail.com>
Date: Mon, 6 Dec 2021 23:01:00 -0500
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.3.0
Subject: Re: Transaction Size in PostgreSQL
Content-Language: en-US
To: pgsql-admin@lists.postgresql.org
References: 
 <CAGH8HirGbc_=EU3GYBWQYnofWU2y_ygjP=1gzp-j6wmE3CSMtQ@mail.gmail.com>
From: Mladen Gogala <gogala.mladen@gmail.com>
In-Reply-To: 
 <CAGH8HirGbc_=EU3GYBWQYnofWU2y_ygjP=1gzp-j6wmE3CSMtQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/06613990-9967-9304-ddba-e1fed931af80%40gmail.com>
Precedence: bulk

On 12/6/21 22:26, Sivasamy Subramaniam wrote:
> Hello Experts,
>
> I need a query or a way to find the transaction size in postgreSQL. 
> Can any one provide SQL or a method to figure this out?

How do you define transaction size? By the number of bytes it changed? 
By the duration of the transaction? By the amount of generated WAL 
entries? Maybe it's the amount of memory consumed by the transaction? 
All of these quantities could, with a reasonable justification, be 
called "transaction size". Anyway, the old adage says that size doesn't 
matter.  It's not the size of transaction that matters, regardless of 
how the size is defined, it's the magic in the optimizer. I am quoting 
from memory, I apologize if I got my quotes wrong. Why would you need 
such a measure? Are you trying to do some capacity planning for moving 
to the cloud? I have usually used things like "transactions per second" 
and measured the system response time.

You may want to try pg_stat_xact_user_tables which contains the numbers 
of updated, inserted and fetched rows. That could also be the measure of 
the transaction size. Probably the most useful way would be to build a 
measure into the application and let the application measure whatever 
you define as "transaction size".

Regards

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com