# Autovacuum Bloat Management

> Autovacuum & Table Bloat Management

- **Type:** Skill
- **Install:** `agentstack add skill-carloscape-octorato-autovacuum-bloat-management`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [CarlosCaPe](https://agentstack.voostack.com/s/carloscape)
- **Installs:** 0
- **Category:** [AI & ML](https://agentstack.voostack.com/c/ai-and-ml)
- **Latest version:** 0.1.0
- **License:** MIT
- **Upstream author:** [CarlosCaPe](https://github.com/CarlosCaPe)
- **Source:** https://github.com/CarlosCaPe/octorato/tree/master/skills/autovacuum-bloat-management
- **Website:** https://www.dataqbs.com/octorato

## Install

```sh
agentstack add skill-carloscape-octorato-autovacuum-bloat-management
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

# Autovacuum & Table Bloat Management

> Source: [PostgreSQL Best Practices](../DOCUMENTS/PostgreSQL_BestPractices_Azure.md)
> -- "Maintenance, vacuum, and bloat" section

## What

Monitoring and tuning PostgreSQL's autovacuum system to prevent table and
index bloat. Includes per-table autovacuum parameter tuning for high-churn
tables.

## Why

PostgreSQL uses MVCC (Multi-Version Concurrency Control). Every UPDATE creates
a new row version; DELETE marks the old version as dead. These dead tuples
accumulate as **bloat** until VACUUM reclaims the space.

If autovacuum can't keep up:
- Table size grows beyond the live data size
- Index entries point to dead tuples (index bloat)
- Sequential scans become slower (scanning dead rows)
- `HOT` updates fail more often (no free space on the page)
- Storage costs increase on Azure

## How

### Monitor dead tuples and vacuum activity
```sql
SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1)
        AS dead_pct,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;
```

### Check table bloat estimate
```sql
SELECT
    schemaname || '.' || relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,
    pg_size_pretty(pg_indexes_size(relid)) AS index_size,
    n_live_tup,
    n_dead_tup
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(relid) DESC;
```

### Tune autovacuum for hot tables
```sql
-- High-churn table: vacuum more aggressively
ALTER TABLE public."AuditLog" SET (
    autovacuum_vacuum_scale_factor = 0.05,   -- vacuum at 5% dead (default 20%)
    autovacuum_analyze_scale_factor = 0.02   -- analyze at 2% changes (default 10%)
);

-- Verify settings
SELECT relname, reloptions
FROM pg_class
WHERE relname = 'AuditLog';
```

### Default autovacuum thresholds (PG 16 docs: runtime-config-autovacuum.html)

| Parameter | Default | Meaning |
|-----------|---------|---------||
| `autovacuum_vacuum_threshold` | 50 | Minimum dead tuples before vacuum |
| `autovacuum_vacuum_scale_factor` | 0.20 | Fraction of table that must be dead |
| `autovacuum_vacuum_insert_threshold` | 1000 | Inserts before insert-triggered vacuum (PG 13+) |
| `autovacuum_vacuum_insert_scale_factor` | 0.20 | Fraction of table size for insert-triggered vacuum |
| `autovacuum_analyze_threshold` | 50 | Minimum changes before analyze |
| `autovacuum_analyze_scale_factor` | 0.10 | Fraction of table that must change |

**Trigger formula**: Vacuum runs when
`dead_tuples > threshold + scale_factor * n_live_tup`

For a 1M-row table with defaults: vacuum at 50 + 0.20 * 1,000,000 = **200,050 dead tuples**.
With tuned 0.05: vacuum at 50 + 0.05 * 1,000,000 = **50,050 dead tuples**.

### Manual vacuum (when needed)
```sql
-- Standard vacuum (non-blocking, reclaims dead tuples)
VACUUM (VERBOSE) public."AuditLog";

-- Vacuum + analyze (update planner statistics too)
VACUUM (VERBOSE, ANALYZE) public."AuditLog";

-- VACUUM FULL (rewrites table, reclaims disk space, but LOCKS table)
-- Use only for extreme bloat; prefer REINDEX CONCURRENTLY for indexes
VACUUM FULL public."AuditLog";
```

### Reindex for index bloat
```sql
-- Non-blocking index rebuild
REINDEX INDEX CONCURRENTLY public."ix_auditlog_createddate";

-- Check for invalid indexes (failed concurrent operations)
SELECT indexrelid::regclass, indisvalid
FROM pg_index
WHERE NOT indisvalid;
```

## Decision Matrix

| Scenario | Action |
|----------|--------|
| Dead tuple %  30% | Immediate: manual VACUUM; then tune autovacuum |
| Table size >> expected for row count | Possible bloat; check dead_pct |
| After bulk DELETE/UPDATE | Run `VACUUM ANALYZE` manually |
| After index creation | Run `ANALYZE` to update stats |

## When to Use

- During database audits (check bloat across all tables)
- After implementing retention policies (Skill #21) -- large DELETEs cause bloat
- After bulk data operations (imports, migrations, purges)
- When queries suddenly slow down (possible stale stats)

## Where We Used It

- **/**: Fillfactor tuning (related to HOT update optimization)
- **Audit TDDs**: All three audits include autovacuum monitoring recommendations
- ****: After retention DELETE, vacuum needed for ShiftAuditLog

## Related Skills

- **Skill #23** (Fillfactor Tuning) -- fillfactor + autovacuum work together
- **Skill #21** (Data Retention) -- large DELETEs need vacuum follow-up
- **Skill #29** (EXPLAIN ANALYZE) -- verify stats are fresh before trusting plans

## References

- [PostgreSQL Best Practices](../DOCUMENTS/PostgreSQL_BestPractices_Azure.md)
  -- "Maintenance, vacuum, and bloat" section and Backlog #9

## Gotchas

- **Never disable autovacuum** -- the table will bloat and eventually
  approach transaction ID wraparound (catastrophic)
- `VACUUM FULL` acquires `AccessExclusiveLock` -- blocks all access;
  use only as a last resort during maintenance windows
- `VACUUM` reclaims space for reuse but does NOT return it to the OS --
  the table file stays the same size. Only `VACUUM FULL` shrinks the file.
- After large bulk DELETEs, run `VACUUM ANALYZE` to both reclaim space
  and update planner statistics
- On Azure Flexible Server, autovacuum settings can be adjusted via
  Server Parameters in the Azure Portal (server-wide) or per-table via ALTER

---

*Category: Tooling | Origin: Audit TDDs, PostgreSQL Best Practices*

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [CarlosCaPe](https://github.com/CarlosCaPe)
- **Source:** [CarlosCaPe/octorato](https://github.com/CarlosCaPe/octorato)
- **License:** MIT
- **Homepage:** https://www.dataqbs.com/octorato

Install and usage instructions live in the source repository linked above.

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** no
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** no
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/skill-carloscape-octorato-autovacuum-bloat-management
- Seller: https://agentstack.voostack.com/s/carloscape
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.