SQLite as an AI Application Database

SQLite has no vector type and does not need one. A million float32 embeddings fit in a BLOB column, and an exact scan over a hundred thousand of them takes single-digit milliseconds — which is faster than the approximate index you were going to install. This page shows the dependency-free version, derives the row count at which it stops being enough, and then covers the extension for after that.

When SQLite is the right answer

SQLite wins when the data is on the same machine as the code: a desktop application, a CLI tool, an on-device assistant, a per-tenant file, a test fixture, an evaluation harness. There is no network round trip, no connection pool, no separate process to keep alive, and the entire database is one file you can copy.

It loses when several processes write concurrently. SQLite permits one writer at a time; in WAL mode readers do not block and the writer does not block them, but two writers still serialise and the loser gets SQLITE_BUSY. For a single-process application that is a non-issue. For a web application with four workers it is a design constraint you must accept deliberately.

Exact search with no extension at all

Store the vector as a packed float32 BLOB, read the candidates, score them in the host language. Complete and runnable in Python’s standard library:

import sqlite3, struct, math

db = sqlite3.connect("app.db")
db.executescript("""
CREATE TABLE IF NOT EXISTS chunks (
  id         INTEGER PRIMARY KEY,
  doc_id     INTEGER NOT NULL,
  content    TEXT    NOT NULL,
  model      TEXT    NOT NULL,
  dim        INTEGER NOT NULL,
  embedding  BLOB    NOT NULL      -- packed little-endian float32
);
CREATE INDEX IF NOT EXISTS chunks_doc_idx ON chunks (doc_id);
""")

def pack(vec):
    return struct.pack("<%df" % len(vec), *vec)

def unpack(blob):
    return struct.unpack("<%df" % (len(blob) // 4), blob)

def add(doc_id, content, vec, model):
    db.execute(
        "INSERT INTO chunks (doc_id, content, model, dim, embedding)"
        " VALUES (?, ?, ?, ?, ?)",
        (doc_id, content, model, len(vec), pack(vec)),
    )

def search(query_vec, k=10, model="text-embedding-3-small"):
    # Pre-normalise the query once; store embeddings normalised at write
    # time and cosine similarity becomes a plain dot product.
    n = math.sqrt(sum(x * x for x in query_vec)) or 1.0
    q = [x / n for x in query_vec]
    hits = []
    for row_id, content, blob in db.execute(
        "SELECT id, content, embedding FROM chunks WHERE model = ?", (model,)
    ):
        v = unpack(blob)
        score = sum(a * b for a, b in zip(q, v))
        hits.append((score, row_id, content))
    hits.sort(reverse=True)
    return hits[:k]

Two details in there are load-bearing. Normalising the vectors at write time turns cosine similarity into a dot product, removing a square root and a division from the inner loop. And filtering on model in the SQL means a re-embedding migration can hold both generations in one table without mixing them — what happens when you change embedding models is the longer version of why that matters.

If NumPy is available, replace the loop with one matrix multiply and the same code gets one to two orders of magnitude faster, because the work moves from interpreted Python to BLAS:

import numpy as np

ids, mat = [], []
for row_id, blob in db.execute("SELECT id, embedding FROM chunks"):
    ids.append(row_id)
    mat.append(np.frombuffer(blob, dtype=np.float32))
mat = np.vstack(mat)                       # (N, d), normalised at write time

scores = mat @ np.asarray(q, dtype=np.float32)   # one BLAS call
top = np.argpartition(-scores, k)[:k]            # O(N), not O(N log N)
top = top[np.argsort(-scores[top])]
results = [(float(scores[i]), ids[i]) for i in top]

The row count where this stops working

Brute-force search is exactly N × d multiply-add operations plus a partial sort. That is the entire cost model, and it gives you a ceiling you can compute for your own hardware rather than accepting somebody else’s row count.

work = N × d   multiply-adds

d = 384:
  N =    10,000  ->   3.8 M ops
  N =   100,000  ->    38 M ops
  N = 1,000,000  ->   384 M ops

d = 1536:
  N =    10,000  ->    15 M ops
  N =   100,000  ->   154 M ops
  N = 1,000,000  -> 1,536 M ops

Assumption: throughput. A NumPy float32 matrix-vector product on one
modern core sustains roughly 2–10 G multiply-adds per second, memory
bandwidth bound rather than arithmetic bound. Pure Python is 100–1000×
slower and is not in the same conversation.

At 5 G ops/s:
  384-dim, 1M rows   -> ~77 ms
  1536-dim, 1M rows  -> ~307 ms
  1536-dim, 100k rows -> ~31 ms

Measure your own throughput — time one matrix multiply — and read off the row count that fits your latency budget. The pattern in the numbers is the useful part: up to about a hundred thousand rows, exact search in SQLite is fast enough for an interactive application, and at a million it is not, unless the query is not user-facing.

There is a second ceiling and it usually arrives first: memory. At 1536 dimensions, a million float32 vectors is 6.1 GB before any overhead, so the array-in-memory approach above stops working long before the arithmetic does. Reading the BLOBs from disk on every query converts the problem from arithmetic-bound to I/O-bound and roughly an order of magnitude worse. The full per-row byte arithmetic is in storing embeddings: types, precision and row size.

Two things buy you headroom before you have to move engines. Store float16 instead of float32 and halve both the memory and the bandwidth for a recall cost that is usually negligible. Or binary-quantise for the scan and rerank the top few hundred with the full vectors — the technique in quantising vectors, which turns the inner loop into popcounts over XORs and is roughly thirty times cheaper.

sqlite-vec, and its caveat

sqlite-vec is a loadable extension that adds a vec0 virtual table and distance functions, so the search happens in SQL rather than in your process. It is written in C with no dependencies and loads on every platform SQLite runs on, including WebAssembly.

import sqlite3, sqlite_vec

db = sqlite3.connect("app.db")
db.enable_load_extension(True)
sqlite_vec.load(db)
db.enable_load_extension(False)

db.execute("CREATE VIRTUAL TABLE vec_chunks USING vec0(embedding float[384])")
db.execute("INSERT INTO vec_chunks(rowid, embedding) VALUES (?, ?)",
           (chunk_id, pack(vec)))

rows = db.execute("""
    SELECT rowid, distance
    FROM vec_chunks
    WHERE embedding MATCH ?
      AND k = 10
    ORDER BY distance
""", (pack(query_vec),)).fetchall()

The important thing to understand about it, and the thing that decides whether it helps you: this is still exact search. It scans every vector; it is fast because the scan is in tight C with SIMD rather than in your interpreter. So it moves the ceiling derived above up by a large constant factor, but it does not change the shape of the curve. It is not an ANN index and does not claim to be.

FTS5 for the lexical half

SQLite’s FTS5 module is built into most distributions and gives you a real BM25 implementation, which Postgres core does not. That makes the hybrid retriever easier to build here than there:

CREATE VIRTUAL TABLE chunks_fts USING fts5(
  content,
  content='chunks',      -- external content table: no duplicated text
  content_rowid='id'
);

-- Keep it in sync. FTS5 external-content tables are not automatic.
CREATE TRIGGER chunks_ai AFTER INSERT ON chunks BEGIN
  INSERT INTO chunks_fts(rowid, content) VALUES (new.id, new.content);
END;
CREATE TRIGGER chunks_ad AFTER DELETE ON chunks BEGIN
  INSERT INTO chunks_fts(chunks_fts, rowid, content)
    VALUES('delete', old.id, old.content);
END;

-- bm25() returns a NEGATIVE score; smaller is better.
SELECT rowid, bm25(chunks_fts) AS score
FROM chunks_fts
WHERE chunks_fts MATCH 'index AND bloat'
ORDER BY score
LIMIT 50;

The delete trigger’s odd shape is genuine FTS5 syntax, not a typo: external-content tables are updated through a command inserted into the table itself. Get it wrong and the index silently keeps returning rows that no longer exist, which is the same class of bug as deletion that never reaches the vector index.

The pragmas that matter

PRAGMA journal_mode = WAL;        -- readers do not block the writer
PRAGMA synchronous  = NORMAL;     -- safe under WAL; FULL costs an fsync per commit
PRAGMA busy_timeout = 5000;       -- wait 5 s for a lock instead of failing
PRAGMA foreign_keys = ON;         -- off by default, per connection, every time
PRAGMA mmap_size    = 268435456;  -- 256 MB mapped; helps large BLOB reads
PRAGMA cache_size   = -64000;     -- negative means kibibytes, so 64 MB

One last operational note. A SQLite database is one file, so backup is file copy — but only if nothing is writing. Use the online backup API or VACUUM INTO '/backups/app-2026-08-04.db', which takes a consistent snapshot of a live database into a new file and compacts it on the way. Copying the file with cp while a writer is active produces a corrupt backup that restores without complaint and fails later.

What SQLITE_BUSY actually means

Every SQLite deployment that grows past one process meets this error, and the two messages it produces mean genuinely different things. Being able to tell them apart saves a great deal of time.

The mechanism behind the first one, in WAL mode: readers do not block the writer and the writer does not block readers, but there is only one writer. A second writer arriving while the first holds the write lock waits up to busy_timeout and then fails. So the number of concurrent writers you can sustain is one, and your throughput is one divided by the duration of a write transaction.

writes_per_second ≈ 1 / write_transaction_duration

  A 2 ms transaction  ->  ~500 writes/s
  A 50 ms transaction ->  ~20 writes/s

The duration is what you control, and the usual reason it is
long is that the transaction contains something that is not a
write: an HTTP call, an embedding request, a file read.

Which is the same rule as connection pooling for AI workloads in a different costume: never hold the write lock across a network call. Compute the embedding, then open the transaction, then insert, then commit. Batch inserts into one transaction rather than one transaction per row — a thousand single-row transactions is a thousand fsyncs, and the same thousand rows inside one transaction is one.

Three further things that make busy errors go away in practice: set busy_timeout on every connection, because the default is zero and a zero timeout means fail immediately; begin write transactions with BEGIN IMMEDIATE rather than plain BEGIN, which takes the write lock up front instead of discovering the conflict on the first write and forcing a retry of the whole transaction; and keep long-running analytical reads on a separate connection so they do not sit inside a transaction that later wants to write.

The last thing to know about SQLite in this role is where the file lives. A database on a network filesystem — NFS, SMB, most container volume mounts backed by network storage — is documented by the project as unsafe, because SQLite’s locking depends on file locking semantics that those filesystems implement inconsistently or not at all. The symptom is not an error; it is intermittent corruption under concurrent access, discovered much later. Keep the file on local disk, and if you need it to survive the machine, replicate it rather than sharing it.