High transaction rollback rate — application logic diagnosis

A large fraction of database transactions are rolling back — generating unnecessary WAL, bloat, and load without producing committed work.

Diagnose it

SELECT datname,
       xact_commit,
       xact_rollback,
       round(
           100.0 * xact_rollback / NULLIF(xact_commit + xact_rollback, 0),
       2) AS rollback_pct
FROM pg_stat_database
WHERE datname NOT IN ('template0', 'template1')
ORDER BY rollback_pct DESC;

A rollback rate above 5–10% is worth investigating in an OLTP workload (guidance).
Compare idle_in_transaction_time and active_time (PostgreSQL 14+)
to understand where session time is being spent.

Why it happens

Common causes: (1) application-level exceptions caught after a write, leaving the transaction
in a failed state; (2) optimistic concurrency control patterns that expect frequent conflicts;
(3) statement_timeout or lock_timeout cancelling statements mid-transaction;
(4) serialization failures under REPEATABLE READ or SERIALIZABLE isolation.
Each rollback still generates WAL, triggers row version cleanup by autovacuum, and consumes
CPU on the server.