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.
Default value
The default value for log_transaction_sample_rate
is: 0
.
Usage
When set to 0
(the default), 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
.
Change history
- PostgreSQL 12
- added (commit 799e2203)
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;