Monitor Slow Queries with pgBadger

Your database is responding in seconds, not milliseconds. Users are complaining, dashboards are red, and you have no idea which query is the culprit. You could enable log_min_duration_statement and manually sift through thousands of log lines — but that’s like finding a needle in a haystack while blindfolded. This lesson teaches you how to monitor slow queries with pgBadger, the tool that turns your PostgreSQL logs into a clear, actionable performance report. By the end, you'll not only identify the slow queries but also understand their patterns and be ready to fix them.

The problem this lesson solves

PostgreSQL is fast — until it isn't. Slow queries are inevitable as your data grows, indexes become less effective, or queries get more complex. The challenge isn't just having slow queries; it's finding them efficiently.

Without a monitoring strategy, you face a few painful realities:

• Manual log reading is tedious — grep for duration in a multi-GB log file is slow and error-prone.
• You miss patterns — a query might be slow only at certain times or with specific parameters, and a single log line won't show you that.
• You can't prioritize — when everything is slow, which query do you fix first? You need data, not guesses.

pgBadger solves this by parsing PostgreSQL log files and generating an HTML report filled with charts, tables, and stats. It aggregates data, highlights the worst offenders, and gives you a bird’s-eye view of your database's performance. Instead of hunting for needle-in-a-haystack log lines, you get a structured report that tells you exactly where to look.

Pro tip: pgBadger is a log analyzer, not a real-time monitor. It works after the logs are written, which is perfect for periodic analysis, but if you need live dashboards, pair it with tools like pg_stat_statements or a monitoring agent (covered later).

Core concept / mental model

Think of pgBadger as your database performance auditor. You give it a log file, and it produces a comprehensive report, much like an auditor reviews financial records and produces a summary of the company's health.

The mental model has three stages:

1. PostgreSQL writes logs — you configure PostgreSQL to log slow queries (using log_min_duration_statement).
2. pgBadger reads the logs — it parses the log file, extracting timestamps, durations, query details, and more.
3. You analyze the report — pgBadger generates an HTML file with tables, charts, and summaries that highlight the slowest queries, their frequency, and their impact.

Key terms you'll encounter:

• Log line format — the structure PostgreSQL uses to write log entries. pgBadger supports many formats (stderr, csvlog, syslog, jsonlog). You must set a compatible format.
• Log_min_duration_statement — the PostgreSQL setting that tells it to log any query that runs longer than X milliseconds. This is your primary data source.
• Report — the HTML output of pgBadger, containing sections like "Top Queries by Duration," "Hourly Distribution," and "Slowest Queries."

Analogy: A log file is like a raw data feed, and pgBadger is the analyst that turns that feed into a readable executive summary with charts and highlights.

How it works step by step

To monitor slow queries with pgBadger, you follow a sequence: configure PostgreSQL, run your workload (or let it run naturally), then run pgBadger on the resulting log file. Here's the logical flow:

1. Enable slow query logging in PostgreSQL by setting log_min_duration_statement to a threshold like 1000 (milliseconds).
2. Ensure the log format is compatible — the default stderr format works, but csvlog is better because it includes structured fields.
3. Let your application run — queries that exceed the threshold will be written to the log file.
4. Install pgBadger on your machine (it's a Perl script; no compilation needed).
5. Run pgBadger on the log file, specifying the input and output files.
6. Open the generated HTML report in your browser and analyze the results.

This step-by-step flow is repeatable — you can run pgBadger daily, weekly, or on-demand whenever you suspect a performance issue.

Hands-on walkthrough

Let's put this into practice. We'll configure PostgreSQL to log slow queries, generate some traffic, and then produce a pgBadger report.

Step 1: Configure PostgreSQL logging

Edit your postgresql.conf file (usually in /etc/postgresql/15/main/ or the data directory) and set these parameters:

log_min_duration_statement = 1000   # log queries taking >= 1 second
log_destination = 'stderr'          # or 'csvlog' for structured output
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d.log'
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

Then restart PostgreSQL:

sudo systemctl restart postgresql

Now any query taking over 1 second will be logged.

Step 2: Generate slow queries

Let's create a table and run a slow query to generate log entries.

CREATE TABLE IF NOT EXISTS demo_numbers (id serial PRIMARY KEY, value numeric);
INSERT INTO demo_numbers (value) SELECT random() * 1000 FROM generate_series(1, 1000000);

-- Trigger a slow query (sequential scan on large table)
SELECT count(*) FROM demo_numbers WHERE value > 500;

If the query is fast (unlikely with 1M rows), lower the threshold or use a cross join.

Step 3: Install pgBadger

On Debian/Ubuntu, you can install from source or use the package manager. Here's the source method:

cd /tmp
wget https://github.com/darold/pgbadger/archive/v12.4.tar.gz
tar xzf v12.4.tar.gz
cd pgbadger-12.4
sudo perl Makefile.PL
sudo make install

Alternatively, use a package manager (e.g., apt install pgbadger on Debian).

Step 4: Run pgBadger

After your log file has accumulated entries, run pgBadger on it:

pgbadger /var/log/postgresql/postgresql-$(date +%Y-%m-%d).log -f stderr -o /tmp/report.html

If you used csvlog, change -f to csv.

Step 5: View the report

Open /tmp/report.html in your browser. You'll see sections like:

• Overall statistics — total queries, average duration.
• Top queries by total time — the queries that consumed the most time overall.
• Slowest queries — sorted by max duration.
• Hourly distribution — when slow queries occurred.

In the top queries table, you'll see the slow query we generated (count(*)), with its average and max duration, plus the query fingerprint. This fingerprint masks literals, so you can group similar queries even if parameter values differ.

Pro tip: Use the -q flag to exclude normal queries if you're only interested in the slow ones. For example: pgbadger -q --exclude-query='^(SELECT|INSERT|UPDATE|DELETE) FROM pg_' -o /tmp/report.html yourlog.log.

Compare options / when to choose what

pgBadger isn't the only way to monitor slow queries. Here's how it stacks up against common alternatives:

When to choose pgBadger:

• You need a free, self-contained solution and already have log files.
• You want a visual report to share with your team.
• You're doing post-incident analysis and need to understand historical patterns.

Choose pg_stat_statements if you want a continuous, database-embedded view and don't mind writing SQL queries to inspect the data. For a quick one-off check of a single slow query, EXPLAIN ANALYZE is essential.

Pro tip: You can combine both — use pgBadger to get an overview and then use EXPLAIN ANALYZE on the top queries to drill down into the execution plan.

Troubleshooting & edge cases

pgBadger reports "no log entries found"

This means your log file doesn't contain any lines that pgBadger can parse. Common causes:

• Wrong log format — ensure log_destination matches the -f flag. If you set stderr, use -f stderr. If you set csvlog, use -f csv.
• No slow queries logged — your threshold (log_min_duration_statement) might be too high, or your queries are all fast. Temporarily set it to 0 to log everything for testing.
• Incorrect log file path — double-check the log_directory and log_filename settings.

The report shows wrong durations or missing queries

• Log line prefix — pgBadger relies on the log_line_prefix to extract timestamps and duration. Use a standard format like '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '. If you omit parts, pgBadger may misparse.
• Log rotation — if you're using log rotation (e.g., log_rotation_age), ensure you run pgBadger on the correct file. You might need to concatenate multiple files: cat postgresql-*.log | pgbadger -.

pgBadger crashes or stops parsing

• File size — very large logs can be slow. Use --incremental or split the log into smaller chunks.
• Unsupported log format — some custom log formats confuse pgBadger. Stick to the standard stderr or csvlog.

The report is huge and hard to read

• Use filters: --exclude-query to ignore certain queries (e.g., SELECT 1), or --include-query to focus on specific patterns.
• Limit the number of queries shown with --top (e.g., --top 10).

What you learned & what's next

You now know how to monitor slow queries with pgBadger — from configuring PostgreSQL logging to generating and interpreting an HTML report. You understand the core concept of log analysis, the step-by-step workflow, and how to choose pgBadger over alternatives. You also know common pitfalls and how to fix them.

With this skill, you can confidently identify which queries are hurting your database's performance and prioritize your optimization efforts.

In the next lesson, you'll learn how to analyze and optimize the slow queries you discovered using EXPLAIN and index tuning. You'll take the report from pgBadger and turn it into a list of concrete actions to speed up your database.

Keep practicing — the more you analyze logs, the quicker you'll spot performance patterns and prevent future slowdowns.