Workload-Aware Intelligent Routing

Problem Being Solved

Core Problem Statement

Traditional databases provide only coarse-grained connection pooling and basic query timeouts, lacking the intelligence to classify query intent, route by priority, and dynamically balance resources across competing workloads.

Who Suffers Most

1. SaaS Platform Engineering Teams: Operating multi-tenant B2B applications where tenant admins run expensive ad-hoc reports while end users expect instant page loads.

Concrete Examples with Code, Config & Architecture

Example 1: SaaS Multi-Tenant Platform with Mixed Workloads

Scenario: B2B SaaS platform with 10K tenants.

HeliosDB Lite Configuration (heliosdb-saas.toml):

[database]
name = "saas_platform"
port = 5432
max_connections = 200  # Total connections (managed by routing)

[storage]
data_dir = "/var/lib/heliosdb/data"

# Workload Routing Configuration
[proxy.workload_routing]
enabled = true

# Workload Classification
[proxy.workload_routing.classifier]
# Use ML-based classifier (trained on query patterns)
mode = "ml_based"  # or "rule_based" for simpler classification
# Update classifier model based on execution feedback
adaptive_learning = true
# Minimum confidence threshold to route (else use default)
min_confidence = 0.75

# OLTP Fast Lane (user-facing queries)
[proxy.workload_routing.fast_lane]
enabled = true
# Reserve 4 CPU cores exclusively for fast lane
dedicated_cpu_cores = 4
# Maximum concurrent queries
max_concurrency = 50
target_p95_latency_ms = 100
# Preempt other workloads if SLA at risk
allow_preemption = true
# Route criteria (any query matching these goes to fast lane)
route_criteria = [
    "transaction_context = true",  # Queries in BEGIN/COMMIT blocks
    "estimated_duration_ms < 500",
    "table_row_count < 100000",
    "join_count <= 2"
]

# OLAP Parallel Lanes (analytical queries)
[proxy.workload_routing.parallel_lanes]
enabled = true
# Use remaining CPU cores (8 total - 4 reserved = 4 for parallel)
cpu_cores = 4
# Maximum concurrent heavy queries (each uses multiple threads)
max_concurrency = 4
# Each query can use multiple worker threads
workers_per_query = 4
# SLA guideline: complete in 1-60s
target_completion_time_s = 60
# Yield CPU to fast lane if contention detected
yield_to_fast_lane = true
# Route criteria
route_criteria = [
    "select_only = true",
    "join_count > 2 OR aggregation_count > 0",
    "estimated_duration_ms > 500",
    "estimated_rows_scanned > 100000"
]

# Background Queue (batch jobs, ETL)
[proxy.workload_routing.background_queue]
enabled = true
# Best-effort CPU allocation (idle cycles only)
cpu_cores = "best_effort"
# Maximum concurrent batch jobs
max_concurrency = 2
# Low I/O priority (don't disrupt other workloads)
io_priority = "low"
# Pause background jobs if system overloaded
pause_on_high_load = true
high_load_threshold = 0.85  # CPU utilization > 85%
# Route criteria
route_criteria = [
    "write_heavy = true",
    "bulk_operation = true",
    "estimated_duration_ms > 60000",  # > 1 minute
    "client_tag = 'background_job'"  # Application-provided hint
]

# Admission Control
[proxy.workload_routing.admission_control]
enabled = true
# Reject low-priority queries if system overloaded
reject_on_overload = true
# Queue queries instead of rejecting (with timeout)
enable_queueing = true
max_queue_depth = 100
queue_timeout_ms = 5000  # Reject after 5s in queue

# SLA Policies
[proxy.workload_routing.sla_policies]
# Per-tenant SLA overrides (e.g., enterprise tier gets higher priority)
[[proxy.workload_routing.sla_policies.tenant_overrides]]
tenant_tier = "enterprise"
priority_boost = 2
guaranteed_fast_lane_slots = 10  # Reserve 10 fast lane slots

# Observability
[proxy.workload_routing.observability]
log_routing_decisions = true
log_level = "info"
track_sla_violations = true
export_metrics = true

[metrics]
enabled = true
export_prometheus = true
prometheus_port = 9090

Example 2: E-Commerce Platform with Transaction + Recommendation Queries

Workload Routing Configuration:

[proxy.workload_routing]
enabled = true

# Ultra-fast lane for checkout transactions
[proxy.workload_routing.fast_lane]
dedicated_cpu_cores = 6
max_concurrency = 100
target_p95_latency_ms = 50  # Aggressive SLA for conversions
allow_preemption = true

# Route all write transactions to fast lane
route_criteria = [
    "query_type = 'INSERT' OR query_type = 'UPDATE'",
    "transaction_context = true"
]

# ML recommendation queries to parallel lanes
[proxy.workload_routing.parallel_lanes]
cpu_cores = 8
max_concurrency = 8
workers_per_query = 4

route_criteria = [
    "table_names CONTAINS 'user_embeddings' OR table_names CONTAINS 'product_scores'",
    "aggregation_count > 2",
    "estimated_rows_scanned > 1000000"
]

Application Code (Node.js - unchanged):

const { Pool } = require('pg');

// Single connection pool to HeliosDB-Lite
const pool = new Pool({
  host: 'localhost',
  port: 5432,
  database: 'ecommerce',
  user: 'app_user',
  password: 'password',
  max: 100  // HeliosProxy manages routing
});

// User-facing: Checkout transaction (OLTP, Fast Lane)
async function createOrder(userId, cartItems) {
  const client = await pool.connect();

  try {
    await client.query('BEGIN');

    // Insert order (write transaction → Fast Lane routing)
    const orderResult = await client.query(
      `INSERT INTO orders (user_id, total_amount, status, created_at)
       VALUES ($1, $2, 'pending', NOW())
       RETURNING order_id`,
      [userId, calculateTotal(cartItems)]
    );

    const orderId = orderResult.rows[0].order_id;

    // Insert order items
    for (const item of cartItems) {
      await client.query(
        `INSERT INTO order_items (order_id, product_id, quantity, price)
         VALUES ($1, $2, $3, $4)`,
        [orderId, item.productId, item.quantity, item.price]
      );
    }

    // Update inventory
    for (const item of cartItems) {
      await client.query(
        `UPDATE products
         SET inventory_count = inventory_count - $1
         WHERE product_id = $2`,
        [item.quantity, item.productId]
      );
    }

    await client.query('COMMIT');

    return orderId;
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

// Background: ML-based product recommendations (OLAP, Parallel Lanes)
async function getRecommendations(userId, limit = 20) {
  const client = await pool.connect();

  try {
    const start = Date.now();

    // Complex ML scoring query (multi-threaded execution)
    // → Automatically routed to Parallel Lanes
    const result = await client.query(
      `WITH user_vector AS (
        SELECT embedding
        FROM user_embeddings
        WHERE user_id = $1
      ),
      product_scores AS (
        SELECT
          p.product_id,
          p.name,
          p.price,
          p.category,
          -- Cosine similarity computation (expensive)
          (
            SELECT 1 - (
              (u.embedding <-> pe.embedding) /
              (SQRT(SUM(u.embedding * u.embedding)) * SQRT(SUM(pe.embedding * pe.embedding)))
            )
            FROM user_vector u, product_embeddings pe
            WHERE pe.product_id = p.product_id
          ) as similarity_score,
          -- Popularity boost
          p.sales_30d / (SELECT MAX(sales_30d) FROM products) as popularity_score
        FROM products p
        WHERE p.active = true
          AND p.inventory_count > 0
      )
      SELECT
        product_id,
        name,
        price,
        category,
        (similarity_score * 0.7 + popularity_score * 0.3) as final_score
      FROM product_scores
      ORDER BY final_score DESC
      LIMIT $2`,
      [userId, limit]
    );

    const elapsed = Date.now() - start;
    console.log(`Recommendations computed in ${elapsed}ms (Parallel Lanes)`);

    return result.rows;
  } finally {
    client.release();
  }
}

// Usage
(async () => {
  // Fast checkout (OLTP)
  const orderId = await createOrder(12345, [
    { productId: 1, quantity: 2, price: 29.99 },
    { productId: 5, quantity: 1, price: 49.99 }
  ]);

  // ML recommendations (OLAP, doesn't block checkout)
  const recommendations = await getRecommendations(12345);
  console.log(`Got ${recommendations.length} recommendations`);
})();

Example 3: Docker Deployment with Resource Isolation

Docker Compose with CPU/Memory Limits:

version: '3.8'

services:
  heliosdb:
    image: registry.example.com/heliosdb-lite:<version>  # licensed image from HeliosDB sales, or built from the Lite Dockerfile
    ports:
      - "5432:5432"
      - "9090:9090"
    volumes:
      - heliosdb-data:/var/lib/heliosdb/data
      - ./heliosdb-workload-routing.toml:/etc/heliosdb/heliosdb.toml:ro

    # CPU and memory allocation for workload routing
    deploy:
      resources:
        limits:
          cpus: '12'  # Total CPU cores
          memory: 32G
        reservations:
          cpus: '8'
          memory: 24G

    # Enable cgroup controls for workload isolation
    privileged: true  # Required for CPU pinning

    environment:
      HELIOSDB_ENABLE_CGROUPS: "true"
      HELIOSDB_LOG_LEVEL: "info"

    restart: unless-stopped

volumes:
  heliosdb-data:

Kubernetes Deployment with Quality of Service (QoS) Classes:

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: heliosdb-workload-routing
spec:
  serviceName: heliosdb
  replicas: 1
  template:
    metadata:
      labels:
        app: heliosdb
    spec:
      # Guaranteed QoS class (needed for CPU pinning)
      containers:
      - name: heliosdb
        image: registry.example.com/heliosdb-lite:<version>
        resources:
          requests:
            cpu: "12"
            memory: "32Gi"
          limits:
            cpu: "12"
            memory: "32Gi"

        env:
        - name: HELIOSDB_ENABLE_CGROUPS
          value: "true"

        # CPU Manager policy for dedicated cores
        # Requires kubelet configured with --cpu-manager-policy=static

        volumeMounts:
        - name: config
          mountPath: /etc/heliosdb
        - name: data
          mountPath: /var/lib/heliosdb/data

      volumes:
      - name: config
        configMap:
          name: heliosdb-config

  volumeClaimTemplates:
  - metadata:
      name: data
    spec:
      accessModes: ["ReadWriteOnce"]
      resources:
        requests:
          storage: 500Gi

Example 4: Go API Gateway with Priority-Based Routing

Scenario: API gateway serving millions of requests/day with varying priorities (free tier vs. enterprise tier customers).

Go Application Code:

package main

import (
    "context"
    "database/sql"
    "fmt"
    "log"
    "time"

    _ "github.com/lib/pq"
)

type CustomerTier string

const (
    FreeTier       CustomerTier = "free"
    ProTier        CustomerTier = "pro"
    EnterpriseTier CustomerTier = "enterprise"
)

func main() {
    db, err := sql.Open("postgres", "host=localhost port=5432 user=app_user password=password dbname=api_gateway sslmode=disable")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Simulate API requests from different tiers
    validateAPIKey(db, "free_user_key_123", FreeTier)
    validateAPIKey(db, "enterprise_key_456", EnterpriseTier)
    runAnalytics(db, "admin_user")
}

func validateAPIKey(db *sql.DB, apiKey string, tier CustomerTier) {
    ctx := context.Background()

    // Set priority hint based on customer tier
    // HeliosProxy uses this to boost priority in routing decisions
    var priorityHint string
    switch tier {
    case EnterpriseTier:
        priorityHint = "/* priority=high */"
    case ProTier:
        priorityHint = "/* priority=medium */"
    default:
        priorityHint = "/* priority=low */"
    }

    start := time.Now()

    // Simple query with priority hint
    // Enterprise queries get Fast Lane priority even under load
    query := fmt.Sprintf(`%s
        SELECT user_id, tier, rate_limit, requests_remaining
        FROM api_keys
        WHERE key = $1 AND active = true`, priorityHint)

    var userID int64
    var keyTier string
    var rateLimit int
    var requestsRemaining int

    err := db.QueryRowContext(ctx, query, apiKey).Scan(
        &userID, &keyTier, &rateLimit, &requestsRemaining,
    )

    elapsed := time.Since(start)

    if err != nil {
        log.Printf("API key validation failed: %v", err)
        return
    }

    log.Printf("Tier: %s, User: %d, Latency: %v", tier, userID, elapsed)
}

func runAnalytics(db *sql.DB, adminUser string) {
    ctx := context.Background()

    // Mark as background analytics query
    // → Routed to Parallel Lanes or Background Queue
    query := `/* priority=low, workload=analytics */
        SELECT
            DATE_TRUNC('hour', timestamp) as hour,
            COUNT(*) as request_count,
            AVG(latency_ms) as avg_latency,
            COUNT(*) FILTER (WHERE status_code >= 500) as error_count
        FROM api_requests
        WHERE timestamp > NOW() - INTERVAL '24 hours'
        GROUP BY DATE_TRUNC('hour', timestamp)
        ORDER BY hour DESC`

    start := time.Now()
    rows, err := db.QueryContext(ctx, query)
    if err != nil {
        log.Printf("Analytics query failed: %v", err)
        return
    }
    defer rows.Close()

    var results []map[string]interface{}
    for rows.Next() {
        var hour time.Time
        var requestCount int
        var avgLatency float64
        var errorCount int

        if err := rows.Scan(&hour, &requestCount, &avgLatency, &errorCount); err != nil {
            log.Printf("Scan error: %v", err)
            continue
        }

        results = append(results, map[string]interface{}{
            "hour":          hour,
            "request_count": requestCount,
            "avg_latency":   avgLatency,
            "error_count":   errorCount,
        })
    }

    elapsed := time.Since(start)
    log.Printf("Analytics query completed in %v (Parallel Lanes)", elapsed)
}

Example 5: Observability Dashboard for Workload Routing

Grafana Dashboard JSON (excerpt):

{
  "dashboard": {
    "title": "HeliosDB Workload Routing Metrics",
    "panels": [
      {
        "title": "Query Latency by Workload Type",
        "targets": [
          {
            "expr": "histogram_quantile(0.95, rate(helios_query_duration_seconds_bucket{workload_type=\"fast_lane\"}[5m]))",
            "legendFormat": "Fast Lane P95"
          },
          {
            "expr": "histogram_quantile(0.95, rate(helios_query_duration_seconds_bucket{workload_type=\"parallel_lanes\"}[5m]))",
            "legendFormat": "Parallel Lanes P95"
          },
          {
            "expr": "histogram_quantile(0.95, rate(helios_query_duration_seconds_bucket{workload_type=\"background\"}[5m]))",
            "legendFormat": "Background P95"
          }
        ]
      },
      {
        "title": "Workload Distribution",
        "targets": [
          {
            "expr": "rate(helios_queries_routed_total{workload_type=\"fast_lane\"}[5m])",
            "legendFormat": "Fast Lane QPS"
          },
          {
            "expr": "rate(helios_queries_routed_total{workload_type=\"parallel_lanes\"}[5m])",
            "legendFormat": "Parallel Lanes QPS"
          },
          {
            "expr": "rate(helios_queries_routed_total{workload_type=\"background\"}[5m])",
            "legendFormat": "Background QPS"
          }
        ]
      },
      {
        "title": "SLA Violations",
        "targets": [
          {
            "expr": "rate(helios_sla_violations_total[5m])",
            "legendFormat": "Violations per second"
          }
        ]
      },
      {
        "title": "Queue Depths",
        "targets": [
          {
            "expr": "helios_queue_depth{workload_type=\"fast_lane\"}",
            "legendFormat": "Fast Lane Queue"
          },
          {
            "expr": "helios_queue_depth{workload_type=\"parallel_lanes\"}",
            "legendFormat": "Parallel Lanes Queue"
          }
        ]
      }
    ]
  }
}

Adoption Strategy

Phase 1: Workload Analysis (Weeks 1-2)

1. Profile Current Workload Mix: Use database slow query logs, APM tools, or HeliosDB observability (dry-run mode) to analyze query patterns. Identify interference patterns (e.g., "dashboard latency spikes during report generation").

2. Measure Baseline Performance: Capture current P50/P95/P99 latencies for each workload type under typical load. Identify tail latency spikes and their triggers. Calculate SLA violation rate. Target: Establish baseline metrics for improvement measurement.

Phase 2: Pilot Deployment (Weeks 3-6)

1. Deploy in Staging with Auto-Classification: Enable HeliosProxy workload routing in staging environment with ML-based classifier. Monitor classification accuracy (use log analysis to verify correct routing). Tune routing criteria if needed.

2. Load Test Mixed Workloads: Run production-like load tests with simultaneous OLTP + OLAP + background queries. Measure P95 latency improvements and SLA violation reduction. Verify fast lane isolation (OLTP latency should be stable regardless of OLAP load).

3. Capacity Planning: Determine optimal CPU core allocation for fast lane vs. parallel lanes based on workload mix. Calculate infrastructure savings from consolidating separate OLTP/OLAP instances.

Phase 3: Production Rollout (Weeks 7-12)

1. Continuous Optimization: Use adaptive learning to refine classification and routing decisions based on execution feedback. Adjust CPU core allocations seasonally (e.g., more fast lane cores during holiday shopping). Target: Self-tuning system maintains SLAs through traffic changes.

Conclusion

HeliosDB Lite's HeliosProxy delivers this vision through ML-based automatic classification, dedicated execution queues with resource isolation, SLA-guaranteed fast lanes, and adaptive feedback loops that continuously improve routing decisions.

For SaaS platforms, e-commerce sites, and data analytics applications, this eliminates the painful trade-off between fast user-facing queries and comprehensive analytical capabilities.

HeliosDB Lite's integrated approach—combining PostgreSQL compatibility with intelligent middleware—enables drop-in deployment that immediately solves mixed workload problems without application code changes or operational complexity.

As modern applications increasingly blend transactional and analytical workloads—with real-time dashboards, embedded ML scoring, and on-demand reporting—the ability to handle diverse query types efficiently within a single database instance becomes a strategic advantage. HeliosDB Lite's workload routing positions it as the embedded database that bridges this gap, delivering specialized performance for every query type while maintaining the simplicity and cost-efficiency of a single-instance deployment.