How we built image search in PostgreSQL

Since an image search query runs against things we derived from the pixels—metadata, generated text, embeddings or perceptual hashes—rather than the raw pixels themselves, indexing matters more than it first appears: ranking cannot recover a useful term or signal that never reached an index.

At freedam, the retrieval layer runs in the same PostgreSQL database as the asset records, using pg_textsearch for BM25 ranking and pgvector for vector storage and HNSW search. The embedding models run in a local sidecar while the resulting vectors and both search indexes live in PostgreSQL, so there is no Elasticsearch or separate vector database to keep in sync.

This article describes that production path—how we build the index, combine lexical and semantic results, work around candidate limits, and check whether a result set is complete—using values from a real library and a repeatable relevance test suite. For a less technical overview, see the search page.

Why image search is different

An image has no searchable words

An order row has a customer name, a support ticket has a body, and a PDF often has a text layer, whereas a JPEG gives us pixels and some EXIF but no terms to match against a query. We therefore have to build one or more searchable representations of it; in practice, we use four:

It is tempting to choose one representation and build the system around it, with "AI search" products often starting from embeddings while older asset libraries tend to rely on metadata. Both can produce a convincing demo, but real query logs contain several different kinds of searches:

• Identifier queries: 4471-RD, SS26-HERO-03, a GAID, an invoice number. These need exact lexical matching because vector search tends to return other identifiers that look similar in embedding space.
• Nomenclature queries: galaxy a52 case, taper plate, biodegradable mailer. These want lexical matching over a controlled vocabulary, plus tolerance for how the term was written down.
• Description queries: team celebrating outdoors, something calm for the newsletter header. Nobody may have tagged the asset with those words, so this is where embeddings should help.
• Example queries: "more like this one", "did we already licence this photograph?". No text is involved at either end.

A single retrieval method cannot handle all four well, so the practical problem is how to combine them without weakening exact matches and how to tell when candidate limits have dropped valid results.

Where each approach breaks

The trade-offs are fairly predictable:

Keyword search needs words to match. It works well for identifiers and known terminology, but only when those terms made it into the index, and it misses simple changes in phrasing when, for example, the photographer wrote autumn while the marketer searches fall.

Vector search is weak at exactness. It is useful for descriptive queries but poor at product codes and other identifiers. Since a nearest-neighbour query returns k rows even when none is a good match, a common one-word query such as chair can fill the page with vaguely related interiors when the threshold is too loose.

Manual tagging is expensive and drifts over time. A controlled vocabulary is valuable, but it is rarely complete and eventually diverges from the language people use in search.

An LLM is better at parsing the query than ranking the corpus. Running a model over 200,000 assets for every search would be slow, expensive and difficult to reproduce, so we use it before retrieval, where it can turn a sentence into structured filters.

Our text-search path therefore has two indexes over the same document: BM25 for lexical matches and an embedding for semantic matches. We fuse their ranks, add a separate path for cases that need stronger recall guarantees, and return whether the search was complete.

Building the search document

What search can find depends on what made it into the index, so freedam combines the available metadata and derived text for each asset into a stored search document and rebuilds it whenever the asset changes.

Three implementation details turned out to matter.

We implement field weights with repetition. A field with weight n is appended n times because the BM25 index has no concept of fields, but this also increases document length, which BM25 length normalisation (b = 0.75) penalises. We found eleven product videos outranking the expected product images for a place-name query because their documents were 155 to 493 characters while the image documents were 746 to 1,280, so the weighting scheme had also introduced a document-length bias.

We flatten the vocabulary hierarchy into the document. An asset tagged with the leaf term espresso machine also gets small appliances (parent, weight 2) and kitchen (ancestor, weight 3) written into its document. This makes a taxonomy searchable rather than merely filterable: a query for the category finds the leaf without a join or knowledge of the tree.

The tsvector is a stored generated column. Hybrid retrieval checks each candidate for term coverage after the ranked scan, so we store the tsvector as a GENERATED ALWAYS AS (…) STORED column rather than detoasting and parsing the document with to_tsvector(content) on every search. The trade-off is that its language-to-stemmer mapping is fixed in the schema, so expanding from 7 configurations to 20 required a migration that rebuilt the column and the affected BM25 indexes. The stored tsvector, BM25 index and query-time plainto_tsquery must use the same stemmer, otherwise the coverage check can reject valid matches.

A tokenisation pitfall

One issue we ran into involved slash-separated vocabulary terms in a retail library, where device-compatibility labels such as Galaxy A52/A52S and iPhone 12/12 Pro were present on the assets and in the search document—a LIKE '%A52%' found 178 of them—but galaxy a52 case returned only twelve results.

PostgreSQL's english parser classifies a slash-joined value as a single token of type file, so ts_debug('english', 'Galaxy A52/A52S') yields the lexeme A52/A52S rather than a52 plus a52s: the text was indexed, but the term used by the query never existed in the BM25 index.

The fix was to emit A52 and A52S alongside the original A52/A52S value; re-indexing unchanged input would have produced the same tokens, so the change belonged in the document builder.

There were three useful takeaways:

• Apply this kind of normalisation at the narrowest useful boundary. Splitting every slash in the document also affected EXIF (1/100, 1:1.8/26), voltages (50/60Hz), URLs, EU directive references (2009/125/EC) and conjunctions (and/or, und/oder), so we now split only vocabulary labels.
• Check terms through the production retrieval path, since our typo and prefix dictionary uses a different tokenizer and already reported a52 in 178 documents even though BM25 could retrieve only twelve.
• Keep unrelated control queries in the relevance suite, as the targeted fix changed document lengths and cost one existing test case whose candidate set was already at the cap.

Two retrieval paths over one table

BM25 with pg_textsearch

We run BM25 through the pg_textsearch extension on the same PostgreSQL instance that holds the assets, so there is no separate search cluster or index synchronisation job.

This keeps search in the same transaction as the rest of the application, which means a newly uploaded asset is searchable without waiting for a sync worker, while permissions, embargoes and usage rights remain ordinary SQL predicates rather than a second authorisation model. For a self-hosted deployment, it also removes one stateful service.

The main cost is that query performance depends heavily on the shape PostgreSQL's planner sees. A few details mattered in our queries:

• Indexes are per-language and partial. We build one BM25 index per language with WITH (text_config=…, k1=1.2, b=0.75) WHERE language_code = 'xx', so falling back to another language means changing the index name, language predicate and text configuration together; an earlier version changed only the index name and produced a partial index that could not serve the requested language's rows.
• The ordered index scan only stays fast while it has a row bound. An ordered BM25 scan takes its top-k path when the executor receives a bound, which survives a CTE referenced once because the planner inlines it, as well as a derived table with its own LIMIT, but not a scalar subquery or a CTE referenced twice. Referencing one CTE a second time changed the query from 6 ms to 7.3 s, so we now carry membership flags through the union with BOOL_OR instead of joining back to that CTE.
• Non-indexable predicates go after the scan, not inside it. Term coverage, meaning "does this document actually contain every query term?", is verified against the stored content_tsv on the bounded candidate set the scan returned. Put that predicate inside the ordered scan and the planner abandons the index for a merge join with per-row rescoring; we measured 7.4 s for one such query shape.

The raw scan also fetches 3× the configured candidate count because pg_textsearch's ordered scan emits roughly three rows per document; with bm25_top_k = 1000, the raw limit is 3,000 and the scan takes about 10 ms in our test library, but latency rises sharply as the raw limit approaches 6,000.

Vector search with pgvector

Text and images are embedded into the same 768-dimensional space with nomic-embed-text-v1.5 and nomic-embed-vision-v1.5, both served by a local sidecar so asset content does not leave the deployment. Nomic's models use task prefixes—we embed documents as search_document and queries as search_query—and the wrong prefix still produces vectors but lowers relevance without producing an obvious error.

pgvector stores those vectors and provides the HNSW index, for which we use cosine distance, m = 16 and ef_construction = 200, with a partial index over non-null vectors. We briefly cache query embeddings, including failure markers, so if the sidecar is unavailable, search falls back to BM25 instead of retrying the failed model call on every keystroke.

One distance threshold did not work for every query, so we vary it by query length, and the values are not monotonic:

Query shape	Max cosine distance
1 word	0.35
2 words	0.42
3 to 5 words	0.45
6 to 10 words (or > 30 chars)	0.48
> 10 words (or > 60 chars)	0.40

We use stricter thresholds at both ends for different reasons: a single word such as chair has a broad semantic neighbourhood and can match most interior photographs, while a long pasted product title tends to produce a less specific embedding that sits weakly near many assets. Queries in the middle are specific enough to describe something without averaging over too much text, so we allow a looser threshold there.

We apply one additional rule to single-word queries—they require a lexical hit—so a row matched only by vector similarity is discarded instead of letting a niche term with no real match return a full page of semantically adjacent images.

Fusing the two result lists

Since a BM25 score and a cosine distance measure different things, normalising them into a shared score would make the result depend on the current corpus, language and thresholds, so we combine their ranks with Reciprocal Rank Fusion (RRF):

rrf_score(d) = w_bm25 / (k + rank_bm25(d))  +  w_vector / (k + rank_vector(d))
                                                          k = 60,  w = 1.0

Three implementation details caused bugs or surprising behaviour.

Keep the fusion arithmetic in floating point. If the database evaluates the division as integer arithmetic, the fractional RRF scores collapse to zero and the query still returns rows but no longer ranks them meaningfully, so we cast the operands explicitly.

The adaptive floor has structural effects. We drop low-scoring rows using a minimum RRF score based on word count: 0.008 for one word, 0.015 for two, 0.025 for three to five, 0.028 for six to ten, and 0.012 beyond. Each value is scaled by (w_bm25 + w_vector)/2 × 61/(k+1) so changing k or the weights does not invalidate it. This has two direct consequences:

• A two-word query can never return zero results because a vector-only row scores 1/(60 + rank), and the two-word floor of 0.015 admits exactly ranks 1 through 6, which is why a nonsense two-word query returns exactly six things.
• At three to five words the floor of 0.025 exceeds the best possible vector-only score of 1/61 ≈ 0.0164, which makes a purely semantic match structurally impossible in that band, so every result in a four-word query carries a lexical rank.

Verified lexical matches bypass the floor. For queries of ten words or fewer, we check whether every BM25 candidate contains all query terms, so those rows do not need the minimum RRF score and the floor applies only to rows without a verified lexical match.

Candidate caps can hide matches

Every retrieval system limits how many candidates it will score, and our defaults are 1,000 BM25 candidates from a raw scan of 3,000 rows, 200 vector candidates, and 1,000 rows in the fused set.

Once an asset falls outside a candidate cap, later ranking stages cannot see it, but the first page can still look reasonable, which makes the problem difficult to notice. We call this cap crowding, and in one case 1,953 documents contained every query term but competed for 1,000 BM25 slots, leaving tied assets excluded by the final alphabetical tie-break.

For searches where we need stronger recall, we add a guaranteed band to the candidate set: its rows receive positions after the normal scan, and each later cap explicitly allows them through, making them reachable without moving them ahead of normally ranked results.

Searching inside a collection

We saw cap crowding most often when searching inside a collection: if 30 of 400 collection members contain a term, a tenant-wide search may return only four. This happens because assets outside the collection occupy the other positions in the top 1,000 before the collection filter is applied, so the missing results are a retrieval problem rather than a ranking problem.

For each search with a text term, freedam resolves the current scope and creates a retrieval band for it. The implementation depends on the number of assets in that scope:

Unscoped searches do not run this additional SQL; we materialise the band only when there is a scope and its size fits one of the useful strategies above.

Reporting whether results are complete

Alongside the results, freedam returns one more piece of information: whether the search found every match it could prove exists.

This matters when a user applies an action to the entire result set, because "select all 1,240 results and apply this rights policy" is safe only when 1,240 is the complete set; if retrieval stopped at 1,000 candidates, the same operation would update only part of the intended library.

We compute a completeness verdict with probes: extra COUNT columns that record how many rows each candidate source produced and whether that source reached its cap.

The verdict is conservative: a source reports Complete only when it can account for every match. Scoring each member of a small collection is therefore complete by construction, whereas a deep scan is complete only when the in-scope population stays below the band cap and the scan does not hit its row limit. New candidate sources default to Unknown, and fallbacks such as typo correction or prefix expansion get their own verdict rather than inheriting one from the original query.

This work also gave us a useful rule for monitoring: if the monitored behaviour breaks, the check must produce a different result. We had a monitor that stayed quiet after its job died, a tie-break test that stayed green after the tie-break disappeared from three SQL layers, and quality warnings that could not change the exit code, so we now ask a simple question when adding a check: what output changes when this breaks?

Other ways to search

The path above starts with a text query, but we also support typo and prefix fallbacks, image input, confirmed faces and natural-language filters.

Typos and prefixes, in that order

We also index the document with a GIN trigram index for "did you mean" suggestions and typo fallback, with corrections running per token against the library's own vocabulary and a stricter similarity threshold for short tokens. Prefix expansion has a separate candidate budget and runs first, because otherwise typo correction could rewrite a valid prefix before it gets a chance to match.

The language-specific indexes, dictionaries and fallback rules are covered in Searching a library in twenty languages.

Search by picture

Reverse image search ("have we seen this before?", "find the rest of this shoot", "is this a near-duplicate of something we already licensed?") uses a different index: we generate perceptual hashes for six regions of each image, retrieve candidates with multi-index hashing, and verify them with a thumbnail vector and a vision embedding, because the hash lookup is cheap and broad while vector verification is slower and more precise. The full candidate and verification pipeline is covered in Finding the same image twice.

Faces

Faces use a separate detector and 512-dimensional embedding space from InsightFace's buffalo_l, with HNSW and cosine distance, while detection runs in a local sidecar. The model groups similar faces, but a person must confirm the identity before we add the name to the search document, so photos of <person> at the launch event becomes an ordinary text query. The detection, identity and threshold choices are covered in A face is not a name.

Natural language, compiled to a filter tree

For conversational search, freedam's assistant does not rank assets but turns the sentence into a filter tree, the same structure produced by the advanced filter UI. The request also carries an intent—replace, refine, add, remove or reset—which controls how it combines with the active filters before the existing query compiler runs that tree with the same permissions and completeness checks as any other search.

Users can inspect and edit the generated filters, and once compiled, retrieval is deterministic, requires one model call rather than one call per candidate, and can return only assets that exist. External agents use the same path over MCP with the permissions attached to their token, while the filter compiler and conversational state model are covered in The chat that never sees your library.

How we test search

The thresholds and candidate limits above are tuned values rather than general constants, so we evaluate them with a manifest of realistic queries and agreed ground truth. The suite runs through the production search path against a replica of a real library and reports hard pass/fail, recall@10, recall@50 and mean reciprocal rank by category, while remaining read-only: it can search and select the ground-truth rows but cannot change settings or rebuild an index.

The first runs changed how we worked on search:

1. The first full run found fifteen hard failures in sixty cases. People using the same search had described it as "pretty good" because the top few results usually looked plausible.
2. Failures grouped by mechanism. The fifteen failures came from a few causes—scope truncation, tokenisation, cap crowding and document-length effects—so fixing a cause improved several cases at once.
3. Our initial diagnosis was often wrong. We attributed five recall regressions to a tokenisation change, but the measurements showed that four had not moved at all and document length was the actual cause.
4. A correct fix can still have a cost. Restricting slash splitting to vocabulary labels fixed the bug but lost one test case that had been benefiting from longer competing documents, moving the suite from 48 passes to 47, so we shipped the fix and recorded the trade-off.

What we would do again

The choices we would carry into another search system are straightforward:

• Do the indexing work up front. Retrieval quality is bounded by the document and vectors you stored, because ranking cannot recover a term the tokenizer never emitted.
• Fuse ranks, not scores. Reciprocal rank fusion does not require score calibration and remains useful when the corpus, language or thresholds change. k = 60 is a reasonable starting point and was rarely the setting worth tuning for us.
• Vary thresholds by query shape. One-word and ten-word queries behave differently, and for single words, requiring a lexical match removes a large amount of semantic noise.
• Treat candidate caps as a correctness constraint. Add a path that can reach important rows beyond the cap, report truncation, or do both.
• Return an unknown state. Complete, truncated and unknown are more useful than a total that looks exact but is not.
• Build probes from the production query. A separate handwritten version will eventually drift.
• Test the instrumentation too. Our term dictionary reported a52 in 178 documents while BM25 could retrieve only twelve.
• Keep stable control queries in the relevance suite. They show what changed outside the cases a fix was meant to improve.

Implementation and demo

This retrieval path is shared by the gallery, API, TypeScript SDK, assistant and agents connected over MCP, with BM25 running through pg_textsearch and HNSW vector search through pgvector on the same PostgreSQL instance as the assets. The embedding sidecar runs locally, so no asset content leaves the deployment, and the same stack is used for self-hosted installations.

For a product overview, see search and discovery, or try the demo to run the search against a seeded media library; a useful test is a visual description that would not normally appear in manually entered tags.