PostgreSQL Log Analysis for Performance Troubleshooting

PostgreSQL logs are one of the most useful performance troubleshooting tools you have.

They are also one of the easiest tools to misuse.

A badly configured logging setup gives you one of two useless outcomes:

• almost no signal when a real problem happens
• or so much noise that the real problem disappears inside the flood

That is why good PostgreSQL log analysis is not about “turn on more logs.” It is about logging the right kinds of events:

• slow statements
• lock waits
• temp file spills
• checkpoints
• auto-explained plans
• and enough request or session context to connect a database event back to the application behavior that caused it

This guide explains how to use PostgreSQL logs for performance troubleshooting in a way that is practical in production.

Why Logs Matter When You Already Have Metrics

A lot of teams rely on:

• dashboards
• database metrics
• pg_stat_statements
• pg_stat_activity
• and application monitoring

All of those are important.

But logs solve a different problem.

Metrics tell you:

• something got slower
• a wait increased
• CPU or I/O spiked
• temp files grew
• connections piled up

Logs help answer:

• which exact query was slow
• what time it happened
• which app service or client ran it
• whether it was blocked
• whether it spilled to disk
• whether PostgreSQL logged a plan for it
• and what else happened around that same moment

This is why logs are so useful during:

• incidents
• regression debugging
• deploy troubleshooting
• strange workload shifts
• and production-only failures that are hard to reproduce manually

The First Goal: Make the Logs Useful, Not Maximal

A good PostgreSQL logging setup should help you answer:

• which statements are slow
• which waits are blocking work
• which queries spill to disk
• which checkpoints or background events correlate with latency spikes
• which app or service emitted the query
• and whether the issue is one bad query, too many queries, or infrastructure pressure

If the logging setup cannot answer those questions, it probably needs improvement.

If it logs so much that nobody can read it, it also needs improvement.

That balance matters.

1. Start With the Right Log Format

PostgreSQL supports several destinations and formats for logs.

For performance troubleshooting, the most useful production-friendly choices are often:

• plain stderr
• csvlog
• jsonlog

PostgreSQL’s docs note that csvlog and jsonlog require logging_collector to be enabled. Structured formats are especially useful when you want to load logs into analysis tools or dashboards. :contentReference[oaicite:1]{index=1}

When plain text is enough

Plain text can be fine when:

• the environment is small
• you are reading logs directly
• the log pipeline is simple
• or you already have strong parsing around a stable text format

When structured logs are better

csvlog or jsonlog are often better when:

• logs flow into centralized observability tooling
• you need easier parsing
• you want structured field extraction
• you need incident correlation across systems
• or you plan to build saved searches around fields like user, database, duration, or application name

Practical recommendation

If you are building a modern, centralized log workflow, jsonlog is often the cleanest long-term option. If you want easy bulk analysis in SQL-like tools, csvlog can also work very well.

2. Make log_line_prefix Do Real Work

One of the highest-value PostgreSQL logging settings is log_line_prefix.

This setting controls what identifying context PostgreSQL includes in each log line.

For performance troubleshooting, you usually want enough context to answer:

• which database
• which user
• which session or process
• which application
• and when

Why this matters

If a slow query shows up in the log but you cannot tell:

• which service emitted it
• which tenant it came from
• or which session it belongs to

you have only half the evidence you need.

Good practical fields to consider

A useful prefix often includes things like:

• timestamp
• pid
• database name
• user
• application name
• session id
• remote host if relevant

Why application_name matters so much

If your services set application_name consistently, PostgreSQL logs become much easier to read.

Instead of:

• “some query was slow”

you get closer to:

• “billing-worker emitted this slow query”
• “reporting-api caused this spill”
• “webhook-consumer held this lock wait”

That is a huge improvement in troubleshooting clarity.

3. Log Slow Queries, Not Every Query

One of the most common mistakes is enabling very broad statement logging in production.

Logging every statement can be useful during short targeted debugging windows, but for ongoing performance troubleshooting it is usually too noisy and too expensive.

Better approach

Log slow statements.

The most important setting here is:

log_min_duration_statement = 500ms

This tells PostgreSQL to log statements whose duration is at least the threshold.

Why this works well

It gives you:

• exact slow SQL text
• exact timing
• a time window for incident analysis
• lower noise than full statement logging

Choosing a threshold

The right threshold depends on the workload.

Examples:

• low-latency APIs may want a lower threshold
• heavy reporting systems may want a higher one
• batch systems may need different settings during different windows

Practical rule

Use a threshold that is low enough to catch real pain, but high enough that the logs remain readable.

4. Consider Sampled Duration Logging for Busy Systems

Modern PostgreSQL also supports sampled statement-duration logging.

This matters when:

• the system runs very high query volume
• you want representative visibility without logging every slow-ish statement
• or you want duration logging to remain useful without turning into a firehose

Relevant settings include:

• log_min_duration_sample
• log_statement_sample_rate

Why this is useful

On very busy systems, full slow-query logging at a low threshold can create:

• too much I/O
• too much ingestion volume
• too much search noise

Sampled logging gives you a middle path:

• enough real examples
• but lower operational cost

Practical recommendation

Use sampled duration logging when:

• volume is too high for low-threshold full duration logging
• but you still want broad performance visibility

5. Be Careful With log_statement

log_statement controls which SQL statements PostgreSQL logs by class.

Possible values include:

• none
• ddl
• mod
• all

This setting can be useful during specific investigations, but it is often too noisy for general performance troubleshooting.

Good uses

• short-term debugging
• auditing DDL changes
• investigating specific application behavior
• controlled reproduction windows

Weak default for performance logging

For ordinary performance troubleshooting, log_statement = all is often too much.

It tells you everything. That usually means it tells you nothing clearly.

Better pattern

Prefer:

• slow statement logging
• sampled duration logging
• and auto_explain

over permanent full statement logging.

6. Log Lock Waits When Blocking Might Be the Real Problem

A “slow query” is not always slow because it is doing lots of compute or I/O. Sometimes it is just waiting.

That is where log_lock_waits becomes useful.

PostgreSQL’s docs say that when log_lock_waits is enabled, PostgreSQL logs waits that last longer than deadlock_timeout, and that lowering deadlock_timeout can be useful when investigating locking delays. :contentReference[oaicite:2]{index=2}

Why this matters

Lock-wait logging helps you catch cases where:

• application requests stall unpredictably
• migrations block ordinary traffic
• one transaction quietly blocks many others
• performance symptoms are really concurrency symptoms

Practical pairing

These settings work together:

log_lock_waits = on
deadlock_timeout = 200ms

That does not mean those exact values are always right. It means lock troubleshooting is much easier when the threshold is low enough to reveal meaningful blocking.

Practical rule

If you suspect lock contention, turn on lock-wait logging and shorten deadlock_timeout enough to capture useful evidence.

7. Log Temp Files to Catch Sort and Hash Spills

One of the most underused PostgreSQL logging settings for performance work is log_temp_files.

This setting logs temporary files created by statements, which often means:

• sorts spilled to disk
• hash operations spilled
• or some query needed more working space than memory allowed

Why this is valuable

A lot of slow-query investigations miss the fact that the real cost is:

• disk spill
• not the join or sort idea itself

If a query is repeatedly creating large temp files, that is a strong signal.

Example

log_temp_files = 0

This logs all temp files.

In practice, you may choose a threshold to reduce noise.

What temp file logs help you answer

• Which query spilled?
• How large was the spill?
• Was it isolated or frequent?
• Did the spill line up with a latency spike?
• Is the issue more about query shape or about working memory pressure?

For sort/group-by/query-plan troubleshooting, this setting is incredibly useful.

8. Use auto_explain for Production-Only Slow Queries

EXPLAIN ANALYZE is great when you already know which query to inspect.

But many real-world slow queries:

• happen only in production
• occur under concurrency
• are hard to reproduce manually
• or are emitted deep inside application paths

That is where auto_explain shines.

PostgreSQL’s docs describe auto_explain as a module that logs execution plans of slow statements automatically, without needing to run EXPLAIN by hand. :contentReference[oaicite:3]{index=3}

Why this matters

Instead of seeing only:

• “statement took 1700 ms”

you can also get:

• the execution plan
• row counts
• timing
• and often the real reason the query was slow

Example baseline settings

shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '500ms'
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_timing = on
auto_explain.log_nested_statements = on

Important caution

auto_explain is powerful, but it can add overhead and log noise if configured too aggressively.

Use it intentionally:

• low enough to catch real issues
• high enough not to drown the system in plans

9. Use auto_explain to Catch ORM and Nested Statement Problems

One of the best reasons to use auto_explain is that it helps expose:

• hidden ORM-generated SQL
• nested statements inside functions
• query shapes the app does not surface clearly elsewhere
• production-only regressions after deploys

If you have ever thought:

• “I know the endpoint is slow, but I cannot tell which SQL inside it is the problem”

then auto_explain is often the answer.

This is especially true when:

• app logs are incomplete
• the ORM obscures the actual SQL
• or the query only becomes slow under live data shape

10. Log Checkpoints When Write Spikes Matter

If the system experiences periodic latency spikes, one possible cause is checkpoint behavior.

Checkpoint logs help you see:

• how often checkpoints happen
• whether they are forced too often
• how much data is being written
• whether the write path is becoming bursty

Why this matters

A database can feel “slow every few minutes” because of writeback and checkpoint behavior, not because a new query suddenly appeared.

Practical recommendation

Enable checkpoint logging when:

• investigating write-latency spikes
• tuning WAL/checkpoint behavior
• or trying to correlate storage pressure with application symptoms

This can be one of the cleanest ways to tell whether latency spikes are really about write smoothing.

11. Keep an Eye on Autovacuum Logs During Performance Incidents

Autovacuum is not usually the villain. But it can become part of the performance story.

Logging around vacuum and autovacuum can help explain:

• why table churn is high
• whether cleanup is falling behind
• whether certain tables are unusually hot
• or whether maintenance is colliding with foreground workload

Why this matters

A performance problem is sometimes not:

• “this query is slow”

It is:

• “this write-heavy table has grown more painful because maintenance is lagging”
• or
• “autovacuum is working very hard because workload churn is higher than expected”

Practical use

Autovacuum logs are especially useful when the issue is:

• bloat
• churn-heavy tables
• stale statistics
• or periodic maintenance pressure

12. Correlate Log Events, Do Not Read Them in Isolation

A single log line is useful. A sequence of related log lines is where the real story often appears.

Example pattern during an incident:

• checkpoint log
• temp file log
• slow statement log
• lock wait log
• application error spike

That sequence is much more powerful than any one event alone.

Good troubleshooting habit

When reading PostgreSQL logs, do not ask only:

• “what was the slow query?”

Also ask:

• “what else happened around the same time?”

That often reveals whether the real problem was:

• lock contention
• temp spill
• checkpoint pressure
• a deploy
• background maintenance
• or a workload shift

13. Prefer Structured Logs When You Want Serious Analysis

Structured logging pays off quickly when:

• the system is busy
• multiple services share the same cluster
• incident timelines matter
• or you want to build reusable searches and dashboards

PostgreSQL’s docs note that csvlog and jsonlog are especially convenient for loading logs into programs and tools. :contentReference[oaicite:4]{index=4}

Why this matters

Structured logs make it easier to:

• filter by user
• filter by database
• group by application name
• search only duration events
• extract temp file sizes
• correlate waits and statements
• and build saved incident queries

If your performance troubleshooting still depends entirely on grep against raw text, structured logs can be a major improvement.

14. Log Analysis Works Best With pg_stat_statements, Not Instead of It

This is one of the most important practical points.

Logs and pg_stat_statements are complements.

Logs are better for:

• exact incident timelines
• exact slow statement examples
• lock waits
• temp spills
• checkpoint and maintenance events
• production-only weirdness
• auto_explain plans

pg_stat_statements is better for:

• aggregated query cost over time
• identifying frequent expensive statements
• comparing recurring workload patterns

Practical rule

Use logs to understand what happened during this incident. Use pg_stat_statements to understand which queries are expensive over time.

You usually want both.

15. Common PostgreSQL Logging Mistakes

Mistake 1: Logging too little

You only discover this during an incident.

Mistake 2: Logging too much

The signal gets buried, storage costs rise, and nobody reads the logs well.

Mistake 3: No application identity in logs

Without application_name or a strong log prefix, slow queries are much harder to trace.

Mistake 4: No lock-wait visibility

Blocked queries get mistaken for “mysteriously slow queries.”

Mistake 5: No temp file logging

Sort/hash spills stay invisible.

Mistake 6: No auto_explain during hard incidents

You know the query was slow, but not why.

Mistake 7: Using logs without timing or thresholds

The result is usually noisy statement spam.

Mistake 8: Never revisiting thresholds

What was a good threshold six months ago may be too high or too low now.

16. A Good Practical Logging Baseline

A solid production-oriented starting point often looks like this:

logging_collector = on
log_destination = 'jsonlog'
log_line_prefix = '%m [%p] db=%d,user=%u,app=%a,client=%h '
log_min_duration_statement = 500ms
log_lock_waits = on
deadlock_timeout = 200ms
log_temp_files = 0
log_checkpoints = on

And, when needed:

shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '500ms'
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_nested_statements = on

This is not the only correct configuration. But it is a strong shape for many real systems.

17. A Practical Log Analysis Workflow

When troubleshooting PostgreSQL performance from logs, use this order:

Step 1

Find the time window where the application got slower.

Step 2

Search for:

• slow statement logs
• lock wait logs
• temp file logs
• checkpoint logs
• auto_explain plan logs

Step 3

Group by:

• application name
• database
• user
• query pattern
• repeated blocking session

Step 4

Decide whether the incident was mainly:

• one bad query
• too many queries
• blocking
• spill
• write-path pressure
• or background maintenance interaction

Step 5

Then move to:

• EXPLAIN ANALYZE
• pg_stat_statements
• pg_stat_activity
• storage/CPU metrics
• and application traces

This keeps the investigation focused.

FAQ

What PostgreSQL log settings are most useful for performance troubleshooting?

For most systems, the highest-value settings are log_min_duration_statement or sampled duration logging, log_line_prefix with enough context, log_lock_waits, log_temp_files, and auto_explain for slow-query plans.

Should I log every SQL statement in PostgreSQL?

Usually no in production. Logging every statement creates too much noise and I/O. It is usually better to log only slow statements, sampled statements, or targeted statement classes during investigations.

What is the difference between pg_stat_statements and PostgreSQL logs?

pg_stat_statements gives you aggregated query statistics over time, while logs show specific events and moments. Logs are better for incident timelines and exact slow statements, while pg_stat_statements is better for ranking recurring cost.

When should I use auto_explain?

Use auto_explain when you need PostgreSQL to automatically log execution plans for slow statements, especially when the query is hard to reproduce manually or only appears under real production traffic.

Conclusion

PostgreSQL log analysis is one of the best ways to turn vague performance complaints into concrete evidence.

Done well, it helps you see:

• which query was actually slow
• whether it was blocked
• whether it spilled to disk
• whether checkpoints or maintenance were involved
• and which application path caused it

The best setup is not the noisiest one.

It is the one that logs:

• slow statements
• waits
• spills
• plans
• and enough context to connect those events back to real workload behavior

That is what makes PostgreSQL logs useful for performance troubleshooting instead of just technically enabled.