Filtering With WHERE: SQL and Vectors Together

Ordinary SQL conditions just work

Because the vector is a column, you filter with ordinary WHERE: WHERE tenant = 'acme' AND year = 2025 ORDER BY embedding <=> :q LIMIT 2. You can also join to other tables (users, permissions, products), use IN lists, ranges, OR and row-level security to enforce who may see which rows, which is a major advantage over engines with limited query languages. Be aware that with an approximate index the order of operations matters (the next section shows a filter that returns zero rows), and an exact scan always honours the filter. Put security filters in the query or in row-level security, never in application text prompts.

Similarity plus relational filters, run

I ran this SQL on PostgreSQL 16 with the pgvector extension, version 0.8.6, in a Docker container. Restricted to tenant acme and year 2025, the 2022 policy and the other tenant's memo are excluded, leaving the leave policy and the travel document, nearest first.

SELECT id, tenant, year, body
FROM docs
WHERE tenant = 'acme' AND year = 2025
ORDER BY embedding <=> '[1,0,0]'
LIMIT 2;

Output:

 id | tenant | year |       body        
----+--------+------+-------------------
  1 | acme   | 2025 | leave policy 2025
  3 | acme   | 2025 | travel and hotels
(2 rows)

Index your filter columns too

A B-tree index on tenant or year helps the planner when a filter is selective. Verify with EXPLAIN.

Quick check: Which is a strength of pgvector for filtering?

• Full SQL: joins, ranges, row-level security
• No filtering is possible
• Only equality on one field
• Filtering only in the prompt