skip to content

On a busy PostgreSQL primary where logging every statement for pgBadger is too costly, how do you set the logging and what do the reports lose?

level: seniorimportance: should knowfreq 40%

answer

  1. log volume versus report truth
  2. a threshold hides frequent fast queries
  3. sampling below a threshold
  4. per role, per database, per window
  5. cumulative stats fill the gap

basics

~20 s

Raise log_min_duration_statement to a threshold, sample below it with log_min_duration_sample and log_statement_sample_rate, or log everything only for a window or one role; each loses part of the most-frequent and total-time picture, which pg_stat_statements can cover.

solid answer

~40 s

`log_min_duration_statement = 0` writes a line per statement, which on a busy primary means gigabytes of log a day, extra I/O and a slower collector. The usual compromise is a **threshold** such as `250ms`: the slowest-queries report stays accurate, but **most frequent** and **total time** only count statements above it, so the cheap query run 50 million times disappears. **Sampling** adds `log_min_duration_sample` with `log_statement_sample_rate`, which logs a fraction of statements above a lower bound, so counts become estimates. A **capture window** (set `0` on reload for a day, then back) or a **per-role** setting (`ALTER ROLE ... SET`) gives full detail where it matters. Keep `pg_stat_statements` for exact cumulative counts, and never combine `log_duration` with `log_statement = 'all'` and a threshold.

go deeper

for a junior

Recall that logging every statement is the pgBadger ideal but costs disk and I/O, and that a threshold logs only slower statements.

for a middle

Explain which report sections a threshold distorts and which it leaves alone, and how log_min_duration_sample and log_statement_sample_rate sample below it.

for a senior

Show the production plan: threshold from the latency budget, capture windows or per-role settings for full detail, off-host parsing, filters for maintenance jobs, pg_stat_statements for exact totals.

for a principal

Argue the observability budget: what log volume the fleet can carry, which questions need per-execution samples versus cumulative counters, and how to keep reports comparable across weeks.

## The cost of logging everything pgBadger's documentation starts from `log_min_duration_statement = 0`: every completed statement is logged with its duration and text, which gives the most complete report. On a busy primary, for example a retail chain's order database taking point-of-sale traffic from hundreds of stores, that is expensive: - **Volume**: one line or more per statement, with full query text and bind parameters, can reach gigabytes per day. - **I/O and contention**: the log is written by the server, usually through the logging collector, on the same host; a saturated log disk or collector slows the server itself. - **Downstream cost**: rotation, retention and pgBadger's own parsing time grow with the log. The question an interviewer wants answered is not "turn it on" but **which loss you accept**. ## Option 1: a duration threshold Set `log_min_duration_statement` to, say, `250ms`. Only statements at or above it are logged. | Report | With `0` | With `250ms` | |---|---|---| | Slowest queries | complete | complete | | Most frequent queries | complete | only statements over 250ms | | Queries that took the most time | complete | misses fast, high-volume queries | | Query-time histogram | full distribution | only the tail | | Checkpoints, locks, temp files, autovacuum | unaffected | unaffected | The hidden trap: the query that costs most in aggregate is often a **2 ms lookup run 50 million times**, which never crosses the threshold. The event sections are unaffected because they come from their own settings (`log_checkpoints`, `log_lock_waits`, `log_temp_files`, `log_autovacuum_min_duration`). ## Option 2: sampling PostgreSQL can sample statements between two bounds: - `log_min_duration_sample` (default `-1`, disabled) marks the lower bound for sampling; - `log_statement_sample_rate` (default `1.0`) is the fraction of those statements logged; - `log_min_duration_statement` still logs **everything** above its own, higher threshold. For example, sample 5% of statements over 10 ms and log all over 500 ms. pgBadger parses the sampled lines like any duration line, but its counts and totals for the sampled band are now a **sample**: multiply by the rate in your head, and do not compare those counts with an unsampled week. ## Option 3: narrow the scope `log_min_duration_statement` can be set by a superuser per **role** and per **database**, and server-wide on reload, so full detail can be targeted: ```sql -- full detail for the point-of-sale API only; new sessions pick it up ALTER ROLE pos_api SET log_min_duration_statement = 0; -- or one capture day for the whole server, then revert ALTER SYSTEM SET log_min_duration_statement = 0; SELECT pg_reload_conf(); ``` A role or database setting applies to **sessions that start afterwards**; pooled connections keep the old value until they reconnect. A capture window such as one weekday a month gives pgBadger a complete picture of a representative day while keeping the steady-state volume low. ## Option 4: durations without text Setting `log_min_duration_statement = -1` and `log_duration = on` logs only durations, without statement text. pgBadger then reports overall counts and timing, but no per-query sections. It is cheaper in bytes, but rarely what a team wants. What **not** to do: pgBadger's documentation warns that enabling `log_min_duration_statement`, `log_duration` and `log_statement` together gives **wrong counters** and a much bigger log. ## Filling the gap: cumulative statistics The usual production answer combines two sources: 1. **pgBadger** on a threshold: timestamped samples, hourly charts, the tail of slow statements, and the event sections (locks, checkpoints, temp files, autovacuum). 2. **pg_stat_statements** for exact cumulative calls and total time per normalised query, including the fast, frequent ones the threshold hides. How that extension works is a separate subject. ## Choosing a threshold - Start from the latency budget of the application: a threshold at or just below the slowest acceptable response. - Measure log volume per hour after the change and adjust. - Run pgBadger **off the database host** (copy, ssh or `-r`) so parsing never competes with the server. - Use pgBadger's own filters (`--exclude-appname`, `--exclude-query`, `--exclude-time`) to keep maintenance jobs such as `pg_dump` from topping the slow list, rather than raising the threshold for them.

  • When would you trust pgBadger over pg_stat_statements for finding a slow query, and the reverse?
    pgBadger wins when you need when and with which parameters: timestamped executions, sample values, hourly patterns, plus lock and temp-file events tied to queries. pg_stat_statements wins for exact cumulative calls and total time across every execution, including fast frequent queries below any log threshold. Most teams run both.
  • A weekly pgBadger report shows pg_dump's COPY statements as the slowest queries. How do you fix the report without raising the threshold?
    Filter in pgBadger: `--exclude-appname pg_dump` drops entries whose application name is pg_dump, or `--exclude-time` removes the backup window. The threshold stays where the application needs it.

  • A threshold only drops noise; the most-frequent report stays accurate
  • Sampled logging gives pgBadger exact query counts
  • ALTER ROLE SET changes logging for that role's already-open sessions
  • Enable log_duration, log_statement and a threshold together for completeness
  • log_lock_waits stops working when log_min_duration_statement is raised