# PostgreSQL Log Patterns

## Slow Statement

Typical shape:

```text
duration: 1234.567 ms  statement: SELECT ...
```

Interpretation:

- `duration` includes execution time and may include waiting, depending on where the wait occurred.
- Group by normalized fingerprint, not by raw SQL literals.
- A single max duration can be noise; total time and call count often explain CPU better.

## Parse/Bind/Execute

Prepared statements may log as:

```text
duration: 12.345 ms  execute <name>: SELECT ...
parameters: $1 = '...'
```

Use the `execute` SQL body as the fingerprint. Parameters help reproduce selectivity, but redact sensitive values.

## Temporary Files

```text
temporary file: path "base/pgsql_tmp/...", size ...
```

Usually indicates sort/hash/materialization spilled to disk. Correlate with the previous statement, `pg_stat_statements.temp_blks_written`, and execution plans containing `Sort`, `HashAggregate`, `Hash Join`, or `Materialize`.

## Lock Waits

```text
process ... still waiting for ... lock
process ... acquired ... lock after ...
```

Identify both blocked and blocking sessions. Do not optimize the blocked SQL before confirming the blocker and transaction age.

## Checkpoints And WAL

```text
checkpoint starting
checkpoint complete
```

Frequent checkpoints can amplify I/O and CPU overhead. Check WAL volume, `max_wal_size`, checkpoint timing, bulk writes, and autovacuum activity.

## Autovacuum

Autovacuum logs with high elapsed time, many dead tuples, or repeated cancellation can indicate bloat and stale visibility maps. Avoid `VACUUM FULL` during business hours; prefer diagnosing long transactions, autovacuum thresholds, and table-specific settings first.

## Cancelled Or Timeout Statements

```text
canceling statement due to statement timeout
canceling statement due to user request
```

Timeouts protect the system but can cause retry storms. Correlate with application retry policy and connection pool behavior.