Troubleshooting Postgres Performance

IF you think postgres performance is the cause of an application performing badly these sql statements may help



  Long Running Transactions


The statement below shows the current running sessions and how long they’ve been running with.

  now() - pg_stat_activity.query_start AS duration,
FROM pg_stat_activity
WHERE state != 'idle'
order by duration desc;


you can further troubleshoot what these queries are doing by using the pg_top utility






Use the ‘Q’ key and enter a PID of the statement you’re interested in to get more information.





Temporary data such as sort and temporary tables are handled very differently in postgres. Mostly this is done in memory and not on disk. A section of memory is allocated to each session for this. It’s called the work_mem and can be increased if performance is poor. When the memory set is too small it writes temp file but this is expensive.

See work_mem in

SQL Tracing


This is simple In postgres but adds load and generates a lot of data which has to be analyzed manually as far as I am aware.


Here’s the procedure.

Find –name postgresql.conf

Back it up

Edit the postgresql.conf to update these entries

track_activities = on
track_activity_query_size = 6000
log_statement_stats = on
log_statement = ‘all’


reload the postgres instance

/opt/vmware/vpostgres/current/bin/pg_ctl reload –U secureall -D /common/db/vshield

The values of the-U and –D commands will be differnet you can get these from

By doing a ps –ef | grep postgres you’ll be able to see the values for –U and –D

Verify logs have verbose information. These logs will be postgresql.log or server,log.

To roll back the change just replace the postgresql.conf with the original and reload again.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s