SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
/* 3. Connections waiting for a lock */
SELECT count(distinct pid) FROM pg_locks WHERE granted = false
/* 4. Maximum Transaction Age */
SELECT max(now() -xact_start) FROM pg_stat_activity WHERE state IN ('idle in transaction','active');
/* 5. Checkpoint Interval */
Frequent Checkpoints leads down performance. PostgreSQL will display about those checkpoints in its log. Also, you can check the frequency in the pg_stat_bgwriter table.
/* 6. Query Execution Time */
SELECT max(now() - xact_start) FROM pg_stat_activity WHERE state IN ('idle in transaction', 'active');
