[RFC] Direct Query External Query Scheduler

[noCharger]

Summary

This RFC proposes a new solution for managing automatic index refreshes in Flint, addressing the limitations of the current approach and providing improvements in user experience, cost management, and observability.

Motivation

The current implementation of automatic index refreshes in Flint has several limitations:

User Experience: The ALTER clause does not support updating certain index options, such as the refresh_interval for auto-fresh indexes, requiring multiple queries.
Cost Management: Maintaining skipping indexes (SI), covering indexes (CI), or materialized views (MVs) with auto-refresh enabled requires a continuously running driver node, resulting in significant cost overhead when using EMR-S.
Observability Challenges: Users face challenges in understanding the index state and billing details when using auto-refresh.

Proposed Solution

The proposed solution involves the following modifications:

Flint Index State: After an index with auto refresh is created, it will remain in the ACTIVE state. Only during the refresh procedure does it move to the REFRESHING state.
Query Scheduler Integration: Flint will support external scheduler, and refresh operations will be scheduled based on the refresh_interval from the query.
Scheduler Control: Users can enable or disable the scheduler using the ALTER clause and change the refresh_interval directly.
Streaming Job Management: There is no need to cancel the streaming EMR job; instead, the external
Manual Execution: Users can manually execute the REFRESH statement, but only one refresh operation can be active for any given index or MV at a time.

Design Details

SQL Syntax Changes

A new field, scheduler_mode, will be introduced to control the scheduling behavior. When auto_refresh=true and scheduler_mode=external, the refresh is trigger by external scheduler.

Scheduler Service

A new scheduler service interface will be introduced, providing methods for scheduling, unscheduling, and updating jobs. Both the SQL Plugin and Flint will implement this interface, allowing for seamless integration with different components and services.

Job Index

The OpenSearch JobScheduler plugin will be used to manage the scheduled refresh jobs, as the default scheduler service implementation. Each SI/CI/MV will have a corresponding job index document, which will store the scheduling information and metadata.

Scheduled Job Runner

The ScheduledJobRunner interface will be implemented to handle the logic required for refreshing queries periodically. A locking service will be employed to ensure that job executions do not overlap.

Streaming Job Migration

During cluster bootstrapping, all existing streaming jobs will be searched to generate new scheduled jobs

Metrics

New metrics and dimensions will be introduced to track the usage of the external scheduler.

Alternatives Considered

Continuous Micro-batch and Continuous Processing Modes: These modes were not considered due to the limitations of the external scheduler in supporting low-latency use cases.
Using Cron or Other Open-Source Scheduling Libraries: These alternatives were not chosen as they would not integrate seamlessly with OpenSearch, resulting in additional engineering effort.

Tasks breakdown:

• [FEATURE] Add scheduler_mode index option #417
• Introduce scheduling service #418
• [FEATURE] Integrate job scheduler plugin sql#2832
• [FEATURE] Introduce scheduling service sql#2833
• Optimization: Use external scheduler with configurable interval #622
• Streaming job migration