PostgreSQL log sampling: how to see slow queries without flooding the disk

The problem Christophe Pettus lays out in his series All Your GUCs in a Row, published on Planet PostgreSQL, is familiar to anyone who administers a production database: after setting log_min_duration_statement to a threshold worth investigating, everything that runs below it becomes invisible. And what runs below it is usually exactly the story that matters.

The article's arithmetic is the best argument. A query that takes one minute is a problem you spot immediately. But twenty million queries at one millisecond each add up to five and a half hours of database time, and produce not a single log line. pg_stat_statements shows that aggregate, but it doesn't deliver a specimen: a real execution, with the real parameter values, at the real moment it happened. That specimen is what the three sampling parameters buy you, at a log volume the DBA decides in advance.

The pair that samples by duration

The first two GUCs work together, and both require superuser context. log_min_duration_sample (default -1, disabled; measured in milliseconds, with 0 counting everything) sets the floor of the range you want to sample. log_statement_sample_rate (default 1.0) is the dial: the fraction of statements above that floor that actually get logged. The resulting line is indistinguishable from a regular log_min_duration_statement line, because it is the same line:

LOG: duration: 0.300 ms statement: SELECT 12 + 0

Sampling is a coin flip per statement, not a fixed interval. Pettus reports that, in one of his tests, the 0.25 rate logged exactly 10 statements out of 40; 0.5 logged 17 out of 40. In his words, it's "statistically honest, arithmetically messy." In other words: don't expect 0.5 to produce exactly 50%.

The rule that makes the pair safe to use is the absolute priority of log_min_duration_statement. Any statement above that threshold is always logged, regardless of what sampling says. Pettus verified this behavior with log_statement_sample_rate = 0: a 415ms query still made it into the log even with a sampling configuration that, nominally, logs nothing.

The practical corollary matters so you don't waste configuration: log_min_duration_sample only does something when it's set below log_min_duration_statement. If you set it above, everything it would sample is already being logged unconditionally. A working configuration looks like this:

log_min_duration_statement = '1s'    # the alarm
log_min_duration_sample    = '100ms' # the sample floor
log_statement_sample_rate  = 0.05    # 5% of the range between 100ms and 1s

Why there are two thresholds, not one

The priority rule, according to Pettus, "isn't a design whim, it's a scar." The history lesson is worth telling. Statement sampling was committed to PostgreSQL 12 in a simpler form: a single dial, applied to statements that already exceeded log_min_duration_statement. The side effect was serious: your worst statement of the day could be dropped by the sampler, and the one guarantee the log had always offered, that the worst statements are in it, silently stopped holding.

The community caught the problem while it was still in beta and reverted the feature entirely, arguing that the second threshold needed to be part of the feature from the start, not arrive later as a fix that breaks compatibility. The two-threshold version only landed in PostgreSQL 13. The lesson generalizes to any sampling scheme you build, inside or outside the database:

Decide first what can never be dropped by sampling.

>

-- Christophe Pettus, Planet PostgreSQL

For a DBA, this means treating the alarm threshold as sacred and the sample as a separate statistical instrument, one that never competes with it.

Sampling the whole transaction

log_transaction_sample_rate (default 0, also superuser, in core since PostgreSQL 12) operates on a completely different axis. The coin is flipped once, when the transaction begins, and duration doesn't factor in. If the transaction wins the draw, all of its statements are logged, from BEGIN to COMMIT, each with its own duration:

LOG: duration: 0.099 ms   statement: BEGIN;
LOG: duration: 2.068 ms   statement: INSERT INTO orders VALUES (9000001, 4242, now());
LOG: duration: 397.231 ms statement: SELECT count(*) FROM orders WHERE customer_id = 4242;
LOG: duration: 1.344 ms   statement: COMMIT;

This is the trace you want when the problem isn't a statement, but a shape: which statement acquired the lock everything else queued behind, what ran before the slow one, whether the application is holding a transaction open across a network call it shouldn't. Duration-threshold logging shows none of that, because it delivers the slow statement stripped of its context. For investigating contention and long-running transactions, this full view is usually worth more than the isolated specimen.

The autocommit trap

Before turning on transaction sampling, there's a catch the article is careful to flag. A statement outside an explicit transaction is a transaction. So, in an autocommit workload (which, in practice, is most of an ORM's read traffic), log_transaction_sample_rate degenerates into duration-blind statement sampling. In Pettus's test, 40 autocommit statements at 0.5 produced 17 logged "transactions" of one statement each.

In that kind of workload, the parameter generates volume without the coherence that justifies it. It only pays off where multi-statement transactions are the norm, and even there it's a parameter you turn on for an investigation and turn back off afterward.

When it isn't worth it, and what to use instead

The recommended permanent state is the default one, with log_min_duration_statement doing the daily work. The sampling pair comes into play when the queries you need to see live below the alarm threshold; the transaction dial comes in when you need context, not specimens.

There's one clear case where none of the three is the right tool. If what you want is the aggregate, how many times a query ran, the total time it consumed, that was never a logging problem. That's pg_stat_statements's job, and, as Pettus sums it up, no sampling rate will beat it at what it specializes in. Sampling logs to reconstruct aggregate statistics is the wrong path: it generates disk I/O, noise, and still delivers a number skewed by the sampling itself.

The takeaway for those running PostgreSQL databases in Brazil is straightforward: log sampling is a surgical instrument, not a routine setting. It solves the blind spot between pg_stat_statements's aggregate and log_min_duration_statement's alarm, capturing real executions at a predictable log cost. Left on permanently, or misordered relative to the alarm threshold, it becomes just one more log line to store and rotate.