Building a Multilingual Book Search with FastAPI and PostgreSQL Full-Text Search

#python #postgres #fastapi #tutorial

How we store, index, and search book translations across 30+ languages using PostgreSQL's built-in full-text search.

At LectuLibre, we let users upload books in any language and get AI-powered translations into dozens of languages. That means our database stores not just the original text, but multiple translated versions of every paragraph. When a user searches for a phrase, they expect results across all languages—and they expect it fast. Here’s how we built that search system using FastAPI and PostgreSQL’s full-text search, and what we learned along the way.

The Problem: Searching Across Languages

Our platform ingests EPUB and PDF files, extracts the text, splits it into paragraphs, and then runs each paragraph through an LLM translation pipeline. A single book can produce translations in 30+ languages, meaning every original paragraph spawns dozens of translated rows. We needed to:

Store millions of paragraphs and their translations efficiently.
Provide keyword search across all content, regardless of language.
Return relevant results with sensible ranking.
Keep query times under 100ms for a responsive UI.

Initially we considered Elasticsearch, but our scale (hundreds of thousands of paragraphs, not billions) didn’t justify the operational overhead. PostgreSQL’s built-in full-text search promised enough features for our needs: language-specific stemming, ranking, and GIN indexes. So we decided to go all-in on PostgreSQL.

Our Approach: Normalized Tables + Per-Language tsvector Columns

Data Model

We use SQLAlchemy 2.0 with asyncpg for async database access. Our core tables are books, chapters, paragraphs, and translations. Here’s the simplified model:

from sqlalchemy import String, ForeignKey, Text, Index, UniqueConstraint
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship
from sqlalchemy.dialects.postgresql import TSVECTOR

class Base(DeclarativeBase):
    pass

class Book(Base):
    __tablename__ = "books"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(500))
    source_language: Mapped[str] = mapped_column(String(10))

class Chapter(Base):
    __tablename__ = "chapters"
    id: Mapped[int] = mapped_column(primary_key=True)
    book_id: Mapped[int] = mapped_column(ForeignKey("books.id"))
    order: Mapped[int]

class Paragraph(Base):
    __tablename__ = "paragraphs"
    id: Mapped[int] = mapped_column(primary_key=True)
    chapter_id: Mapped[int] = mapped_column(ForeignKey("chapters.id"))
    order: Mapped[int]
    original_text: Mapped[str] = mapped_column(Text)

class Translation(Base):
    __tablename__ = "translations"
    id: Mapped[int] = mapped_column(primary_key=True)
    paragraph_id: Mapped[int] = mapped_column(ForeignKey("paragraphs.id"))
    language: Mapped[str] = mapped_column(String(10))
    translated_text: Mapped[str] = mapped_column(Text)
    # We'll add a tsvector column per language later
    __table_args__ = (
        UniqueConstraint("paragraph_id", "language", name="uq_paragraph_language"),
    )

We chose a normalized structure rather than storing translations in a JSONB column. The reason: we wanted to create separate GIN indexes for each language’s full-text search vector. If we had a single JSONB column, PostgreSQL couldn’t efficiently index the individual language fields for FTS.

Generating tsvector Columns

The key to fast full-text search is precomputing a tsvector column for each translation. PostgreSQL 12+ supports generated columns, which are automatically updated when the source column changes. We use a generated column that applies the language-specific text search configuration:

from sqlalchemy import Computed, text

class Translation(Base):
    # ... existing fields ...
    search_vector: Mapped[str] = mapped_column(
        TSVECTOR,
        Computed(
            "to_tsvector(language::regconfig, translated_text)",
            persisted=True
        ),
        nullable=True
    )

But here’s a catch: language is stored as a string like 'english', 'spanish', 'french'. PostgreSQL’s to_tsvector expects a regconfig value, which is an OID. We use the cast language::regconfig to convert the language name to its configuration. This works as long as the language name exactly matches a built-in configuration (english, spanish, french, german, etc.). For languages that don’t have a built-in configuration (e.g., Chinese, Japanese), we handle them separately—more on that later.

We then create a GIN index on search_vector per language to speed up queries:

CREATE INDEX idx_translations_search_vector_english ON translations USING GIN (search_vector) WHERE language = 'english';
CREATE INDEX idx_translations_search_vector_spanish ON translations USING GIN (search_vector) WHERE language = 'spanish';
-- ... repeat for all supported languages

This partial index approach keeps each index smaller and more efficient than a single huge GIN index on the whole table.

Searching with SQLAlchemy

Our FastAPI endpoint receives a query string and an optional target language filter. We use websearch_to_tsquery to parse user input (supports quotes, OR, minus, etc.) and join translations with paragraphs and books. Here’s a simplified async query:

from sqlalchemy import select, func, or_, and_
from sqlalchemy.dialects.postgresql import websearch_to_tsquery, ts_rank

async def search_books(query: str, language: str | None = None, limit: int = 20):
    tsquery = websearch_to_tsquery(language or 'simple', query)
    stmt = (
        select(
            Book.id,
            Book.title,
            Paragraph.order,
            Translation.language,
            Translation.translated_text,
            ts_rank(Translation.search_vector, tsquery).label('rank')
        )
        .select_from(Translation)
        .join(Paragraph, Translation.paragraph_id == Paragraph.id)
        .join(Chapter, Paragraph.chapter_id == Chapter.id)
        .join(Book, Chapter.book_id == Book.id)
        .where(Translation.search_vector.op('@@')(tsquery))
        .order_by(desc('rank'))
        .limit(limit)
    )
    if language:
        stmt = stmt.where(Translation.language == language)

    result = await session.execute(stmt)
    return result.all()

Important note: We pass the language to websearch_to_tsquery as the first argument (config name). If the user searches without specifying a language, we default to 'simple', which doesn’t apply stemming but still tokenizes properly. We could also search across all languages by using multiple tsquery configurations, but that would require separate queries and merging results. For now, we let users pick a language or search in the original language.

Handling CJK Languages

PostgreSQL’s built-in full-text search works great for European languages with word boundaries and stemming, but Chinese, Japanese, and Korean (CJK) don’t use spaces between words. The default simple configuration treats each character as a separate token, which is useless for phrase search. We tried a few approaches:

pg_trgm trigram similarity – Good for fuzzy matching, but not true full-text search. We use it for ILIKE-style queries.
Custom text search parser – e.g., zhparser for Chinese, but it requires compiling and installing a PostgreSQL extension, which we wanted to avoid on our managed VPS.
External tokenization – We use the jieba Python library during ingestion to segment Chinese text into words, then store the segmented text in a separate column (translated_text_segmented) and run FTS on that with the simple configuration.

We went with option 3 because it keeps everything inside PostgreSQL and only adds a preprocessing step in Python. For Japanese, we use fugashi with mecab-ipadic. For Korean, we use mecab-ko. The segmented text column has its own generated tsvector column and index.

Performance Numbers

We benchmarked with 1.2 million translation rows across 40 languages (mostly English, Spanish, French, German).

Before GIN indexes: a typical search query took 2–4 seconds because PostgreSQL had to scan and compute tsvectors on the fly.
After adding per-language GIN indexes on generated columns: median query time dropped to 35ms, p95 at 80ms. That’s well within our 100ms target.
Index size: the GIN indexes added about 15% overhead to the table size, which was acceptable.

We also tested using a single composite GIN index on search_vector without partial indexes. Performance was similar but index creation time was longer and the index was larger.

Lessons and Trade-offs

PostgreSQL FTS is powerful but not a replacement for Elasticsearch if you need advanced features like synonym dictionaries, custom analyzers, or distributed search. For our current scale, it’s perfect.
Generated columns simplify maintenance but you must be careful when changing the text. Since the vector is persisted, updates to translated_text automatically update the vector and index (with a small write overhead).
CJK requires extra work outside the database. We originally tried to use PostgreSQL’s built-in simple config with trigram indexes and found it lacking for phrase search. The Python tokenization step added ~50ms per paragraph during ingestion, which is negligible compared to LLM translation time.
Language configuration names must match exactly – typos lead to errors. We validate language codes at ingestion time and store only supported configurations.
Async SQLAlchemy with asyncpg gave us excellent concurrency for search endpoints, easily handling hundreds of requests per second on a single VPS.

What’s Next

We’re considering moving to a dedicated search engine like Meilisearch or Typesense for more advanced relevance tuning and typo tolerance. But for now, PostgreSQL’s full-text search has been a reliable workhorse.

Key takeaway: If you’re building a multilingual application with moderate data volume, don’t reach for Elasticsearch right away. PostgreSQL’s built-in full-text search, combined with generated columns and partial GIN indexes, can deliver sub-100ms search across dozens of languages with minimal infrastructure overhead.

Have you used PostgreSQL full-text search for multilingual content? How did you handle CJK languages? Let us know in the comments.