FTS5 Content Tables, Rowid Consistency, and Contentless Trade-offs

---

Rowid Synchronization Between FTS5 Virtual Tables and Source Content Tables

The core challenge revolves around ensuring that the rowid exposed by an FTS5 virtual table (v_tbl) matches the primary key (id) of the source content table (tbl) when using the content='tbl' and content_rowid='id' options. This synchronization is critical for JOIN operations and data integrity. A secondary concern involves schema design choices when using FTS5 with external content tables: whether to include additional columns (e.g., c, d) as UNINDEXED fields in the virtual table or to rely on JOINs to fetch them. Performance implications of the content option and the practical use cases for contentless FTS5 tables further complicate the decision-making process.

The FTS5 virtual table is configured to index specific columns (description, fulltext) from the source table tbl while excluding others (a, b) via the UNINDEXED keyword. This setup raises questions about how the virtual table interacts with the source data, especially when retrieving non-indexed columns or joining the two tables. Misunderstanding the relationship between the virtual table’s rowid and the source table’s primary key can lead to incorrect query results or inefficient data retrieval strategies.

The content='tbl' directive binds the FTS5 virtual table to the content of tbl, meaning the virtual table does not store its own copy of the indexed text but instead references the source table. The content_rowid='id' parameter explicitly maps the virtual table’s rowid to the id column of tbl. However, subtle behaviors emerge when querying the virtual table directly versus joining it with the source table. For instance, selecting rowid from v_tbl should theoretically align with tbl.id, but edge cases involving concurrent writes or schema modifications could disrupt this linkage.

---

Architectural Ambiguities and Schema Design Misconfigurations

The confusion stems from three primary areas:

1. Rowid Mapping Mechanics: The FTS5 virtual table’s rowid is not inherently tied to the source table’s primary key unless explicitly declared via content_rowid. Even then, discrepancies can arise if the source table’s id is not an INTEGER PRIMARY KEY (which enforces rowid aliasing in SQLite).
2. Column Inclusion Strategy: Including non-indexed columns (a, b, c, d) in the FTS5 virtual table via UNINDEXED trades JOIN simplicity for increased storage overhead. Each UNINDEXED column duplicates data from the source table into the FTS5 index, which may bloat the database file but eliminate JOINs in queries.
3. Content vs. Contentless Tables: The content='' option creates a contentless FTS5 table, where the virtual table stores both the indexed text and auxiliary data. This contrasts with content-enabled tables, which rely on an external source for column values. Contentless tables are smaller and faster for write-heavy workloads but lack direct access to non-indexed columns without redundant storage.

A deeper issue involves FTS5’s tokenization and query execution pipeline. When a MATCH operation is performed on v_tbl, the FTS5 engine only scans the indexed columns (description, fulltext). However, the content option introduces a layer of indirection: the virtual table must dereference the source table’s id to retrieve non-indexed columns during query execution. This process can introduce latency if the source table lacks covering indexes or if the query planner selects suboptimal join strategies.

---

Validating Rowid Alignment, Optimizing Column Retrieval, and Evaluating Content Strategies

Step 1: Confirm Rowid-to-ID Synchronization

To verify that v_tbl.rowid equals tbl.id, execute a diagnostic query:

SELECT 
    v_tbl.rowid AS fts_rowid, 
    tbl.id AS source_id 
FROM 
    v_tbl 
JOIN tbl ON v_tbl.rowid = tbl.id 
LIMIT 100;

If all rows return matching fts_rowid and source_id, the content_rowid='id' configuration is functioning correctly. If mismatches occur, inspect the schema of tbl to ensure id is declared as INTEGER PRIMARY KEY (not merely PRIMARY KEY), as SQLite’s rowid aliasing requires strict typing.

Step 2: Choose Between UNINDEXED Columns or JOINs

Including c and d as UNINDEXED in v_tbl avoids JOINs but duplicates data:

CREATE VIRTUAL TABLE v_tbl USING fts5(
  a UNINDEXED,
  b UNINDEXED, 
  c UNINDEXED,  -- New
  d UNINDEXED,  -- New
  description,
  fulltext, 
  content='tbl', 
  content_rowid='id'
);

Pros:

• Queries can retrieve c and d directly from v_tbl, simplifying SQL.
• Eliminates JOIN overhead during query execution.

Cons:

• Increases FTS5 storage size, as c and d are duplicated in the virtual table’s internal structures.
• Requires updating the FTS5 schema if tbl’s columns change.

To measure the impact, compare query plans and execution times for both approaches. For read-heavy workloads favoring speed over storage, UNINDEXED may be preferable. For large datasets or infrequent access to c/d, JOINs are more efficient.

Step 3: Assess content= Performance Trade-offs

FTS5 content tables incur overhead during index maintenance. When rows are inserted, updated, or deleted in tbl, the FTS5 virtual table must synchronize its index via triggers (automatically created by SQLite). This imposes a write penalty proportional to the volume of text data.

To benchmark, perform bulk insert/update operations on tbl with and without the FTS5 virtual table. Use SQLite’s sqlite3_profile function or .timer on in the CLI to measure execution times. Content-enabled tables will show slower write speeds compared to contentless tables, which store data directly and avoid trigger-based synchronization.

Step 4: Leverage Contentless Tables for Specific Use Cases

Contentless FTS5 tables (content='') are ideal when:

• The indexed text is self-contained and does not require association with external data.
• Write performance is prioritized over storage efficiency.

For example, a contentless table storing searchable logs might look like:

CREATE VIRTUAL TABLE logs_fts USING fts5(
  log_text, 
  content=''
);

To retrieve data, use logs_fts.rowid as a foreign key to an external metadata table. Contentless tables cannot return non-indexed columns, so all queried data must reside within the FTS5 structure or be JOINed externally.

Step 5: Implement Column Filters for Targeted Searching

To restrict FTS5 searches to specific columns (e.g., fulltext), use column filters in the MATCH clause:

SELECT rowid, a, b FROM v_tbl WHERE fulltext MATCH 'word';

This syntax ensures only the fulltext column is scanned for word, avoiding false positives from description.

Step 6: Mitigate Content= Overhead with Indexing

If JOINs between v_tbl and tbl are slow, create covering indexes on tbl’s frequently accessed columns:

CREATE INDEX tbl_cover ON tbl(id, c, d);

This allows the query planner to retrieve c and d directly from the index, bypassing table scans.

---

By methodically validating rowid alignment, profiling schema configurations, and tailoring FTS5 usage to specific access patterns, developers can optimize full-text search performance while maintaining data consistency. Content-enabled tables offer flexibility at the cost of write overhead, whereas contentless tables excel in write efficiency but demand careful data management.