PostgreSQL Logs: Slow Queries, Line Prefix, and JSON Output

Last verified on Nov 8, 2026.

PostgreSQL logs are configured in postgresql.conf and, for most settings, change with a reload and no restart. The server writes startup messages, errors, checkpoints, and anything you ask for beyond that, such as slow statements, connections, and lock waits. Out of the box it is quiet: the statement logging settings are off, so a slow query leaves no trace until you turn them on.

All of the output below was captured from a PostgreSQL 18.6 instance, and the parameter details are from the current runtime-config-logging docs. In PostgreSQL 18, log_connections takes a list of options. Older versions take on or off, so check the docs for your version.

Choose a Destination and Turn On the Collector

log_destination is a comma-separated list. The default is stderr alone, and the options are stderr, csvlog, jsonlog, and syslog (plus eventlog on Windows). Both csvlog and jsonlog need the logging collector.

logging_collector starts a background process that captures stderr and writes it to files. The docs prefer it over syslog because some messages would otherwise never appear, such as dynamic-linker failures and errors from scripts like archive_command. It can only be set at server start.

# postgresql.conf
logging_collector = on
log_destination = 'stderr,jsonlog'
log_directory = 'log'                        # relative to the data directory
log_filename = 'postgresql-%Y-%m-%d.log'
log_rotation_age = 1d
log_rotation_size = 100MB

The defaults the docs list are log for log_directory, postgresql-%Y-%m-%d_%H%M%S.log for log_filename, a rotation age of 24 hours, and a rotation size of 10 MB. When jsonlog is enabled, .json replaces the .log suffix of the file name, and a current_logfiles file in the data directory lists the files in use. From the test instance:

stderr log/postgresql-2026-11-08.log
jsonlog log/postgresql-2026-11-08.json

PostgreSQL rotates these files itself. If you give log_filename a name without time escapes, the docs say to plan for a rotation utility so the disk does not fill. See logrotate for that.

Log Slow Statements

log_min_duration_statement logs every completed statement that ran for at least the given time. The default is -1, which disables it, and 0 logs all durations. A value without units is milliseconds.

psql -c "ALTER SYSTEM SET log_min_duration_statement = '250ms'"
psql -c "SELECT pg_reload_conf()"

ALTER SYSTEM writes postgresql.auto.conf and the reload applies it. The reload is asynchronous, so a SHOW in the same session right afterwards still printed the old value in the test run. Open a new session or query pg_settings:

psql -At -c "SELECT name, setting, context FROM pg_settings WHERE name = 'log_min_duration_statement'"

Running select pg_sleep(0.25) with the threshold at 100ms produced:

2026-11-08 06:47:08.784 UTC [29060] ubuntu@postgres/orders-api LOG:  duration: 251.118 ms  statement: select pg_sleep(0.25)

For extended-query-protocol clients such as most drivers, the durations of the Parse, Bind, and Execute steps are logged independently.

On a busy server, logging every slow statement can itself become the problem. log_min_duration_sample and log_statement_sample_rate log a fraction of statements above a lower threshold, and log_min_duration_statement overrides them, so statements above that threshold are always logged. That is log sampling done inside the database. A related parameter, log_statement, logs by statement type (none, ddl, mod, all) and includes Bind parameter values, so statement text and data can land in the file. Read secrets in logs before using all. The MySQL equivalent is covered in the MySQL slow query log guide.

Set a Useful log_line_prefix

The default prefix is %m [%p] : a timestamp with milliseconds and the process ID. That is not enough to tell which application ran a slow query. The docs give this example:

log_line_prefix = '%m [%p] %q%u@%d/%a '

Set application_name in each service's connection string. Slow statement lines then start with ubuntu@postgres/orders-api, which is the difference between "something is slow" and "the orders service is slow". The docs also recommend a trailing space or punctuation, and say syslog already adds its own timestamp and process ID, so leave %m and %p out if you use syslog.

Log Connections, Lock Waits, and Temp Files

log_connections = 'authentication,authorization'   # PostgreSQL 18; on/off before
log_lock_waits = on
log_temp_files = 0
log_checkpoints = on
log_autovacuum_min_duration = 1s

• log_lock_waits logs when a session waits longer than deadlock_timeout for a lock. In a test with one session holding a row lock and a second updating the same row, it logged process 30296 still waiting for ShareLock on transaction 753 after 1000.085 ms, then acquired ShareLock ... after 1484.464 ms.
• log_temp_files logs each temporary file with its size when it is deleted. 0 logs all of them, and a bare number is kilobytes. A large temp file usually means a sort or hash spilled to disk.
• log_checkpoints is on by default.
• log_autovacuum_min_duration defaults to 10 minutes. Lower it if you want to see autovacuum runs.
• Failed authentication is always logged, whatever log_connections says.

Log Execution Plans With auto_explain

A duration line tells you a query was slow but not why. auto_explain logs the plan of slow statements. It does nothing until auto_explain.log_min_duration is set. Load it into one session with LOAD 'auto_explain' (superuser), or preload it for all sessions with session_preload_libraries or shared_preload_libraries. Expect some overhead.

LOAD 'auto_explain';
SET auto_explain.log_min_duration = '50ms';
SET auto_explain.log_analyze = on;
SELECT count(*) FROM generate_series(1,3000000) g WHERE g % 7 = 0;

The server log then contained the query text, the plan, and the actual timings:

LOG:  duration: 360.866 ms  plan:
	Query Text: SELECT count(*) FROM generate_series(1,3000000) g WHERE g % 7 = 0;
	Aggregate  (cost=45037.50..45037.51 rows=1 width=8) (actual time=360.836..360.837 rows=1.00 loops=1)
	  ->  Function Scan on generate_series g  ...  Filter: ((g % 7) = 0)

auto_explain.log_format accepts text, xml, json, and yaml, with text as the default. log_analyze collects actual row counts and timings for every statement, so enable it briefly or on a sample rather than permanently.

Read the Log as JSON

With jsonlog enabled, each line is a JSON object. The error from select 1/0 was:

{
  "timestamp": "2026-11-08 06:47:08.810 UTC",
  "user": "ubuntu",
  "dbname": "postgres",
  "pid": 29063,
  "remote_host": "[local]",
  "session_id": "6af01aec.7187",
  "line_num": 3,
  "ps": "SELECT",
  "vxid": "1/2",
  "txid": 0,
  "error_severity": "ERROR",
  "state_code": "22012",
  "message": "division by zero",
  "statement": "select 1/0",
  "application_name": "orders-api",
  "backend_type": "client backend",
  "query_id": 0
}

String fields with null values are left out, and the docs say consumers should ignore keys they do not know because fields may be added. Keys include error_severity, state_code (the SQLSTATE), message, statement, application_name, and query_id. A single filter is enough to list errors:

jq -r 'select(.error_severity=="ERROR" or .error_severity=="FATAL") | [.timestamp, .application_name, .state_code, .message] | @tsv' log/postgresql-2026-11-08.json

The slow-statement duration is part of message in JSON, not a separate numeric field, so parse the duration: N ms text if you want to chart it. See the JSON logging guide for how structured fields help, and note query_id is only filled in when compute_query_id is on.

Sending Postgres Logs to Sazabi

If you run Postgres yourself, tail the .json file with the OpenTelemetry Collector filelog receiver or Fluent Bit and forward it, as described in the OpenTelemetry Collector integration. The Postgres integration page covers the database side.

Managed providers handle this differently because you cannot reach the log files. Sazabi, an AI-native observability and security platform, documents two no-agent routes. Neon can export Postgres logs over OpenTelemetry to a Sazabi endpoint, on the Scale plan with admin access to the project, and Sazabi's Neon page notes the export is in Beta. Supabase log drains forward Postgres, Auth, and Edge Function logs to Sazabi over OTLP, and need a Pro, Team, or Enterprise plan. Both setups are in the Sazabi docs for the Neon and Supabase log sources.