Performance Considerations

Critical Query Patterns

Two queries run on every conversation turn and must be fast:

Index Coverage

The plugin's migrations create the following composite indexes to ensure efficient execution of the common query patterns:

-- The most-hit index: load episode history for context assembly
CREATE INDEX IX_Octopus_EpisodeMessages_Episode
    ON Octopus_EpisodeMessages (EpisodeId, CreatedAt ASC)
    INCLUDE (Role, Content, TokenCount, ToolCallId, ToolName);

-- Agent resolution by tenant + name
CREATE UNIQUE INDEX UQ_Octopus_Agents_Tenant_Name
    ON Octopus_Agents (TenantId, Name)
    WHERE IsActive = 1;

-- Episode lookup by user (for conversation history API)
CREATE INDEX IX_Octopus_Episodes_Tenant_User
    ON Octopus_Episodes (TenantId, UserId, LastActiveAt DESC)
    INCLUDE (AgentId, SessionId, IsActive);

-- Procedure retrieval for intent matching
CREATE INDEX IX_Octopus_Procedures_TenantActive
    ON Octopus_Procedures (TenantId, IsActive, Priority DESC);

Table Growth and Archival

The Octopus_EpisodeMessages table is the highest-growth table — it grows by one row per message. For a deployment with 1,000 daily active users sending 20 messages each, this is approximately 20,000 rows per day. Without archival, this table will degrade query performance over time.

Configure the built-in archival job to move old episodes to a separate archive table:

// appsettings.json — archival configuration
{
  "SqlServerPlugin": {
    "Archival": {
      "Enabled": true,
      "RetentionDays": 90,
      "ArchivalSchedule": "0 2 * * *",  // Run at 2 AM daily (CRON format)
      "BatchSize": 1000
    }
  }
}

The archival job moves episodes older than RetentionDays from Octopus_Episodes to Octopus_Episodes_Archive in batches, keeping the primary table small and fast.

Connection Pool Sizing

Read Replica for Analytics

Conversation history reporting and analytics queries are read-heavy and can be directed to a read replica without affecting the primary database. Configure a secondary connection string credential for read-only workloads:

{
  "SqlServerPlugin": {
    "ConnectionStringCredentialId": 1,           // Primary (read-write)
    "ReadOnlyConnectionStringCredentialId": 2,   // Read replica (for reporting)
    "UseReadReplicaForReporting": true
  }
}

Monitoring Key Metrics