log_transaction_sample_rate

A configuration parameter which enables a sample of all transaction types to be logged

log_transaction_sample_rate is a configuration parameter which enables a sample of all transaction types to be logged.

This is useful for obtaining a reasonably representative sample of transactions being executed without generating a potentially unmanageable logging volume.

log_transaction_sample_rate was introduced in PostgreSQL 12 (commit 799e2203).

Values

The default is 0, meaning no statements wll be logged due to this setting (although other logging parameters may generate statement logging).

The maximum is 1, meaning all statements for all transactions will be logged. Any value between 0 and 1 will result in the corresponding percentage of transactions being logged.

Permissions

Superuser permissions are required to change this parameter. A SIGHUP is required to make any change take effect.

Caveats and restrictions

  • This parameter applies to all databases in a PostgreSQL cluster.
  • When turned on, it can result in additional performance overhead.
  • The actual number of logged transactions may not exactly match the value specified for log_transaction_sample_rate.

Examples

Given following table:

CREATE TABLE foo (id serial primary key, val text);

and with log_transaction_sample_rate set to 0.1, executing the following shell command:

for I in `seq 1 10`; do psql -c 'INSERT INTO foo VALUES(DEFAULT, CLOCK_TIMESTAMP()::TEXT)'; done

results in one entry in the log:

2019-12-24 14:32:08.340 UTC [27705] LOG:  parameter "log_transaction_sample_rate" changed to "0.1"
2019-12-24 14:32:15.182 UTC [16899] LOG:  duration: 2.130 ms  statement: INSERT INTO foo VALUES(DEFAULT, CLOCK_TIMESTAMP()::TEXT)

With log_transaction_sample_rate set to 0.5, re-executing the same shell command results in six log entries:

2019-12-24 14:35:44.475 UTC [27705] LOG:  parameter "log_transaction_sample_rate" changed to "0.5"
2019-12-24 14:35:46.160 UTC [17023] LOG:  duration: 10.140 ms  statement: INSERT INTO foo VALUES(DEFAULT, CLOCK_TIMESTAMP()::TEXT)
2019-12-24 14:35:46.168 UTC [17025] LOG:  duration: 1.980 ms  statement: INSERT INTO foo VALUES(DEFAULT, CLOCK_TIMESTAMP()::TEXT)
2019-12-24 14:35:46.188 UTC [17027] LOG:  duration: 5.223 ms  statement: INSERT INTO foo VALUES(DEFAULT, CLOCK_TIMESTAMP()::TEXT)
2019-12-24 14:35:46.197 UTC [17029] LOG:  duration: 1.959 ms  statement: INSERT INTO foo VALUES(DEFAULT, CLOCK_TIMESTAMP()::TEXT)
2019-12-24 14:35:46.208 UTC [17031] LOG:  duration: 2.846 ms  statement: INSERT INTO foo VALUES(DEFAULT, CLOCK_TIMESTAMP()::TEXT)
2019-12-24 14:35:46.227 UTC [17035] LOG:  duration: 1.910 ms  statement: INSERT INTO foo VALUES(DEFAULT, CLOCK_TIMESTAMP()::TEXT)
2019-12-24 14:35:46.258 UTC [17041] LOG:  duration: 1.946 ms  statement: INSERT INTO foo VALUES(DEFAULT, CLOCK_TIMESTAMP()::TEXT)

(As noted above, the actual number of sampled transactions does not correspond precisely to the value specified in the parameter).

Categories

GUC configuration item, Logging

See also

log_statement_sample_rate