PostgreSQL¶

Production backend using asyncpg, pgvector for vector search, and PostgreSQL's built-in full-text search.

When to use¶

Multi-process or multi-server deployments
Production workloads
When you need connection pooling
When you already have a Postgres infrastructure

Setup¶

Install with the postgres extra:

Bash

uv add memvee[postgres]
# or: pip install memvee[postgres]

This installs asyncpg and pgvector.

Your PostgreSQL server needs the pgvector extension. Most managed providers (Supabase, Neon, AWS RDS) include it. For self-hosted:

SQL

CREATE EXTENSION IF NOT EXISTS vector;

memv runs this on first connection, but the database role needs superuser privilege (or pg_extension_owner membership on PostgreSQL 15+). Most managed providers (Supabase, Neon) pre-install pgvector and allow non-superuser roles to enable it.

Usage¶

Pass a PostgreSQL connection URL as db_url:

Python

from memv import Memory
from memv.embeddings import OpenAIEmbedAdapter
from memv.llm import PydanticAIAdapter

memory = Memory(
    db_url="postgresql://user:password@localhost:5432/mydb",
    embedding_client=OpenAIEmbedAdapter(),
    llm_client=PydanticAIAdapter("openai:gpt-4o-mini"),
)

async with memory:
    await memory.add_exchange(...)

memv detects the backend from the URL prefix (postgresql:// or postgres://). You can also set it explicitly:

Python

from memv import Memory, MemoryConfig

config = MemoryConfig(backend="postgres", db_url="postgresql://...")
memory = Memory(config=config, embedding_client=embedder, llm_client=llm)

How it works¶

Component	Implementation
MessageStore	Regular SQL tables
EpisodeStore	SQL + `jsonb`
KnowledgeStore	SQL + `jsonb`, bi-temporal columns
VectorIndex	`pgvector` with HNSW index, L2 distance
TextIndex	`tsvector` generated column + GIN index

Unlike SQLite, pgvector and tsvector support WHERE clauses directly, so memv filters by user in the same query as the search.

Connection pooling¶

memv creates a single asyncpg.Pool and shares it across all stores. The pool is created when you call memory.open() (or enter the async with block) and closed on memory.close().

Schema¶

Tables are created on first open(). All tables use CREATE TABLE IF NOT EXISTS, so multiple instances can share the same database.

Table	Purpose
`messages`	Raw conversation messages
`episodes`	Grouped message episodes
`semantic_knowledge`	Extracted knowledge with bi-temporal fields
`vec_knowledge`	Vector embeddings (pgvector)
`fts_knowledge`	Full-text search index (tsvector)

Docker quickstart¶

Bash

docker run -d \
  --name memv-postgres \
  -e POSTGRES_USER=memv \
  -e POSTGRES_PASSWORD=memv \
  -e POSTGRES_DB=memv \
  -p 5432:5432 \
  pgvector/pgvector:pg17

Then:

Python

memory = Memory(
    db_url="postgresql://memv:memv@localhost:5432/memv",
    embedding_client=embedder,
    llm_client=llm,
)