FrameQuery
Back to blog

Technology

Making SQLite Fly as a Desktop Video Database

SQLite holds FrameQuery's video library, search indexes, and biometric data. From one mutex to a concurrent, trigger-maintained, full-text-searchable backend.

FrameQuery Team26 March 20267 min read

FrameQuery stores everything about your video library in a single SQLite database file. Video metadata, source folders, processing status, tags, projects, face data, perceptual hashes. One file, portable and easy to back up. But as the schema grew to 30+ tables and libraries scaled to tens of thousands of clips, we had to move past the default SQLite configuration to keep the app responsive.

The single-mutex problem

The first implementation was one connection wrapped in a Rust Arc<Mutex<Connection>>, shared across all threads.

It is slow. SQLite in WAL (Write-Ahead Logging) mode supports concurrent readers alongside a single writer. The mutex eliminated that advantage entirely. Every read waited for every write. Every write waited for every read. The UI thread blocked while a background scan inserted new videos. Searches stalled while the dashboard loaded stats.

Reader/writer split

The fix was separating read and write connections:

/// Singleton DB connection for writes
static DB: OnceLock<Arc<Mutex<Connection>>> = OnceLock::new();
/// Pool of read-only connections for concurrent reads
static READ_POOL: OnceLock<ReadConnectionPool> = OnceLock::new();

The write connection stays behind a mutex because SQLite only supports one writer at a time. That constraint is fundamental to SQLite. Reads have no such constraint.

ReadConnectionPool maintains a Vec<Connection> with a configurable maximum size. When a thread needs to read, it calls acquire(), which pops an existing connection from the pool or creates a new one if the pool is empty. Each read connection is opened with the SQLITE_OPEN_READ_ONLY flag, which tells SQLite this connection will never write.

A ReadPoolGuard RAII wrapper returns the connection to the pool when the guard goes out of scope.

The UI can query video metadata, run searches, and load dashboard stats while a background thread inserts newly discovered clips. Reads no longer serialize behind writes.

PRAGMA tuning

SQLite's defaults are conservative. They prioritize safety and compatibility over performance. For a desktop application where the database file lives on a local SSD and a power loss means at most losing the last few seconds of work, we can be more aggressive.

PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;
PRAGMA cache_size=-131072;     -- 128 MB
PRAGMA temp_store=MEMORY;
PRAGMA mmap_size=1073741824;   -- 1 GB
PRAGMA foreign_keys=ON;
PRAGMA busy_timeout=5000;      -- 5 seconds

journal_mode=WAL enables the concurrent reader/writer model. Without WAL, SQLite uses a rollback journal that locks the entire database during writes. WAL is strictly better for our workload.

synchronous=NORMAL is the main performance setting. The default (FULL) calls fsync after every transaction commit, which is expensive. In WAL mode, NORMAL is still safe against data corruption from a crash. You can lose the last transaction if the OS crashes mid-write, but the database will not become corrupted. FrameQuery accepts that trade-off.

cache_size=-131072 gives SQLite 128 MB of page cache. The negative number means kibibytes, not pages. This keeps hot pages (indexes, frequently accessed tables) in memory. When a user browses their library, the same pages get hit repeatedly. Keeping them cached avoids redundant disk reads.

mmap_size=1073741824 enables memory-mapped I/O for up to 1 GB of the database file. Instead of read() syscalls, SQLite accesses pages through the virtual memory system. This reduces context switches and lets the OS page cache work more efficiently. For read-heavy workloads on a local SSD, the improvement is measurable.

busy_timeout=5000 tells SQLite to retry for up to 5 seconds when it encounters a lock conflict instead of immediately returning SQLITE_BUSY. This smooths out brief contention between the write connection and read pool without requiring application-level retry logic.

Prepared statement caching

Every query in FrameQuery uses prepare_cached() instead of prepare():

conn.prepare_cached(&sql).map_err(|e| ...)?

SQLite parses SQL into a bytecode program before executing it. Parsing is not free, especially for complex queries with joins and subqueries. prepare_cached() keeps the compiled bytecode in a per-connection cache keyed by the SQL string. The second time the same query runs, parsing is skipped entirely.

The pattern is used across about 100 query sites in the codebase, on both read and write paths. For queries that run thousands of times per session (fetching thumbnails, checking processing status, resolving tags), the savings add up.

Trigger-maintained dashboard stats

The FrameQuery dashboard shows summary statistics: total videos, total duration, total file size. The naive implementation runs aggregate queries:

SELECT COUNT(*) FROM videos;
SELECT SUM(duration) FROM videos;
SELECT SUM(size) FROM videos;

These are full table scans. For a library with 50,000 videos, they are fast enough individually but add latency to every dashboard load. They also hold read locks while scanning, competing with other queries.

Starting in schema version 32, we pre-compute these values and maintain them with triggers:

CREATE TABLE IF NOT EXISTS stats_cache (
    key TEXT PRIMARY KEY,
    value_int INTEGER NOT NULL DEFAULT 0,
    value_real REAL NOT NULL DEFAULT 0.0
);

Three triggers keep the cache in sync:

  • trg_stats_video_insert increments the counts and sums when a video is inserted.
  • trg_stats_video_delete decrements them when a video is deleted.
  • trg_stats_video_update adjusts duration and size when those columns change.

Reading a stat becomes a single primary-key lookup instead of a table scan:

get_cached_stat_int(conn, "total_videos")   // returns 0 if key is missing
get_cached_stat_real(conn, "total_duration")

Users upgrading from older versions (pre-v32) will not have the stats_cache table populated yet. The migration backfills the values, and the lookup returns zero during the transition instead of failing.

The trade-off is complexity. Every write path that touches the videos table now has trigger overhead. The triggers add a few integer additions to inserts and deletes, and they eliminate repeated full table scans on reads.

Indexing strategy

FrameQuery's schema has 30+ indexes.

Compound indexes match common query patterns. When the UI filters videos by source folder and processing status, this index covers it without touching the main table:

CREATE INDEX idx_videos_source_status ON videos(source_id, status);

Partial indexes reduce index size for sparse columns. Not every video has been hashed yet, so we only index rows where the hash exists:

CREATE INDEX idx_videos_src_size_qhash
   ON videos(source_id, size, quick_hash) WHERE quick_hash IS NOT NULL;
CREATE INDEX idx_videos_src_size_dhash
   ON videos(source_id, size, detail_hash) WHERE detail_hash IS NOT NULL;

These indexes power duplicate detection. When a new file is discovered, we check if any existing video in the same source has the same size and hash. The partial index means we only pay storage costs for videos that have been hashed, and the query planner can skip un-hashed rows entirely.

Junction table indexes cover both directions of many-to-many relationships:

CREATE INDEX idx_video_tags_map_video ON video_tags_map(video_id);
CREATE INDEX idx_video_tags_map_tag ON video_tags_map(tag_id);

This lets us efficiently answer both "what tags does this video have" and "what videos have this tag" without full table scans on the junction table.

Full-text search with Tantivy

FrameQuery's search needs field-level boosting, stemming, phrase queries, range filters on numeric fields, and biometric filtering, which go beyond SQLite's built-in FTS5. FrameQuery uses Tantivy, a Rust full-text search library, as a separate index alongside SQLite.

The tokenizer pipeline handles English text:

pub const EN_STEM_TOKENIZER: &str = "en_stem";

let analyzer = TextAnalyzer::builder(SimpleTokenizer::default())
    .filter(RemoveLongFilter::limit(40))
    .filter(LowerCaser)
    .filter(StopWordFilter::new(Language::English)?)
    .filter(Stemmer::new(Language::English))
    .build();

Tokens pass through whitespace splitting, removal of anything over 40 characters (garbage strings from OCR or corrupt metadata), lowercasing, English stop word removal, and English stemming. This means "running" and "run" match each other, and common words like "the" and "is" do not pollute results.

The search schema (currently version 6) indexes seven text fields and six numeric fields per document. Each text field gets a different BM25 boost weight:

Filename (3.0) > match_text (2.5) > transcript (2.0) >
tags (1.8) > scene_desc (1.5) > objects (1.2) > catch-all (1.0)

If a user searches for "interview," a file named "interview.mp4" is almost certainly the best result. A transcript containing the word "interview" is a strong signal. An object detection label is weaker. Boosted queries for each field are combined with Occur::Should, so documents matching multiple fields rank higher.

The Tantivy writer uses a 50 MB heap and commits once per video. A single commit batches the video document, all its scene documents, and all transcript segments. This keeps the index consistent (you never see partial results for a video) and amortizes the commit overhead across many documents.

Perceptual and visual hashes

Schema version 31 added two binary columns to the videos table:

  • thumb_hash (28 bytes): A ThumbHash encoding of the video's representative frame. This is small enough to store inline and decode instantly into a blurred placeholder image. When you scroll through a large library, FrameQuery can show placeholders before the actual thumbnails load from disk.
  • perceptual_hash (32 bytes): A perceptual hash for near-duplicate detection. Two videos with a small Hamming distance between their perceptual hashes are visually similar, even if they differ in resolution, codec, or compression level.

Both are stored as BLOBs rather than hex strings. This halves the storage cost and makes Hamming distance comparisons faster since they operate on raw bytes instead of parsing hex.

What we would do differently

Implement the reader/writer split from day one. A single mutex is simpler to build, but its performance problems are hard to diagnose: connection contention looks like slow queries, and slow queries look like missing indexes.

Add the stats_cache triggers early. Aggregate queries are fine when the table has 100 rows and degrade gradually until the dashboard feels sluggish.

The PRAGMA tuning was the easiest win. A few lines of SQL, applied once when the database is opened, with a large and immediate effect on throughput. If you are building a desktop application with SQLite, start with these settings.

Download FrameQuery to try FrameQuery.