How to solve PostgreSQL multilingual full-text search limitations with AlloyDB AI

AlloyDB powers enterprise-grade search for some of the largest organizations, providing robust hybrid search capabilities that combine text, vector, and keyword searches into a simple ranked SQL query. And with our recent launch of RUM index support, AlloyDB customers now have even more powerful full-text search capabilities at their fingertips. 

However, database developers often face limitations when indexing continuous text in logographical languages like Chinese, Japanese, and Korean, where traditional whitespace-based tokenization fails. Gemini’s multilingual capabilities enable you to intelligently parse text in these languages to implement intelligent word segmentation and stop-word removal, but orchestrating row-wise API calls on massive datasets is slow and fragile. Now you can integrate the world knowledge of Gemini models natively into your database using AlloyDB AI Functions, enabling highly accurate full-text search for logographical languages without the administrative overhead of complex ETL pipelines.

Continuous text and logographical languages

To understand why this native integration is such a significant advancement, we must first examine the underlying mechanics of text search and why traditional indexing methods fail when processing continuous text.

To build an effective full-text search index in PostgreSQL, the engine must parse text into search tokens (lexemes) using the to_tsvector function. By default, standard text search configurations like simple or english assume that words are separated by whitespace. The database engine extracts search terms by splitting the input string at these spaces.

However, Chinese and other logographical languages do not use spaces between words. Words are written continuously, with punctuation serving as the only boundaries. Because of this, standard PostgreSQL parsers fail to extract individual keywords. Instead, they treat entire sentences or long clauses as a single, continuous lexeme.

For example, consider this input string: "你们研究所有十个图书馆" (Your research institute has ten libraries)

Without spaces, passing this to to_tsvector('simple', ...) produces a single, massive lexeme: '你们研究所有十个图书馆'

If you search for "研究所" (research institute) or "图书馆" (library), the query fails to return a match. The keywords are trapped inside the larger string, forcing you to search for the exact, long-form sentence to get a result.

Traditional workarounds and their limits

You might try to resolve this using traditional tools and pipelines, each of which introduces significant operational friction or accuracy limits:

1. Third-party database extensions: Extensions like zhparser or pg_jieba add Chinese tokenization to PostgreSQL. However, these are often not supported in fully-managed database environments. They also rely on static dictionaries, which frequently fail to parse modern jargon, brand names, or context-dependent terms correctly.

2. External preprocessing pipelines: Exporting text to an external application (such as a Python microservice running jieba or spaCy) to insert spaces before saving it to the database. This pattern introduces substantial ETL complexity, network latency, and data exposure risks by moving your data out of the database tier.

3. Inadequacy of rule-based and dictionary-driven segmentation: Traditional tokenizers rely on static dictionaries and hand-coded syntactic rules to split text. They often struggle to resolve semantic ambiguity, where the exact same sequence of characters must be segmented differently depending on the context. To perform accurate segmentation, you need the world knowledge and contextual intelligence of a large language model like Gemini.

In-database pre-processing with Gemini

AlloyDB AI bypasses these workarounds — and their limits — by introducing native, in-database AI Functions like ai.generate(). This allows you to call Gemini directly from SQL, keeping your data and intelligence in one place.

This approach provides three core advantages:

• No data movement: All text preprocessing and segmentation happen directly within the database engine. This minimizes network latency and keeps your data protected within your database boundaries.

• In-database intelligence: You do not need to build, deploy, or maintain external microservices or orchestration frameworks. The database engine coordinates the model calls natively.

• Stored procedure-based batching: By using a PL/pgSQL stored procedure with array aggregation, you can process rows in parallel batches, unpack the results safely using GENERATE_SERIES, and commit each batch immediately. This prevents database memory exhaustion, bypasses row lock contention, and supports stable, performant execution even when handling massive tables.

Implementing semantic word segmentation

To implement this solution, we will create a database table where the raw content, the segmented text, the search vector, and the vector embeddings are stored together.

By defining search_vector and embedding as generated columns, AlloyDB automatically updates both the full-text search index and the vector embeddings whenever the content_segmented column is updated. This reduces your application-side logic to a single update statement.

Step 1: Document batch segmentation

If you consult the AlloyDB AI documentation, it recommends using cursor-based processing when dealing with large datasets (10,000 to millions of rows) to avoid memory bottlenecks.

However, the documentation’s cursor examples focus on append-only operations — streaming text into a new, empty table using INSERT. Our use case requires an in-place UPDATE on our live documents table. Implementing this with a raw cursor loop in a standard anonymous block (DO $$) introduces three important production hazards: excessive row locking that freezes live applications, a rollback risk if a network blip occurs, and alignment risks where parallel cursors fall out of step.

To mitigate these challenges and accelerate performance, we use a stored procedure configured with high-throughput array-based batching:

To run this preprocessing pipeline across your entire table, simply call the stored procedure:

This stored procedure approach provides three benefits that directly solve your production challenges:

1. Parallelized array aggregation (solving the sequential bottleneck): By aggregating the batch into arrays and calling ai.generate(prompts => ...) with the array, AlloyDB batches the model requests and executes them in parallel. This is faster than processing rows one-by-one sequentially.

2. Safe index-based unpacking (solving cursor desync): Using GENERATE_SERIES to unpack the array indexes maps the model output back to the correct document ID, reducing the risk of parallel streams falling out of step.

3. Immediate commits and lock release (solving blocking and rollback risks): Committing the transaction at the end of each loop iteration (COMMIT;) is an essential optimization. It immediately saves progress to disk and releases row locks, preventing long-running transactions from freezing your live application and ensuring a network blip won’t roll back hours of work.

Once this pipeline runs, our example sentence is stored in content_segmented as: "你们 研究所 有 十个 图书馆"

Step 2: Choosing simple vs. english

When defining your tsvector generated column, you must choose the appropriate PostgreSQL text search configuration. This choice depends on your dataset:

• When to use simple: If your database contains only Chinese text, the simple configuration is ideal. It converts text to lowercase but does not perform stemming or default stop-word removal. Since the model prompt already handles semantic segmentation and custom stop-word filtering, the simple configuration maps directly to the model’s optimized output without further modification.

• When to use english: In modern enterprise applications, Chinese documentation and user queries frequently contain embedded English terms (e.g., product codes, brand names, or technical terms). In these bilingual scenarios, the english configuration is superior. The English Porter stemmer leaves non-ASCII Chinese characters completely intact as exact lexemes, while automatically normalizing the English terms (e.g., stemming “running” to “run”) and filtering out generic English stop words (“the”, “and”). This achieves unified bilingual search capabilities without requiring separate columns or complex routing logic.

Step 3: Query-time preprocessing

Segmenting the document content is only half the battle. To match the indexed data, the incoming search queries must be pre-processed using the same Gemini-based segmentation logic.

We can take this a step further to improve search precision. We can instruct the model to act as an intelligent stop-word filter, stripping away low-value grammatical noise that would otherwise clutter your search results:

• Grammatical particles (e.g., 的, 了)

• Pronouns (e.g., 你, 我们)

• Comparison words (e.g., 比, 最)

• Question words (e.g., 怎么, 为什么)

For example, we can process a user query on the fly using a SQL query:

The model processes this query and returns the keyword string: "研究所 图书馆"

Step 4: executing the search with RUM

With your documents segmented and indexed, you can create a RUM index on your generated search_vector column. A RUM index is an index type that stores lexeme positions directly. This helps AlloyDB calculate search relevance and word distance directly within the index, avoiding the slow re-scan operations required by traditional GIN indexes.

To run a search, you convert your preprocessed query string (“研究所 图书馆”) into a search query using plainto_tsquery and execute it against the RUM index. You can use the RUM distance operator () to sort the results by relevance:

Because the RUM index calculates the distance score directly, this query executes with high efficiency, returning relevant matches in milliseconds.

Extending to hybrid search

While keyword-based text search is excellent for finding exact matches, it can miss relevant documents that use different terminology. To solve this, you can combine your segmented full-text search with semantic vector search using the multilingual gemini-embedding-001 model. This pattern, known as hybrid search, retrieves results that are both lexically and semantically relevant.

AlloyDB makes it easy to run hybrid search. You can create a ScaNN index (Google’s vector index technology) on your embedding column and combine it with your RUM index.

Creating the ScaNN index

To accelerate your vector search, you create a ScaNN index on the embedding column:

Running the hybrid search query

To combine the results of your vector and text searches, you can use a SQL query that implements Reciprocal Rank Fusion (RRF). RRF is a rank-based algorithm that merges multiple search result lists into a single, unified list by assigning a score to each document based on its rank in the individual lists.

The following query performs both searches in parallel using Common Table Expressions (CTEs), joins the results using a FULL OUTER JOIN, and calculates the final RRF score:

In this query:

• The vector_search CTE uses the ScaNN index to find the top 10 documents that are semantically closest to your query, using the gemini-embedding-001 model.

• The text_search CTE uses the RUM index to find the top 10 documents that match your segmented keywords, using the RUM distance operator for ranking.

• The final select statement joins these lists and calculates the RRF score using the standard constant of 60. The top 5 results are returned, providing a precise blend of exact keyword matches and semantic matches.

Why AlloyDB AI is ideal for search

By using AlloyDB AI to solve the challenges of multilingual and hybrid search, you build a robust, scalable, and cost-effective foundation for your enterprise AI applications.

• Native, in-database intelligence: By running model processing directly within AlloyDB AI, you avoid the cost, latency, and data exposure risks of moving transactional data to external AI services.

• Enterprise-grade search performance: Combining ScaNN vector search (built on Google’s search technology) and RUM full-text search in a single relational database delivers fast, accurate search outcomes without needing to maintain separate, complex search engines.

• High context-aware accuracy: Unlike static, rule-based dictionaries that struggle with modern terminology, Gemini‘s world knowledge brings deep semantic understanding to word segmentation, providing high search precision.

• Operational simplicity: You get robust bilingual and hybrid search capabilities using standard SQL. This means you can build and scale AI applications using the database skills you already have, without learning new APIs or managing complex external pipelines.

What’s next

Giving your applications the ability to safely and efficiently interact with transactional data moves us away from fragmented data silos and toward an architecture where AI can reliably access enterprise truth.

Ready to build? Discover AlloyDB with a 30-day free trial, and dive into the Getting started with hybrid search in AlloyDB Codelab to start creating intelligent search experiences in your applications today.