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;