# MessageStore PostgreSQL vs SQLite for Vector Storage: Implementation Guide

> Explore MessageStore for vector storage. Compare PostgreSQL with pgvector for production and SQLite for local development. Understand implementation differences and usage patterns.

- Repository: [Heurist/heurist-agent-framework](https://github.com/heurist-network/heurist-agent-framework)
- Tags: comparison
- Published: 2026-03-03

---

**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`](https://github.com/heurist-network/heurist-agent-framework/blob/main/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`](https://github.com/heurist-network/heurist-agent-framework/blob/main/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`](https://github.com/heurist-network/heurist-agent-framework/blob/main/core/embedding.py), the `PostgresVectorStorage.initialize()` method creates a table with a dedicated `vector` column type:

```sql
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

```python
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:

```sql
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`](https://github.com/heurist-network/heurist-agent-framework/blob/main/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

```python
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`](https://github.com/heurist-network/heurist-agent-framework/blob/main/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`](https://github.com/heurist-network/heurist-agent-framework/blob/main/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`](https://github.com/heurist-network/heurist-agent-framework/blob/main/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`](https://github.com/heurist-network/heurist-agent-framework/blob/main/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`](https://github.com/heurist-network/heurist-agent-framework/blob/main/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.