Building Multilingual Full-Text Search with FastAPI and PostgreSQL

When the same book needs to exist simultaneously in Spanish, French, German, and other language versions, the design difficulty of the search system rises sharply. When building an AI book translation platform, the LectuLibre team chose a seemingly conservative but highly pragmatic route: instead of introducing Elasticsearch, they powered multilingual retrieval with FastAPI + PostgreSQL native full-text search. Their experience offers strong reference value for developers of small and medium-scale AI applications.

Problem Definition: Search Challenges in a Multilingual Book Library

LectuLibre lets users upload EPUB or PDF books, and the system calls large models such as Claude and DeepSeek^Ad to complete translations, ultimately forming a library where the same book coexists in multiple languages. This imposes three hard requirements on the backend: efficiently store original text and translations; perform fast full-text search across all languages so users can find target books or passages in their native language; and manage translation tasks asynchronously without blocking the API.

Many teams' first reaction to such requirements is to reach for Elasticsearch. But after evaluation, LectuLibre found that at the scale of a single VPS and a few hundred thousand documents, PostgreSQL's built-in full-text search is already sufficient and saves an entire layer of operational burden.

Architectural Decision: No Extra Infrastructure

The tech stack is Python/FastAPI plus PostgreSQL, with SQLAlchemy 2.0 async mode and the asyncpg driver for the ORM, and Alembic for migrations. The core design revolves around four decisions:

• Normalized translation table: Each translation is stored as an independent row with an ISO 639-1 language code, rather than crammed into a JSON blob.
• Generated tsvector columns: Each language has its own tsvector column with a GIN index.
• Language-specific text search configurations: Use built-in configurations such as english, spanish, and french, falling back to simple for unknown languages.
• pg_trgm fuzzy matching: Use trigram similarity to handle misspellings in book titles.

Here there is a noteworthy lesson learned the hard way: the team initially stored translations in a JSON column, and queries required constant JSON parsing, which was both painful and slow. After normalizing to a separate table, everything became clean, and PostgreSQL's full-text search capabilities could be used directly.

tsvector and GIN indexes: tsvector is a data type designed specifically by PostgreSQL for full-text search. It splits text into lexemes and stores them normalized with positions, thereby supporting stemming (for example, reducing running and runs to run) and position-related ranking. A generated column allows this vector to be computed automatically by the database on write, with no manual maintenance needed in the application layer. GIN (Generalized Inverted Index) is PostgreSQL's inverted index for data structures such as arrays, JSONB, and tsvector where "one value contains multiple keys." Compared with B-tree, which can only do prefix or equality matching, a GIN index lets @@ matching avoid a full table scan, which is precisely the root reason search degrades from milliseconds to seconds.

Code Mainline: Models, Migrations, and the Search Endpoint

Three Core Tables

books, chapters, and translations form the data skeleton. The translations table contains chapter_id, language, content, and the content_tsv column. The key point is that content_tsv is not maintained on the Python side; instead, it is created as a generated column in the migration, with the database responsible for the generation logic.

class Translation(Base):
    __tablename__ = "translations"
    id: Mapped[int] = mapped_column(primary_key=True)
    chapter_id: Mapped[int] = mapped_column(ForeignKey("chapters.id"))
    language: Mapped[str] = mapped_column(String(10))
    content: Mapped[str] = mapped_column(Text)
    content_tsv: Mapped[str] = mapped_column(TSVECTOR, nullable=True)

Migration and Triggers

After adding the tsvector column in the Alembic migration, an UPDATE statement selects the corresponding text search configuration by language (en→english, es→spanish, fr→french, others→simple), and then a GIN index is created. To keep it synchronized when content changes, the team wrote a PostgreSQL trigger function that automatically recomputes content_tsv before INSERT or UPDATE. This way the application layer does not need to care about index updates at all.

Search Endpoint

The FastAPI endpoint receives q, lang, and limit parameters, maps the language code to the PostgreSQL configuration, and then builds a single SQL query that does all the work: websearch_to_tsquery parses the user input (supporting quotes and OR syntax), the @@ operator filters matching rows, ts_headline generates a highlighted snippet with <mark> tags, and ts_rank sorts by relevance. The entire search, snippet generation, and ranking close the loop in a single SQL statement.

stmt = (
    select(
        Translation.id,
        Translation.language,
        Chapter.order,
        Book.title,
        func.ts_headline(
            ts_config,
            Translation.content,
            func.websearch_to_tsquery(ts_config, q),
            'StartSel=<mark>, StopSel=</mark>, MaxWords=50, MinWords=20'
        ).label("snippet"),
    )
    .join(Chapter, Translation.chapter_id == Chapter.id)
    .join(Book, Chapter.book_id == Book.id)
    .where(
        Translation.language == lang,
        Translation.content_tsv @@ func.websearch_to_tsquery(ts_config, q)
    )
    .order_by(
        func.ts_rank(
            Translation.content_tsv,
            func.websearch_to_tsquery(ts_config, q)
        ).desc()
    )
    .limit(limit)
)

websearch_to_tsquery and the tsquery family: PostgreSQL provides several functions that convert raw query strings into tsquery. to_tsquery requires strict syntax, plainto_tsquery treats the whole text as AND terms, while websearch_to_tsquery mimics Google's style—supporting double-quoted phrases, OR conjunction, and - exclusion, with high tolerance for malformed input, making it more suitable for direct exposure to end users. It is paired with ts_headline for generating highlighted snippets and ts_rank (and the faster ts_rank_cd) for relevance scoring. Combining them into a single SQL statement means a user request needs only one database round trip to complete filtering, ranking, and snippet generation, avoiding the overhead of pulling all text back to the application layer for processing.

Fuzzy Book Title Search

For misspellings in book titles, the team enabled the pg_trgm extension, created a gin_trgm_ops index on books.title, and used similarity(Book.title, q) > 0.3 in queries to filter and sort by similarity.

pg_trgm and trigram similarity: pg_trgm is an official PostgreSQL extension that splits strings into "trigrams" made of three consecutive characters—for example, "book" produces units such as " b", " bo", "boo", "ook", and "ok ". By comparing the proportion of trigrams shared by two strings, it computes a similarity score between 0 and 1, thereby tolerating typos, missing characters, or reversed word order. Paired with a GIN index built using the gin_trgm_ops operator class, similarity lookups no longer need row-by-row computation, which is the key reason fuzzy book title search dropped from 1.2s to 15ms. It often complements full-text search: the former handles "close spelling," the latter handles "semantic relevance."

Performance and Trade-offs

The before-and-after comparison is very intuitive. Before adding GIN indexes and generated tsvector columns, searching across 10,000 translations took about 800ms, because the database had to scan and convert text on the fly. After adding indexes, even when the data grew to 100,000 translations, the same query dropped to about 20-40ms. Fuzzy book title search went from 1.2s to 15ms.

The biggest trade-off was giving up Elasticsearch. At the current scale, PostgreSQL is simpler to operate, requires no extra services, and integrates naturally with relational data. But the team is also clear that if they grow to millions of documents or need advanced features such as faceted search, they may still need to reconsider Elasticsearch.

Another unresolved trade-off is language configuration. PostgreSQL supports many languages out of the box, but CJK languages such as Chinese and Japanese require different tokenization approaches (such as pg_jieba or zhparser). Currently they can only fall back to the simple configuration that splits on spaces, and the results are not ideal.

The CJK tokenization challenge: PostgreSQL's built-in text search configurations tokenize by spaces and punctuation by default, which works for English, French, and similar languages, but Chinese, Japanese, and Korean do not use spaces as separators. An entire passage may be treated as a single lexeme, resulting in extremely low recall. The simple configuration only lowercases and does not perform stemming, so it is not a true substitute. The common community solution is to install tokenization extensions: zhparser performs Chinese tokenization based on SCWS, and pg_jieba wraps Jieba tokenization. Both can be registered as custom text search configurations and then work seamlessly with tsvector, GIN indexes, and ts_rank. They need to be deployed by compiling extensions or using prebuilt images, which is why the team lists this as an open question.

Lessons Learned and Open Questions

The LectuLibre team offered several practical suggestions. Squeeze PostgreSQL's built-in capabilities dry before considering new infrastructure, which can significantly simplify deployment. Use generated columns and triggers to synchronize search vectors, avoiding manual index maintenance in the application layer. FastAPI works well with async SQLAlchemy, but pay attention to session management; they use async_sessionmaker plus dependency injection to ensure the session closes after every request. Be sure to test with realistic data volumes; queries that perform fine at 1,000 rows degrade rapidly as data grows, and adding indexes and generated columns is a direct fix.

The team ends with an open question: for languages that PostgreSQL does not natively support (especially CJK), how does the community handle full-text search? They are considering integrating the pg_jieba tokenizer for Chinese and welcome experienced developers to share alternatives.

One-line Summary

LectuLibre uses FastAPI + PostgreSQL native full-text search to power multilingual book retrieval, replacing Elasticsearch and pushing latency down to milliseconds.

Paragraph Summary

LectuLibre is an AI book translation platform that allows the same book to coexist in multiple language versions and requires users to search original text and translations in their native language. The team abandoned Elasticsearch and chose the FastAPI + PostgreSQL route: normalize each translation into an independent table row with an ISO 639-1 language code, create tsvector generated columns and GIN indexes per language, use triggers to automatically synchronize search vectors, and rely on websearch_to_tsquery, ts_headline, and ts_rank to complete filtering, highlighting, and ranking in a single SQL statement. In addition, they enabled pg_trgm to solve fuzzy matching for misspelled book titles. After optimization, search across 100,000 translations dropped from 800ms to 20-40ms, and fuzzy search dropped from 1.2s to 15ms. The team also notes that at million-scale or with faceted search, Elasticsearch may still need to be re-evaluated, and CJK tokenization for Chinese and Japanese remains an unsolved problem.