PostgreSQL Slow Query Logging & auto_explain Configuration

Supanote Core Team @supanote · 1 min read

plain (sql) 13 hours ago · 28 lines · 6 views

-- PostgreSQL 16 Slow Query Logging & Auto Explain
-- Append these directives to postgresql.conf or run as ALTER SYSTEM

-- 1. Log Queries Exceeding 250ms
log_min_duration_statement = 250

-- 2. Detail Logging for Slow Statements
log_checkpoints = on
log_connections = off
log_disconnections = off
log_lock_waits = on
log_temp_files = 0
log_autovacuum_min_duration = 1000

-- 3. Log Line Prefix (useful for pgbadger or log parsers)
log_line_prefix = '%m [%p] %q%u@%d '

-- 4. Auto Explain Extension for Slow Queries
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = 250
auto_explain.log_analyze = true
auto_explain.log_buffers = true
auto_explain.log_timing = true
auto_explain.log_triggers = true
auto_explain.log_verbose = true
auto_explain.log_format = 'text'
auto_explain.sample_rate = 1.0

1-- PostgreSQL 16 Slow Query Logging & Auto Explain

2-- Append these directives to postgresql.conf or run as ALTER SYSTEM

4-- 1. Log Queries Exceeding 250ms

5log_min_duration_statement = 250

7-- 2. Detail Logging for Slow Statements

8log_checkpoints = on

9log_connections = off

10log_disconnections = off

11log_lock_waits = on

12log_temp_files = 0

13log_autovacuum_min_duration = 1000

15-- 3. Log Line Prefix (useful for pgbadger or log parsers)

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

18-- 4. Auto Explain Extension for Slow Queries

19shared_preload_libraries = 'auto_explain'

20auto_explain.log_min_duration = 250

21auto_explain.log_analyze = true

22auto_explain.log_buffers = true

23auto_explain.log_timing = true

24auto_explain.log_triggers = true

25auto_explain.log_verbose = true

26auto_explain.log_format = 'text'

27auto_explain.sample_rate = 1.0

Replies 0

Reply with note

No replies yet

Every reply is a note. Start a discussion, ask a question, or attach a code snippet.

Reply with a note