Postgres 9.1 wait events

Question

Is there anything in Postgresql 9.1 similar to wait events dynamic views of oracle? I need to find queries which are waiting for a long time, and the events they are blocked on.

Answer 1

There isn't such a fine grained tracking available in Postgres (other than using DTrace which apparently gives a lot of information).

There is a whole chapter in the manual "Monitoring Database Activity"
http://www.postgresql.org/docs/current/static/monitoring.html

Another option is the module pg_stats_statements:
http://www.postgresql.org/docs/current/static/pgstatstatements.html

Answer 2

For 9.6 and 10 you can try PASH-Viewer to observe wait events in the chart. You can see top sql queries and top sessions in period of time.

It's free and open source. enter image description here

Screenshots: https://habrastorage.org/webt/4h/ac/nv/4hacnv4ty2shh41gw3srzhp9t0i.png https://habrastorage.org/webt/f2/4s/yt/f24sytk_inxgiav3wuajcjjifro.png https://habrastorage.org/webt/lw/yp/kg/lwypkg-wu4timhbp6ge7p3nf8ok.png

GitHub: https://github.com/dbacvetkov/PASH-Viewer

• RDFozz: Just to clarify - from the GitHub link, are you involved in this product? If so, you should probably make note of that.
• dcvetkov: Yes, i took an opensource product for Oracle (ash-viewer) and rewrited it for postgresql.