This article is the second article of the performance series. You can find the first articles link
In the postgresql database, we can make the following settings to log long DML and DDL operations.
In the postgresql.conf file, we’ll activate the following parameters and restart the database, then we’ll observe how long the update takes.
logging_collector = on
log_directory = 'log'
log_statement = 'all' - this parameter is all for logging both DDM and DML operations
log_min_duration_statement = 1000 - this parameter is in milliseconds. We will log queries that longer than 1000 ms.
log_line_prefix = '%m'
After changing these parameters, the database must be restarted using the following commands. (If you are using PostgreSQL 10 :))
systemctl stop postgresql-10.service
systemctl start postgresql-10.service
We will connect to the database and run the update command.
su - postgres
update experiment set surname = 'ylmz';
After this process, we can reach the following log when we open the log file in /var/lib/pgsql/10/data/log
LOG: statement: update trial set surname = 'ylmz';
LOG: duration: 11767.525 ms
As a result, a database process that longer than 1000 ms was captured in the database log.