SQLite vs PostgreSQL Backends for memU Deployment: Architecture and Performance Comparison

SQLite provides a zero-configuration file-based store ideal for local development and small-scale deployments, while PostgreSQL with pgvector delivers production-grade concurrency, scalability, and indexed vector search for high-throughput workloads.

When deploying the NevaMind-AI/memU memory service, selecting the appropriate database backend is critical for performance and scalability. Both SQLite and PostgreSQL backends for memU deployment implement identical repository interfaces, but they differ fundamentally in concurrency handling, vector search capabilities, and operational complexity.

Deployment Model and Configuration

The backend selection occurs through the metadata_store.provider configuration field, resolved via the factory pattern in src/memu/database/factory.py.

SQLite operates as a single-file database requiring zero external infrastructure. It uses connection strings like sqlite:///path/to/db.sqlite or sqlite:///:memory: for ephemeral testing. This makes it optimal for edge devices, personal bots, and prototyping scenarios where portability matters.

PostgreSQL requires a running server instance (Docker, cloud managed service, or self-hosted) and accepts DSNs formatted as postgresql+psycopg://user:pass@host:5432/dbname. When configured with provider: "postgres", memU automatically enables the pgvector extension for optimized vector operations.

Schema Initialization and Management

Schema creation differs significantly between the two backends according to the source implementation.

In src/memu/database/sqlite/sqlite.py, tables are created automatically on first use via SQLModel.metadata.create_all. This auto-creation eliminates migration overhead for single-user scenarios.

Conversely, src/memu/database/postgres/postgres.py implements a migration-driven approach controlled by the ddl_mode parameter. Valid modes include:

• create – builds schema from scratch
• upgrade – runs pending Alembic migrations
• skip – assumes existing schema

Vector Search Architecture

Vector search implementation represents the most significant functional difference between backends.

Brute-Force Search in SQLite

SQLite lacks native vector types. As documented in docs/sqlite.md, embeddings store as JSON text columns, and memU performs brute-force cosine similarity calculations in memory. This approach works for datasets up to approximately 100,000 items but becomes computationally expensive at scale.

Indexed pgvector Search in PostgreSQL

When provider: "pgvector" is configured (automatically set for PostgreSQL deployments), memU leverages the pgvector extension for indexed nearest-neighbor lookups. This enables millisecond-scale similarity searches across millions of embeddings, essential for production semantic search workloads.

Concurrency and Scalability Characteristics

SQLite enforces a single-writer policy; while multiple readers execute concurrently, write operations serialize. Heavy write loads trigger "Database Locked" errors, limiting throughput for multi-user applications.

PostgreSQL provides full ACID-compliant concurrency with row-level locking. It handles hundreds of simultaneous client connections without contention, supporting multi-tenant SaaS deployments and high-frequency write operations.

Scalability limits:

• SQLite: Practical ceiling around 100,000 items due to memory-based vector scanning and write serialization
• PostgreSQL: Designed for millions of items with disk-based vector indexes and horizontal scaling capabilities

Configuration Examples

SQLite File-Based Setup

Instantiate via build_sqlite_database in src/memu/database/sqlite/sqlite.py:

from memu.app import MemoryService

service = MemoryService(
    llm_profiles={"default": {"api_key": "YOUR_KEY"}},
    database_config={
        "metadata_store": {
            "provider": "sqlite",
            "dsn": "sqlite:///./data/memu.db",
        },
        # Vector search defaults to "bruteforce"

    },
)

PostgreSQL with pgvector Setup

Instantiate via build_postgres_database in src/memu/database/postgres/postgres.py:

from memu.app import MemoryService

service = MemoryService(
    llm_profiles={"default": {"api_key": "YOUR_KEY"}},
    database_config={
        "metadata_store": {
            "provider": "postgres",
            "dsn": "postgresql+psycopg://postgres:pwd@localhost:5432/memu",
            "ddl_mode": "create",
        },
        # Vector index auto-configures to "pgvector"

    },
)

Migration from SQLite to PostgreSQL

For production migration, docs/sqlite.md provides a reference implementation using the repository layer. Since both backends expose identical interfaces (ResourceRepo, MemoryItemRepo), data transfer requires no schema transformation:

from memu.database.sqlite import build_sqlite_database
from memu.database.postgres import build_postgres_database
from memu.app.settings import DatabaseConfig
from pydantic import BaseModel

class UserScope(BaseModel):
    user_id: str

# Source

sqlite_cfg = DatabaseConfig(metadata_store={"provider": "sqlite", "dsn": "sqlite:///memu.db"})
sqlite_db = build_sqlite_database(config=sqlite_cfg, user_model=UserScope)
sqlite_db.load_existing()

# Target

postgres_cfg = DatabaseConfig(metadata_store={"provider": "postgres", "dsn": "postgresql://..."})
postgres_db = build_postgres_database(config=postgres_cfg, user_model=UserScope)

# Migration loop

for rid, res in sqlite_db.resources.items():
    postgres_db.resource_repo.create_resource(
        url=res.url,
        modality=res.modality,
        local_path=res.local_path,
        caption=res.caption,
        embedding=res.embedding,
        user_data={"user_id": getattr(res, "user_id", None)},
    )

Backup strategies differ: SQLite requires only shutil.copy("memu.db", "backup.db"), while PostgreSQL utilizes pg_dump and psql for enterprise backup workflows.

Summary

• SQLite backends offer zero-configuration, single-file deployment with automatic schema creation, suitable for development and small-scale deployments under 100,000 items
• PostgreSQL backends provide production-grade concurrency, migration management via ddl_mode, and indexed vector search through pgvector for million-scale workloads
• Both implement identical repository interfaces in src/memu/database/, ensuring seamless backend swapping without application code changes
• Vector search defaults to brute-force for SQLite and indexed pgvector for PostgreSQL, configured automatically based on metadata_store.provider

Frequently Asked Questions

When should I use SQLite over PostgreSQL for memU?

Use SQLite for local development, single-user applications, edge deployments without database infrastructure, or prototypes handling fewer than 100,000 memory items. Choose PostgreSQL for multi-user production services, high-write-throughput scenarios, or when sub-second vector similarity search across large datasets is required.

How does memU handle vector search without pgvector?

When using SQLite, memU stores embeddings as JSON text in standard columns and performs brute-force cosine similarity calculations in Python memory. This occurs in src/memu/database/sqlite/sqlite.py and requires no external dependencies, though it scales poorly beyond tens of thousands of vectors.

Can I migrate existing SQLite data to PostgreSQL without losing embeddings?

Yes. Since both backends implement the same repository interfaces (ResourceRepo, MemoryItemRepo), you can extract embeddings from the SQLite store via build_sqlite_database and recreate them in PostgreSQL using build_postgres_database without format conversion. The docs/sqlite.md file contains the official migration script template.

What connection string format does memU require for PostgreSQL?

MemU expects SQLAlchemy-compatible DSNs formatted as postgresql+psycopg://user:password@host:port/database. When using the pgvector extension specifically, the standard postgresql:// scheme also functions. The SQLiteStore and PostgresStore classes in their respective src/memu/database/ subdirectories parse these connection strings during initialization.

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