# Building a Multilingual Book Platform with FastAPI and PostgreSQL Full-Text Search > Source: > Published: 2026-09-30 03:01:47+00:00 *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. 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: 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. 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: `tsvector` for each book and each language it's available in.`ts_rank`. Because a single book can exist in multiple languages, we created a separate table to hold language-specific search vectors. Here are the relevant tables (using SQLAlchemy models): ``` python 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. 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: ``` python 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. 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. ``` python 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. 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%. 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. 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. 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. 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. `work_mem` and `maintenance_work_mem`. 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.