---
id: c942ed0b-82a9-4246-8ec8-2a2dd56a8c1b
---

# Postgresql high rate statement timeout incident.
---

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.

### Parameters
```shell
# Environment Variables

export NEW_TIMEOUT_VALUE="PLACEHOLDER"

export DATABASE_PORT="PLACEHOLDER"

export DATABASE_USER="PLACEHOLDER"

export DATABASE_PASSWORD="PLACEHOLDER"

export DATABASE_HOST="PLACEHOLDER"

export DATABASE_NAME="PLACEHOLDER"
```

## Debug

### Verify that the Postgresql service is running
```shell
systemctl status postgresql.service
```

### Check the Postgresql logs for errors
```shell
journalctl -u postgresql.service --no-pager -n 50
```

### Check the Postgresql configuration for any settings that may be causing the timeouts
```shell
grep -v '^$\|^\s*\#'  /etc/postgresql/main/postgresql.conf | grep -iE 'statement_timeout|idle_in_transaction_session_timeout'
```

### Check the system resource usage to see if the server is overloaded
```shell
top
```

### Check the network connectivity between the application and database server
```shell
ping ${DATABASE_HOST}
```

### Check the disk usage of the database server
```shell
df -h
```

## Repair

### Define the variables
```shell
PG_CONFIG_FILE="/etc/postgresql/main/postgresql.conf"

TIMEOUT_VALUE="${NEW_TIMEOUT_VALUE}"
```

### Backup the original configuration file
```shell
cp $PG_CONFIG_FILE $PG_CONFIG_FILE.bak
```

### Update the timeout value in the configuration file
```shell
sed -i "s/statement_timeout = .*/statement_timeout = $TIMEOUT_VALUE/g" $PG_CONFIG_FILE
```

### Restart the Postgresql service to apply the changes
```shell
service postgresql restart
```

### Optimize the queries being executed in the database to reduce the load and prevent timeouts.
```shell
bash

#!/bin/bash



# Define the database connection parameters

DB_HOST=${DATABASE_HOST}

DB_PORT=${DATABASE_PORT}

DB_NAME=${DATABASE_NAME}

DB_USER=${DATABASE_USER}

DB_PASSWORD=${DATABASE_PASSWORD}



# Define the query to optimize

QUERY="PLACEHOLDER"



# Run the optimization command

PGOPTIONS="-c statement_timeout=0" psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c "EXPLAIN ANALYZE $QUERY" > /dev/null


```

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.


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 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.


Postgresql high rollback rate incident

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

This incident type occurs when the number of connections to a Postgres server is approaching its limit and may cause a saturation of the connection pool. This can lead to slow queries, timeouts, and other performance issues. It is important to monitor and manage the number of connections to ensure the smooth functioning of the database.


Postgres Connection Saturation

```shell
# Environment Variables

export NEW_TIMEOUT_VALUE="PLACEHOLDER"

export DATABASE_PORT="PLACEHOLDER"

export DATABASE_USER="PLACEHOLDER"

export DATABASE_PASSWORD="PLACEHOLDER"

export DATABASE_HOST="PLACEHOLDER"

export DATABASE_NAME="PLACEHOLDER"
```


### Verify that the Postgresql service is running

```shell
systemctl status postgresql.service
```

### Check the Postgresql logs for errors

```shell
journalctl -u postgresql.service --no-pager -n 50
```

### Check the Postgresql configuration for any settings that may be causing the timeouts

```shell
grep -v '^$\|^\s*\#'  /etc/postgresql/main/postgresql.conf | grep -iE 'statement_timeout|idle_in_transaction_session_timeout'
```

### Check the system resource usage to see if the server is overloaded

```shell
top
```

### Check the network connectivity between the application and database server

```shell
ping ${DATABASE_HOST}
```

### Check the disk usage of the database server

```shell
df -h
```


### Define the variables

```shell
PG_CONFIG_FILE="/etc/postgresql/main/postgresql.conf"

TIMEOUT_VALUE="${NEW_TIMEOUT_VALUE}"
```

### Backup the original configuration file

```shell
cp $PG_CONFIG_FILE $PG_CONFIG_FILE.bak
```

### Update the timeout value in the configuration file

```shell
sed -i "s/statement_timeout = .*/statement_timeout = $TIMEOUT_VALUE/g" $PG_CONFIG_FILE
```

### Restart the Postgresql service to apply the changes

```shell
service postgresql restart
```

### Optimize the queries being executed in the database to reduce the load and prevent timeouts.

```shell
bash

#!/bin/bash



# Define the database connection parameters

DB_HOST=${DATABASE_HOST}

DB_PORT=${DATABASE_PORT}

DB_NAME=${DATABASE_NAME}

DB_USER=${DATABASE_USER}

DB_PASSWORD=${DATABASE_PASSWORD}



# Define the query to optimize

QUERY="PLACEHOLDER"



# Run the optimization command

PGOPTIONS="-c statement_timeout=0" psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c "EXPLAIN ANALYZE $QUERY" > /dev/null


```


Postgresql high rate statement timeout incident.

Overview

Parameters

Debug

Verify that the Postgresql service is running

Check the Postgresql logs for errors

Check the Postgresql configuration for any settings that may be causing the timeouts

Check the system resource usage to see if the server is overloaded

Check the network connectivity between the application and database server

Check the disk usage of the database server

Repair

Define the variables

Backup the original configuration file

Update the timeout value in the configuration file

Restart the Postgresql service to apply the changes

Optimize the queries being executed in the database to reduce the load and prevent timeouts.

Learn more

Related Runbooks

Tomcat High JDBC Connection Pool Usage Incident.

Postgresql too many locks acquired

Postgresql high rollback rate incident

Postgres Connection Limit Reached

Support