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

LectuLibre, an AI-powered book translation platform, replaced slow SQL ILIKE queries with PostgreSQL full-text search to handle multilingual search across more than 10,000 books in 5 languages. The team stores one tsvector per book per language in a dedicated table with a GIN index, using PostgreSQL's language-specific text search configurations for stemming and stop words, and generates vectors asynchronously via asyncpg. The approach avoids the operational overhead of Elasticsearch or Meilisearch at their current scale.

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.