Scaling Multilingual Search in a Book Translation Platform: FastAPI + PostgreSQL Full-Text Search Architecture

Lessons learned implementing multilingual search and translation management in a production FastAPI app.

The Problem: Translating Books Means Storing and Searching Many Languages

At LectuLibre, we built a platform where users upload EPUB or PDF books, and our system translates them using LLMs like Claude and DeepSeek. The result is a multilingual library: the same book can exist in Spanish, French, German, and more. We needed a backend that could handle:

• Storing original and translated text efficiently.
• Performing fast full-text search across all languages, letting users find books or passages in their preferred language.
• Managing asynchronous translation jobs without blocking the API.

We chose Python/FastAPI for the API layer and PostgreSQL for persistence. This article focuses on how we designed the database schema and search functionality to support multiple languages without adding complex infrastructure.

Our Approach: Keep It Simple, Leverage PostgreSQL

After evaluating options like Elasticsearch, we decided to stick with PostgreSQL’s built-in full-text search (FTS). For a single VPS deployment with a few hundred thousand documents, PostgreSQL’s FTS is more than capable and avoids operational overhead. We use SQLAlchemy 2.0 (async) with asyncpg driver, and Alembic for migrations.

The key design decisions:

• Normalized translation table: each translation is a separate row with a language code, not a JSON blob.
• Generated tsvector columns for each translation language, with a GIN index.
• Language-specific text search configurations (e.g., english, spanish, french) and a simple fallback.
• pg_trgm extension for fuzzy matching on book titles.
• FastAPI async endpoints that run search queries directly against PostgreSQL.

This approach let us implement multilingual search with minimal code and excellent performance.

Code Walkthrough: Database Models and Search Endpoint

We'll show the essential parts.

Database Models

We have three core tables: books, chapters, and translations. Each translation belongs to a chapter and has a language code (ISO 639-1) and the translated text.

# models.py
from sqlalchemy import String, Text, ForeignKey, Index, text
from sqlalchemy.dialects.postgresql import TSVECTOR
from sqlalchemy.orm import Mapped, mapped_column, relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Book(Base):
    __tablename__ = "books"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(500))
    # ... other metadata

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 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))  # 'en', 'es', etc.
    content: Mapped[str] = mapped_column(Text)
    # Generated tsvector column (created via migration)
    content_tsv: Mapped[str] = mapped_column(TSVECTOR, nullable=True)

We create the content_tsv column as a generated column in the migration, not directly in the model. This keeps the model simple and lets PostgreSQL handle the generation.

Migration snippet (Alembic):

# migrations/versions/xxxx_add_tsvector.py
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

def upgrade():
    op.add_column('translations', sa.Column('content_tsv', postgresql.TSVECTOR()))
    # Generate tsvector from content using language-specific config
    op.execute("""
        UPDATE translations SET content_tsv = 
            to_tsvector(
                CASE 
                    WHEN language = 'en' THEN 'english'
                    WHEN language = 'es' THEN 'spanish'
                    WHEN language = 'fr' THEN 'french'
                    ELSE 'simple'
                END,
                content
            )
    """)
    op.create_index('ix_translations_content_tsv', 'translations', ['content_tsv'], postgresql_using='gin')

We also create a trigger to keep content_tsv in sync when content changes. We use a PostgreSQL trigger function:

CREATE OR REPLACE FUNCTION translations_tsv_trigger() RETURNS trigger AS $$
BEGIN
    NEW.content_tsv := to_tsvector(
        CASE 
            WHEN NEW.language = 'en' THEN 'english'
            WHEN NEW.language = 'es' THEN 'spanish'
            WHEN NEW.language = 'fr' THEN 'french'
            ELSE 'simple'
        END,
        NEW.content
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_translations_tsv BEFORE INSERT OR UPDATE ON translations
FOR EACH ROW EXECUTE FUNCTION translations_tsv_trigger();

This ensures content_tsv is always up-to-date.

Search Endpoint

We expose a search endpoint that accepts a query, optional language filter, and uses PostgreSQL's websearch_to_tsquery for user-friendly syntax (supports quotes, OR, etc.).

# api/search.py
from fastapi import APIRouter, Query
from sqlalchemy import select, func, text
from sqlalchemy.ext.asyncio import AsyncSession
from models import Translation, Book, Chapter

router = APIRouter()

@router.get("/search")
async def search(
    q: str = Query(..., min_length=1),
    lang: str = Query("en"),
    limit: int = Query(20, le=100),
    db: AsyncSession = Depends(get_db),
):
    # Map language code to PostgreSQL text search config
    config_map = {"en": "english", "es": "spanish", "fr": "french", "de": "german"}
    ts_config = config_map.get(lang, "simple")

    # Build query: join translations with chapters and books, filter by tsvector
    stmt = (
        select(
            Translation.id,
            Translation.language,
            Chapter.order,
            Book.title,
            # Highlight snippet
            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)
    )

    results = await db.execute(stmt)
    return {"results": [dict(row) for row in results.mappings()]}

The endpoint uses ts_headline to generate a snippet with highlighted matches, websearch_to_tsquery for query parsing, and ts_rank for relevance ranking. All done in a single SQL query.

Fuzzy Title Search

For book titles, we added trigram similarity to allow misspellings. We enable the pg_trgm extension and create a GIN index on title using gin_trgm_ops.

# migration
op.execute("CREATE EXTENSION IF NOT EXISTS pg_trgm")
op.create_index('ix_books_title_trgm', 'books', ['title'], postgresql_using='gin', postgresql_ops={'title': 'gin_trgm_ops'})

Then in a search endpoint for titles, we can use:

stmt = select(Book).where(
    func.similarity(Book.title, q) > 0.3
).order_by(func.similarity(Book.title, q).desc()).limit(10)

Performance Numbers and Trade-offs

Before adding the GIN index and generated tsvector, a search query across 10,000 translations took ~800ms because it had to scan and cast text on the fly. After the index, the same query dropped to ~20-40ms, even with 100,000 translations. The pg_trgm index on titles improved fuzzy title search from 1.2s to 15ms.

The biggest trade-off was choosing PostgreSQL FTS over Elasticsearch. For our scale (a few hundred thousand rows), PostgreSQL is simpler to operate, requires no extra services, and integrates perfectly with our existing relational data. However, we know that if we grow to millions of documents or need advanced features like faceting, we may need to revisit Elasticsearch.

Another trade-off: language-specific configurations. PostgreSQL supports many languages out of the box, but for languages like Chinese or Japanese, full-text search requires different tokenization (e.g., pg_jieba or zhparser). We haven't tackled that yet; currently, we fall back to the simple config, which splits on whitespace—not ideal for those languages. That's an area for future work.

We also learned that storing translations in a JSON column was a mistake initially. Querying into JSON for search was painful and slow. Normalizing into a separate table made everything cleaner and allowed us to use PostgreSQL's FTS directly.

Lessons Learned

• Leverage PostgreSQL's built-in features before adding new infrastructure. It saved us from managing Elasticsearch and kept our deployment simple.
• Use generated columns and triggers to keep search vectors in sync. It eliminates the need for application-level index updates.
• Async SQLAlchemy with FastAPI works well, but be mindful of session management. We use async_sessionmaker and dependency injection to ensure sessions are closed after each request.
• Always test with real data volume. Our initial search was fine with 1,000 rows, but degraded quickly; adding indexes and generated columns was a straightforward fix once we measured.

Open Question for the Community

How do you handle full-text search for languages that PostgreSQL doesn't support natively (e.g., CJK)? We're considering integrating a tokenizer like pg_jieba for Chinese, but we'd love to hear about your experiences or alternatives.