Probing PostgreSQL with DTrace and SystemTap

Operating system developments in recent years have provided administrators with new and powerful ways of peeking into live, production applications to investigate behaviors and solve problems in real time without significant system impact. PostgreSQL provides several probe points allowing these dynamic tracing tools access to running applications that was formerly available only with a debugger. In this discussion we will explore the DTrace and SystemTap applications and some of their capabilities, with specific focus on PostgreSQL.

Ever wished an application's logging were just a bit more detailed? Ever wanted an application to spit out some internal variable, or count the iterations of a section of code? Ever wanted to see exactly what values resulted from some convoluted calculation? Ever been unable to provide these details because you couldn't recompile the application to add the code you needed? Dynamic tracing can provide all those details, on a running, production application, and -- theoretically, at least -- do it all without your users noticing.

PostgreSQL provides a wide array of tracing probes which allow users to gather data regarding parsing, planning, execution, storage, locking, and many other back-end behaviors, all without recompiling the application or adding new PostgreSQL code. We'll examine these probes and the capabilities of systems like DTrace and SystemTap to gather and analyze the data they provide.