mediumSystem Design

Filtered vector search with pgvector — how do you combine WHERE category = 'X' with ORDER BY embedding <=> $1?

Q: Filtered vector search with pgvector — how do you combine WHERE category = 'X' with ORDER BY embedding $1?

Naïve `WHERE … ORDER BY vec $1 LIMIT` post-filters and may return Filtered ANN: pre-filter vs post-filter — pgvector iterative scan (2025) solves filtered search Naïve: Post-filter ORDER BY vec <=> $1 LIMIT 10 then WHERE category=‘X’ may return 0 after filter ✗ wrong rec

752 views

Understand the problem

Solve pre-filter vs post-filter ANN problem using partial indexes or iterative scan for correct recall.

pgvectorfilteringann

Attempt it yourself

Sketch your approach before reading the solution — that's what interviews test.

Nudge consolestandby

Stuck? Beam a request up — the console returns a conceptual nudge that guides your logic without spoiling the implementation.

Study the solution

Naïve WHERE … ORDER BY vec <=> $1 LIMIT post-filters and may return < LIMIT rows. Fix with a partial HNSW index per hot filter, or enable iterative scan (pgvector 0.6+) so the executor keeps scanning until LIMIT is satisfied after filtering.

Solution ready — 2 min read

Classified // press E to declassify

Join the discussion

Discussion (0)

No responses yet. Be the first to share what you think.

Transmission complete // awaiting log

KEEP THE
STREAK ALIVE.

Dossier 33 of 70 decoded in the SQL track. One more won't hurt.

Back to track

Next up→

What is a CTE (Common Table Expression)?

Continue