# Autovacuum Tuning

PostgreSQL's autovacuum process reclaims storage and updates statistics used by the query planner. EntryTarget includes per-table autovacuum settings in its schema, optimized for each table's write pattern.

## Per-Table Settings

| Table                | Write Pattern                  | Custom Settings                                                               |
| -------------------- | ------------------------------ | ----------------------------------------------------------------------------- |
| `journal`            | Fast growth, few UPDATE/DELETE | `autovacuum_analyze_scale_factor = 0.05` — keeps planner stats fresh          |
| `ledger`             | Fast growth, few UPDATE/DELETE | `autovacuum_analyze_scale_factor = 0.05` — keeps planner stats fresh          |
| `idempotency_record` | High churn (INSERT + DELETE)   | `vacuum_scale_factor = 0.02`, `analyze_scale_factor = 0.02`, `cost_delay = 2` |
| `heartbeat`          | 1 row, UPDATE 1x/day           | Default — no tuning needed                                                    |
| All others           | Moderate growth                | Default — RDS settings are sufficient                                         |

### Why Custom Settings?

* **`journal` and `ledger`** grow rapidly but rarely have UPDATEs or DELETEs. The lower `analyze_scale_factor` ensures the query planner has fresh statistics even at high row counts.
* **`idempotency_record`** has high churn — rows are inserted and then deleted by the cleanup job. Without aggressive vacuum settings, dead tuples accumulate and cause table bloat.

## RDS Parameter Group Settings

These are recommended settings at the RDS instance level:

| Parameter                      | Recommended Value | Notes                                                       |
| ------------------------------ | ----------------- | ----------------------------------------------------------- |
| `autovacuum`                   | `on`              | Default — never turn this off                               |
| `autovacuum_max_workers`       | `3`               | Default, sufficient for most workloads                      |
| `autovacuum_naptime`           | `30`              | Default is 60s — lower means faster response to dead tuples |
| `autovacuum_vacuum_cost_delay` | `2`               | Default on RDS, already good                                |
| `maintenance_work_mem`         | `256MB`           | Default is often too low — increase for faster vacuum       |

## Monitoring Bloat

Check for dead tuple accumulation:

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

### What to Look For

* **`n_dead_tup` growing on `idempotency_record`** — the per-table settings in the schema should prevent this. If it grows, check that autovacuum is running (see `last_autovacuum`).
* **`n_dead_tup` on `journal` or `ledger`** — should be very low since these tables rarely have DELETEs (except during archive operations).
* **`last_autovacuum` is NULL or very old** — autovacuum may be blocked or not running.

## After Archive Operations

After running `POST /ledger/archive`, the ledger table will have many deleted rows (dead tuples). The autovacuum settings should handle this automatically, but you can verify:

```sql
-- Check dead tuples after archive
SELECT n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'ledger';
```

If dead tuples accumulate significantly, you can trigger a manual vacuum:

```sql
VACUUM (VERBOSE) ledger;
```

## Best Practices

* **Never disable autovacuum** — it is essential for PostgreSQL performance
* **Monitor `n_dead_tup`** regularly via the query above
* **Increase `maintenance_work_mem`** on RDS — the default is often too low for efficient vacuuming
* **Let per-table settings work** — the schema includes optimized settings; avoid overriding them at the instance level unless you have specific needs


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://entrytarget.gitbook.io/docs/maintenance/autovacuum.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.