Tracing PostgreSQL performance
Channel: Andrea Ross
Date: 2015-06-29

by Ilya Kosmodemiansky

“How could I trace a session in PostgreSQL” - this is quite a common question from an Oracle/DB2 DBA, who is new to Postgres. Usually we start to mumble something about systemtap/perf and pgstatbgwriter. Although these tools are great and useful, but they do not exactly meet this need, and besides, some of them are really difficult to use.

In this talk, I’ll make a brief introduction to tracing and wait event based performance analysis in commercial databases such as Oracle and DB2, and what actually can be done in this respect using the built-in PostgreSQL tools. Then we will consider what cannot be done with those built-in tools, and why and when a DBA needs dtrace/systemtap/perf and debuggers. I will also cover the drawbacks of these external tools, and give concrete use cases and recipes for their usage. Over the years, the community has been considering other possibilities to implement tracing, performance event system, pgstatlwlocks, etc. I will give a brief overview of these attempts, and finally give some thoughts on how we can implement the performance tracing less intrusively.