Beyond FTS5: Building Transactional Full-Text Search in TursoDB

TursoDB introduces an innovative full-text search (FTS) engine that builds upon the limitations of SQLite's FTS5 by leveraging Tantivy, a high-performance search engine library written in Rust. This new engine, designed to meet the needs of modern applications including AI agents, offers powerful search capabilities through advanced features such as tokenizers, BM25 ranking, and efficient segment management. Unlike SQLite, TursoDB's implementation bypasses the Virtual Table abstraction, allowing it to use Postgres-style syntax for custom index definitions and better integration into the query planner. The FTS engine is integrated directly into TursoDB's B-Tree storage system, offering transactional guarantees, crash safety, and single-file distribution. However, the system faces challenges with segment merging due to the B-Tree model's single-writer semantics, leading to the introduction of an OPTIMIZE INDEX command for manual merge operations. TursoDB aims to provide an API-compatible alternative to SQLite, with enhanced ergonomics and transactional index updates, as part of its strategic goal to offer a comprehensive solution for full-text and vector searches within an embedded database environment.