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

> Compare SQLite and PostgreSQL for memU deployment. Discover SQLite's ease for local use and PostgreSQL's power for scalable, high-performance vector search.

- Repository: [NevaMind AI/memU](https://github.com/nevamind-ai/memu)
- Tags: performance
- Published: 2026-02-19

---

**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](https://github.com/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`](https://github.com/NevaMind-AI/memU/blob/main/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`](https://github.com/NevaMind-AI/memU/blob/main/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`](https://github.com/NevaMind-AI/memU/blob/main/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`](https://github.com/NevaMind-AI/memU/blob/main/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`](https://github.com/NevaMind-AI/memU/blob/main/src/memu/database/sqlite/sqlite.py):

```python
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`](https://github.com/NevaMind-AI/memU/blob/main/src/memu/database/postgres/postgres.py):

```python
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`](https://github.com/NevaMind-AI/memU/blob/main/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:

```python
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`](https://github.com/NevaMind-AI/memU/blob/main/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`](https://github.com/NevaMind-AI/memU/blob/main/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.