> ## Documentation Index
> Fetch the complete documentation index at: https://docs.encoreos.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Database Index Cleanup Analysis

> Generated: 2026-01-11 Analysis Period: Since 2025-11-15 (2+ months of index statistics)

**Generated:** 2026-01-11
**Analysis Period:** Since 2025-11-15 (2+ months of index statistics)

## Executive Summary

This document details the cleanup of **1,307 unused database indexes** to improve write performance and reduce storage overhead by approximately **11.3 MB**.

## Indexes Removed by Module

| Module    | Description            | Indexes Removed | Size Freed    |
| --------- | ---------------------- | --------------- | ------------- |
| FA        | Finance & Accounting   | 122             | 976 KB        |
| FM        | Facilities Management  | 87              | 696 KB        |
| FW        | Forms & Workflow       | 162             | 1.4 MB        |
| GR        | Governance & Risk      | 168             | 1.3 MB        |
| HR        | Human Resources        | 226             | 1.8 MB        |
| IT        | Information Technology | 111             | 904 KB        |
| LO        | Leadership OS          | 69              | 632 KB        |
| PF        | Platform Foundation    | 150             | 1.5 MB        |
| RH        | Recovery Housing       | 212             | 1.7 MB        |
| **Total** |                        | **1,307**       | **\~11.3 MB** |

## Indexes NOT Removed

The following indexes were explicitly preserved:

* **PRIMARY KEY indexes** (413) - Required for table identity
* **UNIQUE constraint indexes** (185) - Required for constraint enforcement
* **Foreign Key indexes** - Required for referential integrity

## Safety Measures

The `pf_index_cleanup_audit` table that stored rollback `CREATE INDEX` definitions was dropped on 2026-03-19 after 2+ months with no reported regressions from the index cleanup.

## Categories of Removed Indexes

### 1. Organization ID Indexes (\~250)

Many tables had explicit `organization_id` indexes that are redundant with RLS policies which already filter by organization.

### 2. Status/Type Indexes (\~160)

Low-cardinality indexes on status and type columns that provide minimal query benefit but add write overhead.

### 3. GIN Indexes (\~22)

Full-text search and array indexes with highest write overhead, not being used by any queries.

### 4. Composite Indexes (\~400)

Multi-column indexes that were never used, often superseded by more specific indexes.

### 5. Partial Indexes (\~200)

Conditional indexes (WHERE clause) that were never matched by queries.

## Expected Benefits

1. **Faster Writes:** Each INSERT/UPDATE/DELETE no longer needs to maintain 1,307 index entries
2. **Reduced Storage:** \~11.3 MB immediately freed, grows with data
3. **Faster Vacuum:** Less index bloat to manage during maintenance
4. **Lower WAL Volume:** Fewer index entries to write to transaction log

## Monitoring

After cleanup, monitor:

1. Query performance for any regressions
2. `pg_stat_statements` for slow query increases
3. Database Health Dashboard for any issues

## Decision Criteria

An index was removed if ALL of the following were true:

1. `idx_scan = 0` (never used in 2+ months)
2. NOT a PRIMARY KEY index
3. NOT a UNIQUE constraint index
4. NOT backing any FOREIGN KEY constraint
5. Statistics have been accumulating since 2025-11-15

## References

* Migration: `20260111_cleanup_unused_indexes.sql`
* Database Health Dashboard: `/settings/system-health/database`