Phase 3: Fully automatic index optimization cycle

[jensens]

Part of #85 (Self-optimizing index system)

Depends on #87 (Phase 2: Semi-automatic)

Goal

Fully automatic cycle: analyze slow queries, create indexes, evaluate, rollback — without human intervention.

Design

Automatic cycle

Background worker (daemon thread or cron-like via startup handler):

1. Check pgcatalog_slow_queries every N minutes (configurable)
2. For each pattern with count >= min_occurrence (default 10):
   • Skip if already has a managed index (verified or blocked)
   • Generate suggestion via Phase 1 smart suggestions
   • Check index budget (max N auto-indexes, default 10)
   • Create index via CREATE INDEX CONCURRENTLY
3. Evaluate pending indexes after evaluation period
4. Rollback failures, verify successes

Leader election

Multiple pods must not create the same index simultaneously:

• PG advisory lock before auto-index creation
• Only one pod wins the lock and creates
• Others skip that cycle

Index budget

Total auto-created indexes is bounded:

• Default max 10 (configurable via PGCATALOG_AUTO_INDEX_BUDGET)
• When budget is full, only create if new suggestion has higher estimated impact than lowest-impact existing auto-index
• Consider: swap lowest-impact verified index for higher-impact new one

Re-evaluation triggers

• After GenericSetup catalog.xml import (new/removed indexes)
• After pack (table statistics change)
• Manual "Re-evaluate all" button in ZMI
• Configurable periodic re-evaluation (monthly?)

Audit trail

All actions logged to pgcatalog_managed_indexes with timestamps:

• Created, evaluated, verified, rolled_back, blocked, unblocked
• Before/after performance metrics
• Human-readable notes

Migration of hardcoded composites

• Import existing hardcoded composites as "locked" managed indexes
• Status=verified, blocked=true (auto-system doesn't touch them)
• Over time, admin can unlock individual ones for re-evaluation

Deliverables

1. Background worker with configurable interval
2. Leader election via PG advisory lock
3. Index budget with impact-based eviction
4. Re-evaluation triggers (GenericSetup, pack, manual)
5. Full audit trail
6. Migration of hardcoded composites to managed indexes
7. Documentation