Logging Strategy, auto_explain, Sampling, Dashboards, Baselines, and Alert Design

Learning outcomes

Live views help while an incident is happening; logs preserve event context after sessions disappear. But “log everything forever” can produce unacceptable overhead, cost, and secret exposure. ServiceHub needs a strategy that captures slow statements, lock waits, maintenance, errors, connection identity, and occasional execution plans while keeping a measurable sampling/baseline model.

1. Logging begins with correlation fields

log_line_prefix = '%m [%p] %q%u@%d/%a Q=%Q 'log_connections = 'authentication,authorization'log_disconnections = on

PID and application name join naturally to pg_stat_activity; %Q carries query ID when computed. This gives a stable incident thread even if SQL text is truncated or intentionally not logged. Note: messages emitted by log_statement itself report query ID as zero because they are logged before the ID is available.

2. Prefer duration thresholds and sampling to indiscriminate SQL logging

# Always log statements exceeding the service's investigated slow boundary:log_min_duration_statement = '1500ms'# Separately sample a fraction of moderately slow statements:log_min_duration_sample = '250ms'log_statement_sample_rate = 0.05# Keep full-statement logging off unless a narrowly scoped investigation requires it:log_statement = 'none'# Avoid dumping full bind parameters into non-error duration logs:log_parameter_max_length = 0log_parameter_max_length_on_error = 0

The numbers above are examples for a lab/design discussion, not recommended universal thresholds. Choose boundaries from ServiceHub latency objectives, normal query distributions, traffic volume, log budget, and incident response needs. Sampling is stochastic; dashboards must treat it as sampled evidence.

3. Lock waits become visible after deadlock_timeout

log_lock_waits = on# deadlock_timeout controls when a lock-wait log is emitted# and when PostgreSQL performs its deadlock check.deadlock_timeout = '1s'

log_lock_waits emits a message when a session has waited longer than deadlock_timeout for a lock. Lowering deadlock_timeout increases deadlock-check frequency and log sensitivity; do not choose a tiny value without measuring contention/overhead.

4. Maintenance/checkpoint observability

SELECT name, setting, sourceFROM pg_settingsWHERE name IN (  'log_checkpoints',  'log_autovacuum_min_duration',  'log_lock_waits',  'deadlock_timeout',  'log_min_duration_statement',  'log_min_duration_sample',  'log_statement_sample_rate')ORDER BY name;SELECT * FROM pg_stat_checkpointer;SELECT relname, last_autovacuum, last_autoanalyze,       autovacuum_count, autoanalyze_count,       total_autovacuum_time, total_autoanalyze_timeFROM pg_stat_user_tablesWHERE schemaname = 'app'ORDER BY relname;

Logs tell you discrete events and durations; cumulative views tell you totals since reset. Use both. A maintenance event is not automatically a problem—alert when its timing/resource impact correlates with user-facing symptoms or deviates from the normal baseline.

5. auto_explain captures plans that you did not manually EXPLAIN

auto_explain is a PostgreSQL-supplied module. It can be preloaded globally/session-wide, or a superuser can LOAD it for a single diagnostic session. The safest classroom lab uses one isolated session and immediately resets the settings.

LOAD 'auto_explain';SET auto_explain.log_min_duration = 0;SET auto_explain.log_analyze = on;SET auto_explain.log_timing = off;SET auto_explain.log_buffers = on;SET auto_explain.log_wal = on;SET auto_explain.log_settings = on;SET auto_explain.log_parameter_max_length = 0;SET auto_explain.sample_rate = 1.0;SET application_name = 'ch21_auto_explain_lab';SELECT status, count(*)FROM app.ch21_workloadWHERE tenant_id BETWEEN 1 AND 20GROUP BY statusORDER BY status;

The plan is written to the server log, not returned to the client. log_analyze=on means the module collects execution statistics. PostgreSQL warns that per-node timing can be extremely expensive for all statements while analyze mode is active; log_timing=off avoids much of that clock-reading overhead while retaining actual row counts/buffer information.

RESET auto_explain.log_min_duration;RESET auto_explain.log_analyze;RESET auto_explain.log_timing;RESET auto_explain.log_buffers;RESET auto_explain.log_wal;RESET auto_explain.log_settings;RESET auto_explain.log_parameter_max_length;RESET auto_explain.sample_rate;

6. Production auto_explain needs sampling and privacy design

session_preload_libraries = 'auto_explain'auto_explain.log_min_duration = '2s'auto_explain.sample_rate = 0.05auto_explain.log_analyze = onauto_explain.log_timing = offauto_explain.log_buffers = onauto_explain.log_wal = offauto_explain.log_parameter_max_length = 0

This is a starting pattern to test, not a universal profile. Loading auto_explain into every session has overhead; logging plans can reveal table/column names and SQL context; log_parameter_max_length=0 reduces parameter disclosure but does not make all query text non-sensitive.

7. Dashboards should preserve baselines and reset timestamps

8. Build one baseline snapshot table

DROP TABLE IF EXISTS app.ch21_observability_baseline;CREATE TABLE app.ch21_observability_baseline ASSELECT  clock_timestamp() AS observed_at,  d.stats_reset AS database_stats_reset,  d.xact_commit,  d.xact_rollback,  d.temp_bytes,  d.deadlocks,  w.stats_reset AS wal_stats_reset,  w.wal_records,  w.wal_fpi,  w.wal_bytes,  (SELECT stats_reset FROM pg_stat_statements_info) AS statements_stats_reset,  (SELECT dealloc FROM pg_stat_statements_info) AS statements_deallocFROM pg_stat_database AS dCROSS JOIN pg_stat_wal AS wWHERE d.datname = current_database();SELECT * FROM app.ch21_observability_baseline;

In a real observability system, collect snapshots externally at a fixed cadence and compute rates. Do not pollute the monitored database with an ever-growing local monitoring table unless that is an intentional architecture.

9. Alert on symptoms and service objectives—not folklore

A good alert says “p95 API database time breached its Service Level Objective (SLO) while lock-wait time/count rose above its rolling baseline” or “replication replay byte gap is growing continuously and recovery point objective is at risk.” A poor alert says “cache hit ratio below 99%” without user impact or baseline context.

Use multi-window logic where practical: a fast-burn alert catches acute incidents; a slow-burn alert catches persistent degradation. Maintenance/deploy windows can change normal distributions, so annotate them rather than training the team to ignore alerts.

10. Correlate one request across application, PostgreSQL, and logs

SET application_name = 'servicehub-api/request-class-orders';SELECT pid, application_name, query_id, state,       wait_event_type, wait_eventFROM pg_stat_activityWHERE pid = pg_backend_pid();

The application should carry its own request/trace ID outside SQL where possible. PostgreSQL's application_name, PID, query ID, SQLSTATE, and timestamp then become database-side join keys to logs/traces rather than a replacement distributed tracing system.

11. Final Chapter 21 cleanup

DROP TABLE IF EXISTS app.ch21_observability_baseline;DROP TABLE IF EXISTS app.ch21_table_stat_sample;DROP TABLE IF EXISTS app.ch21_workload CASCADE;DROP TABLE IF EXISTS app.ch21_work_order_lock CASCADE;-- Keep pg_stat_statements installed/preloaded if it is part of the-- approved server observability baseline; do not drop it just because-- the chapter ended.

Authoritative references

Statistics and logging fields evolve across PostgreSQL majors. These PostgreSQL 18 primary sources define the mechanisms used in this lesson.

• PostgreSQL 18 — Error Reporting and Logging
• PostgreSQL 18 — auto_explain
• PostgreSQL 18 — pg_stat_statements
• PostgreSQL 18 — Monitoring Database Activity
• PostgreSQL 18 — Query Identifier Configuration