# FM-KED-002 — PostgreSQL Table and Index Bloat (Missing or Ineffective VACUUM)

**Severity:**<span style="white-space: pre-wrap;"> S2 — High</span>  
**Recovery Class:**<span style="white-space: pre-wrap;"> B — Standard Recovery</span>  
**Covered by Monthly Support:**<span style="white-space: pre-wrap;"> Yes</span>

---

### Description

<span style="white-space: pre-wrap;">PostgreSQL database performance degrades over time due to table and index bloat caused by insufficient or ineffective </span>`<span class="editor-theme-code">VACUUM</span>`<span style="white-space: pre-wrap;"> operations.</span>

This issue manifests gradually and is commonly observed on systems with high write activity, long-running transactions, or misconfigured autovacuum settings.

---

### Typical Symptoms

- Slow queries without obvious query plan changes
- Increased disk usage on database volumes
- Tables or indexes significantly larger than expected
- Elevated I/O usage
- Application timeouts under normal load

---

### Diagnostic Checklist

#### Identify Database Size and Largest Tables

```sql
SELECT
  relname AS table_name,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;
```

#### Check Autovacuum Activity

```sql
SELECT
  relname,
  last_vacuum,
  last_autovacuum,
  n_dead_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;
```

---

### Recovery Procedure

<span style="white-space: pre-wrap;">Follow steps </span>**carefully**. Some operations are I/O intensive.

---

#### 1. Run Manual VACUUM (Non-blocking)

```sql
VACUUM (VERBOSE, ANALYZE);
```

Recommended for moderate bloat and active systems.

---

#### 2. Vacuum Specific Tables

```sql
VACUUM (VERBOSE, ANALYZE) table_name;
```

Use when bloat is localized.

---

#### 3. Reclaim Disk Space (Blocking)

```sql
VACUUM FULL table_name;
```

⚠️ Locks the table for the duration of the operation  
⚠️ Use during maintenance windows only

---

#### 4. Reindex Bloated Indexes

```sql
REINDEX TABLE table_name;
```

Or concurrently, when supported:

```sql
REINDEX INDEX CONCURRENTLY index_name;
```

---

---

### Preventive Notes

- Ensure autovacuum is enabled and properly tuned
- <span style="white-space: pre-wrap;">Monitor </span>`<span class="editor-theme-code">n_dead_tup</span>`<span style="white-space: pre-wrap;"> growth over time</span>
- Avoid long-running transactions
- Schedule periodic maintenance for write-heavy tables

---

### Operational Notes

- <span style="white-space: pre-wrap;">Disk space reclaimed by </span>`<span class="editor-theme-code">VACUUM</span>`<span style="white-space: pre-wrap;"> is reusable by PostgreSQL, not always returned to the OS</span>
- `<span class="editor-theme-code">VACUUM FULL</span>`<span style="white-space: pre-wrap;"> physically rewrites tables and should be used sparingly</span>

---