Properly Deleting Records from FTS5 External Content Tables in SQLite

Issue Overview: FTS5 External Content Table Deletion Mechanics and Order Dependency

When working with SQLite’s FTS5 virtual tables configured to use external content (via the content= option), managing deletions requires a nuanced understanding of how the full-text search index interacts with the underlying source data. The core challenge arises when attempting to delete records from both the external content table (e.g., events) and the associated FTS5 table (e.g., fts) in a single operation. A common scenario involves batch deletions where rows identified by a temporary table (temp_to_delete) must be removed from both tables.

The FTS5 table does not store the actual content; it maintains an inverted index that references the external content table’s data. When a row is deleted from the external content table, the FTS5 index is not automatically updated unless explicitly instructed. This creates a critical dependency on the order of operations when performing deletions. If the external content row is deleted first, the FTS5 engine loses access to the data required to locate and remove the corresponding index entries. Conversely, deleting from the FTS5 table first ensures the index is updated while the external content is still available.

The confusion often stems from the interaction between standard DELETE statements and FTS5’s internal mechanics. For example, executing DELETE FROM fts WHERE rowid IN (...) appears straightforward, but its validity depends on whether the external content rows still exist at execution time. Additionally, the use of triggers (e.g., AFTER DELETE triggers on the external table) introduces an alternative method for synchronizing deletions, but this approach requires explicit handling of FTS5’s 'delete' directive via an INSERT into the FTS5 table—a non-intuitive pattern that can lead to errors if misunderstood.

Possible Causes: Incorrect Deletion Order, Trigger Misconfiguration, and Index Staleness

1. Incorrect Order of Deletion Operations

The most common cause of "orphaned" FTS5 index entries or failed deletions is performing deletions on the external content table before updating the FTS5 index. When DELETE FROM events is executed first, the FTS5 engine cannot resolve the rowid values to their original content, as the rows no longer exist in the events table. This leaves the FTS5 index entries referencing nonexistent data, causing inconsistencies in search results and potential integrity issues.

2. Misunderstanding FTS5’s 'delete' Directive in Triggers

Triggers designed to synchronize deletions between the external content table and the FTS5 table must use the INSERT INTO fts(fts, ...) VALUES ('delete', ...) syntax. This directive instructs FTS5 to remove the specified rowid from its index. However, developers accustomed to standard SQL DELETE operations might mistakenly issue a direct DELETE FROM fts WHERE rowid = ... within the trigger, which bypasses FTS5’s internal cleanup logic. While direct DELETE statements on FTS5 tables are valid in specific contexts, they require careful coordination with the external content’s state.

3. Assumptions About Automatic Index Maintenance

FTS5 does not enforce referential integrity between the external content table and the index. Developers might incorrectly assume that deleting a row from the external table automatically propagates the deletion to the FTS5 index. This is only true if triggers or application-level logic explicitly handle the synchronization. Without such mechanisms, the FTS5 index becomes stale, containing entries that reference deleted rows.

4. Mismatched content_rowid Configuration

The content_rowid option in FTS5 specifies the column name in the external content table that serves as the unique row identifier (defaulting to rowid). If the external table’s rowid is aliased (e.g., content_rowid=event_id) or modified, deletions targeting the FTS5 table must reference the correct column. A mismatch here can lead to failed deletions or partial index updates.

Troubleshooting Steps, Solutions & Fixes: Ensuring Consistent Deletions in FTS5 External Content Workflows

Step 1: Validate Deletion Order and External Content Availability

Scenario: Batch deletions using a temporary table (temp_to_delete) to identify target rows.
Action:

1. Delete from FTS5 first, while the external content rows still exist:

   DELETE FROM fts WHERE rowid IN (SELECT rowid FROM temp_to_delete);
   DELETE FROM events WHERE rowid IN (SELECT rowid FROM temp_to_delete);
   

   This order ensures FTS5 can access the external content to resolve the index entries.

2. Verify rowid alignment: Confirm that the rowid values in temp_to_delete match both the external content table’s rowid and the FTS5 table’s content_rowid configuration. If the external table uses an aliased rowid (e.g., event_id), adjust the queries accordingly:

   DELETE FROM fts WHERE rowid IN (SELECT event_id FROM temp_to_delete);
   DELETE FROM events WHERE event_id IN (SELECT event_id FROM temp_to_delete);
   

Troubleshooting Tip: After deletions, search the FTS5 table for the deleted content to detect orphaned entries:

SELECT * FROM fts WHERE fts MATCH 'deleted_term';

If results appear, the FTS5 deletion was incomplete.

Step 2: Implement Triggers Correctly for Real-Time Synchronization

Scenario: Using triggers to automate index updates upon deletions in the external table.
Action:

1. Define an AFTER DELETE trigger on the external content table (events) to propagate deletions to the FTS5 table:

   CREATE TRIGGER events_ad AFTER DELETE ON events BEGIN
     INSERT INTO fts(fts, rowid, aaa, data) VALUES ('delete', OLD.rowid, OLD.aaa, OLD.data);
   END;
   

   The 'delete' directive informs FTS5 to remove the index entry for OLD.rowid.

2. Avoid direct DELETE in triggers: Using DELETE FROM fts ... inside a trigger on the external table creates a race condition, as the external row might already be gone when the trigger fires.

Troubleshooting Tip: Test trigger behavior by manually deleting a row from events and checking if the FTS5 index reflects the change:

-- Delete a test row
DELETE FROM events WHERE rowid = 123;
-- Verify FTS5 index
SELECT rowid FROM fts WHERE fts MATCH 'test_content';

Step 3: Handle Bulk Deletions Efficiently Without Triggers

Scenario: Large-scale deletions where triggers introduce unacceptable overhead.
Action:

1. Disable triggers temporarily during batch operations:

   PRAGMA defer_foreign_keys = 1; -- Optional, if foreign key constraints exist
   DROP TRIGGER IF EXISTS events_ad;
   -- Perform batch deletions
   DELETE FROM fts WHERE rowid IN (SELECT rowid FROM temp_to_delete);
   DELETE FROM events WHERE rowid IN (SELECT rowid FROM temp_to_delete);
   -- Recreate trigger
   CREATE TRIGGER events_ad ... ; -- Restore trigger definition
   

   This minimizes trigger overhead but requires careful transaction management.

2. Use transactions to ensure atomicity:

   BEGIN;
   DELETE FROM fts WHERE rowid IN (SELECT rowid FROM temp_to_delete);
   DELETE FROM events WHERE rowid IN (SELECT rowid FROM temp_to_delete);
   COMMIT;
   

Step 4: Diagnose and Repair Index Staleness

Scenario: Orphaned FTS5 entries persist despite correct deletion procedures.
Action:

1. Rebuild the FTS5 index using the rebuild command:

   INSERT INTO fts(fts) VALUES ('rebuild');
   

   This reconstructs the index from the current state of the external content table.

2. Identify mismatched rowid ranges: Compare the rowid sequences between the external table and FTS5:

   -- External table rowids
   SELECT MIN(rowid), MAX(rowid) FROM events;
   -- FTS5 rowids
   SELECT MIN(rowid), MAX(rowid) FROM fts;
   

   Discrepancies indicate incomplete deletions or missing trigger updates.

Step 5: Optimize Performance for High-Volume Deletions

Scenario: Slow deletions due to large temp_to_delete tables or frequent commits.
Action:

1. Use temporary in-memory tables: Store temp_to_delete in MEMORY for faster access:

   CREATE TEMP TABLE temp_to_delete(rowid INTEGER) STRICT;
   

2. Batch deletions in chunks: Process deletions in smaller batches to avoid lock contention:

   WITH chunks AS (
     SELECT rowid FROM temp_to_delete LIMIT 1000 OFFSET 0
   )
   DELETE FROM fts WHERE rowid IN chunks;
   

   Repeat with increasing OFFSET until all rows are processed.

3. Leverage indexes on temp_to_delete: Add an index to the temporary table’s rowid column:

   CREATE INDEX tmp_del_idx ON temp_to_delete(rowid);
   

Final Recommendations

• Always delete from FTS5 before the external content table in batch operations.
• Prefer triggers for real-time synchronization unless performance constraints dictate otherwise.
• Monitor index integrity periodically using rebuild and rowid range checks.
• Document the deletion workflow to ensure team-wide awareness of FTS5’s external content requirements.

By adhering to these principles, developers can maintain consistent FTS5 indexes while efficiently managing deletions in external content-driven applications.