Automatic Index Recommendation

Problem Being Solved

Core Problem Statement

Manual index analysis requires understanding query execution plans, table statistics, selectivity calculations, and index type trade-offs—skills that take years to master and are scarce in startup and DevOps environments. Teams either over-index (creating redundant indexes that waste storage and slow writes) or under-index (missing critical indexes causing production slowdowns), with no systematic way to measure the business value (ROI) of each index investment.

HeliosDB Lite Solution

Architecture Overview

┌─────────────────────────────────────────────────────────────────┐
│                    HeliosDB-Lite Application                     │
├─────────────────────────────────────────────────────────────────┤
│  Query Workload Tracker  │  Index Recommender  │  Redundancy Detector  │
│  - Access pattern logs   │  - ROI calculation  │  - Overlapping indexes │
│  - Frequency counters    │  - Index type rules │  - Duplicate detection │
├─────────────────────────────────────────────────────────────────┤
│                     Query Planner & Executor                     │
│  - EXPLAIN plan generation                                       │
│  - Table statistics (row count, cardinality)                     │
├─────────────────────────────────────────────────────────────────┤
│                  RocksDB Storage Layer + Indexes                 │
│  - BTree, Hash, GIN, BRIN index structures                       │
└─────────────────────────────────────────────────────────────────┘

Concrete Examples with Code, Config & Architecture

Example 1: Missing Index Detection for E-Commerce Product Search - Embedded Configuration

Architecture:

Web Application (React Frontend)
    ↓
HeliosDB-Lite Rust API (Embedded in-process)
    ↓
RocksDB Storage (Local SSD)
    ↓
No external database server required

Configuration (heliosdb.toml):

# HeliosDB-Lite configuration for index recommendation
[database]
path = "/var/lib/ecommerce/products.db"
memory_limit_mb = 512
enable_wal = true

[query_analysis]
enabled = true
workload_tracking = true
recommendation_threshold = 30.0  # Minimum ROI score

[monitoring]
metrics_enabled = true
verbose_logging = false

Example 3: JSONB GIN Index Recommendation - Docker Microservice Deployment

Docker Deployment (Dockerfile):

FROM rust:latest as builder

WORKDIR /app

# Copy source
COPY . .

# Build HeliosDB-Lite microservice
RUN cargo build --release

# Runtime stage
FROM debian:bookworm-slim

RUN apt-get update && apt-get install -y \
    ca-certificates \
    curl \
    && rm -rf /var/lib/apt/lists/*

COPY --from=builder /app/target/release/iot-device-service /usr/local/bin/

# Create data volume mount point
RUN mkdir -p /data

# Expose HTTP API
EXPOSE 8080

# Health check
HEALTHCHECK --interval=30s --timeout=3s --start-period=40s --retries=3 \
    CMD curl -f http://localhost:8080/health || exit 1

# Set data directory as volume
VOLUME ["/data"]

ENTRYPOINT ["iot-device-service"]
CMD ["--config", "/etc/heliosdb/config.toml", "--data-dir", "/data"]

Microservice Configuration (config.toml):

[server]
host = "0.0.0.0"
port = 8080

[database]
path = "/data/iot_devices.db"
memory_limit_mb = 1024
enable_wal = true
page_size = 4096

[query_analysis]
enabled = true
workload_tracking = true
recommendation_threshold = 40.0

[index_recommender]
auto_apply_threshold = 80.0  # Auto-apply indexes with ROI > 80/100
report_interval_hours = 24    # Daily recommendation reports

Kubernetes Deployment (k8s-deployment.yaml):

apiVersion: apps/v1
kind: Deployment
metadata:
  name: iot-device-service
  namespace: default
spec:
  replicas: 3
  selector:
    matchLabels:
      app: iot-device-service
  template:
    metadata:
      labels:
        app: iot-device-service
    spec:
      containers:
      - name: service
        image: iot-device-service:latest
        imagePullPolicy: Always

        ports:
        - containerPort: 8080
          name: http
          protocol: TCP

        env:
        - name: RUST_LOG
          value: "info"
        - name: HELIOSDB_DATA_DIR
          value: "/data"

        volumeMounts:
        - name: data
          mountPath: /data
        - name: config
          mountPath: /etc/heliosdb
          readOnly: true

        resources:
          requests:
            memory: "512Mi"
            cpu: "200m"
          limits:
            memory: "1Gi"
            cpu: "1000m"

        livenessProbe:
          httpGet:
            path: /health
            port: 8080
          initialDelaySeconds: 30
          periodSeconds: 10

      volumes:
      - name: config
        configMap:
          name: heliosdb-config
      - name: data
        persistentVolumeClaim:
          claimName: iot-device-data

---
apiVersion: v1
kind: Service
metadata:
  name: iot-device-service
spec:
  type: LoadBalancer
  selector:
    app: iot-device-service
  ports:
  - port: 80
    targetPort: 8080
    name: http

Sample Index Recommendation for JSONB:

$ curl http://iot-device-service/admin/index-recommendations

═══════════════════════════════════════════════════════════════
              INDEX RECOMMENDATION REPORT
═══════════════════════════════════════════════════════════════

Total Recommendations: 1
Workload Queries Analyzed: 5000

───────────────────────────────────────────────────────────────
───────────────────────────────────────────────────────────────

Table: devices
Columns: metadata
Index Type: GIN


COST:
  • Storage: 52,000,000 bytes (49.6 MB)

REASON:
  JSONB containment queries using @> operator. GIN index provides
  efficient inverted index for nested JSON property lookups.

CREATE INDEX STATEMENT:
  CREATE INDEX idx_devices_metadata ON devices USING GIN (metadata);

Auto-Apply High-ROI Index:

$ curl -X POST http://iot-device-service/admin/apply-recommendations

{
  "applied_indexes": [
    {
      "index": "CREATE INDEX idx_devices_metadata ON devices USING GIN (metadata);",
      "roi_score": 92.7,
    }
  ],
  "count": 1
}

Example 5: Edge Device BRIN Index for Time-Series - IoT Fleet Management

Edge Device Configuration (edge_config.toml):

[database]
# Ultra-low memory for edge devices
path = "/var/lib/iot/sensor_data.db"
memory_limit_mb = 128        # Constrained edge hardware
page_size = 512              # Optimized for flash storage
enable_wal = true
cache_mb = 32

[query_analysis]
enabled = true
workload_tracking = true
recommendation_threshold = 50.0

[index_recommender]
prefer_brin_for_sequential = true
auto_recommend_interval_hours = 168  # Weekly recommendations

[sync]
enable_remote_sync = true
sync_interval_secs = 3600  # Hourly cloud sync
batch_size = 10000

Adoption Strategy

Phase 1: Proof of Concept (Weeks 1-4)

Target: Validate index recommendations on single production database

Tactics:

• Enable workload tracking on most-used microservice
• Generate recommendations, review with senior engineer
• Measure before/after query latency

Phase 2: Pilot Deployment (Weeks 5-12)

Target: Expand to 10-20 production services/databases

Tactics:

• Deploy recommendation API to all microservices
• Weekly automated reports to #database-performance Slack channel
• Track storage savings from redundant index cleanup
• Measure P99 latency improvement across fleet

Phase 3: Full Rollout

Target: Organization-wide automated index optimization

Tactics:

• Enable workload tracking for all production databases
• Weekly index recommendation reports for every team
• Automated redundant index cleanup (with approval workflow)
• Dashboard showing fleet-wide storage/performance gains
• Quarterly review of index ROI vs business metrics

Conclusion

Automatic index recommendation transforms database performance optimization from a specialized, time-intensive DBA responsibility into a fully automated, data-driven process accessible to any development team.

The embedded nature of HeliosDB Lite's recommendation engine uniquely positions it to serve edge computing, IoT fleets, and offline-first applications where cloud-based database advisors are architecturally incompatible.

As embedded databases proliferate across edge computing, mobile applications, and serverless architectures—environments where traditional client-server databases are prohibitively expensive or architecturally infeasible—automated index recommendation becomes a fundamental requirement, not a luxury feature.

Call to Action: Until automatic index recommendation is available, find missing indexes with EXPLAIN and EXPLAIN ANALYZE. In a world where database performance directly impacts customer experience, revenue conversion, and infrastructure costs, automated optimization is no longer optional—it's the baseline expectation for any serious data-driven application.