Handling FTS5 Index Mismatch with Content Table Row Count Discrepancies

Understanding the Impact of Diverging Row Counts Between FTS5 Index and Base Content Tables

Core Dynamics of FTS5 Index-Content Table Relationships

SQLite’s FTS5 extension is designed to create full-text search indexes over content stored in ordinary SQLite tables. A foundational assumption in its architecture is that the virtual FTS5 table and its associated content table share a strict 1:1 correspondence in row counts and rowid alignment. This relationship is enforced through SQLite’s internal bookkeeping mechanisms, which map FTS5 index entries directly to the rowid of the content table. When this alignment is disrupted—such as by omitting rows from the FTS5 index that exist in the content table—the integrity of search results, update operations, and even basic query logic can degrade unpredictably.

The documentation explicitly states that the content table must be queryable in a way that returns rows in ascending rowid order with all columns present. This is not a suggestion but a requirement for FTS5 to function correctly. The FTS5 engine relies on this structure to synchronize its internal index with the content table during queries, updates, and maintenance operations like integrity checks. If the FTS5 index contains fewer rows than the content table, or if the rowid sequence has gaps, the engine may misinterpret the relationship between indexed tokens and their source data. For example, a search for "Paris" might return results pointing to rowid 5 in the FTS5 index, but if the content table’s rowid 5 corresponds to a different city due to skipped entries, the application will return incorrect data.

This misalignment introduces risks in three key areas:

1. Query Accuracy: Search results may reference incorrect rowids, leading to mismatched data.
2. Update Propagation: Triggers or automatic updates that synchronize the content table with the FTS5 index may fail to account for missing rows.
3. Index Maintenance: Operations like rebuilding the index or running integrity checks (e.g., INSERT INTO fts_table(fts_table) VALUES('integrity-check');) may throw errors or corrupt the index.

The problem becomes more acute when using FTS5’s contentless table mode, where the index acts as both the search structure and the sole source of data. If the original content table is modified independently of the FTS5 index, the system will lack mechanisms to reconcile differences, leading to irreversible data drift.

Architectural Pitfalls Leading to Row Count Mismatches

The desire to reduce index size by omitting duplicate entries is understandable, but doing so without adhering to FTS5’s constraints introduces several technical antipatterns:

1. Direct Manipulation of FTS5 Index Entries

A common mistake is manually inserting or deleting rows in the FTS5 index to exclude duplicates. For example:

-- Content table
CREATE TABLE cities (
  id INTEGER PRIMARY KEY,
  name TEXT,
  language TEXT
);

-- FTS5 index with manual entry filtering
CREATE VIRTUAL TABLE cities_fts USING fts5(name, content='cities', content_rowid='id');
INSERT INTO cities_fts(rowid, name) 
  SELECT id, name FROM cities 
  WHERE id NOT IN (SELECT MIN(id) FROM cities GROUP BY name);

This approach breaks the 1:1 rowid correspondence. The FTS5 index now contains only a subset of the content table’s rows, but the content_rowid attribute still expects every row in cities to have a matching entry in cities_fts. Queries joining the two tables will produce incorrect results because the FTS5 index’s rowids no longer align with the content table’s id column.

2. Misuse of External Content Management

When using content='cities', FTS5 expects the content table to be the authoritative source of truth. Any modifications to the content table (inserts, updates, deletes) should automatically propagate to the FTS5 index via SQLite’s internal triggers. However, if the application uses custom logic to filter rows before inserting them into the FTS5 index, these triggers are bypassed. Over time, the index becomes out of sync with the content table, leading to phantom search results (rows that exist in the content table but not in the index) or stale data (rows deleted from the content table but lingering in the index).

3. Overlooking Tokenization and Language-Specific Duplicates

The original scenario involves skipping "duplicate" city names across languages. However, what constitutes a duplicate depends on the tokenizer and collation settings. For instance, "Köln" (German) and "Cologne" (English) are distinct names for the same city but would not be considered duplicates by most tokenizers. Conversely, "Mumbai" and "Bombay" (historical names for the same city) might be treated as duplicates if a custom tokenizer normalizes them. If the deduplication logic does not account for tokenizer behavior, the FTS5 index may exclude entries that should be searchable or include entries that should be omitted, further exacerbating row count mismatches.

Resolving Mismatches Through Schema Refactoring and Query Optimization

To maintain FTS5 index integrity while achieving storage efficiency, consider these strategies:

1. Materialized View with Deduplicated Content

Create a materialized view that consolidates unique city names and their associated metadata, then build the FTS5 index over this view. This ensures a 1:1 rowid correspondence while eliminating duplicates:

-- Deduplicated view
CREATE TABLE cities_deduped AS
SELECT MIN(id) AS id, name, group_concat(language) AS languages
FROM cities
GROUP BY name;

-- FTS5 index over the deduplicated table
CREATE VIRTUAL TABLE cities_fts USING fts5(name, content='cities_deduped', content_rowid='id');

This approach guarantees that every row in cities_deduped has a corresponding entry in cities_fts. However, searches will return the consolidated languages field instead of individual rows. To map results back to the original cities table, use a join:

SELECT c.* 
FROM cities_fts f
JOIN cities_deduped d ON f.rowid = d.id
JOIN cities c ON c.name = d.name;

2. Contentless FTS5 Table with Manual Synchronization

If disk space is a critical concern, use a contentless FTS5 table and manage data insertion explicitly:

CREATE VIRTUAL TABLE cities_fts USING fts5(name, content='');

Populate the index with deduplicated entries:

INSERT INTO cities_fts(rowid, name) 
  SELECT MIN(id), name 
  FROM cities 
  GROUP BY name;

This eliminates the content table entirely, but you lose the ability to query non-indexed columns (e.g., language). To associate search results with the original data, maintain a mapping table:

CREATE TABLE fts_mapping (
  fts_rowid INTEGER PRIMARY KEY,
  city_ids TEXT -- Comma-separated list of original ids
);

INSERT INTO fts_mapping 
  SELECT MIN(id), group_concat(id) 
  FROM cities 
  GROUP BY name;

When a search returns fts_rowid=5, query fts_mapping to retrieve all original city_ids associated with that entry.

3. Hybrid Approach with External Triggers

For applications requiring real-time synchronization between the content table and FTS5 index, implement triggers that enforce deduplication during insert/update operations:

-- Trigger to prevent duplicate inserts
CREATE TRIGGER cities_deduplicate 
BEFORE INSERT ON cities 
WHEN EXISTS (SELECT 1 FROM cities WHERE name = NEW.name) 
BEGIN
  SELECT RAISE(IGNORE);
END;

-- Trigger to update FTS5 index only for new names
CREATE TRIGGER cities_fts_insert 
AFTER INSERT ON cities 
WHEN NOT EXISTS (SELECT 1 FROM cities WHERE name = NEW.name) 
BEGIN
  INSERT INTO cities_fts(rowid, name) VALUES (NEW.id, NEW.name);
END;

This ensures that the FTS5 index only contains unique names while preserving rowid alignment. However, it complicates updates—for example, renaming a city would require deleting all associated rows in cities and reinserting them to trigger the deduplication logic.

4. Leveraging Partial Indexes with Filtered Views

If using SQLite 3.24 or later, employ WITHOUT ROWID tables and partial indexes to simulate a filtered FTS5 index:

CREATE TABLE cities_unique (
  id INTEGER PRIMARY KEY,
  name TEXT
) WITHOUT ROWID;

INSERT INTO cities_unique 
  SELECT MIN(id), name 
  FROM cities 
  GROUP BY name;

CREATE VIRTUAL TABLE cities_fts USING fts5(name, content='cities_unique');

By building the FTS5 index over a pre-deduplicated table, you maintain rowid consistency. The WITHOUT ROWID clause optimizes storage for the deduplicated data.

Final Considerations:

• Performance Tradeoffs: Deduplication reduces index size but increases query complexity when mapping results back to the original content.
• Tokenizer Consistency: Ensure that the deduplication logic respects the tokenizer’s behavior. For example, case-insensitive tokenizers may treat "Paris" and "paris" as duplicates, while others do not.
• Maintenance Overhead: Manual synchronization strategies require rigorous testing to avoid data drift between the content table and FTS5 index.

By adhering to these patterns, you can achieve efficient FTS5 indexing without violating SQLite’s architectural assumptions.