Processes
Currently-running queries
Long-running queries
select now() - query_start as running_since
, pid
, datname
, usename
, application_name
, client_addr
, left(query, 60)
from pg_stat_activity
where state in ('active', 'idle in transaction')
and (now() - query_start) > interval '5 minutes';
Oldest running queries
with q as (
select
(
select max(age(backend_xmin))
from pg_stat_activity
where state != 'idle'
) as oldest_running_xact_age,
(
select max(age(transaction))
from pg_prepared_xacts
) as oldest_prepared_xact_age,
(
select max(greatest(age(catalog_xmin),age(xmin)))
from pg_replication_slots
) as oldest_replication_slot_age,
(
select max(age(backend_xmin))
from pg_stat_replication
) as oldest_replica_xact_age
)
select *
, 2^31 - oldest_running_xact_age as oldest_running_xact_left
, 2^31 - oldest_prepared_xact_age as oldest_prepared_xact_left
, 2^31 - oldest_replication_slot_age as oldest_replication_slot_left
, 2^31 - oldest_replica_xact_age as oldest_replica_xact_left
from q;
Idle transactions
select pid
, age(backend_xid) AS age_in_xids
, now() - xact_start AS xact_age
, now() - query_start AS query_age
, state
, query
from pg_stat_activity
where state != 'idle'
order by 2 desc
limit 10;