Runbook: HighTransactionRollback Alert

Alert Details

• Alert Name: HighTransactionRollback
• Expression: rate(pg_stat_database_xact_rollback{datname!~"template.*", job="integrations/postgres"}[5m]) / (rate(pg_stat_database_xact_commit{datname!~"template.*", job="integrations/postgres"}[5m]) + rate(pg_stat_database_xact_rollback{datname!~"template.*"}[5m])) * 100 > 5

Description

The rollback-to-commit ratio for PostgreSQL the database is >5%. This indicates a high rate of failed transactions, which can degrade performance and suggest application or database issues.

Possible Causes

• Application Bugs: Logic errors causing transactions to fail (e.g., constraint violations)
• Deadlocks or race conditions
• Database Issues:Long-running queries blocking transactions.Resource contention (CPU, I/O, or locks).
• Connectivity Problems: Network timeouts or connection resets.

Troubleshooting Steps

1. Investigate Recent Changes:

Check application logs for errors around {{ $labels.datname }}.

Review recent deployments or schema modifications.

2. Database Checks:

Query pg_stat_activity for blocked transactions.

Monitor for deadlocks (pg_stat_database_deadlocks).

3. Optimize Workloads:

Break large transactions into smaller batches.

Adjust idle_in_transaction_session_timeout if needed.