跳到正文
DEV Community· 龚旭东·· 3 小時前AI 評分45

如何以 FastAPI 和 PostgreSQL 建構多語言圖書搜尋架構

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

AI 導讀

LectuLibre 以 FastAPI 和 PostgreSQL 全文搜尋建構多語言圖書平台，使用 LLM 翻譯書籍，並讓讀者瀏覽及搜尋譯本。平台將各語言譯文存於 translations 表，為各語言建立生成式 tsvector 欄位及 GIN 索引，再按搜尋語言使用相應設定查詢。此架構利用 PostgreSQL 內置搜尋，無需額外搜尋服務或同步工作。

正文

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.

The Problem: Multilingual Content and Search

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 Approach: FastAPI + Async PostgreSQL

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:

  • Store each book's translated content in a translations table, one row per language.
  • Add generated tsvector columns for each supported language on that table.
  • Create GIN indexes on those columns for fast lookups.
  • Use PostgreSQL's to_tsquery() with the appropriate language configuration based on the user's query language.

Let's dive into the details.

Implementation Details

Data Model

Here's a simplified version of our SQLAlchemy models:

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.

Alembic Migration for Generated Columns

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

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.

Search Endpoint

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:

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é".

Performance Numbers

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.

Trade-offs and Lessons Learned

PostgreSQL FTS vs Elasticsearch

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:

  • No fuzzy matching by default: pg_trgm can help with misspellings, but it's not as sophisticated as Elasticsearch's analyzers.
  • Ranking is basic: PostgreSQL's ts_rank works, but tuning relevance for multilingual content is tricky.
  • Scaling: If we ever hit hundreds of millions of rows, we might need to offload search to a dedicated engine.

Handling Many Languages

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.

Content Storage

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.

Unicode and Collations

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.

Async Pitfalls

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.

Results

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.

Open Question

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.

來源：DEV Community · dev.to

#教學/實踐#搜尋#部署/工程