Skip to content
HeliosDB HeliosDB Lite

Feature 03: Query Routing Hints

Feature 03: Query Routing Hints

Priority: High | Complexity: Low | Phase: 1 (Foundation)

Overview

Problem Statement

Applications have varying consistency requirements:

  • Critical transactions need the primary
  • Dashboard queries can use any replica
  • Analytics queries should target async replicas
  • AI embeddings should route to vector-optimized nodes

Without routing hints, the proxy makes suboptimal decisions:

  • All writes go to primary (correct but inflexible)
  • Reads randomly distributed (ignores query requirements)
  • No way to express business intent

Solution

Implement SQL comment-based routing hints that give applications fine-grained control:

-- Route to primary for critical reads
/*helios:route=primary*/
SELECT balance FROM accounts WHERE id = $1 FOR UPDATE;


-- Route to any standby for read scaling
/*helios:route=standby*/
SELECT * FROM products WHERE category = 'electronics';


-- Route to sync standby for strong consistency reads
/*helios:route=sync*/
SELECT * FROM orders WHERE user_id = $1;


-- Route to specific node for debugging
/*helios:node=standby-sync-1*/
SELECT * FROM debug_logs;

Architecture

                    ┌─────────────────────────────────────────────────┐
                    │              QUERY ROUTER                        │
                    │                                                  │
  Query ───────────►│  ┌──────────────────────────────────────────┐   │
                    │  │ 1. Parse Routing Hints                   │   │
                    │  │    /*helios:route=X*/                    │   │
                    │  │    /*helios:node=Y*/                     │   │
                    │  │    /*helios:consistency=Z*/              │   │
                    │  └──────────────────────────────────────────┘   │
                    │                    │                             │
                    │                    ▼                             │
                    │  ┌──────────────────────────────────────────┐   │
                    │  │ 2. Apply Routing Rules                   │   │
                    │  │    - Hint takes precedence               │   │
                    │  │    - Fall back to query analysis         │   │
                    │  │    - Default to configured policy        │   │
                    │  └──────────────────────────────────────────┘   │
                    │                    │                             │
                    │                    ▼                             │
                    │  ┌──────────────────────────────────────────┐   │
                    │  │ 3. Select Target Node                    │   │
                    │  │    - Filter by hint requirements         │   │
                    │  │    - Apply load balancing                │   │
                    │  │    - Check node health                   │   │
                    │  └──────────────────────────────────────────┘   │
                    └─────────────────────────────────────────────────┘
                                           │
                    ┌──────────────────────┼──────────────────────┐
                    │                      │                      │
                    ▼                      ▼                      ▼
             ┌──────────┐          ┌──────────────┐       ┌──────────────┐
             │ Primary  │          │ Standby-Sync │       │ Standby-Async│
             │ (writes) │          │ (strong)     │       │ (eventual)   │
             └──────────┘          └──────────────┘       └──────────────┘

Hint Parser

pub struct HintParser;


impl HintParser {
    /// Parse routing hints from SQL query
    pub fn parse(&self, query: &str) -> Vec<RoutingHint> {
        let mut hints = Vec::new();


        // Match /*helios:key=value*/ pattern
        let re = Regex::new(r"/\*helios:(\w+)=([^*]+)\*/").unwrap();


        for cap in re.captures_iter(query) {
            let key = &cap[1];
            let value = cap[2].trim();


            if let Some(hint) = self.parse_hint(key, value) {
                hints.push(hint);
            }
        }


        hints
    }


    fn parse_hint(&self, key: &str, value: &str) -> Option<RoutingHint> {
        match key {
            "route" => Some(RoutingHint::Route(value.parse().ok()?)),
            "node" => Some(RoutingHint::Node(value.to_string())),
            "consistency" => Some(RoutingHint::Consistency(value.parse().ok()?)),
            "pool" => Some(RoutingHint::Pool(value.parse().ok()?)),
            "cache" => Some(RoutingHint::Cache(value.parse().ok()?)),
            "timeout" => Some(RoutingHint::Timeout(value.parse().ok()?)),
            "priority" => Some(RoutingHint::Priority(value.parse().ok()?)),
            _ => None,
        }
    }
}


#[derive(Debug, Clone)]
pub enum RoutingHint {
    /// Target node type
    Route(RouteTarget),


    /// Specific node by name
    Node(String),


    /// Consistency level
    Consistency(ConsistencyLevel),


    /// Connection pool mode
    Pool(PoolingMode),


    /// Cache behavior
    Cache(CacheBehavior),


    /// Query timeout override
    Timeout(Duration),


    /// Query priority
    Priority(QueryPriority),
}


#[derive(Debug, Clone, Copy)]
pub enum RouteTarget {
    Primary,
    Standby,
    Sync,
    SemiSync,
    Async,
    Any,
    Local,
}


#[derive(Debug, Clone, Copy)]
pub enum ConsistencyLevel {
    Strong,      // Must read from primary or sync standby
    Bounded,     // Allow semi-sync with bounded lag
    Eventual,    // Allow any replica
}

Query Router

pub struct QueryRouter {
    /// Available nodes
    nodes: Arc<NodeRegistry>,


    /// Hint parser
    parser: HintParser,


    /// Load balancer
    balancer: Arc<LoadBalancer>,


    /// Default routing policy
    default_policy: RoutingPolicy,
}


impl QueryRouter {
    pub fn route(&self, query: &str) -> RoutingDecision {
        // 1. Parse hints from query
        let hints = self.parser.parse(query);


        // 2. Determine route target
        let target = if let Some(hint) = hints.iter().find_map(|h| match h {
            RoutingHint::Route(t) => Some(*t),
            _ => None,
        }) {
            hint
        } else if self.is_write_query(query) {
            RouteTarget::Primary
        } else {
            self.default_policy.read_target
        };


        // 3. Filter eligible nodes
        let eligible = self.filter_nodes(target, &hints);


        // 4. Select best node
        let node = self.balancer.select(&eligible);


        RoutingDecision {
            target_node: node,
            hints,
            reason: format!("Routed to {:?}", target),
        }
    }


    fn filter_nodes(&self, target: RouteTarget, hints: &[RoutingHint]) -> Vec<Node> {
        let mut nodes: Vec<Node> = self.nodes.all()
            .filter(|n| self.matches_target(n, target))
            .filter(|n| n.is_healthy())
            .collect();


        // Apply consistency filter
        if let Some(consistency) = hints.iter().find_map(|h| match h {
            RoutingHint::Consistency(c) => Some(*c),
            _ => None,
        }) {
            nodes.retain(|n| self.meets_consistency(n, consistency));
        }


        // Apply specific node filter
        if let Some(node_name) = hints.iter().find_map(|h| match h {
            RoutingHint::Node(name) => Some(name.clone()),
            _ => None,
        }) {
            nodes.retain(|n| n.name == node_name);
        }


        nodes
    }
}

API Specification

Routing Hints Reference

HintValuesDescription
routeprimary, standby, sync, semisync, async, any, localTarget node type
nodeNode nameSpecific node
consistencystrong, bounded, eventualConsistency requirement
poolsession, transaction, statementPooling mode
cacheskip, refresh, ttl=NCache behavior
timeoutDuration (e.g., 5s, 100ms)Query timeout
prioritylow, normal, high, criticalScheduling priority
lagDuration (e.g., 100ms, 1s)Maximum acceptable lag
retrytrue, false, NRetry behavior

SQL Examples

-- Route critical financial reads to primary
/*helios:route=primary,consistency=strong*/
SELECT balance FROM accounts WHERE id = $1 FOR UPDATE;


-- Route analytics to async replica with high lag tolerance
/*helios:route=async,lag=10s,priority=low*/
SELECT DATE(created_at), COUNT(*)
FROM orders
GROUP BY DATE(created_at);


-- Route to local region for latency
/*helios:route=local*/
SELECT * FROM users WHERE region = 'us-west';


-- Combine multiple hints
/*helios:route=standby,cache=skip,timeout=30s,priority=high*/
SELECT * FROM real_time_metrics;


-- Route vector search to vector-optimized node
/*helios:route=vector,consistency=eventual*/
SELECT * FROM embeddings
ORDER BY vector <-> $1
LIMIT 10;

Configuration (heliosproxy.toml)

[routing]
# Default routing policy
default_read_target = "standby"
default_write_target = "primary"
default_consistency = "eventual"


# Enable/disable specific hints
[routing.hints]
enabled = true
allow_node_hints = true      # Allow routing to specific nodes
allow_primary_reads = true   # Allow /*helios:route=primary*/ for reads
require_auth = false         # Require authentication for hints


# Route mapping
[routing.aliases]
vector = ["standby-vector-1", "standby-vector-2"]
analytics = ["standby-async-1", "standby-async-2"]
local = "auto"  # Auto-detect closest node


# Consistency level definitions
[routing.consistency.strong]
allowed_nodes = ["primary", "standby-sync"]
max_lag_ms = 0


[routing.consistency.bounded]
allowed_nodes = ["primary", "standby-sync", "standby-semisync"]
max_lag_ms = 1000


[routing.consistency.eventual]
allowed_nodes = ["*"]
max_lag_ms = -1  # No limit

Admin API

GET /routing/hints
# List supported hints
{
  "hints": [
    {"name": "route", "values": ["primary", "standby", "sync", "async", "any"]},
    {"name": "consistency", "values": ["strong", "bounded", "eventual"]},
    ...
  ]
}


GET /routing/decisions?limit=100
# Recent routing decisions (for debugging)
{
  "decisions": [
    {
      "query_hash": "abc123",
      "hints_parsed": [{"route": "standby"}],
      "target_node": "standby-sync-1",
      "reason": "Hint: route=standby",
      "timestamp": "2026-01-25T10:30:00Z"
    }
  ]
}

AI/Agent Innovations

1. Agent Tool Routing

Route different tool calls to appropriate nodes:

-- Knowledge retrieval (eventual consistency OK)
/*helios:route=async,tool=knowledge_search*/
SELECT content FROM documents
WHERE embedding <-> $1 < 0.5;


-- User data lookup (strong consistency needed)
/*helios:route=sync,tool=user_lookup*/
SELECT * FROM users WHERE id = $1;


-- Conversation history (session affinity)
/*helios:route=local,tool=conversation*/
SELECT * FROM conversation_turns
WHERE session_id = $1
ORDER BY turn_number;

2. RAG Pipeline Hints

Optimize retrieval-augmented generation:

-- Embedding search (high parallelism, eventual OK)
/*helios:route=async,priority=high,pool=statement*/
SELECT chunk_id, content, embedding <-> $1 AS distance
FROM document_chunks
ORDER BY embedding <-> $1
LIMIT 20;


-- Reranking query (needs freshness)
/*helios:route=sync,cache=skip*/
SELECT * FROM document_metadata
WHERE chunk_id = ANY($1);

3. Agentic Workflow Hints

Express workflow step requirements:

-- Planning step (read-only, can use replica)
/*helios:route=standby,workflow=planning*/
SELECT * FROM task_templates WHERE type = $1;


-- Execution step (may write, needs primary)
/*helios:route=primary,workflow=execution*/
INSERT INTO task_executions (task_id, status) VALUES ($1, 'running');


-- Verification step (needs consistent read)
/*helios:route=sync,workflow=verification*/
SELECT status FROM task_executions WHERE id = $1;

4. LLM Context Hints

Optimize context window operations:

-- Context retrieval (prefetch multiple)
/*helios:route=async,prefetch=true*/
SELECT context FROM conversation_contexts
WHERE session_id = $1;


-- Context update (critical, sync)
/*helios:route=primary,priority=critical*/
UPDATE conversation_contexts
SET context = $2
WHERE session_id = $1;

HeliosDB-Lite Integration

1. Sync Mode Routing

Route based on HeliosDB-Lite sync modes:

impl QueryRouter {
    fn route_by_sync_mode(&self, hint: RouteTarget) -> Vec<Node> {
        match hint {
            RouteTarget::Sync => {
                // Route to fully-synchronous standbys
                self.nodes.filter(|n| n.sync_mode == SyncMode::Sync)
            }
            RouteTarget::SemiSync => {
                // Route to semi-sync standbys
                self.nodes.filter(|n| n.sync_mode == SyncMode::SemiSync)
            }
            RouteTarget::Async => {
                // Route to async standbys
                self.nodes.filter(|n| n.sync_mode == SyncMode::Async)
            }
            _ => self.nodes.all()
        }
    }
}

2. Branch-Aware Routing

Route queries to branch-specific nodes:

-- Route to node hosting specific branch
/*helios:branch=analytics,route=local*/
SELECT * FROM monthly_reports;


-- Route experimental queries to experimental branch
/*helios:branch=experiment_v2*/
SELECT * FROM new_feature_data;

3. TWR Routing

Transparent Write Routing through standbys:

-- Explicitly use TWR through sync standby
/*helios:route=sync,twr=true*/
INSERT INTO logs (message) VALUES ($1);

4. Vector-Optimized Routing

Route to nodes with vector indexes:

-- Route to vector-optimized nodes
/*helios:route=vector*/
SELECT * FROM embeddings
ORDER BY vector <-> $1
LIMIT 10;
impl QueryRouter {
    fn route_vector_query(&self, query: &str) -> Vec<Node> {
        // Detect vector operations
        if self.contains_vector_op(query) {
            // Prefer nodes with HNSW indexes in memory
            return self.nodes.filter(|n| n.has_vector_capability())
                .sorted_by(|a, b| a.vector_index_loaded.cmp(&b.vector_index_loaded));
        }


        self.nodes.all()
    }
}

Implementation Notes

File Locations

src/proxy/
├── routing/
│   ├── mod.rs            # Public API
│   ├── hint_parser.rs    # SQL comment hint parsing
│   ├── query_router.rs   # Routing logic
│   ├── node_filter.rs    # Node selection criteria
│   └── metrics.rs        # Routing metrics

Key Considerations

  1. Hint Validation: Validate hints before execution. Reject invalid combinations (e.g., route=async,consistency=strong).

  2. Fallback Behavior: If no nodes match hints, log warning and fall back to default routing.

  3. Security: Consider whether to allow all users to use routing hints. Some hints (like route=primary) could be abused.

  4. Prepared Statements: Strip hints from query before sending to backend (they’re comments but could confuse caching).

  5. Observability: Log routing decisions for debugging. Expose metrics on hint usage.

Hint Stripping

impl HintParser {
    /// Remove hints from query for backend execution
    pub fn strip_hints(&self, query: &str) -> String {
        let re = Regex::new(r"/\*helios:[^*]+\*/\s*").unwrap();
        re.replace_all(query, "").to_string()
    }
}


// Example:
// Input:  "/*helios:route=primary*/ SELECT * FROM users"
// Output: "SELECT * FROM users"

Performance Targets

MetricTargetMeasurement
Hint parsing<10μsp99
Routing decision<50μsp99
No added latency<100μs totaloverhead