Logs are one of the most important, and most misunderstood parts of running Postgres in production. When configured well, Postgres logs help you debug incidents, understand performance issues, and gain visibility into database behavior. When configured poorly, they create noise, increase load, and still fail to answer the questions you care about.
This guide explains what Postgres logs are, how logging works, which settings matter most, and how to use logs effectively in real systems, without overcomplicating things.
What Postgres logs are
Postgres logs record what the database server is doing. They capture server-side events such as:
- SQL statements (optionally)
- Slow queries
- Errors and warnings
- Connection and disconnection events
- Lock waits and deadlocks
- Background activity like checkpoints and autovacuum
They are primarily an operational tool. Engineers rely on Postgres logs to answer questions like:
- Why is this query slow?
- Why are connections spiking?
- What caused this error?
- Is the database under unusual load?
Postgres logs are generated by the database server itself and are independent of application logs.
How Postgres logging works (high level)
By default, Postgres writes log output to stderr. When the logging_collector is enabled, Postgres captures this output and writes it to log files on disk.
Key points to know:
- Logs are written on the database server, not the client
- Log files can be plain text or structured (
csvlog) - Log rotation can be based on time or file size
- Retention is your responsibility — Postgres does not manage it automatically
Common log formats
-
Plain text logs Easy to read, harder to parse at scale.
-
CSV logs (
csvlog) Structured, machine-friendly, and much easier to ingest into log systems.
If you plan to analyze logs beyond manual inspection, csvlog is usually the better choice.
The most important Postgres logging parameters
Postgres exposes many logging options, but only a subset matter for most production systems. It’s best to think about them by intent, not alphabetically.
🔍 Query performance and slow queries
This is where most teams start — and where most mistakes happen.
log_min_duration_statement
Logs statements that run longer than a specified duration (in milliseconds).
log_min_duration_statement = 500This logs queries that take longer than 500 ms and is often the best default for production systems.
log_duration
Logs how long every statement took.
Useful for debugging, but very noisy if left on permanently.
log_statement
Controls which statements are logged.
Values include none, ddl, mod and all.
log_statement = noneSetting log_statement = all in production is rarely a good idea — it generates huge volumes of logs and can noticeably impact performance.
🔌 Connections and authentication
Connection-related logs help diagnose spikes, leaks, and unexpected traffic.
Key settings are log_connections, log_disconnections and log_hostname.
These are especially useful when:
- Using connection pools
- Investigating sudden increases in load
- Debugging authentication issues
They are typically safe to enable, but still generate noticeable volume on busy systems.
⚠️ Errors, locks, and failures
These settings help explain why queries are blocked or failing.
-
log_error_verbosityControls how much detail is logged for errors. -
log_lock_waitsLogs statements that wait longer thandeadlock_timeoutfor locks. -
deadlock_timeoutHow long Postgres waits before checking for deadlocks.
Together, these settings are invaluable for diagnosing:
- Lock contention
- Deadlocks
- Long-running transactions blocking others
🧠 Context: making Postgres logs readable
Logs without context are frustrating. This is where log_line_prefix matters.
log_line_prefix = '%m [%p] %u@%d 'This adds:
- timestamp
- process ID
- user
- database name
Without a proper prefix, it becomes very difficult to correlate log lines with:
- applications
- users
- specific databases
A good log_line_prefix dramatically improves log usability with minimal overhead.
Common Postgres logging mistakes
Even experienced teams run into these.
Logging everything
Turning on full statement logging in production creates massive noise and real performance cost — often without delivering useful insight.
Enabling too many options at once
Logging connections, disconnections, durations, statements, and background activity simultaneously can overwhelm both storage and humans.
Missing context
Logs without user, database, or timestamp information are hard to interpret and nearly impossible to analyze at scale.
No retention strategy
Keeping logs forever increases cost and risk. Keeping them too briefly makes post-incident analysis impossible.
Collecting logs but never using them
Logs should answer questions. If nobody looks at them, they’re just expensive text files.
Performance impact of Postgres logging
Logging is not free.
Each logged event adds:
- CPU overhead
- Disk IO
- Additional load on log processing pipelines
High-volume logging can amplify existing performance issues, especially under load.
Practical rules of thumb:
- Prefer slow-query logging over full statement logging
- Increase logging temporarily during incidents
- Validate logging changes in staging before production
- Scale logging back down once the issue is resolved
How teams use Postgres logs in practice
In real systems, Postgres logs are commonly used to:
- Identify slow endpoints by spotting recurring slow queries
- Investigate failed deployments or migrations
- Diagnose connection storms and pool misconfigurations
- Understand background activity like checkpoints or autovacuum
- Correlate application errors with database-side failures
They work best when paired with clear operational workflows and disciplined configuration changes.
Postgres logging best practices quick summary
- Log slow queries, not everything
- Always configure
log_line_prefix - Prefer structured logs (
csvlog) for analysis - Rotate and retain logs intentionally
- Treat logging as a tuning tool, not a permanent dump
Final thoughts
Postgres logs are one of the most powerful tools you have for understanding database behavior — but only if they’re configured with intent. Start minimal, focus on slow queries and errors, and increase verbosity only when you need answers.
Good logging doesn’t mean more logs. It means the right logs, at the right time, for the right questions.
