PostgreSQL + pgvector#

pip install "wardowl[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 wardowl.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="wardowl_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 wardowl 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 keeps strings, numbers and booleans apart, which matches the reference semantics, and the GIN index serves it. jsonb compares numbers by value, though, so {"level": 1.0} contains {"level": 1}, where the reference semantics match integers only. An integer value therefore also requires the stored number, or an element of the stored list, to read as that integer:

EXISTS (SELECT 1 FROM jsonb_path_query(metadata -> 'level', 'lax $[*]') AS e(v) WHERE v::text = ANY('{1}'))

The text of a jsonb number keeps its scale (1.0 reads 1.0), so a float never matches. It does not keep an exponent: json.dumps writes the float 1e16 as 1e+16, which jsonb would store as the integer 10000000000000000. So add and set_metadata write every whole float with its fraction (10000000000000000.0), in lists and nested objects too, and floats come back as floats. This matters for large ids from a pandas integer column with a missing value, which holds them as floats. Metadata keys must be strings: add and set_metadata refuse others with ValueError, since JSON would turn the key 1 into "1", which a filter on the field "1" would then match.

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.