Query Rewriting and Optimization

Problem Being Solved

Core Problem Statement

Database query optimizers can choose efficient execution plans for well-written queries but cannot fundamentally restructure poorly written queries (e.g., converting correlated subqueries to joins, eliminating redundant predicates, recognizing materialized view opportunities), forcing organizations to employ expensive database specialists for manual query tuning or suffer degraded application performance that drives user churn.

Who Suffers Most

1. Business Intelligence & Analytics Teams: Building dashboards, reports, and ad-hoc queries using tools like Tableau, Looker, Metabase that generate inefficient SQL with excessive JOINs, unfiltered subqueries, and missing indexes.

2. SaaS Application Developers Using ORMs: Writing Ruby on Rails, Django, or Laravel applications where ActiveRecord/Eloquent ORM generates N+1 queries, eager loading misconfigurations, and correlated subqueries. They lack SQL expertise to optimize queries, rely on caching to mask performance problems, and face production incidents when cache fails or new features bypass cache layer.

3. Data Engineering Teams: Running ETL pipelines and batch jobs that process millions of rows with complex transformations (window functions, recursive CTEs, multi-table joins).

---

Concrete Examples with Code, Config & Architecture

Example 1: Embedded Configuration for Analytics Platform

Scenario: SaaS analytics platform where users build custom dashboards with complex filters, aggregations, and joins. BI tool (Metabase) generates inefficient SQL.

HeliosDB Lite Configuration (heliosdb-analytics.toml):

[database]
name = "analytics_platform"
port = 5432

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

# Query Rewriting Configuration
[proxy.query_rewriting]
enabled = true

# Enable all optimization rule categories
[proxy.query_rewriting.rules]
subquery_flattening = true
predicate_pushdown = true
join_reordering = true
projection_pruning = true
constant_folding = true
redundant_predicate_elimination = true
outer_to_inner_join_conversion = true
materialized_view_substitution = true
n_plus_one_batching = true

# Cost-based optimization settings
[proxy.query_rewriting.cost_model]
# Use database statistics for cost estimation
use_table_statistics = true
# Fall back to heuristics if stats unavailable
fallback_to_heuristics = true

# Execution plan cache
[proxy.query_rewriting.plan_cache]
enabled = true
max_entries = 10000  # Cache 10K most common query patterns
ttl = "1h"  # Invalidate cached plans after 1 hour
invalidate_on_ddl = true  # Clear cache on schema changes

# Learning and feedback
[proxy.query_rewriting.learning]
enabled = true
collect_execution_stats = true
regression_threshold = 3
ab_test_new_rules = true

# Observability
[proxy.query_rewriting.observability]
log_rewrites = true
log_level = "info"
# Log slow queries that couldn't be optimized
log_optimization_failures = true
slow_query_threshold = "1s"

# Materialized view intelligence
[proxy.query_rewriting.materialized_views]
auto_detect_candidates = true
suggest_new_mvs = true
min_query_frequency = 100  # Suggest MV if query runs 100+ times
refresh_strategy = "incremental"  # or "full"

[metrics]
enabled = true
export_prometheus = true
prometheus_port = 9090
# Metrics for query rewriting
track_rewrite_stats = true

Original Inefficient Query (generated by Metabase):

-- BAD: Correlated subquery executed once per row in events table
-- If events has 10M rows, subquery runs 10M times!
SELECT
    e.event_id,
    e.event_name,
    e.timestamp,
    (
        SELECT u.username
        FROM users u
        WHERE u.user_id = e.user_id
    ) as username,
    (
        SELECT COUNT(*)
        FROM event_properties ep
        WHERE ep.event_id = e.event_id
    ) as property_count
FROM events e
WHERE e.timestamp > '2025-01-01'
    AND e.event_type = 'page_view'
ORDER BY e.timestamp DESC
LIMIT 1000;

HeliosDB Lite Rewritten Query (automatic):

-- GOOD: Correlated subqueries flattened to LEFT JOINs
-- Executed once with proper join algorithms
SELECT
    e.event_id,
    e.event_name,
    e.timestamp,
    u.username,
    COALESCE(ep_counts.property_count, 0) as property_count
FROM events e
LEFT JOIN users u ON u.user_id = e.user_id
LEFT JOIN (
    SELECT event_id, COUNT(*) as property_count
    FROM event_properties
    GROUP BY event_id
) ep_counts ON ep_counts.event_id = e.event_id
WHERE e.timestamp > '2025-01-01'
    AND e.event_type = 'page_view'
ORDER BY e.timestamp DESC
LIMIT 1000;

Observability - Query Rewrite Log:

{
  "timestamp": "2025-12-15T10:23:45Z",
  "query_fingerprint": "a3f2b9c1d4e5f6a7b8c9d0e1f2a3b4c5",
  "original_query": "SELECT e.event_id, ... (correlated subqueries)",
  "rewritten_query": "SELECT e.event_id, ... (LEFT JOINs)",
  "rules_applied": [
    "subquery_flattening:correlated_scalar_to_join",
    "subquery_flattening:correlated_aggregate_to_join"
  ],
  "execution_time_original": null,
  "execution_time_rewritten": "1.18s",
  "status": "success"
}

---

Example 2: ORM N+1 Query Batching (Ruby on Rails)

Scenario: Rails e-commerce app loading 100 orders with associated users. Classic N+1 problem: 1 query for orders + 100 queries for users.

Rails Model Code (unchanged):

# app/models/order.rb
class Order < ApplicationRecord
  belongs_to :user
end

# app/models/user.rb
class User < ApplicationRecord
  has_many :orders
end

# app/controllers/orders_controller.rb
class OrdersController < ApplicationController
  def index
    # WARNING: This triggers N+1 queries without eager loading!
    # But HeliosDB-Lite detects and batches them automatically
    @orders = Order.limit(100)

    # Accessing user in view triggers 1 query per order
    # Rails generates: SELECT * FROM users WHERE id = 123
    #                  SELECT * FROM users WHERE id = 124
    #                  ... (100 times)
  end
end

# app/views/orders/index.html.erb
<% @orders.each do |order| %>
  <tr>
    <td><%= order.id %></td>
    <td><%= order.user.name %></td>  <!-- N+1 trigger -->
    <td><%= order.total %></td>
  </tr>
<% end %>

Generated SQL Without HeliosDB Lite:

-- First query: fetch orders
SELECT * FROM orders LIMIT 100;

-- Then 100 individual user queries (N+1 problem!)
SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE id = 2;
SELECT * FROM users WHERE id = 3;
-- ... (97 more queries)
SELECT * FROM users WHERE id = 100;

-- Total: 101 queries

HeliosDB Lite Automatic Batching:

-- Automatically batches into single query:

SELECT * FROM orders LIMIT 100;

-- Batched user query (1 instead of 100!)
SELECT * FROM users WHERE id IN (1, 2, 3, ..., 100);

HeliosDB Lite Configuration for N+1 Detection:

[proxy.query_rewriting.n_plus_one]
enabled = true
coalescing_window_ms = 10
# Detect repeated query patterns with different parameters
pattern_matching = true
# Maximum queries to batch (prevent excessively large IN clauses)
max_batch_size = 1000
# Log detected N+1 patterns for developer awareness
log_n_plus_one_detection = true

---

Example 3: Docker Deployment with Query Rewriting Observability

Dockerfile:

FROM debian:bookworm-slim

# HeliosDB-Lite binary, built from the licensed source distribution
# (https://www.heliosdb.com/docs/lite/getting-started/installation/)
COPY heliosdb-lite /usr/local/bin/heliosdb-lite
RUN apt-get update && apt-get install -y ca-certificates \
    && apt-get clean && rm -rf /var/lib/apt/lists/*

COPY heliosdb-analytics.toml /etc/heliosdb/heliosdb.toml

EXPOSE 5432 9090

RUN useradd -r heliosdb && \
    mkdir -p /var/lib/heliosdb/data /var/run/heliosdb && \
    chown -R heliosdb:heliosdb /var/lib/heliosdb /var/run/heliosdb

USER heliosdb

CMD ["heliosdb-lite", "start", "--config", "/etc/heliosdb/heliosdb.toml"]

Docker Compose with Grafana for Query Rewriting Metrics:

version: '3.8'

services:
  heliosdb:
    build: .
    ports:
      - "5432:5432"
      - "9090:9090"
    volumes:
      - heliosdb-data:/var/lib/heliosdb/data
      - ./heliosdb-analytics.toml:/etc/heliosdb/heliosdb.toml:ro
    environment:
      HELIOSDB_LOG_LEVEL: "info"
    restart: unless-stopped

  prometheus:
    image: prom/prometheus:latest
    ports:
      - "9091:9090"
    volumes:
      - ./prometheus.yml:/etc/prometheus/prometheus.yml:ro
      - prometheus-data:/prometheus
    restart: unless-stopped

  grafana:
    image: grafana/grafana:latest
    ports:
      - "3000:3000"
    volumes:
      - grafana-data:/var/lib/grafana
      - ./grafana-dashboards:/etc/grafana/provisioning/dashboards:ro
    environment:
      GF_SECURITY_ADMIN_PASSWORD: "admin"
      # Pre-configured dashboard for query rewriting metrics
      GF_DASHBOARDS_DEFAULT_HOME_DASHBOARD_PATH: /etc/grafana/provisioning/dashboards/query-rewriting.json
    restart: unless-stopped

volumes:
  heliosdb-data:
  prometheus-data:
  grafana-data:

Prometheus Configuration (prometheus.yml):

global:
  scrape_interval: 15s

scrape_configs:
  - job_name: 'heliosdb-lite'
    static_configs:
      - targets: ['heliosdb:9090']
    metrics_path: '/metrics'

Grafana Dashboard Queries:

// Panel 1: Query Rewrite Success Rate
rate(helios_query_rewrites_total[5m]) / rate(helios_queries_total[5m])

// Panel 2: Average Speedup by Rule
avg by (rule) (helios_query_rewrite_speedup_ratio)

// Panel 3: Plan Cache Hit Rate Over Time
helios_plan_cache_hit_rate

// Panel 4: Top 10 Most Optimized Query Patterns
topk(10, helios_query_rewrite_speedup_ratio)

---

Example 4: Go Microservice with Materialized View Substitution

Scenario: Go microservice serving API for user activity reports. Repeated complex joins benefit from materialized views.

Go Application Code:

package main

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

    _ "github.com/lib/pq"
)

type UserActivityReport struct {
    UserID        int64
    Username      string
    EventCount30d int
    LastActive    time.Time
    TopEvents     []string
}

func main() {
    // Connect to HeliosDB-Lite
    connStr := "host=localhost port=5432 user=app_user password=password dbname=analytics sslmode=disable"
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Test connection
    if err := db.Ping(); err != nil {
        log.Fatal(err)
    }

    // Run report query (HeliosDB-Lite will optimize)
    report, err := getUserActivityReport(db, 12345)
    if err != nil {
        log.Fatal(err)
    }

    fmt.Printf("User Activity Report: %+v\n", report)
}

func getUserActivityReport(db *sql.DB, userID int64) (*UserActivityReport, error) {
    ctx := context.Background()

    // Complex query with joins and aggregations
    // HeliosDB-Lite will detect this matches a materialized view pattern
    query := `
        SELECT
            u.user_id,
            u.username,
            COUNT(DISTINCT e.event_id) as event_count_30d,
            MAX(e.timestamp) as last_active,
            array_agg(DISTINCT e.event_name ORDER BY e.event_name) FILTER (WHERE e.event_name IS NOT NULL) as top_events
        FROM users u
        LEFT JOIN events e ON u.user_id = e.user_id
            AND e.timestamp > NOW() - INTERVAL '30 days'
        WHERE u.user_id = $1
        GROUP BY u.user_id, u.username
    `

    var report UserActivityReport
    var topEvents sql.NullString

    start := time.Now()

    err := db.QueryRowContext(ctx, query, userID).Scan(
        &report.UserID,
        &report.Username,
        &report.EventCount30d,
        &report.LastActive,
        &topEvents,
    )

    elapsed := time.Since(start)
    fmt.Printf("Query executed in %v\n", elapsed)

    if err != nil {
        return nil, err
    }

    // Parse array (simplified)
    if topEvents.Valid {
        // Parse PostgreSQL array format
        report.TopEvents = []string{topEvents.String}
    }

    return &report, nil
}

-- HeliosDB-Lite suggestion logged in observability:
-- "Query pattern 'user_activity_30d' detected 523 times in past 24h"

CREATE MATERIALIZED VIEW user_activity_30d AS
SELECT
    u.user_id,
    u.username,
    COUNT(DISTINCT e.event_id) as event_count_30d,
    MAX(e.timestamp) as last_active,
    array_agg(DISTINCT e.event_name ORDER BY e.event_name) FILTER (WHERE e.event_name IS NOT NULL) as top_events
FROM users u
LEFT JOIN events e ON u.user_id = e.user_id
    AND e.timestamp > NOW() - INTERVAL '30 days'
GROUP BY u.user_id, u.username;

-- Create index for fast lookups
CREATE INDEX ON user_activity_30d(user_id);

-- Schedule refresh every hour
-- (HeliosDB-Lite handles this automatically based on configuration)

Query Rewriting with MV Substitution:

-- Original query (from Go application):
SELECT u.user_id, u.username, COUNT(DISTINCT e.event_id) ...
FROM users u LEFT JOIN events e ...
WHERE u.user_id = 12345 ...

-- HeliosDB-Lite rewrites to use materialized view:
SELECT user_id, username, event_count_30d, last_active, top_events
FROM user_activity_30d
WHERE user_id = 12345;

---

Example 5: Kubernetes Deployment with A/B Testing of Rewrites

Scenario: Large-scale deployment where query rewriting must be tested before production rollout.

Kubernetes Deployment:

apiVersion: v1
kind: ConfigMap
metadata:
  name: heliosdb-config
  namespace: analytics
data:
  heliosdb.toml: |
    [database]
    name = "analytics_prod"
    port = 5432

    [proxy.query_rewriting]
    enabled = true

    [proxy.query_rewriting.learning]
    ab_test_new_rules = true
    ab_test_percentage = 50  # 50% traffic gets rewrites

    # Log all rewrite decisions for analysis
    [proxy.query_rewriting.observability]
    log_rewrites = true
    log_ab_test_results = true
    export_ab_metrics = true

---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: heliosdb-analytics
  namespace: analytics
spec:
  replicas: 3
  selector:
    matchLabels:
      app: heliosdb
  template:
    metadata:
      labels:
        app: heliosdb
    spec:
      containers:
      - name: heliosdb
        image: registry.example.com/heliosdb-lite:<version>  # licensed image from HeliosDB sales, or built from the Lite Dockerfile
        ports:
        - containerPort: 5432
        - containerPort: 9090
        volumeMounts:
        - name: config
          mountPath: /etc/heliosdb
        - name: data
          mountPath: /var/lib/heliosdb/data
        resources:
          requests:
            cpu: "2"
            memory: "8Gi"
          limits:
            cpu: "4"
            memory: "16Gi"
      volumes:
      - name: config
        configMap:
          name: heliosdb-config
      - name: data
        persistentVolumeClaim:
          claimName: heliosdb-data-pvc

---
apiVersion: batch/v1
kind: CronJob
metadata:
  name: heliosdb-ab-test-analyzer
  namespace: analytics
spec:
  schedule: "0 */6 * * *"  # Every 6 hours
  jobTemplate:
    spec:
      template:
        spec:
          containers:
          - name: analyzer
            image: registry.example.com/ab-test-analyzer:latest
            env:
            - name: HELIOSDB_METRICS_URL
              value: "http://heliosdb-analytics:9090/metrics"
            - name: SLACK_WEBHOOK_URL
              valueFrom:
                secretKeyRef:
                  name: slack-webhook
                  key: url
            command:
            - /app/analyze-ab-test.sh
          restartPolicy: OnFailure

A/B Test Analysis Script (analyze-ab-test.sh):

#!/bin/bash

# Fetch A/B test metrics from HeliosDB-Lite
curl -s http://heliosdb-analytics:9090/metrics | grep helios_ab_test > /tmp/ab_metrics.txt

# Parse metrics
REWRITE_P95=$(grep 'helios_query_latency_seconds.*variant="rewritten".*quantile="0.95"' /tmp/ab_metrics.txt | awk '{print $2}')
ORIGINAL_P95=$(grep 'helios_query_latency_seconds.*variant="original".*quantile="0.95"' /tmp/ab_metrics.txt | awk '{print $2}')

# Calculate improvement
IMPROVEMENT=$(echo "scale=2; ($ORIGINAL_P95 - $REWRITE_P95) / $ORIGINAL_P95 * 100" | bc)

# Send to Slack
curl -X POST $SLACK_WEBHOOK_URL \
  -H 'Content-Type: application/json' \
  -d "{
    \"text\": \"HeliosDB Query Rewriting A/B Test Results\",
    \"attachments\": [{
      \"color\": \"good\",
      \"fields\": [
        {\"title\": \"Original P95 Latency\", \"value\": \"${ORIGINAL_P95}s\", \"short\": true},
        {\"title\": \"Rewritten P95 Latency\", \"value\": \"${REWRITE_P95}s\", \"short\": true},
        {\"title\": \"Improvement\", \"value\": \"${IMPROVEMENT}%\", \"short\": true}
      ]
    }]
  }"

if (( $(echo "$IMPROVEMENT > 20" | bc -l) )); then
  echo "A/B test shows ${IMPROVEMENT}% improvement. Recommend enabling rewrites for all traffic."
fi

---

Adoption Strategy

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

1. Identify Worst Queries: Use database slow query logs or APM tools (DataDog, New Relic) to identify top 20 slowest queries. Categorize by pattern (correlated subqueries, N+1, missing joins, etc.). Benchmark current execution time and explain plans.

2. Deploy HeliosDB Lite in Read-Only Mode: Configure HeliosProxy to log rewrite suggestions without actually executing rewrites (dry_run = true). Analyze logs to see which queries would be optimized and estimated speedup. Review for correctness (ensure rewrites don't change semantics).

3. Benchmark Rewrites: Enable query rewriting for dev/staging environment. Run production workload replay or load tests. Compare P50/P95/P99 latencies before vs. after. Track cache hit rates, rewrite success rates, and any regressions.

Phase 2: Production Rollout (Weeks 5-12)

1. Optimize Infrastructure: Once queries are optimized, rightsize database instances (reduce CPU/memory since queries are faster). Migrate from expensive RDS/cloud DB to smaller instances or even single-node HeliosDB Lite for smaller deployments.

Phase 3: Continuous Improvement (Months 4-12)

1. Custom Optimization Rules: Work with HeliosDB support to develop domain-specific optimization rules for your application patterns. Examples: recognize reporting query patterns, optimize multi-tenant filtering, handle time-series data efficiently.

2. Developer Training: Share HeliosDB observability dashboards with engineering team showing which query patterns cause slowness. Train developers to write ORM queries that benefit most from automatic optimization. Create internal best practices guide.

---

Conclusion

Query rewriting and optimization represents a fundamental shift in database architecture: moving from reactive (developers manually optimize slow queries after incidents) to proactive (database automatically optimizes every query transparently).

HeliosDB Lite's integrated proxy architecture, combined with PostgreSQL compatibility, enables drop-in deployment that immediately optimizes existing application code without changes.

As SQL complexity continues to grow—with machine learning pipelines, real-time analytics, and increasingly complex data models—the gap between hand-optimized and auto-generated queries will only widen. HeliosDB Lite's query rewriting positions it as the embedded database that bridges this gap, improving the performance of queries generated by ORM and BI tools. The value proposition is simple: write SQL like a developer, execute like a DBA, all without hiring expensive database specialists or rewriting application code.