A Step-by-Step Guide to Optimizing Vector Search: A Game Company's Intelligent Customer Service and Recommendation System on OceanBase

Author: Zhou Qiang, Senior Development Engineer at a card game company

The Unique Advantages of Vector Databases and Our Selection Experience

A vector database is a database system specifically designed to store, index, and query high-dimensional vector data. It can efficiently process the embedding vectors generated by machine learning models and supports fast similarity-based retrieval.

Compared with traditional databases, vector databases exhibit unique characteristics across many dimensions, allowing them to excel in areas beyond those covered by the former. As shown in Figure 1, traditional databases are mainly used to store structured data and query based on exact matching, making them suitable for business data management. Vector databases, on the other hand, store vector data and search based on similarity queries, making them mainly suitable for AI and machine learning application scenarios.

Figure 1: Traditional database vs. vector database

Today, vector databases are widely used in intelligent retrieval. Unstructured data such as text, images, video, and audio is embedded into vectors through deep neural networks and stored in a vector database, so that the business can perform semantic similarity searches based on the vector database, as shown in Figure 2.

Figure 2: The workflow of intelligent retrieval

Mapped to real business scenarios, the roles of a vector database are:

• In intelligent search engines, to understand user intent and return semantically relevant results.
• In recommendation systems, to perform personalized recommendations based on content similarity.
• In question-answering systems, to find the most relevant answer from a knowledge base.
• In content retrieval, to perform cross-modal search (searching for images by text, for text by images, etc.).
• In data analytics, to discover hidden correlations and patterns among data.

Among the many vector databases, we chose OceanBase to support our intelligent business scenarios, mainly for the following four reasons.

1. Integrated architecture: ensures consistency at low operational cost.

Thanks to OceanBase’s integrated architecture, we can use a single database to store both structured data and vector data, eliminating the challenges of cross-database synchronization and consistency. Its transactional ACID guarantees also directly cover vector tables, ensuring strong data consistency. In addition, OceanBase offers a unified SQL interface and management tools, which lowers our operational cost.

2. Native vector capabilities: guaranteed business performance.

• A native vector column type (VECTOR), supported at the database kernel level.
• HNSW / IVFFlat index algorithms that optimize retrieval performance.
• Millisecond-level Top-K retrieval for blazing-fast similarity search.

3. Distributed elastic scaling: supports ultra-large data volumes.

It supports a distributed architecture and data sharding, with processing power far exceeding single-machine databases—especially excellent performance when handling tens of billions of vectors or several TB of data.

4. Fast ecosystem integration: compatible with the MySQL stack, low onboarding cost.

Because OceanBase supports the MySQL protocol, we can access it via Python or Java SDKs, which lowers the onboarding cost. Moreover, thanks to OceanBase’s rich ecosystem, it adapts to various AI application development frameworks such as LlamaIndex, LangChain, and Dify, making it even more convenient to use.

In summary, using OceanBase incurs essentially zero learning cost at the development level; it adapts directly to development frameworks and is easy to use. We have now applied OceanBase across several scenarios. Below, we use the intelligent customer service and UGC community recommendation system as examples to describe our experience.

Intelligent Customer Service Achieves Millisecond-Level Retrieval, with a 300% Efficiency Boost

Before going into the details of the intelligent customer service workload, let’s first look at the problems with traditional customer service today, so as to appreciate the key role of vector search in an intelligent customer service system.

Most of us have used traditional customer service in daily life, and its typical problems are slow responses—for example, having to wait in a queue, with even longer waits during peak hours. During service, because product information is not updated in time or because each agent understands the product differently, answers can be inconsistent, and even an agent’s mood can affect service quality.

After introducing AI-powered customer service, the above problems with traditional customer service can be largely resolved. First, intelligent customer service can respond around the clock with no waiting and replies within seconds, improving user satisfaction. Second, intelligent customer service analyzes user behavior and provides personalized service; for answers the customer is dissatisfied with, it can keep learning and optimizing itself, continuously improving service quality.

The Intelligent Customer Service Workflow and Key Technologies

As shown in Figure 3, when the intelligent customer service receives a customer message, it first performs intelligent summarization to extract the core intent from the message, then performs vector matching in the knowledge base to find the topic most similar to the customer’s message. Next, it selects the corresponding label based on the topic and performs route matching, beginning a multi-source data query. Several situations may arise during the query: if it relates to an error or bug, it needs to look up the fault record and fix status in the bug table; if it relates to gameplay, it needs to query the game knowledge base; if it is a ticket type, it needs to query historical tickets. In short, through multi-source data querying and information integration, it generates a personalized reply or a professional, accurate solution.

Figure 3: The workflow of the intelligent customer service system

In this intelligent customer service workflow, the most critical step is vector search—that is, finding the topic most similar to the question. The other technical details include keyword query, rerank reranking, the label routing system, and LLM-based intelligent generation, which are relatively complex steps.

• Vector search: quickly matches relevant topics based on semantic similarity, improving recall and accuracy.
• Keyword query: supplements exact matching to handle technical terms and specific nouns.
• Rerank reranking: precisely orders the preliminary results to ensure the most relevant content comes first.
• Label routing system: intelligently routes to the corresponding data source based on the topic type for precise querying.
• LLM-based intelligent generation: integrates multi-source information to generate context-relevant, professional customer service replies.

So how do we ensure the information given by the intelligent customer service is valid and accurate?

The key lies in the quality of vector search. If retrieval accuracy is low, it cannot precisely match the user’s business intent. Therefore, embedding quality determines “what can be retrieved”—it is the upper bound of retrieval quality; the vector database’s retrieval effectiveness (ANN index, recall) determines “whether it can actually be pulled up”—it is the lower bound of retrieval quality. For instance, the two below have a multiplicative relationship, and the lower of the two is where the bottleneck lies.

• Supports HNSW/HNSW_SQ/HNSW_BQ indexes, with a maximum index column dimension of 4096.
• Supports IVF/IVF_SQ/IVF_PQ indexes, with a maximum index column dimension of 4096.

Because embedding vectors are usually produced by open-source models—such as the common open-source Chinese embedding models Qwen3-Embedding-8B and BGE-large-zh-v1.5—we have no room to optimize there. Instead, we turned to optimizing vector database retrieval to better match the actual data.

Vector Database Retrieval Optimization

1. Data Table Design

Below is a concrete knowledge base data table design. You can see it contains the embedding model and the question in a single table. For the vector and index algorithm, we used a 768-dimensional vector, the HNSW index algorithm, and a cosine-similarity index.

CREATE TABLE `data_kf_faq` (
  `id` varchar(36) NOT NULL COMMENT 'Primary key ID, UUID format, uniquely identifies a FAQ record',
  `project_id` varchar(100) DEFAULT NULL COMMENT 'Project ID, used to distinguish data ownership across projects',
  `kid` varchar(100) DEFAULT NULL COMMENT 'Knowledge item ID (Knowledge ID)',
  `question` text DEFAULT NULL COMMENT 'FAQ question text',
  `embedding` VECTOR(768) DEFAULT NULL COMMENT 'Vector representation of the question, used for semantic search (768-dimensional vector)',

  PRIMARY KEY (`id`) COMMENT 'Primary key index, ensures record uniqueness',

  KEY `idx_kid` (`kid`) COMMENT 'Plain index on the kid field, speeds up lookups by knowledge item ID',
  KEY `idx_proj_kid` (`project_id`, `kid`) COMMENT 'Composite index of project ID + knowledge item ID, speeds up multi-condition exact queries',

  VECTOR KEY `idx_embedding_faq` (`embedding`) 
    WITH (DISTANCE = COSINE, TYPE = HNSW, LIB=VSAG) COMMENT 'Vector index using the HNSW algorithm and cosine-similarity computation, supports semantic approximate search'
)

2. Use jieba for Tokenization and Extract Business Keywords

The keyword tables are divided into a main table and an auxiliary table, mainly used to store the knowledge base’s embedding model as well as the decomposition of question keywords, including the business’s special keywords. Queries combine the main and auxiliary tables. If the vector query performs well, the keyword query can be skipped.

CREATE TABLE `data_kf_faq_kw` (
  `faq_id` CHAR(36) NOT NULL COMMENT 'ID of the FAQ main table (UUID), references data_kf_faq.id',
  `kw`     VARCHAR(100) NOT NULL COMMENT 'Keyword (a single term), used for tokenized retrieval',
  `project_id` BIGINT NOT NULL COMMENT 'Project ID, used to distinguish data across projects',
  PRIMARY KEY(`faq_id`, `kw`) COMMENT 'Composite primary key, ensures keywords are not duplicated within the same FAQ',
  INDEX `idx_kw_proj` (`kw`, `project_id`, `faq_id`) COMMENT 'Quickly query the FAQ list by keyword + project ID'
)  COMMENT='FAQ keyword table, used to quickly locate FAQ records by keyword';

3. Retrieval Steps and Optimization

Retrieval is divided into five steps and involves two optimizations.

Step 1: Text vectorization, with the command below.

embedding = vectorize_text(question)

Step 2: Vector similarity search. After text vectorization is complete, we need to search the vector database to pull up some data, searching according to data complexity. Below is a SQL statement that queries OceanBase directly.

vector_results = database.vector_search(
        project_id=project_id, 
        embedding=embedding, 
        top_k=20
    )

    SELECT id, kid, project_id, question, keywords, 
                       COSINE_DISTANCE(embedding, %s) as distance,
                       (1.0 - COSINE_DISTANCE(embedding, %s)) as similarity
                FROM {table_name}
                WHERE project_id = %s
                ORDER BY distance ASC
                LIMIT %s

Step 3: Supplementary keyword search. The keyword supplement involves both a vector query and a keyword query. Keywords refer to proprietary game terms that the AI may struggle to understand and that must be treated as separate tokens.

keyword_results = database.search_by_keywords(project_id, keywords)

Step 4: Merge and deduplicate the candidate results from the keyword query.

seen_ids = set()
result = []
for item in vector_results + keyword_results:
    if item['id'] not in seen_ids:
        seen_ids.add(item['id'])
        result.append(item)

Step 5: Intelligent reranking. After the previous steps are complete, we obtain a best-match result, query the data source based on label routing, and hand it off to the LLM for a personalized reply.

ranked_results = rerank_question(question, result)
# Sort by score in descending order
sorted_ranked_results = sorted(ranked_results, key=lambda x:x.get("rerank_score", 0), reverse=True)

# Return the best-match result
sorted_ranked_results[0]

When implementing the above retrieval steps in a real business environment, two optimization points are also involved.

The first is intelligent control of the candidate count. If we judge a customer question to be complex, we need to retrieve more data for comparison; if it is simple, we don’t need to retrieve as much.

# Optimization: dynamic adjustment
def adaptive_search_size(project_id, question_complexity):
    if question_complexity > 0.8:  # Complex question
        return {"vector_k": 20, "keyword_limit": 30}
    else:  # Simple question
        return {"vector_k": 10, "keyword_limit": 10}

The second is an early-stopping mechanism. If the match in Step 2—the vector similarity search—is already very high, there is no need to proceed to the subsequent keyword search and reranking; we can return the best-match result directly.

# Optimization: high-confidence early stop
if vector_results and vector_results[0]['similarity'] > 0.95:
    logger.info("High-confidence match, skipping keyword search and reranking")
    return format_high_confidence_result(vector_results[0])

The Main Value of Using OceanBase as the Vector Database

In the intelligent customer service workload, after adopting OceanBase, we gained considerable benefits in data architecture, retrieval performance, and operations:

• Because vector data and structured data are stored together, operational costs dropped by 50%.
• After HNSW optimization, we achieved millisecond-level retrieval performance, boosting customer service efficiency by 300%.
• Rapid multi-node scale-out enabled 99.99% availability.
• OceanBase is compatible with the MySQL protocol, so our operations and development staff onboarded quickly with almost zero learning cost, yielding very high development efficiency.

A UGC Community Intelligent Recommendation System, with 75% Lower Latency

Architecture Comparison: Traditional Recommendation System vs. Integrated Intelligent Recommendation System

As shown in Figure 4, the runtime path of a traditional recommendation system architecture is: when a user requests recommendations, the API service queries and retrieves user information from MySQL, then pulls similar data from the vector database, and then goes back to MySQL to supplement the content details. After querying the data here, it may also need to cache it in Redis before finally returning the result to the user. The entire process involves four network round trips—a long path with high latency and high complexity. And because multiple business systems are involved, it is hard to guarantee data consistency.

Figure 4: Traditional recommendation system architecture

After integrating OceanBase, we can fulfill all the query needs of the entire intelligent recommendation system with a single database (see Figure 5). Using OceanBase, only one query is needed to achieve the combined results of the MySQL query, vector database query, and Redis cache steps in the traditional recommendation pipeline. In other words, by replacing multiple databases with a single OceanBase, we gained lower network latency, a simpler architecture, and strong data consistency.

Figure 5: The intelligent recommendation system architecture using OceanBase

Figure 6 shows the detailed architecture of the UGC community intelligent recommendation system built on OceanBase. You can see that OceanBase achieves both vector storage and relational data storage while keeping storage unified. Because OceanBase is highly compatible with MySQL syntax and protocol, the originally MySQL-based UGC community recommendation system could be smoothly migrated to OceanBase. The DBA only needed to handle some compatibility items—no development rework was involved—while the development side gained transactional consistency guarantees with no extra effort.

Figure 6: The UGC community intelligent recommendation system architecture based on OceanBase

Beyond the advantages at the system architecture and development-rework levels, the OceanBase-based intelligent recommendation system also brings three key technical advantages.

1. Native vector type support.

OceanBase natively supports vector types. For example, you can embed the post content and store it directly in the posts table, with no need to introduce a separate vector database.

CREATE TABLE posts (
    post_id BIGINT PRIMARY KEY,
    title VARCHAR(255),
    content TEXT,
    -- Native vector type, no extra storage system needed
    content_vector VECTOR(768),
    view_count INT DEFAULT 0,
    -- Vector index, query performance rivals dedicated vector databases
    VECTOR INDEX idx_content_vec(content_vector) WITH (distance=cosine, type=hnsw)
);

2. Transactional consistency guarantees.

Within a single transaction, OceanBase can update both structured data and vector data at once, guaranteeing transactional consistency. If you used a traditional database instead, you would need to introduce a separate vector database, which involves data synchronization between two databases and cannot guarantee transactional consistency.

-- Update structured data and vector data within the same transaction
BEGIN;
UPDATE posts SET view_count = view_count + 1 WHERE post_id = ?;
UPDATE users SET short_term_vector = ? WHERE user_id = ?;
COMMIT;

3. One query, multi-path recall.

Leveraging OceanBase’s vector computation, you can complete all the complex recommendation logic in a single SQL statement—including fusing short-term and long-term vectors, filtering out already-seen content, time filtering, and so on—without performing complex processing steps on the business side, which is very friendly to business development.

-- Leverage OceanBase's vector computation to complete complex recommendation logic in a single SQL
WITH user_vectors AS (
    SELECT short_term_vector, long_term_vector 
    FROM users WHERE user_id = ?
)
SELECT 
    p.post_id, p.title,
    -- Fuse the dual-vector scores
    0.7 * COSINE_SIMILARITY(p.content_vector, u.short_term_vector) +
    0.3 * COSINE_SIMILARITY(p.content_vector, u.long_term_vector) AS score
FROM posts p, user_vectors u
WHERE p.created_at > DATE_SUB(NOW(), INTERVAL 7 DAY)
-- Filter out already-seen content
AND NOT EXISTS (SELECT 1 FROM user_actions WHERE user_id = ? AND post_id = p.post_id)
ORDER BY score DESC LIMIT 50;

The Innovation of Dual Vectors in User Interest Modeling

The UGC community intelligent recommendation system realized an innovation based on dual vectors for user interest modeling: a short-term vector to capture immediate interest and a long-term vector to maintain the user’s stable preferences. Each player can have two vectors representing short-term and long-term interests: the long-term vector represents fixed interests, and the short-term vector represents the current focus. After dynamically fusing the query results of the two vectors with a certain weighting, a vectorized personalized recommendation is generated.

Figure 7: The dual-vector innovation based on user interest modeling

Short-Term Vector: Capturing Immediate Interest

The short-term vector is used to capture the user’s immediate interest, so after the user performs a query action, the system updates that user’s data in real time. The data storage characteristic of the short-term vector is fast response, capturing the core points the user is most focused on.

def update_short_term_vector(user_id, post_vector, action_type):
    # Behavior weights: different behaviors reflect different interest intensities
    weights = {'view': 0.1, 'like': 0.3, 'collect': 0.5}
    
    # Exponential moving average: new interests gradually replace old ones
    new_vector = 0.85 * current_vector + 0.15 * weights[action_type] * post_vector
    
    # Real-time update, takes effect immediately
    execute_sql("UPDATE users SET short_term_vector = ? WHERE user_id = ?", 
                [new_vector, user_id])

Long-Term Vector: Maintaining Stable Preferences

The long-term vector is mainly used to maintain the user’s core interests. It is generally recomputed in a batch once a day, and the computation dimension incorporates the user’s behavior weights over the past 30 days.

-- Compute long-term interest in a daily batch
UPDATE users u SET long_term_vector = (
    SELECT VECTOR_NORMALIZE(
        VECTOR_SUM(
            -- Time decay: recent behaviors carry higher weight
            VECTOR_MULTIPLY(p.content_vector, 
                EXP(-TIMESTAMPDIFF(DAY, ua.action_time, NOW()) / 30.0) *
                -- Behavior weight: deeper interactions carry higher weight  
                CASE ua.action_type WHEN 3 THEN 0.5 ELSE 0.1 END
            )
        )
    )
    FROM user_actions ua 
    JOIN posts p ON ua.post_id = p.post_id
    WHERE ua.user_id = u.user_id 
    AND ua.action_time > DATE_SUB(NOW(), INTERVAL 30 DAY)
);

The Benefits of Dual Vectors

Let’s illustrate the benefits of dual vectors with an example. Suppose there is an RPG player, Xiao Wang, who usually focuses on story-driven games. One day his graphics card breaks, and he needs to buy a new one on an e-commerce platform. At this point, if there were only a single vector representation, his profile might shift to “graphics card hardware enthusiast”—but in reality, after buying the card, Xiao Wang likely went right back to playing games. Therefore, the dual vectors based on user modeling preserve both Xiao Wang’s long-term, stable RPG game data and his short-term, immediate interest data, making the user profile judgment more accurate.

Summary of the Benefits of the Integrated Intelligent Recommendation System

As shown in Figure 8, after we adopted OceanBase in the UGC community intelligent recommendation system: recommendation latency dropped from 200 ms to 50 ms; storage space was reduced by 40%; and operational complexity was simplified by 75%, going from operating 4 systems to operating just 1 system. In addition, it completely resolved the occasional data inconsistency the system experienced with the traditional architecture.

Figure 8: The benefits of using OceanBase in the UGC community intelligent recommendation system

That concludes our company’s experience with the selection and application of vector databases. Although our business revolves around games, customer service and recommendation system scenarios are quite common, so we hope our experience offers some reference value.

Finally, we recommend the WeChat official account “Lao Ji’s Tech Talk,” run by Lao Ji, the OceanBase open-source lead. It continuously publishes a variety of technical content related to #Databases, #AI, and #Tech Architecture. Friends who are interested are welcome to follow!

“Lao Ji’s Tech Talk” not only hopes to keep bringing you valuable technical sharing, but also hopes to contribute to the open-source community together with everyone. If you appreciate the OceanBase open-source community, please light up a little star ✨! Every Star you give is the motivation behind our efforts.