import json import logging import re from contextlib import contextmanager from typing import Any, List, Optional from urllib.parse import parse_qsl, urlencode, urlsplit from pydantic import BaseModel # Try to import psycopg (psycopg3) first, then fall back to psycopg2 try: from psycopg import sql from psycopg.types.json import Json from psycopg_pool import ConnectionPool PSYCOPG_VERSION = 3 logger = logging.getLogger(__name__) logger.info("Using psycopg (psycopg3) with ConnectionPool for PostgreSQL connections") except ImportError: try: from psycopg2 import sql from psycopg2.extras import Json, execute_values from psycopg2.pool import ThreadedConnectionPool as ConnectionPool PSYCOPG_VERSION = 2 logger = logging.getLogger(__name__) logger.info("Using psycopg2 with ThreadedConnectionPool for PostgreSQL connections") except ImportError: raise ImportError( "Neither 'psycopg' nor 'psycopg2' library is available. " "Please install one of them using 'pip install psycopg[pool]' or 'pip install psycopg2'" ) from mem0.vector_stores.base import VectorStoreBase logger = logging.getLogger(__name__) OPERATOR_SQL_MAP = { "eq": ("payload->>%s = %s", False), "ne": ("payload->>%s != %s", False), "gt": ("(payload->>%s)::numeric > %s", True), "gte": ("(payload->>%s)::numeric >= %s", True), "lt": ("(payload->>%s)::numeric < %s", True), "lte": ("(payload->>%s)::numeric <= %s", True), "in": ("payload->>%s = ANY(%s)", False), "nin": ("NOT (payload->>%s = ANY(%s))", False), "contains": ("payload->>%s LIKE %s", False), "icontains": ("payload->>%s ILIKE %s", False), } def _build_filter_conditions(filters): """Translate a processed filter dict into SQL WHERE fragments and parameter list.""" conditions = [] params = [] if not filters: return conditions, params for key, value in filters.items(): if key == "$or": or_groups = [] for or_filter in value: sub_conds, sub_params = _build_filter_conditions(or_filter) if sub_conds: or_groups.append("(" + " AND ".join(sub_conds) + ")") params.extend(sub_params) if or_groups: conditions.append("(" + " OR ".join(or_groups) + ")") continue if key == "$not": not_groups = [] for not_filter in value: sub_conds, sub_params = _build_filter_conditions(not_filter) if sub_conds: not_groups.append("(" + " AND ".join(sub_conds) + ")") params.extend(sub_params) if not_groups: conditions.append("NOT (" + " OR ".join(not_groups) + ")") continue if value == "*": conditions.append("payload ? %s") params.append(key) continue if isinstance(value, dict): for op, op_value in value.items(): if op not in OPERATOR_SQL_MAP: raise ValueError(f"Unsupported filter operator: {op}") template, is_numeric = OPERATOR_SQL_MAP[op] if op in ("in", "nin"): str_list = [str(v) for v in op_value] conditions.append(template) params.extend([key, str_list]) elif op in ("contains", "icontains"): escaped = str(op_value).replace("\\", "\\\\").replace("%", "\\%").replace("_", "\\_") conditions.append(template + " ESCAPE '\\'") params.extend([key, f"%{escaped}%"]) else: conditions.append(template) if is_numeric: params.extend([key, float(op_value)]) else: params.extend([key, str(op_value)]) elif isinstance(value, list): conditions.append("payload->>%s = ANY(%s)") params.extend([key, [str(v) for v in value]]) else: conditions.append("payload->>%s = %s") if isinstance(value, bool): params.extend([key, json.dumps(value)]) else: params.extend([key, str(value)]) return conditions, params def _with_sslmode(connection_string: str, sslmode: str) -> str: """Add or replace sslmode in URI and keyword conninfo strings. Keyword conninfo values are assumed not to contain nested ``sslmode=`` substrings, such as inside an ``options`` value. """ if "://" in connection_string: parsed = urlsplit(connection_string) query = [(key, value) for key, value in parse_qsl(parsed.query, keep_blank_values=True) if key != "sslmode"] query.append(("sslmode", sslmode)) return parsed._replace(query=urlencode(query)).geturl() if re.search(r"(^|\s)sslmode=", connection_string): return re.sub(r"(^|\s)sslmode=\S+", lambda match: f"{match.group(1)}sslmode={sslmode}", connection_string) return f"{connection_string} sslmode={sslmode}" class OutputData(BaseModel): id: Optional[str] score: Optional[float] payload: Optional[dict] class PGVector(VectorStoreBase): def __init__( self, dbname, collection_name, embedding_model_dims, user, password, host, port, diskann, hnsw, minconn=1, maxconn=5, sslmode=None, connection_string=None, connection_pool=None, ): """ Initialize the PGVector database. Args: dbname (str): Database name collection_name (str): Collection name embedding_model_dims (int): Dimension of the embedding vector user (str): Database user password (str): Database password host (str, optional): Database host port (int, optional): Database port diskann (bool, optional): Use DiskANN for faster search hnsw (bool, optional): Use HNSW for faster search minconn (int): Minimum number of connections to keep in the connection pool maxconn (int): Maximum number of connections allowed in the connection pool sslmode (str, optional): SSL mode for PostgreSQL connection (e.g., 'require', 'prefer', 'disable') connection_string (str, optional): PostgreSQL connection string (overrides individual connection parameters) connection_pool (Any, optional): psycopg2 connection pool object (overrides connection string and individual parameters) """ self.collection_name = collection_name self.use_diskann = diskann self.use_hnsw = hnsw self.embedding_model_dims = embedding_model_dims self.connection_pool = None self._collection_ensured = False # Connection setup with priority: connection_pool > connection_string > individual parameters if connection_pool is not None: # Use provided connection pool self.connection_pool = connection_pool elif connection_string: if sslmode: connection_string = _with_sslmode(connection_string, sslmode) else: connection_string = f"postgresql://{user}:{password}@{host}:{port}/{dbname}" if sslmode: connection_string = _with_sslmode(connection_string, sslmode) if self.connection_pool is None: if PSYCOPG_VERSION == 3: # open=False avoids blocking when DB DNS is not yet resolvable (e.g. Docker startup) self.connection_pool = ConnectionPool( conninfo=connection_string, min_size=minconn, max_size=maxconn, open=False, ) self.connection_pool.open(wait=False) else: # psycopg2 ThreadedConnectionPool self.connection_pool = ConnectionPool(minconn=minconn, maxconn=maxconn, dsn=connection_string) def _ensure_collection(self): if self._collection_ensured: return collections = self.list_cols() if self.collection_name not in collections: self.create_col() self._collection_ensured = True @contextmanager def _get_cursor(self, commit: bool = False): """ Unified context manager to get a cursor from the appropriate pool. Auto-commits or rolls back based on exception, and returns the connection to the pool. """ if PSYCOPG_VERSION == 3: # psycopg3 auto-manages commit/rollback and pool return with self.connection_pool.connection() as conn: with conn.cursor() as cur: try: yield cur if commit: conn.commit() except Exception: conn.rollback() logger.error("Error in cursor context (psycopg3)", exc_info=True) raise else: # psycopg2 manual getconn/putconn conn = self.connection_pool.getconn() cur = conn.cursor() try: yield cur if commit: conn.commit() except Exception as exc: conn.rollback() logger.error(f"Error occurred: {exc}") raise exc finally: cur.close() self.connection_pool.putconn(conn) def _col(self) -> "sql.Identifier": """Return a safely-quoted SQL identifier for the collection table.""" return sql.Identifier(self.collection_name) def create_col(self) -> None: """ Create a new collection (table in PostgreSQL). Will also initialize vector search index if specified. """ with self._get_cursor(commit=True) as cur: cur.execute("CREATE EXTENSION IF NOT EXISTS vector") cur.execute( sql.SQL(""" CREATE TABLE IF NOT EXISTS {} ( id UUID PRIMARY KEY, vector vector({}), payload JSONB ); """).format(self._col(), sql.Literal(self.embedding_model_dims)) ) if self.use_diskann and self.embedding_model_dims < 2000: cur.execute("SELECT * FROM pg_extension WHERE extname = 'vectorscale'") if cur.fetchone(): # Create DiskANN index if extension is installed for faster search cur.execute( sql.SQL(""" CREATE INDEX IF NOT EXISTS {} ON {} USING diskann (vector); """).format( sql.Identifier(f"{self.collection_name}_diskann_idx"), self._col(), ) ) elif self.use_hnsw: cur.execute( sql.SQL(""" CREATE INDEX IF NOT EXISTS {} ON {} USING hnsw (vector vector_cosine_ops) """).format( sql.Identifier(f"{self.collection_name}_hnsw_idx"), self._col(), ) ) cur.execute( sql.SQL(""" CREATE INDEX IF NOT EXISTS {} ON {} USING gin(to_tsvector('simple', payload->>'text_lemmatized')); """).format( sql.Identifier(f"{self.collection_name}_text_lemmatized_idx"), self._col(), ) ) def insert(self, vectors: list[list[float]], payloads=None, ids=None) -> None: self._ensure_collection() logger.info(f"Inserting {len(vectors)} vectors into collection {self.collection_name}") json_payloads = [json.dumps(payload) for payload in payloads] data = [(id, vector, payload) for id, vector, payload in zip(ids, vectors, json_payloads)] if PSYCOPG_VERSION == 3: with self._get_cursor(commit=True) as cur: cur.executemany( sql.SQL("INSERT INTO {} (id, vector, payload) VALUES (%s, %s, %s)").format(self._col()), data, ) else: with self._get_cursor(commit=True) as cur: execute_values( cur, sql.SQL("INSERT INTO {} (id, vector, payload) VALUES %s").format(self._col()), data, ) def search( self, query: str, vectors: list[float], top_k: Optional[int] = 5, filters: Optional[dict] = None, ) -> List[OutputData]: """ Search for similar vectors. Args: query (str): Query. vectors (List[float]): Query vector. top_k (int, optional): Number of results to return. Defaults to 5. filters (Dict, optional): Filters to apply to the search. Defaults to None. Returns: list: Search results. """ self._ensure_collection() filter_conditions, filter_params = _build_filter_conditions(filters) filter_clause = sql.SQL("WHERE " + " AND ".join(filter_conditions)) if filter_conditions else sql.SQL("") with self._get_cursor() as cur: cur.execute( sql.SQL(""" SELECT id, vector <=> %s::vector AS distance, payload FROM {} {} ORDER BY distance LIMIT %s """).format(self._col(), filter_clause), (vectors, *filter_params, top_k), ) results = cur.fetchall() return [OutputData(id=str(r[0]), score=max(0.0, 1.0 - float(r[1])), payload=r[2]) for r in results] def keyword_search(self, query, top_k=5, filters=None): """ Search using PostgreSQL full-text search on lemmatized text. Args: query (str): The search query text. top_k (int, optional): Number of results to return. Defaults to 5. filters (dict, optional): Filters to apply to the search. Defaults to None. Returns: List[OutputData]: Search results ranked by text relevance. """ self._ensure_collection() filter_conditions, filter_params = _build_filter_conditions(filters) filter_clause = sql.SQL("AND " + " AND ".join(filter_conditions)) if filter_conditions else sql.SQL("") try: with self._get_cursor() as cur: cur.execute( sql.SQL(""" SELECT id, ts_rank_cd(to_tsvector('simple', payload->>'text_lemmatized'), plainto_tsquery('simple', %s)) AS score, payload FROM {} WHERE to_tsvector('simple', payload->>'text_lemmatized') @@ plainto_tsquery('simple', %s) {} ORDER BY score DESC LIMIT %s """).format(self._col(), filter_clause), (query, query, *filter_params, top_k), ) results = cur.fetchall() return [OutputData(id=str(r[0]), score=float(r[1]), payload=r[2]) for r in results] except Exception as e: logger.debug(f"Keyword search failed: {e}") return None def delete(self, vector_id: str) -> None: """ Delete a vector by ID. Args: vector_id (str): ID of the vector to delete. """ self._ensure_collection() with self._get_cursor(commit=True) as cur: cur.execute(sql.SQL("DELETE FROM {} WHERE id = %s").format(self._col()), (vector_id,)) def update( self, vector_id: str, vector: Optional[list[float]] = None, payload: Optional[dict] = None, ) -> None: """ Update a vector and its payload. Args: vector_id (str): ID of the vector to update. vector (List[float], optional): Updated vector. payload (Dict, optional): Updated payload. """ self._ensure_collection() with self._get_cursor(commit=True) as cur: if vector is not None: cur.execute( sql.SQL("UPDATE {} SET vector = %s WHERE id = %s").format(self._col()), (vector, vector_id), ) if payload is not None: # Handle JSON serialization based on psycopg version if PSYCOPG_VERSION == 3: # psycopg3 uses psycopg.types.json.Json cur.execute( sql.SQL("UPDATE {} SET payload = %s WHERE id = %s").format(self._col()), (Json(payload), vector_id), ) else: # psycopg2 uses psycopg2.extras.Json cur.execute( sql.SQL("UPDATE {} SET payload = %s WHERE id = %s").format(self._col()), (Json(payload), vector_id), ) def get(self, vector_id: str) -> OutputData: """ Retrieve a vector by ID. Args: vector_id (str): ID of the vector to retrieve. Returns: OutputData: Retrieved vector. """ self._ensure_collection() with self._get_cursor() as cur: cur.execute( sql.SQL("SELECT id, vector, payload FROM {} WHERE id = %s").format(self._col()), (vector_id,), ) result = cur.fetchone() if not result: return None return OutputData(id=str(result[0]), score=None, payload=result[2]) def list_cols(self) -> List[str]: """ List all collections. Returns: List[str]: List of collection names. """ with self._get_cursor() as cur: cur.execute("SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'") return [row[0] for row in cur.fetchall()] def delete_col(self) -> None: """Delete a collection.""" with self._get_cursor(commit=True) as cur: cur.execute(sql.SQL("DROP TABLE IF EXISTS {}").format(self._col())) def col_info(self) -> dict[str, Any]: """ Get information about a collection. Returns: Dict[str, Any]: Collection information. """ self._ensure_collection() with self._get_cursor() as cur: cur.execute( sql.SQL(""" SELECT table_name, (SELECT COUNT(*) FROM {}) as row_count, (SELECT pg_size_pretty(pg_total_relation_size({}::regclass))) as total_size FROM information_schema.tables WHERE table_schema = 'public' AND table_name = %s """).format(self._col(), sql.Literal(self.collection_name)), (self.collection_name,), ) result = cur.fetchone() return {"name": result[0], "count": result[1], "size": result[2]} def list( self, filters: Optional[dict] = None, top_k: Optional[int] = 100 ) -> List[OutputData]: """ List all vectors in a collection. Args: filters (Dict, optional): Filters to apply to the list. top_k (int, optional): Number of vectors to return. Defaults to 100. Returns: List[OutputData]: List of vectors. """ self._ensure_collection() filter_conditions, filter_params = _build_filter_conditions(filters) filter_clause = sql.SQL("WHERE " + " AND ".join(filter_conditions)) if filter_conditions else sql.SQL("") with self._get_cursor() as cur: cur.execute( sql.SQL(""" SELECT id, vector, payload FROM {} {} LIMIT %s """).format(self._col(), filter_clause), (*filter_params, top_k), ) results = cur.fetchall() return [[OutputData(id=str(r[0]), score=None, payload=r[2]) for r in results]] def __del__(self) -> None: """ Close the database connection pool when the object is deleted. """ try: # Close pool appropriately if PSYCOPG_VERSION == 3: self.connection_pool.close() else: self.connection_pool.closeall() except Exception: pass def reset(self) -> None: """Reset the index by deleting and recreating it.""" self._ensure_collection() logger.warning(f"Resetting index {self.collection_name}...") self.delete_col() self.create_col()