PostgreSQL – Difference Between xact_start and query_start

postgresql

I am reviewing over the pg_stat_activity view. And there are two fields that are indistinguishable to me:

xact_start:The time at which the transaction started to run which
could be different than the query_start but should always have
occurred before the query_start time.

query_start: The time at which the query started to run. This can be
useful to determine if a query has been running for a long time.

It says they both determine the start time of the query. It also says that xact_start can be different than query_start, but doesn't explain why – only that it should start prior to query_start. But what is the actual difference between the two? Why would I care about xact_start?

Best Answer

It also says that xact_start can be different than query_start, but doesn't explain why

It differs when the transaction encapsulates several queries, for example:

BEGIN;  -- starts at t0, beginning of transaction
query1; -- starts at t1
query2; -- starts at t2
COMMIT; -- starts at t4, end of transaction

If we look at the pg_stat_activity entry for this session while query2 is running, xact_starts will be t0 and query_start will be t2, with t2>t0, the difference being essentially the duration of query1.

xact_start helps discovering long running transactions, which is useful when troubleshooting or tuning a live database.

Related Question