Align PostgreSQL Search Languages in FastAPI Book API

This guide shows you how to align PostgreSQL full-text search languages with your FastAPI book API, so that search queries match the language of the book content. You’ll learn how to set up a database column for language, use the correct text search configuration, and write queries that respect that language. This ensures accurate stemming and stop-word handling for multi-language book catalogs.

What You’ll Need

• A FastAPI project with a PostgreSQL database (using SQLAlchemy or asyncpg).
• Books table with at least a title and content column.
• Basic understanding of SQLAlchemy models and PostgreSQL full-text search.

Step-by-Step

1. Add a language column to your Book model. In your SQLAlchemy model, add a column like language (String) that stores the language code, e.g., ‘english’, ‘french’, ‘german’. Use ISO 639-1 codes or PostgreSQL’s built-in configuration names.
2. Create a migration (using Alembic) to add this column. Run alembic revision --autogenerate and then alembic upgrade head.
3. Set up a generated column for the search vector. In PostgreSQL, you can create a generated column that stores the tsvector with the correct language. Use a SQL expression like:
   ALTER TABLE books ADD COLUMN search_vector tsvector GENERATED ALWAYS AS (setweight(to_tsvector(language, title), 'A') || setweight(to_tsvector(language, content), 'B')) STORED;
4. Create a GIN index on the search_vector column for fast search: CREATE INDEX books_search_idx ON books USING GIN (search_vector);
5. In FastAPI, create a search endpoint that accepts a query parameter and optionally a language filter. For example, /books/search?q=history&lang=french.
6. Use SQLAlchemy to query with the language-specific configuration. If you didn’t use a generated column, you’d do:
   books = db.query(Book).filter(Book.search_vector.op('@@')(func.plainto_tsquery(lang, query))).all()
   But with the generated column, you can just filter on search_vector using the language from the column itself: filter(Book.search_vector.op('@@')(func.plainto_tsquery(Book.language, query))) – this uses each book’s own language.
7. Handle the case where language is not provided. If no language filter is given, you might want to search across all languages using a default configuration (e.g., ‘simple’) or try multiple configurations. For simplicity, you can default to English.
8. Test with different languages. Insert sample books in English, French, and German, and verify that search results respect the language of the content. For example, searching for “chat” should match a French book about cats, not an English book about chat.

Common Mistakes

• Using a fixed language configuration for all books, ignoring the per-book language. This leads to poor results for non-English content.
• Forgetting to create the GIN index, which makes queries slow on large datasets.
• Storing language codes that don’t match PostgreSQL’s configuration names (e.g., ‘en’ instead of ‘english’). Use the full names like ‘english’, ‘french’, ‘german’, etc.
• Not handling NULL language values – if a book has no language, to_tsvector will fail. Use a default like ‘simple’ or ‘english’.
• Using to_tsvector in a query without a stored column – this prevents index usage and is slow.

Tips

• Use setweight to prioritize title matches over content matches, as shown in the generated column.
• Consider using websearch_to_tsquery instead of plainto_tsquery for a more user-friendly search syntax (supports quotes and OR).
• For multi-language support, you can also store a separate tsvector for each language, but that’s overkill for most apps.
• Use ts_rank or ts_rank_cd to sort results by relevance.
• If you use SQLAlchemy, define the search_vector column in your model as a Computed type to keep it in sync automatically.

Troubleshooting

• Search returns no results even though the word exists. Check the language configuration – maybe the word is a stop word in that language. Try a different configuration or use websearch_to_tsquery.
• Error: function to_tsvector(character varying, text) does not exist – This means the language column is not of type regconfig. Cast it: to_tsvector(language::regconfig, content).
• Slow queries – Ensure the GIN index exists and that you are using the stored search_vector column, not computing it on the fly.
• Language code mismatch – If you store ‘en’ but PostgreSQL expects ‘english’, you’ll get errors. Map your codes to the full names in your application code.
• Generated column fails to add – If you already have data, you may need to backfill the language column first. Also, ensure the expression is immutable (use to_tsvector with a constant, not a mutable function).

Frequently Asked Questions

Can I use ISO language codes like ‘en’ instead of ‘english’?

PostgreSQL’s to_tsvector expects a regconfig value, which is the full name like ‘english’. You can map ‘en’ to ‘english’ in your code, or store the full name directly.

What if a book has no language specified?

Use a default like ‘simple’ or ‘english’. You can set a default value in the column definition, or handle NULL in the generated column expression with COALESCE(language, 'english').

How do I search across multiple languages at once?

You can use to_tsvector('simple', content) for a language-independent search, but you’ll lose stemming. Alternatively, you can create multiple tsvector columns, one per language, and search all of them with OR.

Can I update the language of a book after it’s created?

Yes, if you use a generated column, it will automatically update when the language changes. If you store the tsvector manually, you need to update it in the same transaction.

How do I sort by relevance?

Use ts_rank(search_vector, query) in the ORDER BY clause. For example: ORDER BY ts_rank(search_vector, plainto_tsquery(language, query)) DESC.

Is there a performance difference between a generated column and a regular column?

A generated column is computed at write time, so reads are fast. A regular column that you update manually has the same read performance, but you must ensure it’s updated correctly. The GIN index is the key for search speed.

Related

• Dota 2 Keeps Crashing? Fixes That Work
• Disk Write Protected? Fix It in 5 Steps
• Bootstrap Accordion Plus/Minus Icon Toggle