---
id: 29f12770-c8de-4efc-ad83-c8fa2e80901d
---

# PostgreSQL tablespace full
---

This incident type occurs when the PostgreSQL tablespace of a database has reached its maximum storage capacity. When tablespace is full, it can no longer store any new data and any attempt to insert new data will fail. This can cause disruption in the normal operation of the database and affect the performance of applications that rely on the database.

### Parameters
```shell
export PATH_TO_TABLESPACE="PLACEHOLDER"

export DATABASE_NAME="PLACEHOLDER"

export TABLESPACE_NAME="PLACEHOLDER"

export ADDITIONAL_SPACE_IN_GB="PLACEHOLDER"

export MAX_TABLESPACE_SIZE="PLACEHOLDER"
```

## Debug

### Check the disk space usage of the PostgreSQL tablespace
```shell
df -h ${PATH_TO_TABLESPACE}
```

### Check the size of the tablespace
```shell
du -sh ${PATH_TO_TABLESPACE}
```

### List the largest tables in the database
```shell
psql -c "SELECT pg_size_pretty(pg_relation_size(relid)), relname FROM pg_stat_user_tables ORDER BY pg_relation_size(relid) DESC LIMIT 10;" ${DATABASE_NAME}
```

### List the largest indexes in the database
```shell
psql -c "SELECT pg_size_pretty(pg_relation_size(indexrelid)), relname, indexrelname FROM pg_stat_user_indexes JOIN pg_index USING(indexrelid) WHERE indisunique = false ORDER BY pg_relation_size(indexrelid) DESC LIMIT 10;" ${DATABASE_NAME}
```

### Check the available space in the tablespace
```shell
psql -c "SELECT pg_size_pretty(pg_tablespace_size('${TABLESPACE_NAME}'));" ${DATABASE_NAME}
```

### Large data inserts: If a large amount of data is inserted into the database at once, it can fill up the tablespace quickly and cause it to become full.
```shell


#!/bin/bash



# Set variables

DB_NAME=${DATABASE_NAME}

TABLESPACE_NAME=${TABLESPACE_NAME}

MAX_TABLESPACE_SIZE=${MAX_TABLESPACE_SIZE}



# Check if tablespace is full

TABLESPACE_USAGE=$(psql -d $DB_NAME -t -c "SELECT pg_size_pretty(pg_tablespace_size('$TABLESPACE_NAME'))")

if [ "$TABLESPACE_USAGE" >= "$MAX_TABLESPACE_SIZE" ]; then

  echo "Tablespace $TABLESPACE_NAME is full."

  

  # Check for large data inserts

  LARGE_DATA_INSERTS=$(psql -d $DB_NAME -t -c "SELECT relname, n_tup_ins FROM pg_stat_user_tables WHERE n_tup_ins > 1000")

  if [ -n "$LARGE_DATA_INSERTS" ]; then

    echo "The following tables have had large data inserts:"

    echo "$LARGE_DATA_INSERTS"

  else

    echo "No tables have had large data inserts."

  fi

else

  echo "Tablespace $TABLESPACE_NAME is not full."

fi


```

## Repair

### Increase the storage capacity of the tablespace by adding more disk space to the server where the database is hosted.
```shell


#!/bin/bash



# Set the parameters

TABLESPACE=${TABLESPACE_NAME}

ADDITIONAL_SPACE=${ADDITIONAL_SPACE_IN_GB}



# Check if the tablespace exists

if [ ! -d "$TABLESPACE" ]; then

  echo "Error: Tablespace $TABLESPACE does not exist."

  exit 1

fi



# Check if the additional space is a number

if ! [[ "$ADDITIONAL_SPACE" =~ ^[0-9]+$ ]]; then

  echo "Error: Additional space must be a number."

  exit 1

fi



# Add the additional space to the tablespace

sudo su - postgres -c "psql -c \"ALTER TABLESPACE $TABLESPACE SET (add data '$ADDITIONAL_SPACE GB')\""



echo "Additional space of $ADDITIONAL_SPACE GB added to tablespace $TABLESPACE."


```

This incident type occurs when the PostgreSQL tablespace of a database has reached its maximum storage capacity. When tablespace is full, it can no longer store any new data and any attempt to insert new data will fail. This can cause disruption in the normal operation of the database and affect the performance of applications that rely on the database.


This incident type occurs when the PermGen or Metaspace usage of the Tomcat server exceeds 90%. PermGen and Metaspace are memory pools used for storing class definitions and metadata of the Java virtual machine (JVM). When these memory pools reach their maximum capacity, the server may experience performance issues or even crash. This incident requires immediate attention from the software engineer to investigate the root cause and take appropriate actions to resolve the issue.


Tomcat PermGen or Metaspace Usage exceeds 90%

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


Postgresql table not auto vacuumed 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.


Postgresql high rollback rate incident

```shell
export PATH_TO_TABLESPACE="PLACEHOLDER"

export DATABASE_NAME="PLACEHOLDER"

export TABLESPACE_NAME="PLACEHOLDER"

export ADDITIONAL_SPACE_IN_GB="PLACEHOLDER"

export MAX_TABLESPACE_SIZE="PLACEHOLDER"
```


### Check the disk space usage of the PostgreSQL tablespace

```shell
df -h ${PATH_TO_TABLESPACE}
```

### Check the size of the tablespace

```shell
du -sh ${PATH_TO_TABLESPACE}
```

### List the largest tables in the database

```shell
psql -c "SELECT pg_size_pretty(pg_relation_size(relid)), relname FROM pg_stat_user_tables ORDER BY pg_relation_size(relid) DESC LIMIT 10;" ${DATABASE_NAME}
```

### List the largest indexes in the database

```shell
psql -c "SELECT pg_size_pretty(pg_relation_size(indexrelid)), relname, indexrelname FROM pg_stat_user_indexes JOIN pg_index USING(indexrelid) WHERE indisunique = false ORDER BY pg_relation_size(indexrelid) DESC LIMIT 10;" ${DATABASE_NAME}
```

### Check the available space in the tablespace

```shell
psql -c "SELECT pg_size_pretty(pg_tablespace_size('${TABLESPACE_NAME}'));" ${DATABASE_NAME}
```

### Large data inserts: If a large amount of data is inserted into the database at once, it can fill up the tablespace quickly and cause it to become full.

```shell


#!/bin/bash



# Set variables

DB_NAME=${DATABASE_NAME}

TABLESPACE_NAME=${TABLESPACE_NAME}

MAX_TABLESPACE_SIZE=${MAX_TABLESPACE_SIZE}



# Check if tablespace is full

TABLESPACE_USAGE=$(psql -d $DB_NAME -t -c "SELECT pg_size_pretty(pg_tablespace_size('$TABLESPACE_NAME'))")

if [ "$TABLESPACE_USAGE" >= "$MAX_TABLESPACE_SIZE" ]; then

  echo "Tablespace $TABLESPACE_NAME is full."

  

  # Check for large data inserts

  LARGE_DATA_INSERTS=$(psql -d $DB_NAME -t -c "SELECT relname, n_tup_ins FROM pg_stat_user_tables WHERE n_tup_ins > 1000")

  if [ -n "$LARGE_DATA_INSERTS" ]; then

    echo "The following tables have had large data inserts:"

    echo "$LARGE_DATA_INSERTS"

  else

    echo "No tables have had large data inserts."

  fi

else

  echo "Tablespace $TABLESPACE_NAME is not full."

fi


```


### Increase the storage capacity of the tablespace by adding more disk space to the server where the database is hosted.

```shell


#!/bin/bash



# Set the parameters

TABLESPACE=${TABLESPACE_NAME}

ADDITIONAL_SPACE=${ADDITIONAL_SPACE_IN_GB}



# Check if the tablespace exists

if [ ! -d "$TABLESPACE" ]; then

  echo "Error: Tablespace $TABLESPACE does not exist."

  exit 1

fi



# Check if the additional space is a number

if ! [[ "$ADDITIONAL_SPACE" =~ ^[0-9]+$ ]]; then

  echo "Error: Additional space must be a number."

  exit 1

fi



# Add the additional space to the tablespace

sudo su - postgres -c "psql -c \"ALTER TABLESPACE $TABLESPACE SET (add data '$ADDITIONAL_SPACE GB')\""



echo "Additional space of $ADDITIONAL_SPACE GB added to tablespace $TABLESPACE."


```


PostgreSQL tablespace full

Overview

Parameters

Debug

Check the disk space usage of the PostgreSQL tablespace

Check the size of the tablespace

List the largest tables in the database

List the largest indexes in the database

Check the available space in the tablespace

Large data inserts: If a large amount of data is inserted into the database at once, it can fill up the tablespace quickly and cause it to become full.

Repair

Increase the storage capacity of the tablespace by adding more disk space to the server where the database is hosted.

Learn more

Related Runbooks

Tomcat PermGen or Metaspace Usage exceeds 90%

Tomcat High JDBC Connection Pool Usage Incident.

Postgresql too many locks acquired

Postgresql table not auto vacuumed incident

Support