Search code examples
postgresqlrollback

Postgresql Rollback investigation

I have one PostgreSQL 40TB size of database and every day I face the high rollback rate after every hour. I just want to see the rollback details about session which call rollback, rolled back table name etc. How can I fetch this details from database. Thanks in advance.

Here you can see rollback rate

I didn't find any SQL or another way to fetch Rollback details

Solution

  • Look into the log file. If the rollbacks were caused by errors, these errors should show up in the log. If there was no error, but the application deliberately ran ROLLBACK, the only way to log that is log_statement = 'all', which can write so much log that it can impact database performance.

    An alternative to log_statement = 'all' (if that is too much) is to set log_transaction_sample_rate = 0.01. Then one percent of your database transactions will be logged, which reduces the volume and still allows you to see enough ROLLBACK statements to figure out what is going on.