What is the difference between xact_start and query_start in postgresql?

Question

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?

Answer 1

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.

• PainIsAMaster: doe that mean when Query2 runs, it willl Update the query_start column of the table with the new time?