lib/postgres/vacuum/monitor/query.rb in postgres-vacuum-monitor-0.9.0 vs lib/postgres/vacuum/monitor/query.rb in postgres-vacuum-monitor-0.10.0

@@ -95,9 +95,31 @@
               AND blocking_locks.pid != blocked_locks.pid
             JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
             WHERE NOT blocked_locks.GRANTED;
           SQL
         end
+
+        def connection_state
+          <<-SQL
+            SELECT 
+              state, count(*) as connection_count 
+            FROM pg_stat_activity 
+            GROUP BY state 
+            ORDER BY count DESC;
+          SQL
+        end
+
+        def connection_idle_time
+          <<-SQL
+            SELECT
+                max(EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - state_change))) as max,
+                percentile_cont(0.5) within GROUP (ORDER BY EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - state_change)) DESC) AS median,
+                percentile_cont(0.9) within GROUP (ORDER BY EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - state_change)) DESC) AS percentile_90
+            FROM pg_stat_activity
+            WHERE state = 'idle'
+            LIMIT 1000;
+          SQL
+        end
       end
     end
   end
 end