---
id: 7c6f4cd6-d860-4079-9700-c8e5f5a16bb1
---

# Postgresql high rollback rate incident
---

This incident type refers to a situation where the Postgresql database has a high rollback rate, which means that a high percentage of transactions are being aborted compared to the committed ones. This can cause issues with data consistency and performance, and may require investigation and resolution by the responsible team. The incident details may include information about the affected database instance, the service or system impacted, the urgency level, and any related alerts or escalations.

### Parameters
```shell
# Environment Variables

export TABLE_NAME="PLACEHOLDER"

export DATABASE_NAME="PLACEHOLDER"

export DATABASE_USERNAME="PLACEHOLDER"

export DATABASE_PORT="PLACEHOLDER"

export DATABASE_HOST="PLACEHOLDER"

export DATABASE_PASSWORD="PLACEHOLDER"

```

## Debug

### Check the status of the PostgreSQL service
```shell
systemctl status postgresql
```

### Check the PostgreSQL logs for any errors
```shell
journalctl -u postgresql
```

### Check the PostgreSQL configuration file for any issues
```shell
cat /etc/postgresql/main/postgresql.conf
```

### Check the current connections to PostgreSQL and their status
```shell
psql -U ${DATABASE_USERNAME} -h ${DATABASE_HOST} -c "SELECT * FROM pg_stat_activity;"
```

### Check the PostgreSQL version installed
```shell
psql --version
```

### Check the system resources being used by PostgreSQL
```shell
top
```

### Check the disk usage and available space
```shell
df -h
```

### Check the PostgreSQL database for any inconsistencies
```shell
psql -U ${DATABASE_USERNAME} -h ${DATABASE_HOST} -c "CHECK TABLE ${TABLE_NAME}"
```

### Check the PostgreSQL database for any corrupted data
```shell
psql -U ${DATABASE_USERNAME} -h ${DATABASE_HOST} -c "SELECT * FROM pg_database WHERE datname = '${DATABASE_NAME}'"
```

### Check the PostgreSQL database for any deadlocks
```shell
psql -U ${DATABASE_USERNAME} -h ${DATABASE_HOST} -c "SELECT * FROM pg_locks WHERE NOT granted;"
```

### Check the PostgreSQL database for the current transaction status
```shell
psql -U ${DATABASE_USERNAME} -h ${DATABASE_HOST} -c "SELECT * FROM pg_stat_database WHERE datname = '${DATABASE_NAME}'"
```

### Measure the rollback rate 
```shell
SELECT
  sum(t.rollback_time) / sum(t.xact_rollback) AS rollback_rate
FROM
  pg_stat_database d
JOIN
  pg_stat_bgwriter t
ON
  d.datname = current_database()
WHERE
  d.datname = current_database();
```

## Repair

### Optimize the database configuration to reduce the frequency of rollbacks. Adjusting the database settings can help to optimize the rollback rate.
```shell
#!/bin/bash

# Define the database parameters

${DATABASE_NAME}="${DATABASE_NAME}"

${DATABASE_USER}="${DATABASE_USER}"

${DATABASE_PASSWORD}="${DATABASE_PASSWORD}"

${DATABASE_HOST}="${DATABASE_HOST}"

${DATABASE_PORT}="${DATABASE_PORT}"

# Backup the current database configuration

cp  /etc/postgresql/main/postgresql.conf /etc/postgresql/main/postgresql.conf.bak
# Modify the database configuration to optimize rollback rate

sed -i 's/#max_connections = 100/max_connections = 500/g'  /etc/postgresql/main/postgresql.conf

sed -i 's/#shared_buffers = 128MB/shared_buffers = 2GB/g'  /etc/postgresql/main/postgresql.conf

sed -i 's/#work_mem = 4MB/work_mem = 64MB/g'  /etc/postgresql/main/postgresql.conf

sed -i 's/#maintenance_work_mem = 64MB/maintenance_work_mem = 256MB/g'  /etc/postgresql/main/postgresql.conf

sed -i 's/#max_wal_size = 1GB/max_wal_size = 2GB/g'  /etc/postgresql/main/postgresql.conf

sed -i 's/#min_wal_size = 80MB/min_wal_size = 1GB/g'  /etc/postgresql/main/postgresql.conf

sed -i 's/#checkpoint_completion_target = 0.5/checkpoint_completion_target = 0.9/g'  /etc/postgresql/main/postgresql.conf

sed -i 's/#wal_buffers = -1/wal_buffers = 16MB/g'  /etc/postgresql/main/postgresql.conf

sed -i 's/#wal_writer_delay = 200ms/wal_writer_delay = 2s/g'  /etc/postgresql/main/postgresql.conf

sed -i 's/#commit_delay = 0/commit_delay = 1000/g'  /etc/postgresql/main/postgresql.conf

# Restart the database

systemctl restart postgresql


echo "Database configuration has been optimized to reduce rollback rate."

```

This incident type refers to a situation where the Postgresql database has a high rollback rate, which means that a high percentage of transactions are being aborted compared to the committed ones. This can cause issues with data consistency and performance, and may require investigation and resolution by the responsible team. The incident details may include information about the affected database instance, the service or system impacted, the urgency level, and any related alerts or escalations.


This incident type refers to a situation where the Tomcat server's JDBC connection pool usage surpasses its configured limits. This can lead to a shortage of available connections for incoming requests, resulting in slow response times or even server crashes. It is important to monitor and manage the JDBC connection pool usage to prevent such incidents from occurring.


Tomcat High JDBC Connection Pool Usage Incident.

This incident type occurs when there are too many locks acquired on a Postgresql database instance. This can cause issues with database performance and functionality. It may be necessary to adjust the max\_locks\_per\_transaction setting in Postgresql to prevent this issue from occurring.


Postgresql too many locks acquired

This incident type refers to a scenario where there is a high rate of statement timeouts in a Postgresql database instance. This can lead to degraded performance and potentially impact the availability of the database. It is important to quickly identify and address the underlying cause of the timeouts to ensure the stability of the system.


Postgresql high rate statement timeout incident.

This incident type occurs when there is a failure in the authentication process for the Postgres user's password. This can happen due to a variety of reasons such as incorrect login credentials, expired passwords, or database configuration issues. It is important to address this type of incident promptly to ensure that the database remains secure and accessible only to authorized users.


Password Authentication Failure for Postgres User

This incident type occurs when the maximum number of connections allowed to a Postgres database has been reached. This prevents new connections from being established and can result in errors or downtime. It is important to monitor connection usage and adjust the maximum connection limit as needed to prevent this issue from occurring.


Postgres Connection Limit Reached

```shell
# Environment Variables

export TABLE_NAME="PLACEHOLDER"

export DATABASE_NAME="PLACEHOLDER"

export DATABASE_USERNAME="PLACEHOLDER"

export DATABASE_PORT="PLACEHOLDER"

export DATABASE_HOST="PLACEHOLDER"

export DATABASE_PASSWORD="PLACEHOLDER"

```


### Check the status of the PostgreSQL service

```shell
systemctl status postgresql
```

### Check the PostgreSQL logs for any errors

```shell
journalctl -u postgresql
```

### Check the PostgreSQL configuration file for any issues

```shell
cat /etc/postgresql/main/postgresql.conf
```

### Check the current connections to PostgreSQL and their status

```shell
psql -U ${DATABASE_USERNAME} -h ${DATABASE_HOST} -c "SELECT * FROM pg_stat_activity;"
```

### Check the PostgreSQL version installed

```shell
psql --version
```

### Check the system resources being used by PostgreSQL

```shell
top
```

### Check the disk usage and available space

```shell
df -h
```

### Check the PostgreSQL database for any inconsistencies

```shell
psql -U ${DATABASE_USERNAME} -h ${DATABASE_HOST} -c "CHECK TABLE ${TABLE_NAME}"
```

### Check the PostgreSQL database for any corrupted data

```shell
psql -U ${DATABASE_USERNAME} -h ${DATABASE_HOST} -c "SELECT * FROM pg_database WHERE datname = '${DATABASE_NAME}'"
```

### Check the PostgreSQL database for any deadlocks

```shell
psql -U ${DATABASE_USERNAME} -h ${DATABASE_HOST} -c "SELECT * FROM pg_locks WHERE NOT granted;"
```

### Check the PostgreSQL database for the current transaction status

```shell
psql -U ${DATABASE_USERNAME} -h ${DATABASE_HOST} -c "SELECT * FROM pg_stat_database WHERE datname = '${DATABASE_NAME}'"
```

### Measure the rollback rate

```shell
SELECT
  sum(t.rollback_time) / sum(t.xact_rollback) AS rollback_rate
FROM
  pg_stat_database d
JOIN
  pg_stat_bgwriter t
ON
  d.datname = current_database()
WHERE
  d.datname = current_database();
```


### Optimize the database configuration to reduce the frequency of rollbacks. Adjusting the database settings can help to optimize the rollback rate.

```shell
#!/bin/bash

# Define the database parameters

${DATABASE_NAME}="${DATABASE_NAME}"

${DATABASE_USER}="${DATABASE_USER}"

${DATABASE_PASSWORD}="${DATABASE_PASSWORD}"

${DATABASE_HOST}="${DATABASE_HOST}"

${DATABASE_PORT}="${DATABASE_PORT}"

# Backup the current database configuration

cp  /etc/postgresql/main/postgresql.conf /etc/postgresql/main/postgresql.conf.bak
# Modify the database configuration to optimize rollback rate

sed -i 's/#max_connections = 100/max_connections = 500/g'  /etc/postgresql/main/postgresql.conf

sed -i 's/#shared_buffers = 128MB/shared_buffers = 2GB/g'  /etc/postgresql/main/postgresql.conf

sed -i 's/#work_mem = 4MB/work_mem = 64MB/g'  /etc/postgresql/main/postgresql.conf

sed -i 's/#maintenance_work_mem = 64MB/maintenance_work_mem = 256MB/g'  /etc/postgresql/main/postgresql.conf

sed -i 's/#max_wal_size = 1GB/max_wal_size = 2GB/g'  /etc/postgresql/main/postgresql.conf

sed -i 's/#min_wal_size = 80MB/min_wal_size = 1GB/g'  /etc/postgresql/main/postgresql.conf

sed -i 's/#checkpoint_completion_target = 0.5/checkpoint_completion_target = 0.9/g'  /etc/postgresql/main/postgresql.conf

sed -i 's/#wal_buffers = -1/wal_buffers = 16MB/g'  /etc/postgresql/main/postgresql.conf

sed -i 's/#wal_writer_delay = 200ms/wal_writer_delay = 2s/g'  /etc/postgresql/main/postgresql.conf

sed -i 's/#commit_delay = 0/commit_delay = 1000/g'  /etc/postgresql/main/postgresql.conf

# Restart the database

systemctl restart postgresql


echo "Database configuration has been optimized to reduce rollback rate."

```


Postgresql high rollback rate incident

Overview

Parameters

Debug

Check the status of the PostgreSQL service

Check the PostgreSQL logs for any errors

Check the PostgreSQL configuration file for any issues

Check the current connections to PostgreSQL and their status

Check the PostgreSQL version installed

Check the system resources being used by PostgreSQL

Check the disk usage and available space

Check the PostgreSQL database for any inconsistencies

Check the PostgreSQL database for any corrupted data

Check the PostgreSQL database for any deadlocks

Check the PostgreSQL database for the current transaction status

Measure the rollback rate

Repair

Optimize the database configuration to reduce the frequency of rollbacks. Adjusting the database settings can help to optimize the rollback rate.

Learn more

Related Runbooks

Tomcat High JDBC Connection Pool Usage Incident.

Postgresql too many locks acquired

Postgresql high rate statement timeout incident.

Password Authentication Failure for Postgres User

Support