pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedTransaction Size in PostgreSQL
8+ messages / 5 participants
[nested] [flat]
* Transaction Size in PostgreSQL
@ 2021-12-07 03:26 Sivasamy Subramaniam <sivasamyinfo@gmail.com>
2021-12-07 04:01 ` Re: Transaction Size in PostgreSQL Mladen Gogala <gogala.mladen@gmail.com>
2021-12-07 04:04 ` Re: Transaction Size in PostgreSQL David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: Sivasamy Subramaniam @ 2021-12-07 03:26 UTC (permalink / raw)
To: pgsql-admin <pgsql-admin@lists.postgresql.org>
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?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Transaction Size in PostgreSQL
2021-12-07 03:26 Transaction Size in PostgreSQL Sivasamy Subramaniam <sivasamyinfo@gmail.com>
@ 2021-12-07 04:01 ` Mladen Gogala <gogala.mladen@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Mladen Gogala @ 2021-12-07 04:01 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Transaction Size in PostgreSQL
2021-12-07 03:26 Transaction Size in PostgreSQL Sivasamy Subramaniam <sivasamyinfo@gmail.com>
@ 2021-12-07 04:04 ` David G. Johnston <david.g.johnston@gmail.com>
2021-12-07 05:13 ` Re: Transaction Size in PostgreSQL Sivasamy Subramaniam <sivasamyinfo@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: David G. Johnston @ 2021-12-07 04:04 UTC (permalink / raw)
To: Sivasamy Subramaniam <sivasamyinfo@gmail.com>; +Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
On Monday, December 6, 2021, Sivasamy Subramaniam <sivasamyinfo@gmail.com>
wrote:
>
>
> 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?
>
That doesn’t exist, or is even particularly well defined, that I know of.
You may want to phrase your question using more detail. Though I suspect
your best avenue would be WAL decoding (but that’s well outside my
experience).
David J.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Transaction Size in PostgreSQL
2021-12-07 03:26 Transaction Size in PostgreSQL Sivasamy Subramaniam <sivasamyinfo@gmail.com>
2021-12-07 04:04 ` Re: Transaction Size in PostgreSQL David G. Johnston <david.g.johnston@gmail.com>
@ 2021-12-07 05:13 ` Sivasamy Subramaniam <sivasamyinfo@gmail.com>
2021-12-07 06:41 ` Re: Transaction Size in PostgreSQL Thomas Kellerer <shammat@gmx.net>
2021-12-07 13:00 ` Re: Transaction Size in PostgreSQL David G. Johnston <david.g.johnston@gmail.com>
2021-12-07 14:12 ` Re: Transaction Size in PostgreSQL Mladen Gogala <gogala.mladen@gmail.com>
0 siblings, 3 replies; 8+ messages in thread
From: Sivasamy Subramaniam @ 2021-12-07 05:13 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
Transaction size meaning, for example - any transaction over 1GB in size? I
am looking to set up an alert if any massive data changes are happening in
a single transaction. I can do it for long running transactions or query
but trying to figure out any easy way to calculate the amount of data
changes per transaction.
On Mon, Dec 6, 2021 at 8:04 PM David G. Johnston <david.g.johnston@gmail.com>
wrote:
> On Monday, December 6, 2021, Sivasamy Subramaniam <sivasamyinfo@gmail.com>
> wrote:
>>
>>
>> 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?
>>
>
> That doesn’t exist, or is even particularly well defined, that I know of.
> You may want to phrase your question using more detail. Though I suspect
> your best avenue would be WAL decoding (but that’s well outside my
> experience).
>
> David J.
>
>
--
Thank you,
Siva.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Transaction Size in PostgreSQL
2021-12-07 03:26 Transaction Size in PostgreSQL Sivasamy Subramaniam <sivasamyinfo@gmail.com>
2021-12-07 04:04 ` Re: Transaction Size in PostgreSQL David G. Johnston <david.g.johnston@gmail.com>
2021-12-07 05:13 ` Re: Transaction Size in PostgreSQL Sivasamy Subramaniam <sivasamyinfo@gmail.com>
@ 2021-12-07 06:41 ` Thomas Kellerer <shammat@gmx.net>
2021-12-07 06:59 ` Re: Transaction Size in PostgreSQL Rui DeSousa <rui@crazybean.net>
2 siblings, 1 reply; 8+ messages in thread
From: Thomas Kellerer @ 2021-12-07 06:41 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Sivasamy Subramaniam schrieb am 07.12.2021 um 06:13:
> Transaction size meaning, for example - any transaction over 1GB in
> size? I am looking to set up an alert if any massive data changes are
> happening in a single transaction. I can do it for long running
> transactions or query but trying to figure out any easy way to
> calculate the amount of data changes per transaction.
A long running transaction doesn't necessary mean it wrote "1GB" of data,
and a transaction that writes 1GB of data isn't necessarily a "long running"
transaction.
"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.
A long _running_ transaction - even if it only changed one row - is a much
bigger problem though.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Transaction Size in PostgreSQL
2021-12-07 03:26 Transaction Size in PostgreSQL Sivasamy Subramaniam <sivasamyinfo@gmail.com>
2021-12-07 04:04 ` Re: Transaction Size in PostgreSQL David G. Johnston <david.g.johnston@gmail.com>
2021-12-07 05:13 ` Re: Transaction Size in PostgreSQL Sivasamy Subramaniam <sivasamyinfo@gmail.com>
2021-12-07 06:41 ` Re: Transaction Size in PostgreSQL Thomas Kellerer <shammat@gmx.net>
@ 2021-12-07 06:59 ` Rui DeSousa <rui@crazybean.net>
0 siblings, 0 replies; 8+ messages in thread
From: Rui DeSousa @ 2021-12-07 06:59 UTC (permalink / raw)
To: Thomas Kellerer <shammat@gmx.net>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
> On Dec 7, 2021, at 1:41 AM, Thomas Kellerer <shammat@gmx.net> wrote:
>
> "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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Transaction Size in PostgreSQL
2021-12-07 03:26 Transaction Size in PostgreSQL Sivasamy Subramaniam <sivasamyinfo@gmail.com>
2021-12-07 04:04 ` Re: Transaction Size in PostgreSQL David G. Johnston <david.g.johnston@gmail.com>
2021-12-07 05:13 ` Re: Transaction Size in PostgreSQL Sivasamy Subramaniam <sivasamyinfo@gmail.com>
@ 2021-12-07 13:00 ` David G. Johnston <david.g.johnston@gmail.com>
2 siblings, 0 replies; 8+ messages in thread
From: David G. Johnston @ 2021-12-07 13:00 UTC (permalink / raw)
To: Sivasamy Subramaniam <sivasamyinfo@gmail.com>; +Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
On Monday, December 6, 2021, Sivasamy Subramaniam <sivasamyinfo@gmail.com>
wrote:
> Transaction size meaning, for example - any transaction over 1GB in size?
> I am looking to set up an alert if any massive data changes are happening
> in a single transaction. I can do it for long running transactions or query
> but trying to figure out any easy way to calculate the amount of data
> changes per transaction.
>
So if you truncate a 4GB table you want to know about it? I’m reasonably
confident PostgreSQL does not have the tools needed to accomplish your goal
at 100%. You can possibly pick up some subset though if you really want…
David J.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Transaction Size in PostgreSQL
2021-12-07 03:26 Transaction Size in PostgreSQL Sivasamy Subramaniam <sivasamyinfo@gmail.com>
2021-12-07 04:04 ` Re: Transaction Size in PostgreSQL David G. Johnston <david.g.johnston@gmail.com>
2021-12-07 05:13 ` Re: Transaction Size in PostgreSQL Sivasamy Subramaniam <sivasamyinfo@gmail.com>
@ 2021-12-07 14:12 ` Mladen Gogala <gogala.mladen@gmail.com>
2 siblings, 0 replies; 8+ messages in thread
From: Mladen Gogala @ 2021-12-07 14:12 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 12/7/21 00:13, Sivasamy Subramaniam wrote:
> Transaction size meaning, for example - any transaction over 1GB in
> size? I am looking to set up an alert if any massive data changes are
> happening in a single transaction. I can do it for long running
> transactions or query but trying to figure out any easy way to
> calculate the amount of data changes per transaction.
You will have to build such tool into the application. Application will
have to monitor pg_stat_xact_user_tables and send alert if the alert
threshold is exceeded. That will make the application unnecessarily
complex and will also impact the performance. This falls into the "no
can do" category.
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2021-12-07 14:12 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-12-07 03:26 Transaction Size in PostgreSQL Sivasamy Subramaniam <sivasamyinfo@gmail.com>
2021-12-07 04:01 ` Mladen Gogala <gogala.mladen@gmail.com>
2021-12-07 04:04 ` David G. Johnston <david.g.johnston@gmail.com>
2021-12-07 05:13 ` Sivasamy Subramaniam <sivasamyinfo@gmail.com>
2021-12-07 06:41 ` Thomas Kellerer <shammat@gmx.net>
2021-12-07 06:59 ` Rui DeSousa <rui@crazybean.net>
2021-12-07 13:00 ` David G. Johnston <david.g.johnston@gmail.com>
2021-12-07 14:12 ` Mladen Gogala <gogala.mladen@gmail.com>
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