Searching a library in twenty languages

Turn on a second language in a digital asset manager and nothing appears to happen. The interface translates, the metadata form grows a tab, and search keeps returning results. That last part is the problem. Search kept returning results because it quietly stopped searching the corpus you asked for and started searching a different one, and there is no error, no warning and no empty state to tell you.

This is the third article in a series on how search actually works inside freedam. The first covered the retrieval architecture: manufacturing a searchable document, fusing BM25 with pgvector, guaranteeing recall under caps. The second removed text from the problem entirely and looked at perceptual hashing. This one puts text back and asks the question the first article deliberately skipped: which language is the index in?

The answer is not a setting. It is six settings, in three different places, two of which are frozen into DDL, and the interesting engineering is entirely about what happens when they disagree. They disagreed for us, in production, and the failure mode was not an exception. It was silence.

Part 1: A text search index has a nationality

Full-text search is not string matching. Between the text you store and the term you match sits a text search configuration: a parser that decides where tokens begin and end, a stopword list, and a stemmer that reduces each token to a root form. PostgreSQL ships these as named configurations, and choosing one is not a preference. It changes what is physically stored in the index.

Under the english configuration, running shoes is stored as the two lexemes run and shoe. That is why a search for run finds a document that only ever said running. Under simple, which does no stemming at all, the same phrase is stored as running and shoes, and run finds nothing. Same text, same database, same query, two different answers, decided at index time by a string.

freedam supports thirty interface languages. PostgreSQL 18 ships twenty-nine Snowball stemmers plus simple. The intersection is twenty.

That mapping started at seven stemmers and grew to twenty. Extending it was not a one-line change, and the reason why is the theme of everything below: the mapping is not stored once. It is projected into the DDL of a generated column, into the DDL of every BM25 index, and into a settings row read at query time. Two of those three are frozen the moment the statement runs. A migration exists solely to rebuild them when the PHP function changes, because a stored tsvector built by english and a query compiled by simple do not fail loudly. They just stop agreeing.

Part 2: Translate the document, not the query

There are two ways to make a search index multilingual, and the choice determines everything downstream.

The first is to translate the query. Keep one index, and when a French user searches chaussures, translate the term to shoes and search the English corpus. This is cheap in storage and terrible in practice: it needs a translation service in the hot path of every search, it mistranslates the exact vocabulary that matters most to a brand, and it cannot rank, because after translation you no longer know which of five candidate senses the user meant.

The second is to translate the document. Build one searchable document per asset per language, from the translations that already exist in the system, and search the one matching the user's locale. This is what freedam does. It costs storage, linearly, and it costs a rebuild whenever a translation changes. What it buys is that the query is never transformed, and the index the query hits was built from human-approved terms.

The unit of storage is a row in asset_search_documents, keyed by (asset_id, language_code). Building it is the subject of the first article; the multilingual part is which pieces of that document actually vary by language.

The controlled vocabulary is the part that makes this worth the storage. A taxonomy term is stored once with a code and translated into every active language, so the French document contains the French label, its French parent path, and nothing English. A user searching chaussures de course matches a term whose stored code might be something like footwear.running, because a human approved that translation once, in a controlled vocabulary editor, and every asset carrying the term inherited it.

Everything else is where the design gets less tidy, and pretending otherwise would misrepresent it. A filename is a filename. EXIF is written by a camera. OCR returns whatever was printed on the packaging. An AI caption is generated once, in the default language. All of that text lands in every language's document unchanged, which means a French document is mostly English text that a French stemmer is about to be pointed at.

That sounds like a bug. It is closer to a feature, and the measurements in the next section are the reason.

Part 3: What a stemmer does to text that is not in its language

Take a phrase of ordinary English product vocabulary and run it through four configurations. Everything below is to_tsvector output on PostgreSQL 18, not a description of it.

Under english, running shoes bags images packaging becomes run, shoe, bag, imag, packag. Under french, the same input becomes running, sho, bag, imag, packaging. Under german, running, shos, bag, imag, packaging. Under simple, nothing changes at all.

The French stemmer produces sho from shoes, which is not a word in any language. It does not matter. What matters is that the query goes through the same configuration: a French-locale user searching shoes also produces sho, and the two agree. Of nine English queries tested against that document, eight matched under the French configuration exactly as they did under the English one. The single failure was run, because French has no rule that strips -ing, so running stays whole and the shorter query cannot reach it.

This is the most useful thing we learned about multilingual text search, and it generalises well beyond this codebase:

Consistency between index and query matters far more than correctness of the configuration. A wrong stemmer applied to both sides degrades gracefully towards exact-form matching. A right stemmer applied to one side and not the other is catastrophic.

Hold that sentence. Part 5 is what happens when you violate it.

The wrong stemmer is not free, though, and the cost shows up somewhere unexpected: document length.

The length effect is the subtle one and it is worth spelling out. to_tsvector('english', …) and to_tsvector('french', …) over identical content produce 192.0 and 207.9 distinct lexemes on average across 2,000 real documents. BM25 divides by document length, tuned here by b = 0.75, so a longer document is penalised for the same term frequency. The French rendering of an asset is not just spelled differently in the index, it is scored differently, and by roughly eight percent of the length normalisation term.

Which is fine while you compare French documents to French documents. Part 6 is about the moment you stop doing that.

Two more behaviours worth knowing before you assume a stemmer solves a language:

German does not decompound. Produktfotografie stems to the single lexeme produktfotografi. Produkt Fotografie, written with a space, stems to two. A German user searching Produkt will not find the compound, and no amount of German configuration changes that, because Snowball strips suffixes and does not split words. Compound-heavy languages need a decompounding dictionary, which PostgreSQL supports through ispell dictionaries and which we do not currently ship.

Turkish and Russian do very well. fotoğraflarımızda, five morphemes deep, reduces cleanly to fotoğraf. Russian фотографии and фотография both reduce to фотограф, which is exactly the collapse you want from a heavily inflected language. Agglutination and inflection are what Snowball is good at. Composition is what it is not.

Part 4: One index per language, and it is partial

freedam does not build one full-text index over asset_search_documents. It builds one per active language, each of them a partial index restricted to that language's rows.

The statement, with the tenant's real parameters interpolated, looks like this: CREATE INDEX assets_bm25_fr_idx ON asset_search_documents USING bm25(content) WITH (text_config='french', k1=1.2, b=0.75) WHERE language_code = 'fr'.

Three things are baked into that one statement, and only one of them is visible in the index name.

The name is derived from the language code. The text configuration is derived from the language code through the settings map. The predicate is the language code itself. To use this index, a query must supply a WHERE language_code = 'fr' that PostgreSQL can prove is implied by the index predicate, and it must compile its tsquery with french, or the terms it looks for will not be the terms that were stored.

Alongside it sit two indexes with different keys, which is where the shape gets interesting.

The title index is keyed by configuration, not language, and that is not tidiness. A functional index only serves a predicate whose expression is byte-identical, configuration included. The twenty stemmed languages each map to a distinct configuration, so for them the two keys coincide; the ten unstemmed ones all map to simple, and keying by language would build ten structurally identical indexes over the same column. Keying by configuration collapses them into one.

That same rule bites in the other direction, and it did. This index is created in application code, per language, rather than once in a migration. Inside a migration the settings provider cannot resolve the tenant's language map, so the configuration silently resolves to simple, and the migration would build assets_title_tsv_simple_idx while production emits an english predicate all day. The index would be valid. It would be maintained on every write. It would never be used, and nothing would report that except a sequential scan of the assets table on every search, measured at about 26 ms on the 22,944-asset corpus. The fix was a second entry point that takes the configuration directly, so a migration must state which one it means.

There is a third index, and it is the one that makes the fallback bug in Part 5 possible: none of these can be reached without the query naming the right language in three places at once.

Part 5: The fallback that emptied a corpus

Here is the code that shipped, in its simplest form. A user searches. Their locale is fr. The system asks for the BM25 index name for fr, and since no index exists for fr, the lookup helpfully falls back and returns the English index name.

That is one line, it looks defensive, and it is correct in isolation. The query it produces is not.

Failure shape one is total and, in hindsight, obvious. The broken query carries both defects at once, and the predicate mismatch dominates: nothing comes back, so nothing else can be observed.

Which makes shape two the dangerous one, because it is what remains after the obvious fix. Correct the predicate, leave the configuration, and the query now reaches the right corpus and quietly refuses half of it. It is partial, it is invisible, and it survives exactly the kind of spot check an engineer performs before shipping.

We can measure that residue. Take the English corpus, 22,944 documents, indexed with the english configuration, and query it with a simple-compiled tsquery, which is what an unresolved language produces when it has no entry in the configuration map. Count matches both ways:

• images matches 19,029 documents correctly, and 0 with the mismatched configuration.
• bags matches 1,236, and 0.
• packaging matches 371, and 0.
• shoes matches 4, and 0.
• leather matches 6,171, and 6,171.
• campaign matches 8, and 8.

Look at the last two. The mismatch is completely invisible for any term whose stem happens to equal its surface form, and those are precisely the terms a developer types when checking that search still works. Nobody spot-checks with images. They type a noun.

To find out how much of the corpus that covers, we took the 500 most frequent English terms in the tenant's search dictionary, at least three letters, and compared each term's english stem to its simple form. 281 of the 500 differ, 56.2%. Weighted by how many documents each term appears in, 693,029 of 1,240,305 document references, 55.9%. So a little over half of all real query traffic returns nothing, a little under half behaves perfectly, and no log line distinguishes the two.

The same defect existed twice, in two services, because both had independently paired a helpfully-falling-back index name with an unresolved language predicate. Fixing it in the hybrid search path did not fix it in the autocomplete path; that took a second commit, after somebody noticed that suggestions for non-default locales were quietly empty. The lesson we recorded was not "check the other call site". It was that the helper had the wrong signature: a function called getBm25IndexName(language) invites exactly this bug, because it returns one of three values that need to change together and gives the caller no reason to suspect the other two exist. The helper that replaced it, resolveEffectiveLanguage(), returns the language, and every derived value is computed from its result.

If falling back one value silently invalidates another, do not fall back the value. Fall back the thing they were both derived from.

Part 6: Merging two languages worth of ranks

Resolving the language solves correctness. It does not solve the actual product problem, which is that a French user in a library whose metadata is mostly English should still find things.

So retrieval runs two branches when the user's effective language differs from the tenant default: one over the user's corpus, one over the default corpus. Each branch is a complete, independent BM25 scan against its own partial index, with its own text configuration and its own term-coverage verification. Then the two are merged.

The merge is at the level of ranks, not scores, and that decision deserves the same defence reciprocal rank fusion got in the first article. Two BM25 scores from two indexes are not comparable in any principled way. They were computed over different corpora with different average document lengths, from different lexeme distributions, with an inverse document frequency term calibrated on different vocabularies. Adding them, averaging them or thresholding them together would be arithmetic performed on incompatible units. Ranks are ordinal, they are comparable by construction, and they are what the fusion stage downstream consumes anyway.

Three details in that diagram are load-bearing and were each learned the hard way.

Both branches must be deduped before the union. A BM25 ordered index scan returns roughly three rows per matching document, an artefact of how positions stream out of the index. Deduplicating after the union works, but the cap then applies to a set that is three-quarters redundant, so each branch dedupes to its own top-k first.

The merged set is re-capped. Two branches each capped at the candidate budget produce, in the worst case, twice the budget. Everything downstream, the fusion, the floors, the completeness accounting from article one, was designed against one budget. A union that quietly doubles it is a correctness change dressed as a merge.

The merge lives in one place and serves both the hybrid path and the lexical-only fallback. That sounds like ordinary refactoring; it was a bug fix. The two-branch merge originally existed only on the hybrid path, so when the embedding service was unavailable and search degraded to BM25 alone, non-default-language users silently lost the default-language branch too. A degradation in the semantic half of the pipeline was narrowing the lexical half's corpus, for one class of user only. Extracting the merge into a shared builder is what made the two paths incapable of disagreeing about which corpora exist.

Everything the two branches resolved is frozen once per search execution into an immutable record, alongside the digest of the language-and-configuration map they came from. That is not ceremony. Search touches language in at least five stages: rule compilation, the retrieval branches, prefix generation, the did-you-mean lookup, and the typo-corrected retry. If each of them re-read the locale and the settings independently, a settings change landing mid-execution would let them disagree with each other, and the resulting query would be the fallback bug again, assembled from two different moments in time.

Part 7: The half of the pipeline with no language at all

Look back at figure 2. Every asset has one 768-dimension vector, stored on the asset row, with no language column anywhere near it. That is not an omission, and it has consequences worth stating plainly rather than glossing.

The embedding text is compiled once, in the tenant's default language, and sent to a locally hosted nomic-embed-text-v1.5 model with the task prefix search_document:. Queries go through the same model with search_query:. Both stay inside the deployment, which is a large part of why the whole system remains practical to self-host: no asset content leaves the infrastructure to be embedded.

So for a French user, the search that runs is genuinely asymmetric. The lexical branch searches a French corpus and an English corpus, both stemmed appropriately, and merges them. The semantic branch embeds a French query with a model whose training is overwhelmingly English, and compares it against a vector derived from English document text.

What that buys is real but bounded. Embedding spaces trained mostly on English do carry some cross-lingual structure, so a French query is not noise, and the semantic branch does still surface assets whose English description is conceptually adjacent. What it does not buy is parity. A French query against a French corpus gets excellent lexical retrieval and mediocre semantic retrieval, while an English query in the same tenant gets both. The setting to build per-language embeddings exists and is off by default, because turning it on multiplies embedding cost and storage by the number of active languages to improve the weaker half of a pipeline whose stronger half already handles the language correctly.

The honest summary is that freedam's semantic search is multilingual in the sense that it does not break, not in the sense that it is equally good in every language. Anyone who needs true parity there needs a multilingual embedding model, and that is a model choice rather than an architecture change: the vector column, the HNSW index, the fusion and the thresholds are all indifferent to which model produced the numbers.

Part 8: The languages PostgreSQL cannot help with

Ten of thirty interface languages get simple. For Czech, Polish, Slovak, Slovenian, Bulgarian, Ukrainian and Latvian, that means exact-form matching in languages with rich case systems, so a user must type the same inflected form that appears in the metadata. It is a real degradation and a mild one; a noun in the nominative is usually what somebody types.

For Chinese, Japanese and Korean it is not mild, and it is worth being precise about why, because "no stemmer" understates the problem by a wide margin.

PostgreSQL's default parser finds token boundaries using whitespace and punctuation. Chinese and Japanese are written without spaces between words. So the parser does not produce badly stemmed tokens; it produces one token for an entire phrase.

To put a number on the last row: a sixteen-character Chinese phrase describing an autumn leather sneaker campaign produces exactly one lexeme under simple. Searching for the three-character word for sneakers matches nothing, because tsquery matching compares whole lexemes. The trigram similarity between the query and the phrase is 0.050, against a typo-tolerance threshold that ranges from 0.5 to 0.7 by term length, so the fuzzy path does not rescue it either. A plain ILIKE '%…%' does find it, which is to say the data is there and no index can reach it.

We have not shipped a fix for this, and it would be dishonest to describe the current state as anything other than a limitation. The three viable paths, in rough order of cost:

A dedicated tokenizer extension. pg_bigm indexes character bigrams and works on CJK where trigram similarity does not. It is a real extension with real maintenance implications for a self-hosted deployment, and it changes what "a token" means for every language in the database, not just the CJK ones.

Segment at index time. Run Chinese and Japanese text through a segmenter before it reaches the document builder, and insert spaces. This keeps PostgreSQL entirely stock, which matters a great deal for a product people run on their own infrastructure, and it moves the problem to a place where it can be tested: a segmenter's output is inspectable, and its failures are visible in the document rather than in a query plan. It also has to run on the query, and the two have to stay in agreement, which is the same class of constraint this entire article is about.

Lean on a multilingual embedding model. Change the embedding model, and CJK queries reach assets through the semantic branch even when the lexical branch is blind. This is the smallest code change and the weakest guarantee: semantic retrieval is approximate by nature, and an exact product code typed by a Japanese user should not depend on cosine distance.

Naming the limit matters more than any of the three. A system that quietly returns nothing for a language it claims to support is worse than one that says it does not support it, because the first kind gets bought.

Part 9: What a language costs, and how one gets turned on

Everything above is paid for in storage and in rebuild time, and both scale linearly with the number of active languages.

On the tenant these measurements come from, 22,944 assets in English only: 22,944 document rows holding 30 MB of text, an 8.1 MB BM25 index, a 9.3 MB GIN index over the stored tsvector, a 520 kB title index, and 21,692 distinct words in the typo dictionary. Every one of those numbers is per language by construction: a document row per asset per language, a BM25 index per language, dictionary rows per language. Activating a second language duplicates all of them, including for assets whose translated metadata is byte-identical to the English version, because the document text is not the only thing that differs. The stemmer does. That is a projection from the schema rather than a second measurement; the tenant above has one language active.

The vector index does not grow. Neither does the asset table, the file storage, or any of the renditions. Multilingual search costs text, not pixels, which on a media library is a rounding error against the assets themselves. That is the good news, and it is the reason translating documents is affordable at all.

Activation is a queue job, not a migration, and its ordering encodes something.

Turning a language on updates the language-to-configuration map, then dispatches a backfill for that language alone. The backfill walks the asset table in bounded segments, self-chaining a continuation from the next asset id rather than running one long pass, so a large tenant is covered by a chain of short jobs that can be retried individually. Only the segment that finishes the range dispatches the index build, which drops and recreates rather than creating, because the BM25 extension can hold corrupted internal state when an index is created on an empty table and populated afterwards.

Which means that between activating a language and the last segment completing, the language is active, its documents are partially built, and it has no index. This is exactly the state the fallback in Part 5 exists to handle, and it is the only reason that state is safe: searches in the new language resolve to the default language entirely, index, predicate and configuration together, and return correct default-language results until the index appears. Get the fallback wrong and every search during a multi-hour backfill returns nothing, in a window nobody is watching, for a feature somebody just switched on and is about to test.

Deactivation runs the same steps backwards: documents and dictionary rows for that language are deleted, the index is dropped, and any user whose preference pointed at the removed language is moved to the system default. Nothing about the assets changes, because nothing about the assets was ever language-specific.

What we would tell anyone building this

• Decide whether you translate the query or the document, and accept the whole bill. Translating documents costs linear storage and a rebuild per translation change. It buys a query you never have to transform and an index built from terms a human approved.
• A fallback is a set, not a value. If falling back one derived value invalidates another, do not fall back the derived value. Resolve the thing they were all derived from, once, at the top, and recompute everything below it.
• Prefer consistency to correctness in text configurations. A wrong stemmer on both sides degrades gracefully towards exact matching. A right stemmer on one side is a silent, partial, invisible loss of half your corpus.
• The terms that expose a stemming mismatch are never the terms you spot-check with. Half our most frequent vocabulary broke and half behaved perfectly, split precisely on whether stemming changes the word. Test with an inflected plural, not a noun.
• Merge ranks across corpora, never scores. BM25 scores from two indexes are computed over different vocabularies with different length normalisation. They are incomparable units that happen to be the same data type.
• A capped branch plus a capped branch is two caps. Any union of independently limited candidate sets has to be re-capped, or everything downstream is working with a budget it was not designed for.
• Freeze every language decision once per request. Search touches the locale in five or six stages. If each one re-reads it, a settings change landing mid-request lets them disagree, and you have rebuilt the same bug out of two different moments in time.
• Know which languages your database genuinely cannot index, and say so. "Stemmer unavailable" and "the parser cannot find word boundaries" are different orders of problem, and one of them is not rescued by trigrams, vectors, or optimism.

Where this lives in the product

Everything above runs on every freedam search, in every active language: the gallery, the REST API, the TypeScript SDK, and any AI agent connected over MCP. Which languages are active, and which one is the default, are settings an administrator changes; the indexes, the backfills and the fallbacks follow from that one choice. The product-level view of the same subject lives on the search page.

Next in this series: how we measure whether any of this actually works. A sixty-case relevance panel, what happened the first time we ran it against a real customer corpus, and why the obvious diagnosis was wrong four times out of five.

One honest caveat about seeing it for yourself: the demo runs a single-language library, so it shows the retrieval described in the first two articles rather than anything in this one. Multilingual retrieval is not something a shared demo tenant can show you convincingly anyway, because the interesting behaviour only appears once a real controlled vocabulary has been translated and a real corpus has been backfilled in each language. If that is the position you are in, the thing worth asking us is not whether we support your languages but which of them get a stemmer, and what the ones that do not are falling back to.