Production Agent Memory: SQLite Hybrid for Long Context

TL;DR

• SQLite+FTS5 hits sub-1ms latency for 4,300+ memories on local NVMe
• Summary-first retrieval cuts tokens substantially in long conversations (Mem0 self-reports >90% token savings on standardized benchmarks)
• SQLite holds up to roughly 100K entries with frequent updates; TiDB Cloud is the documented scale-up path

Your customer support agent just lost the conversation thread. After thirty messages tracking a complex refund case, it treats the customer like a stranger. Context windows overflow—and token costs explode. For production agent builders, memory isn’t optional; it’s the difference between a tool users trust and one they abandon.

Most teams default to Pinecone or Weaviate for vector storage. The real value isn’t cloud hosting: it’s hybrid architecture. SQLite with sqlite-vec delivers sub-millisecond query latency for thousands of memories while costing near-zero in infrastructure. This article explores production-grade patterns for implementing agent memory using SQLite as a hybrid database: structured episodic storage married to semantic vector search, with clear scaling boundaries and migration paths when you hit SQLite’s limits.

Why SQLite Works for Production Agent Memory

The skepticism is understandable—SQLite carries a prototyping reputation. Yet production data tells a different story. SQLite with FTS5 achieves sub-1ms query latency for 4,300+ memories on local NVMe; cloud vector databases clock anywhere from 25-50ms (best-case dedicated pods) up to 120-500ms p95 depending on tier and scale [1].

Three factors make this possible. First: SQLite’s single-file portability eliminates network overhead. Second: extensions like sqlite-vec add vector search primitives directly to the database engine—no separate service required [2]. Third: hybrid architectures use SQLite for what it does best (structured episodic storage, session state) while delegating only the vector math to specialized code paths.

Local SSD access versus network round-trip explains the performance gap. Even a fast managed vector database introduces 20-40ms of network latency. SQLite operates at disk speed—sub-millisecond for random reads on NVMe, single-digit milliseconds on SATA SSD.

For agent memory workloads where every millisecond in the hot path directly impacts user experience, this difference compounds across multiple memory retrievals per request. The savings add up fast when your agent queries episodic, semantic, and working memory in a single turn.

The cost delta matters for indie makers. A Pinecone pod starts at $70 monthly; for an agent with modest memory needs, that’s infrastructure spend without proportional value. SQLite’s zero external dependency model means edge deployment becomes viable—your agent runs on a Raspberry Pi with full memory persistence [1].

sqlite-vec: Native Vector Search Inside SQLite

The sqlite-vec extension transforms SQLite from a relational store into a hybrid database capable of semantic search. It implements vector operations as virtual tables; you query cosine similarity with standard SQL syntax [2].

This tight integration eliminates serialization overhead and keeps your data in one place.

graph TD
  A[SQLite Table] --> B[Virtual Table Interface]
  B --> C[Vector Index (HNSW)]
  C --> D[Approximate Nearest Neighbor Search]
  D --> E[Agent Query Result]

The virtual table approach is elegant. Instead of bolting a separate vector store alongside SQLite, sqlite-vec registers new table types that handle vector operations internally. Your SQL looks normal; behind the scenes, the extension manages embedding storage, index building, and similarity computation.

Dimension support varies by implementation. TiDB Cloud supports up to 16,383 dimensions with HNSW vector index type—matching modern embedding models like text-embedding-3-large at 3,072 dimensions [10].

-- Create virtual table for vector storage
CREATE VIRTUAL TABLE memories USING vec0(
  id INTEGER PRIMARY KEY,
  embedding float[1536],
  content TEXT,
  session_id TEXT,
  created_at DATETIME
);

-- Hybrid query: semantic similarity within session
SELECT content, distance
FROM memories
WHERE session_id = 'sess_abc123'
  AND embedding MATCH :query_embedding
  AND k = 5
ORDER BY distance;

The dual-table pattern separates concerns cleanly. Your content table holds raw text, timestamps, and metadata; the virtual vector table manages embeddings and similarity operations. This design lets you rebuild vector indices without touching source data—a production essential when you need to re-embed with a new model [3].

Hybrid Memory Architecture: Three Tiers That Work

Production agents need more than one database. The working/episodic/semantic division maps cleanly to different storage characteristics. Working memory (active conversation context) belongs in Redis or in-process dictionaries for microsecond access. Episodic storage (conversation history, session state) fits SQLite’s structured query model. Semantic retrieval for cross-session recall needs vector search [1].

Hierarchical loading is where the token savings compound. Retrieve summaries first; full episodic data follows only when needed [1][4]. Instead of dumping entire conversation history into the context window, the agent loads a distilled summary. Mem0’s published benchmarks (self-reported on standardized datasets) report over 90% token savings versus naive full-memory injection [1] — your real numbers depend on conversation length and summary quality.

Consider a customer support conversation spanning fifty messages. Full context would consume 8,000+ tokens. A hierarchical approach sends only a 200-token summary plus the most recent five exchanges (another 800 tokens). Only when the user asks a specific question about an earlier part of the conversation does the agent fetch additional detail.

The savings multiply across thousands of sessions. Dollar savings compound too: at GPT-4o pricing, sending 8,000 tokens versus 1,000 tokens per request changes your cost structure entirely.

Relevance scoring should combine multiple signals; recency alone fails. Important memories should persist while trivia fades. A composite score weighting recency, importance (extracted via LLM), and semantic similarity to current context outperforms any single factor. Hmem demonstrates this pattern in practice: verbatim events stored in a messages table, derived tables for entities and summaries, with custom ranking for retrieval [1].

Framework-Specific Implementation Patterns

Abstract memory interfaces are nice. Concrete implementations matter—and here’s how major frameworks handle SQLite persistence.

Agno provides the most turnkey solution. Its SqliteDb class handles automatic memory extraction, user scoping, and session management out of the box [5]. You instantiate with a database path; Agno manages schema, indices, and cleanup. For rapid prototyping that survives restarts, this is the fastest path.

LangChain takes a modular approach. RunnableWithMessageHistory wraps any chain with persistent storage; you wire the SQLite backend yourself via SQLChatMessageHistory [6].

This flexibility comes with boilerplate: you’re responsible for schema decisions, connection pooling, and session cleanup. Teams invested in LangChain’s ecosystem find the integration seamless. Greenfield projects face setup overhead that may not justify the abstraction.

OpenAI Agents SDK bakes SQLiteSession directly into the framework [7]. WAL mode lets multiple agents share the same database file without write contention—and you don’t have to configure it.

Session management is automatic. Persistence requires only a session ID.

Pydantic AI takes a different approach entirely. The framework focuses on type-safe orchestration, leaving persistence as an exercise for the developer—an intentional design choice that prioritizes explicit, composable components over built-in magic, which is why teams should budget time for custom memory plumbing [8].

When SQLite Hits Its Limits: Migration Strategy

SQLite has real scaling boundaries. As a practical guideline, write contention becomes a real constraint somewhere around 100K entries with frequent updates [1]; PingCAP’s analysis flags qualitative tells — sync logic accumulating, sessions that cannot see each other’s state — as upgrade triggers [9]. Single-writer serialization is the architectural cap, not a fixed ops/second number.

Know your workload profile. Read-heavy agents with occasional writes (customer support, coding assistants) scale further. Write-heavy agents logging every thought hit the wall faster.

WAL mode improves concurrency. Crucially, it doesn’t eliminate the single-writer limitation. TiDB Cloud offers the cleanest migration path for SQLite-pushing workloads [10]. It supports up to 16,383 dimensions and HNSW vector index type for hybrid SQL+vector queries—matching sqlite-vec’s capabilities at scale. Unified SQL+vector storage means your query patterns translate directly; you’re just swapping the connection string.

Migration triggers should be metrics-driven: latency p95 exceeding your SLO, write queue depth growing on ingestion, or backup times crossing into business hours. Don’t migrate preemptively; SQLite at its limits still outperforms many cloud alternatives on latency.

Anti-Fragile Design with Markdown Source-of-Truth

The most resilient memory architecture treats SQLite as an index, not a source. Store canonical memory in Markdown files with structured frontmatter; SQLite indexes for fast retrieval. If the database corrupts or you need to migrate, regenerate from the Markdown source [1].

This pattern sacrifices some write performance. The tradeoff is resilience. For agents where memory loss is unacceptable, it’s worth it.

Production Schema Design for Agent Memory

Schema decisions made early reverberate. A well-designed SQLite memory schema supports efficient filtering, relevance scoring, and eviction. The dual-table pattern remains foundational: one table for content storage, one virtual table for vector operations.

-- Episodic storage table
CREATE TABLE conversations (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  session_id TEXT NOT NULL,
  role TEXT CHECK(role IN ('user', 'assistant', 'system')),
  content TEXT NOT NULL,
  summary TEXT,              -- LLM-generated distillation
  importance_score REAL,     -- 0.0 to 1.0 from LLM
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  accessed_at DATETIME,      -- for LRU eviction
  metadata JSON              -- flexible key-value storage
);

-- Indices for common query patterns
CREATE INDEX idx_session_time ON conversations(session_id, created_at);
CREATE INDEX idx_importance ON conversations(importance_score DESC);

-- Virtual table for semantic search (sqlite-vec)
CREATE VIRTUAL TABLE conversation_vectors USING vec0(
  rowid INTEGER PRIMARY KEY,
  embedding float[1536],
  +session_id TEXT
);

Metadata columns should be typed for filtering (session_id, role, created_at) with a JSON column for extensibility. You’ll inevitably need to store framework-specific fields that don’t fit a rigid schema. Index on access patterns—session_id + created_at for chronological retrieval, importance_score for priority ordering.

The summary field deserves attention. Hierarchical loading depends on having concise summaries to fetch before full content. Generate these asynchronously: fire a background task to distill long messages while the conversation continues.

Storage is cheap. Waiting 500ms to generate a summary on retrieval is expensive.

Practical Takeaways

1. Use the dual-table pattern: structured content in a standard table, vectors in a sqlite-vec virtual table. This separation lets you rebuild indices independently and optimize each storage layer.
2. Implement hierarchical loading: fetch summaries first, then drill to full episodic data only when the conversation requires it. The compound savings can be substantial — Mem0’s self-reported benchmarks show over 90% token reduction versus naive full-memory injection [1].
3. Plan your migration trigger: practical guideline is around 100K entries with frequent updates [1]. Monitor write queue depth, latency p95 against your SLO, and backup duration; migrate to TiDB Cloud or PostgreSQL when metrics cross thresholds rather than preemptively.
4. Generate summaries asynchronously during ingestion, not at retrieval time. Storage is cheap; latency from on-the-fly summarization is expensive.
5. Consider Markdown source-of-truth with SQLite as index for anti-fragile memory that survives corruption and migrates cleanly.

Conclusion

The next wave of agent applications won’t be defined by model capabilities alone, but by how effectively they remember. As local inference chips shrink and embedding costs fall below a dollar per million tokens, agents with persistent SQLite memory will operate continuously for months—accumulating context that fundamentally changes their utility. We are moving toward agents that don’t just respond to prompts; they evolve through sustained relationships with users and their data.

Frequently Asked Questions

How does query performance compare between sqlite-vec and dedicated vector databases?

For datasets under 100K entries, sqlite-vec delivers 1-10ms query latency—comparable to or faster than cloud alternatives once network round-trip is factored in. Pinecone reports p95 latency under 120ms on performance-optimized (p1) pods and under 500ms on storage-optimized (s1) pods at very large scale; published comparison benchmarks have shown 25-50ms in best-case dedicated configurations [1]. SQLite wins on raw speed for modest-scale deployments. At larger scales with millions of vectors, dedicated vector databases with distributed indexing pull ahead significantly due to horizontal scaling and specialized hardware acceleration that SQLite cannot match.

Can I use SQLite memory with streaming LLM responses?

Yes. Fetch memory before streaming; write asynchronously after.

What’s the maximum vector dimension sqlite-vec supports?

TiDB supports up to 16,383 dimensions with HNSW vector index type [10]. This covers modern embedding models including OpenAI’s text-embedding-3-large (3,072 dimensions) and likely future models. Production data on the absolute upper limit for sqlite-vec specifically is still emerging in the community; the extension is actively developed and dimension limits may shift with new releases.

How do I handle memory eviction when the database grows too large?

Implement composite scoring combining recency, importance, and access frequency. SQLite’s throughput supports background eviction jobs scanning and removing low-scored entries. For stricter limits, use partitioned tables per user or session and drop entire partitions when they age out.

Is this approach suitable for multi-tenant SaaS applications?

SQLite works for multi-tenant scenarios with one database file per tenant. The single-writer limitation becomes a per-tenant constraint, which scales horizontally. For high-volume tenants exceeding 1K concurrent users, migrate individual tenants to PostgreSQL or TiDB as they grow.

Sources

Image Credits

• Cover photo by Conny Schneider on Unsplash