PostgreSQL - Real Time Performance Analysis and Troubleshooting

Does it really matter or say time permits to analyse reports once your application is LIVE?

Starting with a question as Performance troubleshooting in Prod is different then in Test. Here, In this write up , I'm trying to share and emphasise on Real Time Performance analysis and Troubleshooting.

Since, talking about PostgreSQL Database - we can use pg_stat_activity which keep tracks of real time database operations.

There are 4 key points we will talk about :

1. Long running queries - since they can really hog the system resources and screw the database.
2. Idle connections/session - They can eat up all connections without doing anything useful.
3. Blocking/waiting queries - when blocking queries occur they tend to remain there holding up the connection forever or for a very long time.
4. Kill idle/blocking query session - It's in your judgement whether to let the whole database suffer or you can kill the blocking sessions.

How to get Long running queries

Use the pg_stat_activity table and run following query for a simple view:

select pid, query from pg_stat_activity where state='active';        

Output: All active queries

Sort it by duration to identify long running queries

select pid, 
       query,
       state,
       now() - query_start AS duration,
       wait_event_type,
       wait_event
from pg_stat_activity where state='active' order by duration desc limit 20;        

Output: Long running queries with duration and wait event type if applicable.

Finding Idle connections/session

Again, query the pg_stat_activity table with state='idle'

select pid, 
       query,
       state,
       now() - query_start AS duration,
       wait_event_type,
       wait_event
from pg_stat_activity where state='idle' order by duration desc limit 20;        

Output: Idle sessions with duration and wait event type if applicable.

Identifying Blocking/waiting queries

It's little tricky, sharing a simpler example here:

SELECT                                                                                                                                                       
  activity.pid,
  activity.usename,
  activity.query,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));        

Output: We get all details regarding blocked session, user , pid, query and blocking query.

Now, Let's kill the blocking query

It's not advised to right away kill the blocking query unless you are sure about what it's doing.

But we can kill it using the PID of blocking query from above session .

select pg_terminate_backend(154761);        

Hope it's provide you a good start to analyse and manage PostgreSQL performance issues in real time.