Searching a library in twenty languages

Turning on a second language in a digital asset manager changes the interface and metadata forms, while search may appear to keep working. In our case, that appearance hid a fallback that selected a different corpus from the one requested, without producing an error or warning.

This is the third article in a series on search inside freedam. The first covered the retrieval architecture: building a searchable document, fusing BM25 with pgvector and handling candidate caps. The second looked at perceptual hashing. This one returns to text and asks which language each index uses.

The answer depends on several values in three places, including two values frozen into DDL. When they disagree, queries can return incomplete or empty results while remaining syntactically valid, which is the failure this article examines.

A text search index has a language

Full-text search is not simple string matching. A text search configuration supplies the parser, stopword list and stemmer used between the stored text and the query, so choosing one changes the terms physically stored in the index.

Under the english configuration, running shoes becomes the lexemes run and shoe, so a search for run can find it. Under simple, which does no stemming, the same phrase remains running and shoes, so run finds nothing. The text and query are the same, but the index-time configuration changes the answer.

freedam supports thirty interface languages, while PostgreSQL 18 provides suitable Snowball configurations for twenty of them; the remaining ten use simple.

The mapping started at seven stemmers and grew to twenty. It affects the DDL of a generated column, every BM25 index and a setting read at query time, so extending it also requires rebuilding the DDL-backed parts. A stored tsvector built with english and a query compiled with simple remain valid individually but no longer contain matching terms.

Translating the document rather than the query

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

The first option is to translate the query. A French search for chaussures becomes shoes and runs against one English corpus. This saves storage but puts translation in the hot path, can mishandle brand-specific vocabulary and introduces ambiguity before ranking.

The second option is to build one searchable document per asset and language from translations already stored in the system, then search the document matching the user's locale. This is what freedam does. Storage and rebuild work grow linearly with active languages, but queries are not translated and controlled terms come from human-approved translations.

Each asset therefore has a stored search document per active language. The first article explains how that document is built; the multilingual concern is which parts vary by language and which do not.

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.

Other text is not translated: filenames remain filenames, EXIF comes from the camera, OCR preserves the packaging text and AI captions are generated once in the default language. That content is copied into every language document, so a French document may still contain mostly English text processed by a French stemmer.

This is less harmful than it first appears, provided the index and query use the same configuration.

What a stemmer does to text from another 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.

The practical lesson is that consistency between index and query matters more than choosing the ideal configuration for every token. Applying the same imperfect stemmer to both sides tends to degrade toward exact-form matching, while applying different configurations causes terms to stop matching. The fallback bug below is an example of the latter.

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

There is also a document-length effect. Across 2,000 documents, to_tsvector('english', …) produced 192.0 distinct lexemes on average, compared with 207.9 under french for the same content. With BM25 length normalisation at b = 0.75, the French rendering is therefore scored differently even when the underlying text is identical.

That difference is consistent within a French corpus, but it matters when results from multiple language corpora are combined.

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.

One partial index per language

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'.

The statement binds together three values, although only one 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.

Two related indexes use different keys for practical reasons.

The title index is keyed by configuration, not language, because a functional index only serves a predicate whose expression, including configuration, is identical. The twenty stemmed languages map to distinct configurations, while the ten unstemmed languages all use simple; keying by configuration avoids building ten equivalent indexes over the same column.

This also means index creation cannot depend on a tenant setting that is unavailable during migrations. In that context the configuration resolved to simple, while production queries used an english expression, leaving a valid and maintained index that the queries could not use. The symptom was a sequential scan of the assets table, measured at about 26 ms on the 22,944-asset corpus. We changed the index-building entry point to require an explicit configuration in contexts where the tenant map cannot be resolved.

There is a third index, and it creates the conditions for the fallback bug below: none of these indexes can be reached unless the query names the right language in three places at once.

A fallback that selected the wrong corpus

The bug began when a user searched with locale fr before a French index existed. The index lookup fell back to the English index name, but the language predicate and text configuration did not fall back with it.

The individual fallback looked reasonable, but the three derived values no longer described the same corpus.

With both the predicate and configuration wrong, the query returns nothing. Correcting only the predicate reaches the English corpus but still compiles the query with the wrong configuration, producing a partial failure that simple spot checks may miss.

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.

The last two terms show why the bug survived testing: a mismatch is invisible when the stem equals the surface form. A useful spot check therefore needs an inflected term such as images, not only a noun whose form remains unchanged.

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 appeared in hybrid search and autocomplete because both fell back an index name without resolving the effective language first. We replaced the narrow index-name helper with one that resolves the language as a unit, then derives the index name, predicate and text configuration from that result. This makes it harder for call sites to update only one of the three values.

The reusable rule is to fall back the shared source value, then recompute everything derived from it.

Merging ranks from two language corpora

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 keep the two branches consistent with the rest of the retrieval pipeline.

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 can otherwise produce twice the candidate budget, while downstream fusion, score floors and completeness accounting expect one budget. Reapplying the cap preserves that contract.

The same merge serves hybrid and lexical-only search. The two-branch merge initially existed only on the hybrid path, so an unavailable embedding service caused non-default-language users to lose the default-language lexical branch as well. Moving the merge into a shared builder keeps both paths on the same corpora.

The effective language and its derived configuration are resolved once per search execution and passed through rule compilation, retrieval, prefix generation, suggestions and typo retries. This prevents a settings change during a request from allowing different stages to build the same query from different language states.

The language-neutral vector branch

Every asset has one 768-dimensional vector stored without a language column. This is a deliberate storage choice, and it makes the semantic branch asymmetric with the per-language lexical indexes.

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.

In practice, freedam's semantic search accepts multilingual queries but does not provide equal quality in every language. True parity requires a multilingual embedding model, although the surrounding vector column, HNSW index, fusion and thresholds would remain the same.

Languages the default parser cannot index well

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, the limitation is more serious because it concerns token boundaries rather than stemming.

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.

A sixteen-character Chinese phrase describing an autumn leather sneaker campaign produces one lexeme under simple. Searching for the three-character word for sneakers matches nothing because tsquery compares whole lexemes. Trigram similarity is 0.050 against a typo threshold between 0.5 and 0.7, so that path does not help either, while a plain substring query does find the text. The data is present, but the configured indexes cannot retrieve the subphrase.

We have not shipped a fix for this. The three viable approaches, in rough order of operational cost, are:

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.

Use a multilingual embedding model. This lets CJK queries reach assets through the semantic branch when lexical retrieval misses them. It is the smallest code change but the weakest guarantee because semantic retrieval is approximate, and exact identifiers should not depend on cosine distance.

Until one of these is implemented, the product needs to state that lexical CJK search is limited rather than imply parity with languages that have appropriate tokenisation.

Storage cost and language activation

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

On the tenant used for these measurements, 22,944 English documents occupy 30 MB of text, with an 8.1 MB BM25 index, a 9.3 MB GIN index over the stored tsvector, a 520 kB title index and 21,692 terms in the typo dictionary. These structures are per language, so activating another language duplicates them even when translated metadata is identical because the text configuration can still differ. This is a projection from the schema rather than a measurement from a multilingual tenant.

The vector index, asset table, file storage and renditions do not grow with the language count. The additional cost is in text indexes and documents, which is small relative to the media files for this corpus.

Language activation runs as an ordered queue workflow rather than a migration.

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.

Between activation and completion of the final segment, documents are only partially built and the new index does not yet exist. During that window, search resolves entirely to the default language, including its index, predicate and configuration. Once the backfill completes, the new index becomes available and the effective language can switch as one unit.

Deactivation reverses those steps: documents and dictionary rows for the language are deleted, the index is dropped, and users who selected it move to the system default. The assets themselves remain unchanged because the language-specific state lives in the search documents and indexes.

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.

The demo runs a single-language library, so it does not demonstrate the behaviour in this article. A useful multilingual evaluation needs a translated controlled vocabulary and a corpus backfilled in each language; the relevant questions are which languages receive a stemmer, which use simple, and how fallback is resolved while an index is unavailable.