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):
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)
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:
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.
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)
):
if lang:
tsquery = f"websearch_to_tsquery('{lang}', :q)"
language_filter = f"AND language = '{lang}'"
else:
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.