# Index Maintenance

UUID v7 generates sequential IDs, which means B-tree indexes grow in order with minimal fragmentation. However, all indexes degrade logarithmically with volume and should be maintained periodically.

## Monitoring Index Health

Check index sizes and usage:

```sql
SELECT relname, indexrelname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size,
       idx_scan AS scans
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;
```

Look for:

* **Large indexes with few scans** — may indicate unused indexes
* **Rapidly growing indexes** — normal for high-throughput tables like `journal` and `ledger`

## Rebuilding Indexes

Rebuild indexes without locking the table using `REINDEX CONCURRENTLY`:

```sql
-- Most frequently used indexes
REINDEX INDEX CONCURRENTLY ledger_ix_account_version;
REINDEX INDEX CONCURRENTLY ledger_ix_account_created;
REINDEX INDEX CONCURRENTLY journal_ix_transaction;
REINDEX INDEX CONCURRENTLY account_pkey;
```

### Frequency

* **Every 6–12 months** for most workloads
* **Every 3–6 months** for very high-volume deployments (>1M transactions/day)
* After a large archive operation that deleted many ledger rows

### Zero-Downtime Maintenance

Use RDS Blue/Green Deployments for zero-downtime index maintenance on production databases.

## Key Indexes

| Table               | Index                               | Purpose                                 |
| ------------------- | ----------------------------------- | --------------------------------------- |
| account             | `account_pkey`                      | Primary key lookup                      |
| account             | `account_ix_asset`                  | Balance summary by asset                |
| account             | `account_ix_category`               | Account lookup by category              |
| journal             | `journal_pkey`                      | Primary key lookup                      |
| journal             | `journal_ix_transaction`            | Transaction query (all journal entries) |
| ledger              | `ledger_pkey`                       | Primary key lookup                      |
| ledger              | `ledger_ix_account_version`         | Ledger pagination by version            |
| ledger              | `ledger_ix_account_created`         | Ledger filtering by date                |
| ledger\_archive     | `ledger_archive_ix_account_version` | Archive pagination by version           |
| ledger\_archive     | `ledger_archive_ix_account_created` | Archive filtering by date               |
| idempotency\_record | `idx_idempotency_ttl`               | Cleanup job (by created\_at)            |

## Best Practices

* Always use `CONCURRENTLY` — it avoids locking the table during the rebuild
* Schedule maintenance during off-peak hours
* Monitor index size trends over time to identify when maintenance is needed
* After archiving a large number of ledger records, rebuild `ledger_ix_account_version` and `ledger_ix_account_created`


---

# 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/index-maintenance.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.