Re: Transaction Size in PostgreSQL

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