SQLite普通表与FTS4表增量同步实现及最优方案问询

Hey there, let's tackle your SQLite FTS4 incremental sync problem head-on! First, let's align on assumed table structures to make examples concrete:

• tbl (regular table): Has a primary key id (unique integer) and your content fields, e.g.:

  CREATE TABLE tbl (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      content TEXT NOT NULL
  );
  

• tbl_search (FTS4 virtual table): Mirrors relevant fields from tbl, with id marked as non-fulltext indexed for faster lookups:

  CREATE VIRTUAL TABLE tbl_search USING FTS4(
      id INTEGER,
      content TEXT,
      id UNINDEXED
  );
  

Implementation of Your Proposed Scheme 3 (ID-Based Missing Record Sync) #

This approach focuses on identifying records in tbl that don't exist in tbl_search and inserting them—perfect for incremental sync after your initial full run (run#0).

Core Logic #

You can use either NOT EXISTS (often faster with indexed fields) or LEFT JOIN to filter missing records:

Option 1: Using NOT EXISTS

INSERT INTO tbl_search(id, content)
SELECT id, content
FROM tbl
WHERE NOT EXISTS (
    SELECT 1 FROM tbl_search WHERE tbl_search.id = tbl.id
);

Option 2: Using LEFT JOIN

INSERT INTO tbl_search(id, content)
SELECT t.id, t.content
FROM tbl t
LEFT JOIN tbl_search ts ON t.id = ts.id
WHERE ts.id IS NULL;

Optimization for Large Datasets #

If tbl has millions of records, batch inserts to avoid memory pressure—repeat this query until no rows are affected:

INSERT INTO tbl_search(id, content)
SELECT t.id, t.content
FROM tbl t
LEFT JOIN tbl_search ts ON t.id = ts.id
WHERE ts.id IS NULL
LIMIT 1000; -- Adjust batch size based on your system's capacity

Comparing All Four Sync Schemes (And Finding the Fastest/Optimal) #

Let's break down the four common approaches, their tradeoffs, and which fits your needs best:

Scheme 1: Full Rebuild (Drop & Recreate FTS Table) #

• How it works: Delete tbl_search, recreate it, then insert all records from tbl.
• Pros: Dead-simple logic, no extra field dependencies.
• Cons: Terrible performance for large datasets; risks data gaps if new records are added during the rebuild; duplicates may occur if sync runs overlap.
• Use case: Only viable for tiny datasets or one-off initial syncs (like run#0).

Scheme 2: Timestamp-Based Sync #

• How it works: Add an update_time/create_time field to tbl, then sync only records where update_time > last_sync_timestamp.
• Pros: Efficient incremental sync (only processes changed records); handles both new and updated entries.
• Cons: Requires maintaining a reliable timestamp field and tracking last sync time; fails to catch deletes without soft delete flags; risks missed syncs if timestamps are misconfigured.
• Use case: Great for scheduled batch syncs where you need to handle updates and control tbl's schema.

Scheme 3: ID-Based Missing Record Sync (Your Proposed Approach) #

• How it works: As implemented above—find and insert records in tbl missing from tbl_search.
• Pros: No extra schema dependencies (only needs tbl's primary key); simple logic; far faster than full rebuilds; avoids duplicates entirely.
• Cons: Doesn't handle updates or deletes out of the box; large datasets may need batching.
• Use case: Best choice for pure incremental new-record syncs (your core requirement). It balances performance, simplicity, and reliability.

Scheme 4: Trigger-Automatic Sync #

• How it works: Create INSERT/UPDATE/DELETE triggers on tbl that auto-sync changes to tbl_search in real time.
• Pros: Real-time data consistency; no manual sync logic needed.
• Cons: Adds overhead to every tbl write (slows down your main table); bulk writes will trigger hundreds of FTS operations, crippling throughput.
• Use case: Only ideal for small, low-write-volume datasets where real-time search is critical.

The Fastest & Optimal Solution #

If your primary goal is incremental sync of new records, Scheme 3 is clearly the fastest and most optimal choice:

1. No extra schema changes or maintenance (unlike Scheme 2).
2. Far better performance than full rebuilds (Scheme 1) and avoids data gaps.
3. No write overhead on your main table (unlike Scheme 4).

If you later need to handle updates or deletes, extend Scheme 3 with:

• An update_time field to sync modified records:

  UPDATE tbl_search
  SET content = t.content
  FROM tbl t
  WHERE tbl_search.id = t.id
  AND t.update_time > (SELECT MAX(update_time) FROM tbl_search);
  

• Soft deletes (e.g., is_deleted flag) to remove records from tbl_search:

  DELETE FROM tbl_search
  WHERE id IN (SELECT id FROM tbl WHERE is_deleted = 1);
  

内容的提问来源于stack exchange，提问作者PanDe