Skip to main content

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.

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

ModuleDescriptionIndexes RemovedSize Freed
FAFinance & Accounting122976 KB
FMFacilities Management87696 KB
FWForms & Workflow1621.4 MB
GRGovernance & Risk1681.3 MB
HRHuman Resources2261.8 MB
ITInformation Technology111904 KB
LOLeadership OS69632 KB
PFPlatform Foundation1501.5 MB
RHRecovery Housing2121.7 MB
Total1,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