PostgreSQL parameter log_min_duration_statement

#postgres #sql #database #devops

The log_min_duration_statement is configuration parameter of PostgreSQL.

It sets the minimum execution time in milliseconds (ms) above which all statements will be logged.

The default value for the log_min_duration_statement parameter is -1, which disables logging statements.

Setting the PostgreSQL parameter log_min_duration_statement to 0 will prints all statements durations.

Note. Setting this parameter on Production environments can lead to high volume of logs and significant disk storage allocation.

Usage Examples.

Assume I have created table abc:

postgres=# \d abc
                Table "public.abc"
 Column |  Type  | Collation | Nullable | Default
--------+--------+-----------+----------+---------
 a      | bigint |           |          |

postgres=#

and populated it with data:

postgres=# insert into abc SELECT * FROM generate_series(1,10000000);
INSERT 0 10000000

#1 Example. How to log all the queries that take more than 1ms?

postgres=# set log_min_duration_statement=1;
SET
postgres=# show log_min_duration_statement;
 log_min_duration_statement
----------------------------
 1ms
(1 row)

postgres=# insert into abc values(1);
INSERT 0 1
postgres=# insert into abc values(2);
INSERT 0 1
postgres=# insert into abc values(3);
INSERT 0 1
postgres=# select count(1) from abc;
  count
----------
 10000003
(1 row)

postgres=#

PostgreSQL log file (fragment):

...
2023-03-11 17:20:27.262 UTC [119] LOG:  duration: 5.477 ms  statement: insert into abc values(1);
2023-03-11 17:20:29.281 UTC [119] LOG:  duration: 8.656 ms  statement: insert into abc values(2);
2023-03-11 17:20:34.151 UTC [119] LOG:  duration: 7.263 ms  statement: insert into abc values(3);
2023-03-11 17:20:40.473 UTC [119] LOG:  duration: 336.449 ms  statement: select count(1) from abc;
...

#2 Example. How to disable logging statements?

postgres=# set log_min_duration_statement=-1;
SET
postgres=# show log_min_duration_statement;
 log_min_duration_statement
----------------------------
 -1
(1 row)

postgres=# select count(1) from abc;
  count
----------
 10000003
(1 row)

postgres=# insert into abc values(2);
INSERT 0 1
postgres=#

PostgreSQL log file (fragment):

There is no information about the queries.

#3 Example. How to log all the queries that take more than 100ms?

postgres=# set log_min_duration_statement=100;
SET
postgres=# show log_min_duration_statement;
 log_min_duration_statement
----------------------------
 100ms
(1 row)

postgres=# select count(1) from abc;
  count
----------
 10000006
(1 row)

postgres=# insert into abc values(5);
INSERT 0 1

postgres=# select count(1) from abc where a > 10234;
  count
---------
 9989768
(1 row)

postgres=# insert into abc values(2);
INSERT 0 1

PostgreSQL log file (fragment):

...
2023-03-11 17:18:50.287 UTC [119] LOG:  duration: 301.261 ms  statement: select count(1) from abc;
2023-03-11 17:18:54.134 UTC [119] LOG:  duration: 388.595 ms  statement: select count(1) from abc where a > 10234;
...