Hybrid Search Patterns with Postgres and pgvector | Crunchy Data Blog
Christopher Winslett
Jul 30, 2026·13 min read
More by this author
Latest Articles<br>Hybrid Search Patterns with Postgres and pgvector<br>Postgres 19 Compression: from pglz to LZ4<br>British Columbia, Time Zones, and Postgres<br>Postgres Serials Should be BIGINT (and How to Migrate)<br>Postgres 18 New Default for Data Checksums and How to Deal with Upgrades
Vectors & LLMs<br>Hybrid Search Patterns with Postgres and pgvector
Christopher Winslett
Jul 30, 2026·13 min read·More by this author
Most production vector queries are not simple nearest-neighbor searches. Rarely is the query to return "the 10 most similar documents in the entire table." Typically, it's something closer to: find the 10 most similar documents in the legal category, published in the last 30 days. That mix of similarity ranking plus scalar filters is hybrid search.We have written before about HNSW indexes with pgvector and scaling vector data. Those posts go over indexes used to accelerate Nearest Neighbor queries with Approximate Nearest Neighbor indexes (ANN indexes). The next problem shows up the moment a WHERE clause is added. pgvector's iterative index scans help with filtered search, but they come with some tuning and tradeoffs.When nearest neighbor meets a WHERE clause<br>Here is the query almost everyone writes first:SELECT id, content<br>FROM docs<br>WHERE category = 'legal'<br>ORDER BY emb <=> '[0.031, ...]'<br>LIMIT 10;<br>It looks innocent. With a typical B-tree index, a WHERE plus ORDER BY is a solved problem: the planner picks an index, applies the filter, sorts what is left, and you move on.A vector index finds nearby embeddings; a WHERE clause filters rows. Combining them forces Postgres to sacrifice either recall or performance. The natural follow-up question is: why not just intersect the indexes? Postgres already knows how to combine two B-trees. Scan each index, build bitmaps of matching row IDs, BitmapAnd them together, and done. If you have an index on category and an index on emb, why can't the planner find the rows that are both legal and near the query vector the same way?Because the two indexes are not answering the same kind of question.A B-tree on category returns a set . The predicate category = 'legal' is a yes-or-no membership test. Every qualifying row ID goes into the set. That shape is perfect for intersection: set of legal rows, set of active rows, or tenant = 42, or (you get the point).An HNSW (and IVFFlat) index returns an approximate ordered top-k , not a set. ORDER BY emb <=> query LIMIT 10 is not "rows that match." These indexes returns a ranked list. When returning values, the index walks a graph (or probes inverted lists), compares distances, and emits candidates ranked from nearest to farther and the more it returns, the longer the query time, more than RAM and CPU usage. With a LIMIT 10, you can just return 10, right? The ANN indexes do not include data about conditionals in the WHERE clause, so after returning the 10 closest, it then performs a check against WHERE conditionals. Some (or all) of those 10 may not pass the conditionals. Do you go back and find the next 10 closest? Do you quit? This is the tradeoff.pgvector iterative index scans<br>Starting in pgvector 0.8, it implemented a solution to the problem of "top-k then hope the filter leaves enough rows." Iterative index scans keep walking the HNSW or IVFFlat (ANN index) until enough rows survive the WHERE clause, or until a safety limit says stop:-- Continue scanning until enough filtered rows are found<br>SET hnsw.iterative_scan = relaxed_order;<br>-- or, if exact distance order matters more than recall/speed:<br>-- SET hnsw.iterative_scan = strict_order;<br>-- or ivfflat indexes:<br>-- SET ivfflat.iterative_scan = relaxed_order;
SET hnsw.max_scan_tuples = 20000; -- cap how far the scan may expand<br>SET hnsw.ef_search = 40;
SELECT id, content<br>FROM docs<br>WHERE category = 'legal'<br>ORDER BY emb <=> '[0.031, ...]'<br>LIMIT 10;<br>With hnsw.iterative_scan equal to relaxed_order or strict_order, scan, filter, and if you are short of LIMIT, it go back to the ANN index for the next closest rows (with exact behavior dependant on graph navigation precision).With the default (hnsw.iterative_scan = off), filtering is applied after a single index pass. If the condition matches about 10% of rows and hnsw.ef_search is 40, you should expect only about four survivors on average, even when you asked for LIMIT 10.However, using iterative_scan is not free.Tradeoffs to keep in mind: Low selectivity values: a rare conditional forces will continue to scan far more of the graph to collect 10 survivors than a common one.Setting with hard stop: hnsw.max_scan_tuples bounds how far iterative search will go, after which it will still return fewer than LIMIT rows.Strict vs relaxed ordering. strict_order prioritizes correct order over performance. relaxed_order allows results to be slightly out of order in exchange for performance.Memory usage...