Q: What is the difference between State_change_t and Query_start_t, and why do they sometimes show the same value?

Query_start_t is the moment the current (or last) query began executing. State_change_t is the moment the backend last changed state (e.g., idle → active, active → idle).

They are equal when the query is the cause of the state transition — i.e., the backend was idle, received a new query, and both events happened atomically at the same instant. Once the query finishes and the backend goes idle again, State_change_t advances to that new moment while Query_start_t stays frozen at when the query started (until the next one arrives).

sequenceDiagram
    participant Client
    participant Backend
    participant Snapshotter

    Note over Backend: Backend_start_t — process forked, connection accepted

    Client->>Backend: send query
    Note over Backend: Query_start_t = now<br/>State_change_t = now<br/>(idle → active, simultaneous)

    Backend->>Backend: executing…

    Backend->>Client: return result
    Note over Backend: State_change_t = now  (active → idle)<br/>Query_start_t unchanged

    Note over Snapshotter: Collection_time_t — snapshot taken<br/>(reads pg_stat_activity as-is)
    Snapshotter->>Backend: SELECT * FROM pg_stat_activity

Timeline order (earliest → latest):

1. Backend_start_t — connection established
2. Query_start_t — current/last query started
3. State_change_t — last state transition (≥ Query_start_t; equal when caught mid-query)
4. Collection_time_t — snapshot time (always the latest)

Q: Is Pid_d unique? Or is the same PID collected across multiple snapshots?

Neither unique nor stable across time. Two things to be aware of:

• Same PID, multiple rows — a long-lived connection (connection pool backend, pg_cron, walsender) will appear in every 5-minute snapshot with the same Pid_d for as long as it stays connected. The primary key for a session row is (LogicalServerName_s, Pid_d, Collection_time_t).
• PID reuse — after a connection closes, the OS can assign the same PID to a new connection. A Pid_d seen in a snapshot one hour later may be a completely different session. Use Backend_start_t alongside Pid_d to uniquely identify a session across time.

Q: State_s is always idle in my sample. Does this mean all connections are idle? Is this the state at collection time or across the whole interval?

It is the state at the exact moment the snapshot is taken — a single point-in-time read of pg_stat_activity. It is not an average or a summary of the past 5 minutes.

Seeing mostly idle is normal and expected for a healthy OLTP workload. Typical query durations are milliseconds; the snapshot window is 5 minutes. The probability of the collector catching a query mid-flight is very low:

• Connections exist and are ready, but happen to be between queries when sampled.
• idle in transaction is worth flagging — those sessions hold open transactions long enough to be visible across snapshots.
• For cumulative wait profiling use PostgreSQLFlexQueryStoreWaitStats.

Q: What is the difference between idle, idle in transaction, and the other State_s values?

active — the backend is currently executing a query. CPU is working or the backend is waiting on I/O, locks, or other resources as part of processing a statement.

idle — the backend finished its last query, committed/rolled back any transaction, and is now parked waiting for the next command from the client. No transaction is open, no locks are held. Healthy resting state.

idle in transaction — a BEGIN was issued and the transaction is still open, but no query is currently running. This state is problematic:

• Every lock acquired during the transaction is still held, blocking other sessions.
• The backend's Backend_xmin_d holds back the global xmin horizon, preventing VACUUM from reclaiming dead tuples across the entire cluster.
• Alert if State_s == "idle in transaction" persists across multiple 5-minute snapshots.

idle in transaction (aborted) — same as above, but an error already occurred inside the transaction and Postgres has marked it aborted. Every subsequent SQL statement in this connection will fail with ERROR: current transaction is aborted. The client must send ROLLBACK. Same bloat and lock risks.

fastpath function call — the backend is executing a libpq fast-path call (low-level protocol shortcut for calling a function by OID). Very rarely seen.

disabled — track_activities is off for this backend. Postgres records the row but cannot report the actual state. Treat as a monitoring blind spot.

Q: Xact_start_t, Backend_xid_d, and Backend_xmin_d are always null. Why?

Each field has its own reason, but the common root cause is that most sessions are idle — sitting between queries with no active transaction.

Xact_start_t — set only while a backend is inside a transaction (states active or idle in transaction). The moment the transaction commits or rolls back, Postgres clears it.

Backend_xid_d — Postgres uses two kinds of transaction IDs: virtual (always assigned, free) and real (allocated from a global counter). A real XID is only assigned at the first write in a transaction. Read-only transactions never get a real XID. So this field is null for any idle session and any read-only transaction.

Backend_xmin_d — the oldest XID this backend needs to keep alive (its snapshot horizon). Set when a backend acquires a snapshot to execute a query, cleared when the query finishes. Since queries are short and the collector arrives 5 minutes later, this is almost always null.

In practice these three are only observable when the collector catches a backend mid-write-transaction — a rare event at 5-minute intervals.