# Scaling Multilingual Search: Our FastAPI + PostgreSQL Architecture for Translating Books

> Source: <https://dev.to/jacob_gong/scaling-multilingual-search-our-fastapi-postgresql-architecture-for-translating-books-ijj>
> Published: 2026-10-24 03:01:07+00:00

*How we built a multilingual book platform with FastAPI and PostgreSQL's full-text search to handle translations in multiple languages.*

When we started building LectuLibre, an AI-powered book translation service, we knew we'd have to handle content in many languages. Users upload EPUB or PDF books, our backend translates them using LLMs like Claude and DeepSeek, and readers can browse and search the translated versions. That meant our database needed to store and search text in dozens of languages efficiently. Here's how we built that with FastAPI and PostgreSQL, and what we learned along the way.

Early on, we stored translated text as plain strings in a PostgreSQL `TEXT` column. Searching was as simple as `WHERE content LIKE '%query%'`. That worked for a demo with a few hundred books. But as our library grew to tens of thousands of books across Spanish, French, German, Japanese, and more, `LIKE` queries became painfully slow. A simple search for a common word could take 300–500 ms. Not acceptable for an API that needs to feel snappy.

We also had a deeper issue: language-specific search behavior. Searching for "café" in Spanish should match "cafe" without the accent, and stemming rules differ per language. A one-size-fits-all approach wouldn't cut it.

We considered external search engines like Elasticsearch or Meilisearch. They're powerful but add operational complexity—another service to deploy, monitor, and keep in sync. We were a small team running on a single VPS. We needed something simpler.

PostgreSQL's built-in full-text search (FTS) turned out to be the right fit. It supports language-specific text analysis, GIN indexes for speed, and it lives right next to our data. No sync jobs, no extra services.

Our stack is Python/FastAPI on the backend, PostgreSQL for data, and SQLAlchemy 2.0 for ORM with asyncpg as the async driver. We use Alembic for migrations and Pydantic for validation. The API is fully async, which pairs nicely with asyncpg and FastAPI's async endpoints.

For multilingual search, we decided to:

`translations` table, one row per language.`to_tsquery()` with the appropriate language configuration based on the user's query language.
Let's dive into the details.

Here's a simplified version of our SQLAlchemy models:

``` python
from sqlalchemy import Column, Integer, String, Text, ForeignKey, Index
from sqlalchemy.dialects.postgresql import TSVECTOR
from sqlalchemy.orm import declarative_base, relationship

Base = declarative_base()

class Book(Base):
    __tablename__ = "books"
    id = Column(Integer, primary_key=True)
    title = Column(String, nullable=False)
    original_language = Column(String(10), nullable=False)
    # ... other metadata
    translations = relationship("Translation", back_populates="book")

class Translation(Base):
    __tablename__ = "translations"
    id = Column(Integer, primary_key=True)
    book_id = Column(Integer, ForeignKey("books.id"), nullable=False)
    language = Column(String(10), nullable=False)  # 'en', 'es', 'fr', etc.
    content = Column(Text, nullable=False)  # translated full text
    # Generated tsvector columns for search
    content_search_en = Column(TSVECTOR)
    content_search_es = Column(TSVECTOR)
    content_search_fr = Column(TSVECTOR)
    # ... one per supported language
    book = relationship("Book", back_populates="translations")
```

We generate the tsvector columns automatically using a database trigger or a generated column. We chose **generated columns** (PostgreSQL 12+) because they're maintained by the database and don't require application logic.

Here's how we create a generated column in a migration:

``` python
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects.postgresql import TSVECTOR

def upgrade():
    op.add_column(
        "translations",
        sa.Column(
            "content_search_en",
            TSVECTOR,
            sa.Computed(
                "to_tsvector('english', content)",
                persisted=True
            )
        )
    )
    op.create_index(
        "ix_translations_content_search_en",
        "translations",
        ["content_search_en"],
        postgresql_using="gin"
    )
```

We do this for each language we support. The `persisted=True` makes it stored on disk, so updates to `content` automatically update the tsvector column. The GIN index then makes search lightning fast.

Our search endpoint accepts a `q` parameter and an optional `lang` parameter. If `lang` is provided, we search only that language's tsvector column. If not, we search across all columns using a combined query.

Here's a simplified FastAPI endpoint:

``` python
from fastapi import FastAPI, Query
from sqlalchemy import select, or_
from sqlalchemy.dialects.postgresql import to_tsquery
from sqlalchemy.ext.asyncio import AsyncSession

app = FastAPI()

@app.get("/search")
async def search_books(
    q: str = Query(..., min_length=2),
    lang: str = Query(None, description="Language code e.g. 'en', 'es'"),
    db: AsyncSession = Depends(get_db)
):
    query = select(Translation).join(Book)

    if lang:
        # Map language to its tsvector column and FTS configuration
        tsvector_col = getattr(Translation, f"content_search_{lang}")
        query = query.where(
            tsvector_col.op("@@")(to_tsquery(lang, q))
        )
    else:
        # Search across all languages
        conditions = []
        for lang_code in SUPPORTED_LANGUAGES:
            tsvector_col = getattr(Translation, f"content_search_{lang_code}")
            conditions.append(
                tsvector_col.op("@@")(to_tsquery(lang_code, q))
            )
        query = query.where(or_(*conditions))

    result = await db.execute(query.limit(50))
    translations = result.scalars().all()
    return translations
```

Note: `to_tsquery` is a SQLAlchemy function from `sqlalchemy.dialects.postgresql`. It accepts the language configuration name and the query string.

For accent-insensitive search, we use the `unaccent` extension. We create the tsvector columns using `to_tsvector(lang, unaccent(content))` and query with `to_tsquery(lang, unaccent(q))`. That way "cafe" matches "café".

Before optimization, a `LIKE` search on 100k rows took around 300–500 ms. After moving to FTS with GIN indexes, the same search returns in **10–20 ms**—a 20x improvement. Even with multiple language columns combined via `OR`, we stay under 50 ms for typical queries.

We currently support 12 languages, each with its own generated column and index. The storage overhead for tsvector columns is roughly 30–40% of the original text size, which is acceptable.

We chose PostgreSQL FTS because it eliminated a separate search service. For our scale (tens of thousands of books, maybe a few million rows eventually), it's more than sufficient. But we know its limits:

`pg_trgm` can help with misspellings, but it's not as sophisticated as Elasticsearch's analyzers.`ts_rank` works, but tuning relevance for multilingual content is tricky.
Having a separate tsvector column per language works but feels clunky. Adding a new language requires a new column and index, which means a migration and possible table lock. For now, it's fine. An alternative would be to store a `tsvector` built with the `simple` configuration and do language-specific processing at query time, but that loses stemming. We decided the explicit columns are worth it.

We store the full translated text in the `content` column. For large books (e.g., 500k+ words), this bloats the database. We experimented with storing only the first N characters for search and keeping the full text in object storage, but then searching beyond the excerpt fails. For now, we keep everything in PostgreSQL. The VPS has enough disk.

Ensure your database is UTF-8. We also set `lc_collate` to `en_US.UTF-8` for consistent sorting across languages. For accent-insensitive search, `unaccent` is a must.

We initially used `psycopg2` (sync) with FastAPI, but switched to `asyncpg` and SQLAlchemy's async support. It improved throughput under load. However, be careful with long-running transactions and connection pooling. We use `asyncpg`'s pool with `max_size=20` for our VPS.

LectuLibre now handles search across 12 languages with sub-50ms response times for the API. Users can search in their own language and find books translated into that language, with proper stemming and accent-insensitive matching. The architecture is simple enough to maintain with a small team, and we avoided the operational overhead of Elasticsearch.

One challenge we haven't fully solved: cross-lingual search. If a user searches in English but wants results in Spanish, our current system can't match semantically. We're exploring options like multilingual embeddings or an external search index. How are others handling cross-lingual search in PostgreSQL? We'd love to hear your approaches.

**Takeaway**: For a multilingual app at moderate scale, PostgreSQL's full-text search with generated tsvector columns is a pragmatic, low-maintenance choice. Pair it with FastAPI's async capabilities and you have a robust, fast search without extra infrastructure.