The `slow query log` and when to use it

Capsule overview

auto_explain (capsule 04) captures the plans of slow queries. It's powerful but expensive: for every query that exceeds the threshold, PostgreSQL formats the complete plan (with Buffers, per-node timings, etc.) and writes it to the log. If your system has a lot of traffic and your threshold is low, the overhead adds up.

Sometimes you only need a simpler piece of data: which queries passed X ms? No plan, no buffers — just the text and the time. To answer that, PostgreSQL has an independent parameter: log_min_duration_statement. It's the classic "slow query log", much cheaper than auto_explain, and useful in specific cases where auto_explain is overkill.

This capsule teaches you:

• Enabling log_min_duration_statement and understanding exactly what it logs.
• Distinguishing the three related parameters: log_min_duration_statement, log_statement, auto_explain.log_min_duration.
• Deciding when each one is appropriate and when they combine.
• Configuring a canonical "double net" setup: log_min_duration_statement with a low threshold + auto_explain with a high threshold.
• Anticipating problems (PII in logs, log size, false positives from maintenance queries).

By the end, you'll be able to choose among PostgreSQL's three query-logging tools based on your goal and configure an appropriate combination for your environment.

Mental model: three levels of camera

Let's continue with the security-camera analogy from capsule 04. Imagine your store has three types of camera:

• Camera 1 (everything, low resolution): records everyone who comes in (blurry face, no sound). Low storage cost. Useful for knowing "how many people came in yesterday between 3 and 5pm?".
• Camera 2 (the suspects, high resolution): records only when the sensor goes off. Clear face, sound, full movement plan. Higher cost, but only when there's an alarm.
• Camera 3 (all administrative activity): records every time someone uses the office (not customers). Useful for auditing employees, not normal traffic.

PostgreSQL offers you exactly this pattern in query logging:

The three are independent. You can enable one, two, or all three at the same time, each with its purpose.

log_min_duration_statement: the classic slow query log

This parameter tells PostgreSQL: "for every query that takes longer than X milliseconds, write the query text and the duration to the log." No plan. No buffers. Just text + time.

Configuration

In postgresql.conf:

# Log all queries that take longer than 200ms
log_min_duration_statement = '200ms'

# So the log includes a useful prefix:
log_line_prefix = '%m [%p] %q%u@%d '

Apply with reload (SELECT pg_reload_conf();) or restart. It does not require shared_preload_libraries or CREATE EXTENSION — it's core PostgreSQL functionality.

Example output

After configuring log_min_duration_statement = '200ms', run:

docker exec -it bookstore-pg psql -U bookstore -d bookstore -c \
    "SELECT pg_sleep(0.3), 'slow query log test' AS marker;"

In the log:

2026-05-02 14:32:15.234 UTC [1234] bookstore@bookstore LOG:  duration: 302.456 ms  statement: SELECT pg_sleep(0.3), 'slow query log test' AS marker;

Just the text and the duration. Nothing else.

How does it compare to auto_explain?

For the same query, with both active (log_min_duration_statement = 200ms AND auto_explain.log_min_duration = 200ms):

2026-05-02 14:32:15.234 UTC [1234] bookstore@bookstore LOG:  duration: 302.456 ms  statement: SELECT pg_sleep(0.3), 'slow query log test' AS marker;
2026-05-02 14:32:15.235 UTC [1234] bookstore@bookstore LOG:  duration: 302.456 ms  plan:
        Query Text: SELECT pg_sleep(0.3), 'slow query log test' AS marker;
        Result  (cost=0.00..0.02 rows=1 width=40) (actual time=300.123..300.124 rows=1 loops=1)
          Buffers: shared hit=0 read=0

Two entries: one from log_min_duration_statement (simple line), the other from auto_explain (complete plan). Redundant in this case, but useful when used with different thresholds.

The canonical setup: "double net"

Serious teams use both with different thresholds:

# Slow query log: capture EVERYTHING over 100ms (text only)
log_min_duration_statement = '100ms'

# auto_explain: capture plans only of the truly slow ones (>500ms)
shared_preload_libraries = 'pg_stat_statements,auto_explain'
auto_explain.log_min_duration = '500ms'
auto_explain.log_analyze = on
auto_explain.log_buffers = on

Why this setup:

• log_min_duration_statement = 100ms gives you an exhaustive list of slow queries. Useful for aggregate analysis: "how many slow queries did we have today? which endpoint fires them the most?". Since it only logs text, the cost is low.
• auto_explain = 500ms gives you detailed plans of the most critical queries. Useful for deep diagnosis. Since it only applies to the truly slow ones, the cost is manageable.

Visually:

Query runs in 50ms    → nothing logged
Query runs in 150ms   → log_min_duration_statement: line with text + time
Query runs in 300ms   → log_min_duration_statement: line with text + time
Query runs in 800ms   → log_min_duration_statement: line with text + time
                       → auto_explain: additional line with the complete plan

You have two levels of detail. For aggregate analysis, the slow query log is enough. For the critical cases, the plan is there.

In your local bookstore

Edit pg-config/postgresql.conf continuing the accumulated setup from the previous capsules:

listen_addresses = '*'
max_connections = 100

# Capsule 02: pg_stat_statements
shared_preload_libraries = 'pg_stat_statements,auto_explain'
pg_stat_statements.track = 'all'
pg_stat_statements.max = 10000
pg_stat_statements.save = on

# Capsule 04: auto_explain
auto_explain.log_min_duration = '500ms'
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_format = 'text'

# Capsule 05: slow query log
log_min_duration_statement = '100ms'
log_line_prefix = '%m [%p] %q%u@%d '

Restart the container. From there, your log has two simultaneous levels of capture.

log_statement: the other parameter that confuses people

There's a third parameter that people confuse with log_min_duration_statement. It's called log_statement and it is NOT for performance — it's for auditing.

What it does

log_statement = 'none'    # default: log nothing
log_statement = 'ddl'     # log only DDL: CREATE, ALTER, DROP, GRANT
log_statement = 'mod'     # log DDL + DML: INSERT, UPDATE, DELETE, COPY
log_statement = 'all'     # log absolutely everything

When to use it

log_statement = 'ddl' is the most common case: audit who created/modified schema. Useful for detecting manual changes in production ("who dropped that table?"). Low overhead.

log_statement = 'mod': auditing data changes. Almost always overkill — using audit triggers or the pgaudit extension is better.

log_statement = 'all': extreme debugging. Do not use it in real-traffic production because it generates massive logs.

Difference from log_min_duration_statement

They can coexist. Configuring log_statement = 'ddl' AND log_min_duration_statement = '100ms' gives you schema-change auditing and slow-query capture. No problematic overlap.

Decision matrix: when to use each one

Why this matters in real work

1. Not all teams can afford auto_explain in intensive production. If your app has 5,000 RPS, capturing plans for 100ms queries generates a gigantic log. log_min_duration_statement without auto_explain is often the right compromise: you see which queries are slow, without the cost of the captured plan.

2. Integration with APMs typically parses the slow query log. Datadog, New Relic, Sentry, and the like ingest log_min_duration_statement by default; auto_explain requires additional configuration and JSON format. The slow query log is the common denominator.

3. For incident postmortems, the slow query log is the first source. When there's an outage, looking at "which slow queries occurred in the problem window?" is faster and more useful than parsing complex plans. The plans come later, once you've identified which query to investigate.

4. It's trivially enabled in managed services. RDS, Cloud SQL, Supabase, Neon — they all allow configuring log_min_duration_statement from their console without needing superuser. auto_explain requires shared_preload_libraries, which some managed services don't allow or restrict.

5. The difference among the three parameters is a DBA-savvy interview question. "Difference between log_statement and log_min_duration_statement?" or "when would you enable auto_explain instead of the slow query log?" are questions that separate the backend dev who has only seen EXPLAIN ANALYZE from the one who has operated production.

Traps and common mistakes

Mistake 1 (conceptual): confusing log_statement with log_min_duration_statement

Symptom: "I configured log_statement = 'all' to capture slow queries and my disk filled up in 2 hours."

Why it confuses: the names are very similar. log_statement filters by command type (DDL/DML/all). log_min_duration_statement filters by duration. People read log_statement and think "this logs statements" without noticing it's ALL statements of a certain type, not the slow ones.

How to distinguish: memorize the rule:

• log_statement = filter by type (DDL/DML/all/none)
• log_min_duration_statement = filter by duration (threshold in ms)

How to fix it: if you wanted to log slow queries, use log_min_duration_statement = '100ms' (or the appropriate threshold) and leave log_statement = 'none'.

Mistake 2 (operational): combining a low log_min_duration_statement threshold with log_statement = 'all'

Symptom: "The log is duplicated. Every slow query appears twice."

Why it happens: if log_statement = 'all' already logs all queries, and log_min_duration_statement = 100ms also logs slow queries, the slow queries appear in both. Massive redundancy.

How to distinguish: review the log. If you see LOG: statement: SELECT... (without duration) followed by LOG: duration: Xms statement: SELECT... (with duration) for the same query, you have redundancy.

How to fix it: choose just one based on the use case:

• If you want performance logging: log_min_duration_statement = 'Xms' and log_statement = 'none'.
• If you want complete auditing (rare): log_statement = 'all' and log_min_duration_statement = -1.
• For DDL auditing only: log_statement = 'ddl' and log_min_duration_statement with your normal threshold.

Mistake 3 (security): exposing sensitive data in the log

Symptom: "The slow query log shows queries with passwords or PII data in plain text."

Why it happens: same problem as previous capsules: queries with literals (not parameterized) reach the log with the real values. It applies to both log_min_duration_statement and auto_explain.

How to distinguish: review log samples. If you see concrete strings of sensitive data, there are badly parameterized queries.

How to fix it:

1. Audit the app: any SQL construction with direct interpolation.
2. Migrate to parameterized queries (ORM or text() with a dict of parameters).
3. Restrict access to the PostgreSQL log to authorized personnel.
4. For regulated environments, consider log redaction tools before archiving them.

Mistake 4 (interpretation): considering log_min_duration_statement a replacement for pg_stat_statements

Symptom: "I have the slow query log, I don't need pg_stat_statements."

Why it confuses: the two seem to "log queries". But the slow query log is a sequential stream of events; pg_stat_statements is an aggregate view.

How to distinguish: if your question is "which queries are slow right now?", the slow query log works. If your question is "which queries consume the most aggregate system time?", the slow query log doesn't tell you — you'd need to aggregate it by hand. pg_stat_statements already has the aggregation done.

How to fix it: use both. pg_stat_statements for aggregate views and prioritization; slow query log for individual auditing and temporal patterns.

Mistake 5 (operational): not rotating the log

Symptom: "I enabled log_min_duration_statement = '50ms' 2 weeks ago and the log reached 80GB."

Why it happens: the PostgreSQL log grows indefinitely without rotation. A slow query log with a low threshold on a medium-traffic system can generate GBs daily.

How to distinguish: monitor du -sh /var/log/postgresql/. If it exceeds 1GB without an obvious reason, you're missing rotation.

How to fix it:

log_rotation_age = '1d'
log_rotation_size = '500MB'
log_truncate_on_rotation = on
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

Or use the operating system's logrotate. In cloud-managed services, it usually comes configured by default.

Mistake 6 (UX): a threshold so low it logs maintenance queries

Symptom: "My log has VACUUM, ANALYZE, REFRESH MATERIALIZED VIEW appearing every hour. Does that tell me anything useful?"

Why it happens: log_min_duration_statement logs EVERY statement that passes the threshold, including administrative ones. VACUUM can take perfectly normal minutes without it being a problem.

How to distinguish: review what percentage of your log is DDL/maintenance. If it's high, consider filtering.

How to fix it: PostgreSQL doesn't allow filtering by type within log_min_duration_statement. Workarounds:

• Postprocess the log with scripts: grep -v 'VACUUM\|ANALYZE' to exclude.
• Raise the threshold if the maintenance queries are the only ones that pass the current threshold.
• Use tools like pgbadger that automatically separate categories.

Exercises

Exercise 1: configure the double-net setup in the bookstore

Enable log_min_duration_statement = '100ms' AND auto_explain.log_min_duration = '500ms' in your local bookstore. Verify with queries of different times that each one logs the correct thing.

listen_addresses = '*'
max_connections = 100

shared_preload_libraries = 'pg_stat_statements,auto_explain'

# pg_stat_statements
pg_stat_statements.track = 'all'
pg_stat_statements.max = 10000
pg_stat_statements.save = on

# auto_explain (only very slow queries, with plan)
auto_explain.log_min_duration = '500ms'
auto_explain.log_analyze = on
auto_explain.log_buffers = on

# Slow query log (moderately slow queries, without plan)
log_min_duration_statement = '100ms'
log_line_prefix = '%m [%p] %q%u@%d '

docker restart bookstore-pg

docker exec -it bookstore-pg psql -U bookstore -d bookstore -c \
    "SELECT pg_sleep(0.2), 'fast slow query' AS marker;"

docker logs bookstore-pg 2>&1 | grep "fast slow query"

docker exec -it bookstore-pg psql -U bookstore -d bookstore -c \
    "SELECT pg_sleep(0.7), 'real slow query' AS marker;"

docker logs bookstore-pg 2>&1 | grep -A 5 "real slow query"

docker exec -it bookstore-pg psql -U bookstore -d bookstore -c \
    "SELECT pg_sleep(0.05), 'too fast' AS marker;"

docker logs bookstore-pg 2>&1 | grep "too fast"

Exercise 2: write a script that extracts the top 10 queries from the slow query log

Write a bash script (or Python) that parses the PostgreSQL log and reports the 10 queries that appeared most often in the slow query log during the last day.

#!/bin/bash
# top-slow-queries.sh
# Counts queries that appeared most often in the slow query log

CONTAINER="bookstore-pg"

# Extract all slow query log lines and group by normalized text
docker logs "$CONTAINER" 2>&1 | \
    grep "LOG:.*duration:.*statement:" | \
    sed 's/.*statement: //' | \
    sed 's/[0-9]\+/N/g' | \
    sed "s/'[^']*'/'X'/g" | \
    sort | \
    uniq -c | \
    sort -rn | \
    head -10

     45 SELECT * FROM books WHERE author_id = N;
     32 SELECT b.id, b.title FROM books b JOIN authors a ON a.id = b.author_id;
     18 SELECT * FROM users WHERE email = 'X';
     12 UPDATE books SET title = 'X' WHERE id = N;
     ...

Exercise 3: decide which tool to use for each case

For each of these scenarios, decide which of the three logging tools you'd use and why. If more than one applies, explain why the combination makes sense.

Case A: Your team needs to audit who does DROP TABLE or ALTER TABLE in production.

Case B: You're investigating why a specific endpoint (that you already identified as slow) is slow. You need to see the exact plan that runs.

Case C: You want a Grafana dashboard with "number of slow queries per hour" to alert the team.

Case D: Your CTO asks you which queries represent more than 10% of the database's time.

Case E: You get called at 3am: "the API has been slow for 20 minutes". You need to know which slow queries occurred in that window.

docker logs bookstore-pg 2>&1 | \
    grep "duration:" | \
    awk '$1 >= "2026-05-02 02:40:00" && $1 <= "2026-05-02 03:00:00"'

Exercise 4: measure the real overhead of the slow query log with a low threshold

Configure log_min_duration_statement = '0' (log EVERYTHING) in the local bookstore. Generate 1,000 queries with a script. Measure the total time. Then configure log_min_duration_statement = '-1' (disable). Repeat the same load. Compare the times.

log_min_duration_statement = 0

docker restart bookstore-pg

# load.sh
START=$(date +%s%N)
for i in {1..1000}; do
    docker exec bookstore-pg psql -U bookstore -d bookstore -c \
        "SELECT * FROM books WHERE id = $i;" > /dev/null 2>&1
done
END=$(date +%s%N)
echo "Total time: $(( (END - START) / 1000000 )) ms"

chmod +x load.sh
./load.sh
# Note the time: e.g. "Total time: 12500 ms"

log_min_duration_statement = -1

With log_min_duration_statement = 0:    12500 ms  (12.5 ms/query average)
With log_min_duration_statement = -1:   11800 ms  (11.8 ms/query average)

Exercise 5: identify maintenance queries in the log

Your team complains that the slow query log is full of autovacuum VACUUM and ANALYZE, making it hard to see the app's queries. Design a strategy to separate maintenance queries from application queries in the analysis.

# Exclude maintenance queries from the analysis
docker logs bookstore-pg 2>&1 | \
    grep "duration:" | \
    grep -v "VACUUM\|ANALYZE\|REFRESH MATERIALIZED\|REINDEX\|CHECKPOINT" \
    > app-queries-only.log

pgbadger --exclude-query "^(VACUUM|ANALYZE|REFRESH)" /var/log/postgresql/postgresql.log

# Assuming log_line_prefix = '%m [%p] %q%u@%d '
docker logs bookstore-pg 2>&1 | \
    grep "duration:" | \
    grep "bookstore_app@" \
    > app-queries-only.log

log_min_duration_statement = '500ms'  # slow autovacuum queries are normally >500ms on large tables

Summary and next step

In this capsule you:

• Distinguished the three related query-logging parameters: log_min_duration_statement (classic slow query log), auto_explain.log_min_duration (captured plans), and log_statement (auditing by type).
• Enabled log_min_duration_statement and understood that it captures only text + duration, without a plan.
• Configured the canonical "double net" setup: slow query log with a low threshold + auto_explain with a high threshold, each fulfilling its role.
• Decided when to use each tool using the decision matrix: aggregate view (pg_stat_statements), text + time (log_min_duration_statement), complete plan (auto_explain), auditing (log_statement).
• Anticipated traps: confusing parameters, redundancy with log_statement = 'all', sensitive data in the log, maintenance queries as noise.

Before moving on, you should be able to:

• Explain the difference among the three parameters without confusing them.
• Configure a double-net setup appropriate for your environment.
• Decide, given a goal, which logging tool to use.
• Identify and resolve typical operational problems (log size, redaction, noise).

Next capsule — pg_stat_activity and live locks. The tools you've seen so far are post-mortem: they tell you what happened after it happened. But when there's an active incident —the API is down now, the dashboard is red now— you need to know what's running right now. For that PostgreSQL has pg_stat_activity, a view that shows in real time every active connection: what query is running, how long it's been going, what state it's in, what locks it holds, what it's waiting for. Capsule 06 teaches you to read it, identify hung transactions (idle in transaction), detect deadlocks, and resolve live incidents. It's the tool that will save your team on the next on-call.

Resources

1. PostgreSQL 16 — Error reporting and logging — the official reference for all logging parameters.
2. PostgreSQL 16 — log_statement reference — the specific reference for the auditing parameter.
3. pgBadger — PostgreSQL log analyzer — the canonical tool for parsing and visualizing logs.
4. pgaudit extension — advanced auditing for cases where log_statement isn't enough.
5. Crunchy Data — "PostgreSQL logging best practices" — an operational overview of logging.
6. Datadog — "Configure PostgreSQL logging" — an APM perspective on how to configure logging for ingestion.

Module 5 — Database Performance & Query Tuning Guide