Sun 01 Aug 2021

Logging Tips for Postgres, Featuring Your Slow Queries

In the last several months, we've featured simple yet powerful tools for optimizing PostgreSQL queries. We've walked through how the pg_stat_statements extension can show which queries are taking up the most time to run system-wide. We've also looked at how to use the EXPLAIN command to uncover query plans for individual queries.

You can get a lot out of those two, but you may have also wondered, "What about logs? Surely I can use Postgres' logs to help me find and track slow queries too?" Today we're going to take a look at a useful setting for your Postgres logs to help identify performance issues.

Source: Logging Tips for Postgres, Featuring Your Slow Queries, an article by Kat Batuigas.

A Note About Git Commit Messages

I want to take a moment to elaborate on what makes a well formed commit message. I think the best practices for commit message formatting is one of the little details that makes Git great. Understandably, some of the first commits to rails.git have messages of the really-long-line variety, and I want to expand on why this is a poor practice.

Source: A Note About Git Commit Messages, an article by Tim Pope.

Improve PostgreSQL Query Performance

Understanding query performance patterns is essentially the foundation for query performance tuning. It, in many ways, dictates how a database cluster evolves. And then there are obviously direct and indirect cost connotations as well.

PostgreSQL provides very detailed statistics through a number of catalog views and extensions that can be easily added to provide more detailed query statistics. With each view focused on a particular aspect, the picture almost always needs to be stitched together by combining different datasets. That requires effort and still, the whole picture might not be complete.

The pg_stat_monitor extension attempts to provide a more holistic picture by providing much-needed query performance insights in a single view. The extension has been evolving over the past year and is now nearing the GA release.

Source: Improve PostgreSQL Query Performance Insights with pg_stat_monitor, an article by Ibrar Ahmed and Hamid Akthar.