(presented at PGconf.EU 2015, Vienna, 27-30 October, and at PGDay.IT 2015, Prato, 23 October)
Query profiling is an essential instrument for improving the performance of a PostgreSQL database server. First, most workloads are constantly drifting towards new usage patterns, so that existing optimisation gradually become outdated and lose their efficiency. Also, PostgreSQL itself evolves at a fast and steady pace, providing at every release several new capabilities as well as improved versions of some of the existing ones.
In this talk we review the current state of query profiling instrumentation, starting from log analysis tools such as pgBadger and then moving to real-time solutions, in particular pg_stat_statements and its variant pg_stat_plans. We will show concrete examples, and we will sketch comprehensive architectures for query profiling, concluding with a roadmap and wishlist discussion.