Postgres + pgvector with metadata-filtered search

What you'll learn

• Explain how a metadata filter becomes a WHERE clause in pgvector.
• Write filters that work across stores, and spot the syntax that breaks when code moves.
• Compare pre-filtering with post-filtering by how many results each returns.
• Diagnose a filtered query that returns fewer than k results after an index is added.

Lesson 3 of T5.5 separated tenants with containers. Inside one container, filters do the narrowing.

Larkspur adds an HNSW index to its pgvector table to speed up search. A support query filtered to the refunds page asks for k = 5 and gets 2 back, with no error.

A metadata filter restricts a similarity search to entries whose metadata matches a predicate. It is how one index serves many scopes: one customer, one product, one section of the handbook, documents newer than a date. The notebook indexes two restaurants' FAQs and reviews into one PGVector collection and narrows each query with a filter on restaurant, category or cuisine.

LangChain's PGVector (from langchain_postgres) keeps everything in two tables. langchain_pg_collection has one row per collection. langchain_pg_embedding has one row per chunk: the embedding vector, the document text, a collection_id, and the metadata as a JSONB column, cmetadata, with a GIN index on it. A filter becomes a WHERE clause on cmetadata.

Table 1: Filter forms PGVector accepts.

PGVector accepts $eq, $ne, $lt, $lte, $gt, $gte, $in, $nin, $between, $exists, $like, $ilike, $and, $or and $not. Stores share most of this syntax but differ at the edges. The two-key dict that PGVector reads as AND makes Chroma raise ValueError: Expected where to have exactly one operator, so write $and when code may move between stores.

Filters are exact. "Engineering" does not match "engineering", the case-split filter from lesson 1 of T5.2, and a field that was never written matches nothing. Normalise metadata at ingestion (T5.2) so the filter has something consistent to match.

A filter is not access control, and relying on one as a boundary is the filter-as-access-control mistake. Lesson 1 of T5.5's notebook makes the point: a caller who omits the filter sees everything. For tenant isolation, enforce the filter in the service that builds the query, or use a namespace or a separate collection per tenant (lesson 3 of T5.5).

There are two ways to combine a filter with a top-k search, and they return different results:

Figure 1: Pre-filtering and post-filtering on the same question.

Pre-filtering restricts the candidates, then ranks them. If at least k entries match, you get k. LangChain's PGVector builds one SQL query, WHERE collection_id = … AND <filter> then ORDER BY distance, and it creates no vector index of its own, so Postgres compares the query with every matching row: an exact, pre-filtered search.

Post-filtering ranks first, then drops what fails the filter. LangChain's FAISS wrapper does this on fetch_k=20 candidates (lesson 2 of T5.5). A selective filter can return fewer than k results or none.

Adding an ANN index to pgvector moves it towards post-filtering. The pgvector README says: "With approximate indexes, filtering is applied after the index is scanned." Its example: if a condition matches 10% of rows, an HNSW scan with the default hnsw.ef_search of 40 leaves "only 4 rows" on average. From version 0.8.0, iterative index scans (SET hnsw.iterative_scan = strict_order or relaxed_order) keep scanning until enough rows pass the filter.

The symptom of post-filtering is a query that asks for k = 5 and gets 2, a short result set, as in the opening example. Log len(results) beside k on every filtered query; raising k hides the problem without fixing it.

For the handbook in pgvector, filters follow four rules:

1. Normalise metadata at ingestion (T5.2), so section = "Refunds" matches every refund chunk.
2. Write filters with explicit $and and $or, so the code still runs if the store changes.
3. Enforce the tenant filter in the service, or use a namespace per tenant (lesson 3 of T5.5).
4. After adding an HNSW index, turn on iterative scans and log the result count beside k on every filtered query.

Next: lesson 5 of T5.5 keeps the store itself correct as the handbook changes, with the Indexing API.

The code for this lesson is in these notebooks. Colab runs one in your browser: install the packages its first cell imports and set the API keys it reads. To run every notebook as written, clone the repository and use its uv environment.

• PGVector turns a metadata filter into a WHERE clause on JSONB metadata. Filters match exactly, so normalise values at ingestion, and write $and so filters stay portable.
• A filter is not access control, because a caller can omit it. Enforce it in the service, or use a namespace or collection per tenant.
• Pre-filtering returns up to k matches; post-filtering, including pgvector with an ANN index, can return fewer. Log len(results) beside k on every filtered query.