log_transaction_sample_rate
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).
References
- PostgreSQL documentation: log_transaction_sample_rate
Useful links
- Log a sample of transactions - pgsql-hackers thread
- Log all statements from a sample of transactions - blog entry from April 2019 by select * from depesz;