pgbadger

Log analysis for slow query discovery on PostgreSQL 18.4 - turn log_min_duration_statement output into ranked reports that feed EXPLAIN review and index design tickets.

Recipe

ALTER SYSTEM SET log_destination = 'csvlog';
ALTER SYSTEM SET logging_collector = on;
ALTER SYSTEM SET log_min_duration_statement = '250ms';
ALTER SYSTEM SET log_statement = 'none';
ALTER SYSTEM SET log_line_prefix = '%t [%p]: user=%u,db=%d,app=%a,client=%h ';
SELECT pg_reload_conf();

pgbadger /var/log/postgresql/postgresql-18-main.csv \
  -o /var/reports/pgbadger/$(date +%F) \
  --incremental \
  --exclude-query="^(BEGIN|COMMIT|SET|SHOW)"

When to reach for this:

• Weekly slow-query review without enabling full log_statement = all
• Historical trend: "did deploy X regress p95 query time?"
• Teams without pganalyze budget still need actionable query lists

Working Example

Step 1 - Collect one day of logs

sudo ls -la /var/log/postgresql/
# postgresql-18-main.csv, postgresql-18-main.csv.1, ...

Step 2 - Generate report

OUT=/var/reports/pgbadger/2026-07-09
mkdir -p "$OUT"
pgbadger /var/log/postgresql/postgresql-18-main.csv* \
  -o "$OUT" \
  --jobs=4 \
  --top=50

Step 3 - Triage top query

-- Copy normalized query from report into psql
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total
FROM orders o
WHERE o.tenant_id = $1 AND o.status = 'open'
ORDER BY o.created_at DESC
LIMIT 50;

What this demonstrates:

• pgbadger ranks by total time and call count
• Reports link duration to application app=%a when log_line_prefix includes %a
• EXPLAIN evidence closes the loop from log to plan fix

Deep Dive

Log Settings Table

Incremental Reports

pgbadger --incremental --report-per-database \
  --outdir /var/reports/pgbadger/incr \
  /var/log/postgresql/postgresql-18-main.csv

• Incremental state file avoids reprocessing terabytes monthly
• Per-database reports help multi-tenant ops teams

jq Alternative for One-Off

grep "duration:" /var/log/postgresql/postgresql-18-main.csv \
  | awk -F'duration: ' '{print $2}' | sort -rn | head -20

• Quick SSH triage before full pgbadger run during incidents

Gotchas

• JSON log format mismatch - pgbadger expects CSV or standard text, not only jsonlog without conversion. Fix: use csvlog or verify pgbadger version supports your format.
• Logging every statement - disk fills, IO collapses. Fix: threshold-based log_min_duration_statement.
• PII in query text - logs capture literal binds in some configs. Fix: log_parameter_max_length=0 or redact reports.
• Timezone confusion - %t vs UTC. Fix: align log_timezone with incident tooling.
• Ignoring cache hit context - slow once may be cold cache. Fix: correlate with pg_stat_statements mean vs max time.

Alternatives

FAQs

Related

• Tools Basics - log shipping setup
• Logging Basics - collector config
• CSVlog and JSON Logs - format choice
• EXPLAIN Basics - plan review after triage
• pganalyze / OtterTune - SaaS alternative

Stack versions: This page was written for PostgreSQL 18.4 and pgBadger 12.4+.