The following query shows you how many queries are currently being executed on your database:

test=# SELECT datname, 
        count(*) AS open, 
        count(*) FILTER (WHERE state = 'active') AS active, 
        count(*) FILTER (WHERE state = 'idle') AS idle, 
        count(*) FILTER (WHERE state = 'idle in transaction') 
                 AS idle_in_trans 
FROM pg_stat_activity 
WHERE backend_type = 'client backend'
GROUP BY ROLLUP(1);
 datname | open | active | idle | idle_in_trans 
---------+------+--------+------+---------------
 test    | 2    | 1      | 0    | 1
         | 2    | 1      | 0    | 1
(2 rows)

To show as much information as possible on the same screen, partial aggregates are used. We can see active, idle, and idle-in-transaction queries. If we can see a high number of idle-in-transaction queries, it is definitely important to dig deeper, in order to figure out how long those transactions have been kept open:

test=# SELECT pid, xact_start, now() - xact_start AS duration 
   FROM pg_stat_activity 
   WHERE state LIKE '%transaction%' 
   ORDER BY 3 DESC;
 pid    | xact_start                    | duration
--------+-------------------------------+-----------------
  19758 | 2017-11-26 20:27:08.168554+01 | 22:12:10.194363

(1 row)

The transaction in the preceding listing has been open for more than 22 hours. The main question now is: how can a transaction be open for that long? In most applications, a transaction that takes so long is highly suspicious, and potentially highly dangerous. Where does the danger come from? As we learned earlier in this book, the VACUUM command can only clean up dead rows if no transaction can see it anymore. Now, if a transaction stays open for hours or even days, the VACUUM command cannot produce useful results, which will lead to table bloat.

It is therefore highly recommended to ensure that long transactions are monitored or killed, in case they become too long. From version 9.6 onward, PostgreSQL has a feature called snapshot too old, which allows us to terminate long transactions if snapshots are around for too long.

It is also a good idea to check whether there are any long-running queries going on:

test=# SELECT now() - query_start AS duration, datname, query
       FROM pg_stat_activity
       WHERE state = 'active'
       ORDER BY 1 DESC;
 duration         | datname | query
------------------+---------+---------------------------
 00:00:38.814526  | dev     | SELECT pg_sleep(10000);
 00:00:00         | test    | SELECT now() - query_start AS duration, 
                                     datname, query 
                              FROM  pg_stat_activity 
                              WHERE state = 'active' 
                              ORDER BY 1 DESC;
(2 rows)

In this case, all active queries are taken, and the statements calculate how long each query has already been active. Often, we see similar queries coming out on top, which can give us some valuable clues about what is happening on our system.