# PostgreSQL + pgvector Select `storage.vectordb.backend="pgvector"` to store collection metadata, dense vectors, scalar fields and sparse postings in PostgreSQL. OpenViking connects through a bounded psycopg2 connection pool; vector search executes inside the PostgreSQL process. Embeddings are still generated by the configured embedding provider. ## Setup Install the optional client dependency: ```bash pip install 'openviking[pgvector]' ``` Install pgvector **0.8.0 or newer** on the PostgreSQL server, then enable it in the database used by OpenViking: ```sql CREATE EXTENSION IF NOT EXISTS vector; SELECT extversion FROM pg_extension WHERE extname = 'vector'; ``` The extension must be installed in the `public` schema. The connection role needs access to that schema and permission to create collection tables and indexes, and read/write their data. Collection initialization is serialized with PostgreSQL advisory locks. Table and index metadata use the shared `_ov_collection_meta` and `_ov_index_` catalogs. Use distinct databases or collection names for independent deployments, and do not reuse an openGauss collection name. Set a DSN in the server environment. PostgreSQL DSN options such as `sslmode` and `sslrootcert` can be supplied here: ```bash export OV_POSTGRES_DSN='host=127.0.0.1 port=5432 dbname=openviking user=ov password=CHANGE_ME' ``` Merge this fragment into the existing OpenViking configuration, keeping the embedding dimension equal to the vector dimension: ```json { "storage": { "vectordb": { "backend": "pgvector", "name": "context", "dimension": 4096, "distance_metric": "cosine", "pgvector": { "dsn_env": "OV_POSTGRES_DSN", "index_type": "flat", "connection_pool_min_size": 1, "connection_pool_max_size": 10 } } } } ``` This is a Cluster-level backend. Account-owned `vectordb` settings currently accept only `http`, `volcengine` and `vikingdb`. ## Search and index settings | Setting in `pgvector` | Default | Meaning | | --- | --- | --- | | `dsn_env` | `OV_POSTGRES_DSN` | Environment variable containing the connection DSN | | `index_type` | `flat` | Exact dense search or native `hnsw` ANN | | `connection_pool_min_size` | `1` | Minimum connections per adapter | | `connection_pool_max_size` | `10` | Maximum connections per adapter; must be at least the minimum | | `m` | `16` | HNSW connections per layer, from 2 to 100 | | `ef_construction` | `64` | HNSW build candidate list, from 4 to 1000, at least twice `m` | | `ef_search` | `100` | HNSW search candidate list, from 1 to 1000 | | `candidate_limit` | `1000` | Candidate budget per modality for sparse/hybrid or time-decay ranking, from 1 to 10000 | Dense values use full-precision `vector`, with 1–16000 dimensions. Native HNSW supports at most **2000 dimensions** for this type. For a 4096-dimensional embedding, keep `index_type="flat"`; the backend does not silently truncate or quantize it. `halfvec`, binary quantization and IVFFlat are not implemented. For embeddings with at most 2000 dimensions, select: ```json { "dsn_env": "OV_POSTGRES_DSN", "index_type": "hnsw", "m": 16, "ef_construction": 64, "ef_search": 100 } ``` `distance_metric` accepts `cosine`, `l2`, `ip` and `l1`. Results use the OpenViking convention of higher scores ranking first. Scalar, tenant, ACL and URI scope filters are compiled to parameterized SQL. HNSW uses strict iterative scanning to improve recall with filters, but ANN recall and filtered TopK are not guaranteed; measure recall and p99 with representative filters. Cosine zero vectors are not included in a pgvector HNSW index. Flat search stays exact even when the table also contains an ANN index. On reconnect, the configured collection dimension, metric and index type must match persisted metadata; missing or invalid ANN indexes produce errors. Changing `ef_search` takes effect on reconnect without rebuilding. Changing index type or build parameters requires a planned index/collection rebuild. Sparse terms are stored in JSONB with a posting table. Sparse-only recall uses positive term overlap; hybrid search unions bounded dense and sparse candidates and computes complete scores within that union. It does not guarantee global fused TopK. `sparse_weight` and embedding configuration retain their usual meaning. ## Directory filter tuning For normalized URI scopes on large collections, evaluate a composite B-tree expression index over tenant and path. See [pgvector directory filter indexes](26-pgvector-directory-filtering.md) for SQL, level/depth semantics, million-row results and rollout boundaries. This index requires manual evaluation and deployment; the backend does not create it automatically. ## Data and deployment boundaries Upsert batches are transactional, including sparse postings; a failed batch rolls back. Collection data and index metadata survive restarts. PostgreSQL replication, backup and PITR can protect these database records when configured by the operator. TTL writes and native BM25/full-text search are not supported. The `content` field is stored in PostgreSQL (the service write path retains its existing 1 MB content payload limit), while grep uses OpenViking's filesystem fallback. Collection DDL is performed during initialization; this backend does not provide automatic schema migrations or automatic repair of damaged indexes. Switching the backend does not import existing local vectors. Create a new collection and reindex resources, or perform a separately verified migration. Keep the old vector store until the replacement has been checked. Resource files, queues, account/task stores and PathLock remain independently configured. Sharing pgvector alone does not make local workspaces safe for multiple OpenViking processes. Follow the [deployment guide](03-deployment.md) for shared storage and coordination; PostgreSQL PathLock is a separate feature. ## Integration tests Tests create randomly named collections and remove them afterwards. Use a test database with pgvector enabled: ```bash export OV_PGVECTOR_TEST_DSN='host=127.0.0.1 port=5432 dbname=ov_test user=ov' pytest tests/storage/test_pgvector_adapter.py ```