Logs are one of the most useful parts of running Postgres in production, and one of the most misunderstood. Configured well, they help you debug incidents, chase down slow queries, and see what the database is actually doing. Configured poorly, they generate noise, add load, and still fail to answer the question you opened the log file to ask.
This guide covers 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 first and foremost an operational tool. You reach for Postgres logs when you need to answer questions like:
- Why is this query slow?
- Why are connections spiking?
- What caused this error?
- Is the database under unusual load?
These are generated by the database server itself, and they're separate from your application logs.
How Postgres logging works (high level)
By default, Postgres writes log output to stderr. When you enable the logging_collector, Postgres captures that output and writes it to log files on disk.
A few things worth knowing up front:
- 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 for you.
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 a log system.
If you plan to do anything with logs beyond reading them by hand, csvlog is usually the better choice.
The most important Postgres logging parameters
Postgres exposes a lot of logging options, but only a handful matter for most production systems. The easiest way to keep them straight is to group them by what you're trying to find out, rather than reading the alphabetical list.
Query performance and slow queries
This is where most teams start, and where most of the mistakes happen.
log_min_duration_statement
Logs statements that run longer than a given duration (in milliseconds).
log_min_duration_statement = 500This logs queries that take longer than 500 ms. It's often the best default for production: you catch the slow ones without drowning in everything else.
log_duration
Logs how long every statement took. Handy while you're actively debugging, very noisy if you leave it on permanently.
log_statement
Controls which statements get logged. Values are none, ddl, mod and all.
log_statement = noneSetting log_statement = all in production is rarely worth it. It produces enormous volumes of logs and can measurably slow the server down, so most teams leave it off and reach for slow-query logging instead.
Connections and authentication
Connection logs help you diagnose spikes, leaks, and traffic you didn't expect. The settings to know are log_connections, log_disconnections and log_hostname.
They earn their keep when you're:
- Using connection pools
- Investigating a sudden jump in load
- Debugging authentication issues
They're generally safe to turn on, though on a busy system they still add noticeable volume.
Errors, locks, and failures
These settings explain why queries are blocked or failing.
-
log_error_verbosityControls how much detail you get for each error. -
log_lock_waitsLogs statements that wait longer thandeadlock_timeoutfor a lock. -
deadlock_timeoutHow long Postgres waits before it checks for deadlocks.
Taken together, they're what you want when you're tracking down lock contention, deadlocks, or a long-running transaction that's blocking everyone else.
Context: making Postgres logs readable
A log line without context is frustrating to read, and log_line_prefix is what fixes that.
log_line_prefix = '%m [%p] %u@%d 'That gives you:
- timestamp
- process ID
- user
- database name
Without a sensible prefix, it's hard to tie a log line back to a specific application, user, or database. A good log_line_prefix makes logs far more usable for almost no overhead, so set it.
Common Postgres logging mistakes
Even experienced teams walk into these.
Logging everything
Full statement logging in production buys you massive noise and a real performance cost, often without telling you anything useful.
Enabling too many options at once
Connections, disconnections, durations, statements, and background activity all at once will overwhelm your storage and the people reading it.
Missing context
Logs without user, database, or timestamp information are hard to interpret by hand and nearly impossible to analyze at scale.
No retention strategy
Keep logs forever and you pay for it in cost and risk. Keep them too briefly and post-incident analysis becomes impossible. Pick a window on purpose.
Collecting logs but never using them
Logs exist to answer questions. If nobody ever looks at them, they're just expensive text files.
Performance impact of Postgres logging
Logging is not free. Every logged event adds:
- CPU overhead
- Disk IO
- More work for whatever log pipeline you've built
Under load, heavy logging can make an existing performance problem worse rather than helping you find it.
Some rules of thumb:
- Prefer slow-query logging over full statement logging
- Turn logging up temporarily during an incident
- Test logging changes in staging before production
- Turn it back down once the issue is resolved
How teams use Postgres logs in practice
In real systems, Postgres logs tend to be used to:
- Find 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
- Line up application errors with database-side failures
They work best alongside clear operational workflows and disciplined configuration changes, not as a dumping ground you check after something has already gone wrong.
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 when they're configured with intent. Start minimal, focus on slow queries and errors, and turn up verbosity only when you actually need answers.
Good logging doesn't mean more logs. It means the right logs, at the right time, for the right questions.