---
id: a1872830-6b85-4b4c-87cd-02ad614ef60f
---

# Postgresql table not auto vacuumed incident
---

This incident type refers to situations where a Postgresql table has not been vacuumed automatically, causing potential performance issues. Vacuuming is a process that frees up space and improves performance by removing dead rows and updating statistics. Failure to vacuum a table can result in degraded database performance and slow query execution times. This incident type requires immediate attention to prevent further performance degradation and optimize database performance.

### Parameters
```shell
# Environment Variables

export TABLE_NAME="PLACEHOLDER"

export DATABASE_USER="PLACEHOLDER"

export DATABASE_PASSWORD="PLACEHOLDER"

export DATABASE_NAME="PLACEHOLDER"

export DATABASE_HOST="PLACEHOLDER"
```

## Debug

### Check if vacuum is enabled on the specific table
```shell
sudo -u postgres psql -c "SELECT relname, n_live_tup, last_vacuum, last_analyze FROM pg_stat_user_tables WHERE relname='${TABLE_NAME}'"
```

### Check if auto-vacuum is enabled on the specific table
```shell
sudo -u postgres psql -c "SELECT relname, autovacuum_enabled, autovacuum_vacuum_scale_factor, autovacuum_analyze_scale_factor FROM pg_stat_user_tables WHERE relname='${TABLE_NAME}'"
```

### Check the vacuum and analyze status of the specific table
```shell
sudo -u postgres psql -c "SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_user_tables WHERE relname='${TABLE_NAME}'"
```

### Check the vacuum and analyze details of the specific table
```shell
sudo -u postgres psql -c "SELECT * FROM pgstattuple('${TABLE_NAME}')"
```

### Force an analyze on the specific table
```shell
sudo -u postgres psql -c "ANALYZE ${TABLE_NAME}"
```

### The autovacuum process may not be enabled or configured properly in the database, causing tables to not be vacuumed automatically.
```shell
bash

#!/bin/bash

# Set database credentials

DB_USER=${DATABASE_USER}

DB_PASSWORD=${DATABASE_PASSWORD}

DB_NAME=${DATABASE_NAME}

DB_HOST=${DATABASE_HOST}



# Check if autovacuum process is enabled and properly configured

autovacuum=$(psql -h $DB_HOST -U $DB_USER -d $DB_NAME -c "SELECT name, setting FROM pg_settings WHERE name IN ('autovacuum', 'autovacuum_vacuum_scale_factor', 'autovacuum_analyze_scale_factor', 'autovacuum_vacuum_cost_limit', 'autovacuum_vacuum_cost_delay')")



# Check if autovacuum is enabled

if echo "$autovacuum" | grep -q "on"; then

  echo "Autovacuum process is enabled"

else

  echo "Autovacuum process is not enabled"

fi

# Check if autovacuum scale factors are properly configured

if echo "$autovacuum" | grep -q "0.2"; then

  echo "Autovacuum scale factors are properly configured"

else

  echo "Autovacuum scale factors are not properly configured"

fi

# Check if autovacuum cost limit and delay are properly configured

if echo "$autovacuum" | grep -q "5000"; then

  echo "Autovacuum cost limit and delay are properly configured"

else

  echo "Autovacuum cost limit and delay are not properly configured"

fi
```

## Repair

### Force a vacuum on the specific table
```shell

sudo -u postgres psql -c "VACUUM ${TABLE_NAME}"

```


### Manually vacuum the affected table(s) to free up space and update statistics using the VACUUM command.
```shell

#!/bin/bash

# Set variables

DB_NAME=${DATABASE_NAME}

TABLE_NAME=${TABLE_NAME}

# Connect to the database

psql $DB_NAME <<EOF

# Vacuum the table

VACUUM $TABLE_NAME;

EOF

# Display success message

echo "Table $TABLE_NAME has been vacuumed successfully."

```

### Adjust the automatic vacuum process settings to ensure that it runs more frequently or with different parameters that are more appropriate for your database workload.
```shell
#!/bin/bash

# Define the database name, user, and password

DB_NAME=${DATABASE_NAME}

DB_USER=${DATABASE_USER}

DB_PASS=${DATABASE_PASSWORD}

# Define the frequency and parameters for the vacuum process

VACUUM_FREQ="PLACEHOLDER" # e.g. 10000

VACUUM_SCALE_FACTOR="PLACEHOLDER" # e.g. 0.2

# Connect to the database and run the command to adjust the vacuum settings

psql -d $DB_NAME -U $DB_USER -c "ALTER SYSTEM SET autovacuum_vacuum_scale_factor = '$VACUUM_SCALE_FACTOR';"

psql -d $DB_NAME -U $DB_USER -c "ALTER SYSTEM SET autovacuum_vacuum_cost_limit = '$VACUUM_FREQ';"

psql -d $DB_NAME -U $DB_USER -c "SELECT pg_reload_conf();"


# Output a message indicating success

echo "Automatic vacuum process settings adjusted successfully"


```

This incident type refers to situations where a Postgresql table has not been vacuumed automatically, causing potential performance issues. Vacuuming is a process that frees up space and improves performance by removing dead rows and updating statistics. Failure to vacuum a table can result in degraded database performance and slow query execution times. This incident type requires immediate attention to prevent further performance degradation and optimize database performance.


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 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_USER="PLACEHOLDER"

export DATABASE_PASSWORD="PLACEHOLDER"

export DATABASE_NAME="PLACEHOLDER"

export DATABASE_HOST="PLACEHOLDER"
```


### Check if vacuum is enabled on the specific table

```shell
sudo -u postgres psql -c "SELECT relname, n_live_tup, last_vacuum, last_analyze FROM pg_stat_user_tables WHERE relname='${TABLE_NAME}'"
```

### Check if auto-vacuum is enabled on the specific table

```shell
sudo -u postgres psql -c "SELECT relname, autovacuum_enabled, autovacuum_vacuum_scale_factor, autovacuum_analyze_scale_factor FROM pg_stat_user_tables WHERE relname='${TABLE_NAME}'"
```

### Check the vacuum and analyze status of the specific table

```shell
sudo -u postgres psql -c "SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_user_tables WHERE relname='${TABLE_NAME}'"
```

### Check the vacuum and analyze details of the specific table

```shell
sudo -u postgres psql -c "SELECT * FROM pgstattuple('${TABLE_NAME}')"
```

### Force an analyze on the specific table

```shell
sudo -u postgres psql -c "ANALYZE ${TABLE_NAME}"
```

### The autovacuum process may not be enabled or configured properly in the database, causing tables to not be vacuumed automatically.

```shell
bash

#!/bin/bash

# Set database credentials

DB_USER=${DATABASE_USER}

DB_PASSWORD=${DATABASE_PASSWORD}

DB_NAME=${DATABASE_NAME}

DB_HOST=${DATABASE_HOST}



# Check if autovacuum process is enabled and properly configured

autovacuum=$(psql -h $DB_HOST -U $DB_USER -d $DB_NAME -c "SELECT name, setting FROM pg_settings WHERE name IN ('autovacuum', 'autovacuum_vacuum_scale_factor', 'autovacuum_analyze_scale_factor', 'autovacuum_vacuum_cost_limit', 'autovacuum_vacuum_cost_delay')")



# Check if autovacuum is enabled

if echo "$autovacuum" | grep -q "on"; then

  echo "Autovacuum process is enabled"

else

  echo "Autovacuum process is not enabled"

fi

# Check if autovacuum scale factors are properly configured

if echo "$autovacuum" | grep -q "0.2"; then

  echo "Autovacuum scale factors are properly configured"

else

  echo "Autovacuum scale factors are not properly configured"

fi

# Check if autovacuum cost limit and delay are properly configured

if echo "$autovacuum" | grep -q "5000"; then

  echo "Autovacuum cost limit and delay are properly configured"

else

  echo "Autovacuum cost limit and delay are not properly configured"

fi
```


### Force a vacuum on the specific table

```shell

sudo -u postgres psql -c "VACUUM ${TABLE_NAME}"

```

### Manually vacuum the affected table(s) to free up space and update statistics using the VACUUM command.

```shell

#!/bin/bash

# Set variables

DB_NAME=${DATABASE_NAME}

TABLE_NAME=${TABLE_NAME}

# Connect to the database

psql $DB_NAME <<EOF

# Vacuum the table

VACUUM $TABLE_NAME;

EOF

# Display success message

echo "Table $TABLE_NAME has been vacuumed successfully."

```

### Adjust the automatic vacuum process settings to ensure that it runs more frequently or with different parameters that are more appropriate for your database workload.

```shell
#!/bin/bash

# Define the database name, user, and password

DB_NAME=${DATABASE_NAME}

DB_USER=${DATABASE_USER}

DB_PASS=${DATABASE_PASSWORD}

# Define the frequency and parameters for the vacuum process

VACUUM_FREQ="PLACEHOLDER" # e.g. 10000

VACUUM_SCALE_FACTOR="PLACEHOLDER" # e.g. 0.2

# Connect to the database and run the command to adjust the vacuum settings

psql -d $DB_NAME -U $DB_USER -c "ALTER SYSTEM SET autovacuum_vacuum_scale_factor = '$VACUUM_SCALE_FACTOR';"

psql -d $DB_NAME -U $DB_USER -c "ALTER SYSTEM SET autovacuum_vacuum_cost_limit = '$VACUUM_FREQ';"

psql -d $DB_NAME -U $DB_USER -c "SELECT pg_reload_conf();"


# Output a message indicating success

echo "Automatic vacuum process settings adjusted successfully"


```


Postgresql table not auto vacuumed incident

Overview

Parameters

Debug

Check if vacuum is enabled on the specific table

Check if auto-vacuum is enabled on the specific table

Check the vacuum and analyze status of the specific table

Check the vacuum and analyze details of the specific table

Force an analyze on the specific table

The autovacuum process may not be enabled or configured properly in the database, causing tables to not be vacuumed automatically.

Repair

Force a vacuum on the specific table

Manually vacuum the affected table(s) to free up space and update statistics using the VACUUM command.

Adjust the automatic vacuum process settings to ensure that it runs more frequently or with different parameters that are more appropriate for your database workload.

Learn more

Related Runbooks

Postgresql too many locks acquired

Postgresql high rollback rate incident

Postgresql high rate statement timeout incident.

Password Authentication Failure for Postgres User

Support