MessageStore PostgreSQL vs SQLite for Vector Storage: Implementation Guide

MessageStore in the heurist-agent-framework delegates persistence to either PostgreSQL with pgvector for production-grade indexed similarity search, or SQLite with JSON-encoded embeddings for zero-setup local development, with both implementing the identical VectorStorageProvider interface.

The heurist-agent-framework provides a flexible MessageStore abstraction that supports multiple vector storage backends for persisting message embeddings. When building AI agents that require semantic search capabilities, choosing between PostgreSQL and SQLite vector storage determines your application's scalability, setup complexity, and query performance. This guide examines the architectural differences, implementation details, and optimal usage patterns for both backends based on the source code in core/embedding.py.

Architectural Overview of MessageStore Vector Storage

The MessageStore acts as a thin façade that delegates all persistence operations to a concrete implementation of the abstract VectorStorageProvider class. This design pattern, defined in core/embedding.py, allows seamless swapping of storage backends without modifying consumer code.

Both PostgresVectorStorage and SQLiteVectorStorage implement the same interface methods:

• initialize() – Sets up database connections and schema
• store_embedding(message_data) – Persists message metadata and vector embeddings
• find_similar(embedding, threshold, ...) – Executes cosine similarity search
• find_messages(...) – Filters messages by metadata fields
• close() – Cleans up database connections

PostgreSQL Vector Storage Implementation

Schema and Indexing Strategy

The PostgreSQL backend leverages the pgvector extension to provide native vector support. In core/embedding.py, the PostgresVectorStorage.initialize() method creates a table with a dedicated vector column type:

embedding vector(1024) NOT NULL,
original_embedding vector(1024)

The implementation automatically creates an IVF-Flat index (embedding_idx) using vector_cosine_ops to optimize similarity queries. This index enables O(log N) approximate nearest neighbor search rather than brute-force comparison.

Performance Characteristics

Vector similarity computation occurs inside the database engine using the <=> operator. The find_similar method constructs SQL queries that execute cosine similarity calculations at the storage layer, returning only filtered results to the application. This minimizes network overhead and leverages PostgreSQL's query optimizer for large datasets (millions of rows).

PostgreSQL Setup Example

from heurist_core.embedding import (
    MessageStore,
    PostgresConfig,
    PostgresVectorStorage,
    MessageData,
)

# Configure PostgreSQL connection

pg_cfg = PostgresConfig(
    host="localhost",
    port=5432,
    database="heurist",
    user="heurist_user",
    password="secret",
    table_name="messages",
)

pg_storage = PostgresVectorStorage(pg_cfg)

# Initialise the MessageStore

store = MessageStore(pg_storage)

# Store a message with embedding

store.add_message(
    MessageData(
        message="How's the market today?",
        embedding=[0.01, 0.02, 0.03],  # 1024-dim vector in production

        timestamp="2024-01-02T12:34:56Z",
        message_type="user_prompt",
        chat_id="chat-123",
    )
)

# Find similar messages (executes in PostgreSQL)

similar = store.find_similar_messages(
    embedding=[0.01, 0.02, 0.03],
    threshold=0.9,
    chat_id="chat-123",
)
print(similar)   # → list of dicts ordered by DB-computed similarity

SQLite Vector Storage Implementation

Schema and Storage Format

The SQLite backend provides a zero-configuration alternative implemented in SQLiteVectorStorage. Rather than native vector types, embeddings are serialized as JSON-encoded strings and stored in standard TEXT columns:

embedding TEXT NOT NULL,
original_embedding TEXT

This approach requires no external dependencies or database extensions, making it ideal for environments where PostgreSQL installation is impractical.

Performance Characteristics

Similarity search operates entirely in Python. The find_similar method executes a SELECT * query to load all candidate rows, deserializes the JSON embeddings, and computes cosine similarity using compute_similarity (defined in core/embedding.py). This results in O(N) complexity where N is the total row count, with all embeddings loaded into memory for comparison.

SQLite Setup Example

from heurist_core.embedding import (
    MessageStore,
    SQLiteConfig,
    SQLiteVectorStorage,
    MessageData,
)

# Configure SQLite (defaults to ./heurist.db)

sqlite_cfg = SQLiteConfig()  # Optional: db_path="custom.db"

sqlite_storage = SQLiteVectorStorage(sqlite_cfg)

# Create MessageStore

store = MessageStore(sqlite_storage)

# Add message with embedding

store.add_message(
    MessageData(
        message="Hello world",
        embedding=[0.1, 0.2, 0.3],  # List of floats

        timestamp="2024-01-01T00:00:00Z",
        message_type="user_prompt",
    )
)

# Query similar messages (computed in Python)

similar = store.find_similar_messages(
    embedding=[0.1, 0.2, 0.3],
    threshold=0.85,
    message_type="user_prompt",
)
print(similar)

Key Differences Between PostgreSQL and SQLite Backends

Feature	PostgreSQL (pgvector)	SQLite
Backend Type	Server-based relational database	File-based embedded database
Vector Storage	Native vector(1024) column type	JSON-encoded TEXT column
Similarity Index	IVF-Flat index with vector_cosine_ops	No index; brute-force scan
Search Complexity	O(log N) with index	O(N) linear scan
Execution Location	Database engine (SQL <=> operator)	Python application layer
Concurrency	Multi-process, connection pooling	Single-process optimized (check_same_thread=False allows limited concurrency)
Setup Requirements	PostgreSQL server, pgvector extension	Python standard library only
Scalability	Millions of rows, distributed workloads	Thousands to tens of thousands of rows
Typical Use Case	Production AI agents, multi-tenant services	Local development, unit testing, small bots

When to Use Which Backend

Choose SQLite when you need zero-configuration persistence for development environments, automated testing suites, or lightweight deployments where vector collections remain small (under 10,000 entries). The JSON-encoded storage eliminates infrastructure dependencies while maintaining full API compatibility with core/embedding.py.

Choose PostgreSQL with pgvector for production deployments requiring sub-second similarity search across large vector collections (100,000+ embeddings), concurrent user access, or integration with existing relational data. The native vector type and IVF-Flat index in core/embedding.py enable database-optimized cosine similarity calculations that scale horizontally with your infrastructure.

Summary

• MessageStore provides a unified interface for vector message storage through the VectorStorageProvider abstraction in core/embedding.py.
• PostgreSQL backend utilizes the pgvector extension with native vector(1024) columns and IVF-Flat indexing for O(log N) similarity search executed via the <=> SQL operator.
• SQLite backend stores embeddings as JSON-encoded TEXT and computes similarity in Python with O(N) complexity, requiring no external dependencies.
• Both implementations share identical initialization (initialize()), storage (store_embedding()), and retrieval (find_similar()) APIs, enabling seamless backend swaps without application code changes.
• Select PostgreSQL for production scale and concurrency; choose SQLite for development, testing, and low-volume deployments.

Frequently Asked Questions

Can I switch from SQLite to PostgreSQL without modifying my application code?

Yes. Both storage backends implement the identical VectorStorageProvider interface defined in core/embedding.py. You only need to change the configuration object passed to MessageStore—swap SQLiteConfig() for PostgresConfig(host=..., database=...)—and ensure your PostgreSQL instance has the pgvector extension enabled. All method calls (add_message, find_similar_messages) remain unchanged.

How does vector similarity search performance compare between PostgreSQL and SQLite?

PostgreSQL with pgvector executes similarity searches in O(log N) time using the IVF-Flat index and the <=> cosine distance operator, enabling sub-second queries across millions of embeddings. SQLite loads all embeddings into memory and computes similarity in Python with O(N) linear complexity, making it suitable for collections under 10,000 entries but increasingly slow as data grows. For production workloads requiring low-latency semantic search, PostgreSQL is the recommended backend.

What are the storage requirements for each backend?

SQLite stores embeddings as JSON-encoded strings in standard TEXT columns, resulting in larger per-row storage overhead due to text serialization (e.g., "[0.1, 0.2, ...]" requires more bytes than binary floats). PostgreSQL uses the native vector(1024) type from pgvector, which stores floats in a compact binary format (typically 4 bytes per dimension), significantly reducing storage footprint for large collections. Additionally, PostgreSQL's IVF-Flat index requires extra disk space for the index structure, but this trade-off enables faster query performance.

Is pgvector required for the PostgreSQL backend?

Yes. The PostgresVectorStorage class in core/embedding.py explicitly depends on the pgvector extension being installed and enabled in your PostgreSQL database. During initialization (initialize()), the code executes CREATE EXTENSION IF NOT EXISTS vector and defines columns with the vector(1024) type. Without pgvector, the schema creation will fail with a type error. You must install pgvector on your PostgreSQL server before instantiating the PostgreSQL storage backend.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}