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

We translated 10,000+ books across 5 languages—here's how we handled multilingual search and indexing with FastAPI and PostgreSQL.

At LectuLibre, we're building an AI-powered book translation service. Users upload an EPUB or PDF, and our platform translates it into multiple languages using LLMs like Claude and DeepSeek. While the translation pipeline is the flashy part, the backend infrastructure that serves translated books, especially search, turned out to be a significant engineering challenge.

Our backend is Python/FastAPI with PostgreSQL, deployed on a VPS. As our library grew to over 10,000 books across 5 languages, we hit a wall with search performance. This post is about how we solved multilingual full-text search using PostgreSQL's built-in capabilities, and the lessons we learned along the way.

The Problem: Slow, Language-Agnostic Search

Initially, we stored book metadata (title, author, description) in a simple books table. Search was implemented with SQL ILIKE queries:

SELECT * FROM books WHERE title ILIKE '%query%' OR author ILIKE '%query%';

This worked fine for a few hundred books, but as we scaled, queries took hundreds of milliseconds—often over 500ms for a single search. Worse, it ignored language-specific nuances like stemming, stop words, and diacritics. A user searching for "correr" (to run in Spanish) wouldn't find books with "corriendo" or "corrió".

We needed a robust full-text search solution that:

• Supported multiple languages with proper stemming and stop words.
• Handled diacritics and case-insensitivity.
• Returned results quickly, ideally under 50ms.
• Scaled horizontally without adding complex infrastructure.

After evaluating options like Elasticsearch and Meilisearch, we realized that PostgreSQL's built-in full-text search could meet our needs at our current scale (10k+ books) without the operational overhead of an external service.

Our Approach: PostgreSQL Full-Text Search with Per-Language Vectors

PostgreSQL provides full-text search through tsvector and tsquery types, with built-in text search configurations for many languages. Each configuration includes a stemmer, stop word list, and parsing rules.

Our plan was:

1. Store a tsvector for each book and each language it's available in.
2. Use a GIN index for fast lookups.
3. Query with the appropriate language configuration and rank results using ts_rank.

Because a single book can exist in multiple languages, we created a separate table to hold language-specific search vectors.

Database Schema

Here are the relevant tables (using SQLAlchemy models):

from sqlalchemy import Column, Integer, String, 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)
    original_language = Column(String(10), nullable=False)
    # other metadata fields...
    search_vectors = relationship("BookSearchVector", back_populates="book", cascade="all, delete-orphan")

class BookSearchVector(Base):
    __tablename__ = "book_search_vectors"
    id = Column(Integer, primary_key=True)
    book_id = Column(Integer, ForeignKey("books.id", ondelete="CASCADE"), nullable=False)
    language = Column(String(10), nullable=False)
    vector = Column(TSVECTOR, nullable=False)
    book = relationship("Book", back_populates="search_vectors")

    __table_args__ = (
        Index("ix_book_search_vectors_language_vector", "language", "vector", postgresql_using="gin"),
    )

We chose to store one tsvector per language rather than a combined vector. This allows us to search specifically in one language or across languages by querying multiple rows.

Generating Search Vectors

When a book is added or a new translation is completed, we generate the tsvector using PostgreSQL's to_tsvector with the appropriate language configuration. Since our backend is async, we used asyncpg directly for efficiency:

import asyncpg

async def update_search_vector(book_id: int, language: str, title: str, author: str, description: str):
    conn = await asyncpg.connect(DATABASE_URL)
    try:
        await conn.execute(
            """
            INSERT INTO book_search_vectors (book_id, language, vector)
            VALUES ($1, $2, to_tsvector($3::regconfig, $4 || ' ' || $5 || ' ' || $6))
            ON CONFLICT (book_id, language) DO UPDATE
            SET vector = EXCLUDED.vector
            """,
            book_id,
            language,
            language,  # e.g., 'english', 'spanish', 'french'
            title,
            author,
            description
        )
    finally:
        await conn.close()

Note: We didn't have a unique constraint on (book_id, language) initially, leading to duplicate rows. We added one after discovering the issue.

For languages not supported by PostgreSQL's built-in configurations (like some regional variants), we fall back to the 'simple' configuration, which lowercases and splits words but doesn't stem or remove stop words. We also applied the unaccent extension to remove diacritics, making searches more forgiving.

Querying with FastAPI

Our search endpoint accepts a query string and an optional language filter. If no language is specified, we search across all languages and merge results.

from fastapi import FastAPI, Query
from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession

app = FastAPI()

@app.get("/search")
async def search(
    q: str = Query(..., min_length=2),
    lang: str | None = Query(None, regex="^[a-z]{2,3}$"),
    db: AsyncSession = Depends(get_db)
):
    # Build the query
    if lang:
        tsquery = f"websearch_to_tsquery('{lang}', :q)"
        language_filter = f"AND language = '{lang}'"
    else:
        # Use 'simple' for cross-language search (no stemming)
        tsquery = "websearch_to_tsquery('simple', :q)"
        language_filter = ""

    sql = f"""
        SELECT b.id, b.title, b.author, b.original_language,
               ts_rank(sv.vector, {tsquery}) AS rank
        FROM book_search_vectors sv
        JOIN books b ON b.id = sv.book_id
        WHERE sv.vector @@ {tsquery}
        {language_filter}
        ORDER BY rank DESC
        LIMIT 20
    """
    result = await db.execute(text(sql), {"q": q})
    return result.fetchall()

We used websearch_to_tsquery because it handles user-friendly syntax (like Google search) and automatically adds & between words. For language-specific searches, we pass the language code directly.

Performance Results

Before optimization, our ILIKE search took 500-800ms on average for 10k books. After implementing tsvector with GIN index, the same searches now complete in 5-15ms—a 50-100x improvement. The index adds about 20% overhead to write operations, which is acceptable since search reads far outnumber writes.

We also monitored PostgreSQL memory settings. Initially, GIN index scans were slow due to low work_mem. Increasing it from 4MB to 16MB improved indexing speed by 30%.

Challenges and Trade-offs

Language Configurations

PostgreSQL ships with configurations for about 30 languages, but not all dialects are covered. For example, we had to create a custom configuration for Latin American Spanish by copying the spanish config and adjusting stop words. For languages without built-in support, we used simple and accepted less optimal stemming.

Updating Vectors

We initially tried database triggers to update tsvector automatically on insert/update. However, our async workflow made it tricky to manage, and we often ended up with stale data during translation updates. We moved to updating vectors in Python after translation completion, which gave us more control and easier debugging.

Cross-Language Search

When a user searches without specifying a language, we need to search across all languages. Using the 'simple' configuration avoids stemming but still provides decent results. However, for better relevance, we could combine results from multiple language-specific searches, but we haven't needed that yet.

PostgreSQL vs Elasticsearch

At our scale (10k books, <1M search vectors), PostgreSQL is more than sufficient. We saved ourselves the operational burden of running an Elasticsearch cluster. We may revisit this decision if we grow 10x.

Lessons Learned

1. Use database features before adding services. PostgreSQL full-text search is powerful and underutilized.
2. Index and tune. A GIN index is essential; don't forget to adjust work_mem and maintenance_work_mem.
3. Plan for multilingual from the start. Separating search vectors per language saved us from a painful migration later.
4. Test with real data. We underestimated how much stemming and stop words matter for user satisfaction.

Conclusion

Building a multilingual platform with FastAPI and PostgreSQL has been a rewarding journey. Leveraging PostgreSQL's full-text search allowed us to deliver fast, language-aware search without adding complexity. The code examples above are simplified but should give you a starting point.

Open question for the community: How do you handle full-text search for languages not supported by your database's built-in configurations? We'd love to hear your approaches.

If you're building a similar system, start with PostgreSQL full-text search—it might be all you need.