Skip to content
HeliosDB HeliosDB Full

Materialized Views Troubleshooting Guide

Materialized Views Troubleshooting Guide

Last Updated: January 4, 2026

This guide covers common issues encountered when working with materialized views and their solutions.

Table of Contents

  1. View Not Being Used
  2. Refresh Failures
  3. Performance Issues
  4. Stale Data
  5. Memory Problems
  6. Query Rewriting Issues

View Not Being Used

Symptom

Queries against source tables are not being rewritten to use the materialized view.

Diagnosis

// Check view state and freshness
let view = manager.get_view(view_id).await?;


println!("View state: {:?}", view.state);
println!("Is fresh: {}", view.is_fresh());
println!("Last refresh: {:?}", view.statistics.last_refresh_time);

Solutions

1. View is not fresh

// Refresh the view
manager.refresh_view(view_id).await?;

2. Query rewriting is disabled

// Enable query rewriting
let config = RewriterConfig {
    enable_rewriting: true,
    min_cost_savings_percent: 5.0, // Lower threshold
    ..Default::default()
};

3. Cost savings below threshold

// Lower the minimum cost savings threshold
let config = RewriterConfig {
    min_cost_savings_percent: 1.0, // Accept smaller savings
    ..Default::default()
};

4. View not registered with rewriter

// Register view with query rewriter
let rewriter = manager.query_rewriter();
rewriter.register_view(view.clone()).await?;

Refresh Failures

Symptom

Scheduled refreshes fail or timeout.

Diagnosis

// Check failure count and state
let view = manager.get_view(view_id).await?;


if view.statistics.failure_count > 0 {
    println!("Failure count: {}", view.statistics.failure_count);
}


// Check refresh manager statistics
let refresh_stats = refresh_manager.get_statistics(view_id).await?;
println!("Last error: {:?}", refresh_stats.last_error);

Solutions

1. Query timeout

// Increase task timeout
let config = RefreshConfig {
    task_timeout_seconds: 7200, // 2 hours
    ..Default::default()
};

2. Source table changed

// Recreate the view with updated query
manager.drop_view(view_id).await?;
let new_view = MaterializedView::new(name, updated_query, strategy);
manager.create_view(new_view).await?;

3. Resource contention

// Limit concurrent refreshes
let config = RefreshConfig {
    max_concurrent_refreshes: 2, // Reduce from default
    ..Default::default()
};

4. Retry configuration

// Enable retries
let config = RefreshConfig {
    max_retries: 5,
    retry_delay_seconds: 120,
    ..Default::default()
};

Performance Issues

Symptom

Refresh takes too long or uses too many resources.

Diagnosis

// Check refresh duration
let view = manager.get_view(view_id).await?;


if view.statistics.avg_refresh_duration_ms > 60000 {
    println!("WARNING: Average refresh > 1 minute");
}

Solutions

1. Use incremental refresh

// Switch to incremental refresh
let engine = IncrementalRefreshEngine::new();


// Record changes instead of full refresh
engine.record_change(change).await?;


// Apply only the delta
let delta = engine.compute_delta(view_id, &source_tables, view_size).await?;
if let Some(delta) = delta {
    engine.apply_delta(&delta).await?;
}

2. Optimize view query

-- Before: Full table scan
SELECT * FROM large_table;


-- After: Add filters to reduce data
SELECT * FROM large_table WHERE updated_at > '2026-01-01';

3. Reduce refresh frequency

// Change from 5 minutes to 1 hour
let schedule = RefreshSchedule {
    schedule_type: ScheduleType::Interval { seconds: 3600 },
    ..Default::default()
};

4. Use appropriate storage backend

// For analytics queries, use columnar storage
let view = view.with_storage_backend(StorageBackend::Columnar);

5. Configure batch size

let config = IncrementalConfig {
    max_batch_size: 5000, // Reduce batch size
    ..Default::default()
};

Stale Data

Symptom

Materialized view contains outdated data.

Diagnosis

// Check last refresh time
let view = manager.get_view(view_id).await?;
let now = Utc::now();
let last_refresh = view.statistics.last_refresh_time;


let staleness = now - last_refresh;
println!("View staleness: {} seconds", staleness.num_seconds());

Solutions

1. Increase refresh frequency

// More frequent refresh
let schedule = RefreshSchedule {
    schedule_type: ScheduleType::Interval { seconds: 60 }, // Every minute
    ..Default::default()
};

2. Use on-commit refresh

// Real-time updates
let view = MaterializedView::new(
    name,
    query,
    RefreshStrategy::OnCommit,
);

3. Force manual refresh

// Force immediate refresh
manager.refresh_view(view_id).await?;

4. Check if refresh is paused

// Ensure refresh schedule is enabled
if let RefreshStrategy::Scheduled(mut schedule) = view.refresh_strategy {
    schedule.enabled = true;
}

Memory Problems

Symptom

Out of memory errors during refresh or query.

Diagnosis

// Check view size
let view = manager.get_view(view_id).await?;
println!("View size: {} bytes", view.statistics.size_bytes);
println!("Row count: {}", view.statistics.row_count);

Solutions

1. Use disk-backed storage

// Switch from memory to LSM storage
let view = view.with_storage_backend(StorageBackend::Lsm);

2. Enable compression

let config = IncrementalConfig {
    enable_compression: true,
    ..Default::default()
};

3. Reduce batch size

let config = IncrementalConfig {
    max_batch_size: 1000, // Smaller batches use less memory
    ..Default::default()
};

4. Limit view scope

-- Add filters to reduce data size
SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '30 days';

Query Rewriting Issues

Symptom

Queries are not being rewritten correctly or rewriting causes errors.

Diagnosis

let rewriter = QueryRewriter::new();
let result = rewriter.rewrite_query(&query).await?;


println!("Success: {}", result.success);
println!("Match strategy: {:?}", result.match_strategy);
println!("Cost savings: {:.2}%", result.cost_savings);


if !result.success {
    println!("No match reason: {:?}", result.no_match_reason);
}

Solutions

1. Check column coverage

// View must contain all columns needed by query
// Ensure view SELECT includes all required columns

2. Check table coverage

// View must reference all tables in query FROM clause
view.add_source_table(table_id);

3. Disable partial matching

// For strict matching only
let config = RewriterConfig {
    enable_partial_matching: false,
    ..Default::default()
};

4. Increase views to consider

let config = RewriterConfig {
    max_views_to_consider: 50, // Default is 20
    ..Default::default()
};

Diagnostic Commands

List All Views

let views = manager.list_views().await?;
for view in views {
    println!("{}: {:?} (hits: {})",
        view.name, view.state, view.statistics.query_hit_count);
}

Check Rewriter Statistics

let stats = rewriter.get_statistics().await;
println!("Total queries: {}", stats.total_queries);
println!("Rewrites: {}", stats.successful_rewrites);
println!("Success rate: {:.1}%", stats.success_rate * 100.0);
println!("No match count: {}", stats.no_match_count);

Force View Rebuild

// Drop and recreate
manager.drop_view(view_id).await?;
let new_view = MaterializedView::new(name, query, strategy);
manager.create_view(new_view).await?;
manager.refresh_view(new_view_id).await?;