How to log slow queries in PostgreSQL

Slow query logging turns intermittent slowness into actionable evidence by recording the SQL statements that exceed an execution-time threshold.

PostgreSQL can emit a duration: log line for slow statements when log_min_duration_statement is set, capturing both the elapsed time and the statement text. A sensible threshold keeps the signal-to-noise ratio high compared to logging every statement.

Slow-statement logs can grow quickly and may include sensitive literals embedded in SQL text, so threshold choice and retention policies matter. Server-wide configuration via ALTER SYSTEM requires superuser privileges and takes effect on reload, while managed services often expose the same parameter via provider settings instead of systemd.

1. Set a global slow query threshold using ALTER SYSTEM.

   $ sudo -u postgres psql -c "ALTER SYSTEM SET log_min_duration_statement = '500ms';"
   ALTER SYSTEM

   Use a higher threshold first when log volume is unknown. Lower it once noisy statements are filtered. Set log_min_duration_statement to -1 to disable slow statement logging.

2. Reload the PostgreSQL configuration to apply the updated logging threshold.

   $ sudo -u postgres psql -c "SELECT pg_reload_conf();"
    pg_reload_conf
   ----------------
    t
   (1 row)

   Related: How to manage the PostgreSQL service with systemctl in Linux

3. Verify the effective log_min_duration_statement value.

   $ sudo -u postgres psql -Atc "SHOW log_min_duration_statement;"
   500ms

4. Run a test statement that exceeds the threshold.

   $ sudo -u postgres psql -d postgres -c "SELECT pg_sleep(0.6);"
    pg_sleep
   ----------
   
   (1 row)

5. Confirm the test statement appears in the PostgreSQL log with a duration: entry.

   $ sudo grep -hF "SELECT pg_sleep(0.6)" /var/lib/postgresql/data/log/postgresql-2025-12-24.log | tail -n 1
   2025-12-24 23:58:35.749 UTC [397] 694c7e2b.18d 3 postgres@postgres [local] app=psql LOG:  duration: 603.289 ms  statement: SELECT pg_sleep(0.6);

   On hosts that write logs to /var/log/postgresql, search those files or the systemd journal instead.