PGSQL Phriday #010: Log analysis

July 7, 2023
PGSQL Phriday PostgreSQL

If you’re one of my customers, you might know how I insist on monitoring your Postgres logs and digging into them to find precious insights on what’s going on with your Postgres. For a long time now, there is pgBadger. For PGSQL Phriday #010, Alicja asks us to focus on pgBadger.

You might be surprised to find out I am not using pgBadger. I will explain why later, but keep assured that I do think pgBadger is a good tool. It will help DBAs get better performance and follow how their instance is doing before there is a very bad problem.

My problem with Badger is that when I am called by a customer, it’s because they couldn’t figure out by themselves what’s wrong. So, it’s not a very simple problem. I found out that Badger was not flexible enough for my very particular use case. On top of that, I recommend my customers output their Postgres logs in CSV so that it’s easier to work with afterward. Even though Badger can manage CSV files, it asks for another Perl library and the setting of the environment is not that easy.

Log settings

I always recommend my customers to be very greedy with Postgres logging. The more information you get, the more accurate your view of what’s happening.

Here are the typical settings I recommend:

Log analysis

So, how do I analyze logs and why can’t Badger do it for me? What I do normally is using the CSV format output logs and creating a simple FDW (Foreign Data Wrapper) to query the logs as if they were tables in my database.

The firsts things I do are the same as Badger does:

But then, I can get the same indicators during identified workload peaks. If you want that with Badger, You’d have to isolate the portion of the file you want to analyze and the analysis again. Here, I simply adjust my where conditions.

Also, for some indicators, like temporary files, I like to have more granularity. For example, I’d like to know the percentiles of usage of the temporary files. If I find one query using a very huge temporary file, I like taking this query apart, to re-run the analysis on the others and find a good value for work_mem.

I also love the flexibility SQL gives me when the time comes to look at slow queries. I have more precision with SQL.

Badger as a log analyzer for production

That’s why I don’t use Badger. Again, Badger is not a bad tool and I encourage people working in production daily to install it and use it for cold analysis and to get a report on how your instance is performing overall. Moreover, I encourage production DBAs to look at the evolution of the different dashboards they get.

If you need to deep dive into some particular activities in your database, then play with SQL and you will have all the flexibility you need!

PGSQL Phriday #015: Primary keys: UUID, CUID, or TSID?

February 3, 2024
PGSQL Phriday PostgreSQL

PGSQL Phriday #015: UUID: let's fight!

January 27, 2024
PGSQL Phriday PostgreSQL

Triggers: reading other opinions

April 21, 2023
PGSQL Phriday PostgreSQL