Add PostgreSQL Slow Query Logging

Enable slow query logging with a measured threshold, use pg_stat_statements for aggregates, and analyze plans without logging sensitive bind values.

~25 mindatabasepostgresqlobservability

Task

Find the queries that consume the most time, capture their plans, and fix the top offenders without flooding logs or leaking data.

When to use this

API latency rises gradually. The team suspects the database but has no query-level evidence and cannot tell whether the problem is frequency, a bad plan, or lock waits.

Prerequisites

PostgreSQL 13+ with permission to change server settings
Access to the server log and a log aggregation system
The pg_stat_statements extension available on the server

Steps

1. Set a slow-query threshold that captures the tail without logging every normal query, then reload the configuration.

ALTER SYSTEM SET log_min_duration_statement = '500ms'; SELECT pg_reload_conf();

Expected output: Queries over 500 ms appear in the PostgreSQL log with duration and statement text.

2. Enable pg_stat_statements to aggregate by normalized query and identify frequency and total time.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Expected output: The extension is available and pg_stat_statements returns rows.

3. Rank queries by total execution time, then inspect the worst plans with EXPLAIN ANALYZE on a representative parameter set.

SELECT query, calls, total_exec_time, mean_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

Expected output: The top queries by total time are identified, not just the slowest single execution.

4. Review log retention and redaction, and avoid logging bind values or sensitive statement text in shared systems.

ALTER SYSTEM SET log_min_duration_statement = '500ms';
ALTER SYSTEM SET log_statement = 'none';

Expected output: Slow statements are logged, while full statement logging of every query is disabled.

Troubleshooting

Problem

No slow queries are logged

Solution

The setting was applied to a different database or reload failed. Check SHOW log_min_duration_statement and the server log destination.

Problem

Logs contain sensitive values

Solution

PostgreSQL logs statement text with literals for some queries. Use parameterized queries, restrict log access, and avoid logging bind parameters.

Limitations

pg_stat_statements normalizes queries but can group different plans; inspect representative plans separately.
A threshold that is too low increases I/O and storage cost without improving signal.

Duration alone is not the whole story

A query that takes 200 ms and runs 10,000 times per minute matters more than a report that takes 5 seconds once an hour. pg_stat_statements aggregates normalized queries by calls, total execution time, and mean time, so the ranking reflects workload impact. Use slow-query logging to capture the exact statements and plans around the threshold, and use the extension to find frequency.

SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Analyze the plan, not the SQL alone

Run EXPLAIN (ANALYZE, BUFFERS) with parameters that represent real traffic. A sequential scan on a small table is fine; the same scan on a large table under a filter is not. Look for row-estimate errors, nested loops over large inputs, and temporary spills. Compare the plan before and after adding an index, and confirm that the planner uses it.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 AND created_at > now() - interval '7 days';

Protect the logs

Slow query logs can contain personal data in literals. Restrict access, centralize retention, and prefer parameterized queries so values are not embedded in the statement text. Do not set log_statement = 'all' in production to compensate for a missing dashboard; it multiplies volume and exposure. Rotate logs and alert on volume spikes, which may indicate a new query pattern or an attack.

Close the loop

Add the missing index, rewrite the query, or change the access pattern, then verify with the same workload. Track total time and calls for the top queries over time. A slow-query dashboard that nobody reviews is not observability; assign an owner and a review cadence.