PostgreSQL + pgvector#

pip install "needtoknow[pgvector]"

Needs PostgreSQL with pgvector 0.8 or newer. The permission filter runs inside the SQL query, so the database returns the nearest chunks the subject may read, however few of the table’s rows that is.

Set up#

from psycopg_pool import ConnectionPool

from needtoknow.backends.pgvector import PgVectorBackend, create_schema

pool = ConnectionPool("postgresql://localhost/app")
create_schema(pool, dim=768)
backend = PgVectorBackend(pool, embed=embed)  # embed: str -> list[float], your embedding model

create_schema(pool, dim, table="needtoknow_chunks", hnsw=True) creates, if they do not exist:

the vector extension;
a table with id text PRIMARY KEY, content text, metadata jsonb and embedding vector(dim);
a GIN index on metadata (jsonb_path_ops), which serves the permission filter;
an HNSW index on embedding with cosine distance (pass hnsw=False to create it yourself, for example after a bulk load).

To use an existing table, give it those four columns and pass table=.

Write and delete#

from needtoknow import Document

backend.add([Document("salaries-1", "Salary bands for 2026 ...", {"allowed_groups": ["hr"], "doc_id": "salaries"})])
backend.add(documents, embeddings=vectors)  # if you computed the embeddings already
backend.delete(["salaries-1"])

add inserts or replaces by id, so re-indexing a document after its permissions change is the same call.

How the filter becomes SQL#

Each value in an In becomes two JSON containment tests, one for a scalar field and one for a list field:

metadata @> '{"allowed_groups": "hr"}'::jsonb OR metadata @> '{"allowed_groups": ["hr"]}'::jsonb

And and Or become AND and OR. Values are bound parameters, never interpolated. Containment compares by type, which matches the reference semantics, and the GIN index serves it.

Iterative index scans#

An HNSW index normally returns a fixed number of nearest rows (hnsw.ef_search, 40 by default) and the filter is applied to those. With a selective filter, few or none of them pass, and the query returns fewer than k rows. pgvector 0.8 added iterative index scans, which keep scanning until enough rows pass. The backend turns them on for each query (SET LOCAL hnsw.iterative_scan):

`iterative_scan=`	Behaviour
`"relaxed_order"` (default)	Keeps scanning; results can be slightly out of order, so the backend re-sorts them by distance
`"strict_order"`	Keeps scanning in exact distance order; slower
`None`	Off, as before pgvector 0.8

Results are ordered by distance, then by id, so equal scores come back in a stable order. Scores are cosine similarity, 1 - cosine distance.

Search depth#

HNSW is approximate: how many of the true nearest neighbours a search finds depends on hnsw.ef_search, the size of the candidate list it keeps (40 by default, 1 to 1000). Higher finds more and takes longer. On a million chunks at the default, the index’s top 10 held 0.895 of the exact top 10 (see Benchmarks).

backend = PgVectorBackend(pool, embed, ef_search=100)

The backend sets it with SET LOCAL in each search’s transaction, like the iterative scan, so other users of the connection pool keep their own setting. None (the default) leaves the server’s value. Measure the recall and latency of a few values on your own data before choosing one.

Keyword search#

mode="keyword" searches the text with PostgreSQL full-text search instead of the vector, under the same permission condition, for hybrid search with RankFusion:

create_schema(pool, dim=768, text_search="english")
keyword = PgVectorBackend(pool, None, mode="keyword", text_search="english")

The query is parsed with websearch_to_tsquery (quoted phrases, or, -word) in the text_search configuration, and results are ranked by ts_rank_cd. create_schema(..., text_search=...) adds a GIN index on to_tsvector('<configuration>', content); the backend writes the configuration into the query as a literal, so PostgreSQL uses that index (CI checks the plan). Use the same configuration name in both places. A keyword backend reads the table; write chunks with a vector backend, since every row stores its embedding.

Async#

Pass an AsyncConnectionPool as async_pool= and aretrieve uses a native async search. Without one, async calls run search in a thread.