How PolarDB IMCI Unifies Vector Search and Embedding in One SQL Engine

1. Introduction: Hidden challenges of AI application development

In the current large‑model‑driven AI wave, vectors are essential for building RAG knowledge bases and long‑term memory agents. A typical vector data flow consists of two core steps: writing (embedding unstructured data into vectors and storing them in a vector index) and retrieval (converting user queries to vectors and searching the index).

Developers often face a split tech stack: vector indexes, embedding models and business databases are separate systems, causing hidden costs such as development complexity, operational overhead, and high learning curve for mixed vector‑scalar queries.

2. One SQL to power a RAG knowledge base

PolarDB IMCI integrates vector index and embedding capabilities directly in the database kernel, providing a unified vector lifecycle management service.

Example: create a table where the vec column is automatically generated from doc via an EMBEDDING expression and a HNSW vector index is declared in the column comment.

-- Create a table with automatic vector generation
CREATE TABLE t1(
  doc TEXT,
  vec VECTOR(1024) AS (EMBEDDING(doc, "text-embedding-v4", 1024)) STORED
    COMMENT 'imci_vector_index=HNSW(metric=cosine,max_degree=16,ef_construction=300)'
) COMMENT 'COLUMNAR=1';

-- Insert raw text; vectors and index are built automatically
INSERT INTO t1(doc) VALUES
  ("PolarDB IMCI supports Hybrid Plan to access row and column store in one SQL"),
  ("HashMatch is a Join operator in PolarDB IMCI"),
  ("PolarDB IMCI provides built‑in vector index and embedding service");

Query to retrieve information and build a prompt:

SELECT CONCAT("Please refer to the following content: ", GROUP_CONCAT(doc),
               ", answer the user question in an appropriate tone: HashMatch是什么")
FROM (
  SELECT doc
  FROM t1
  ORDER BY DISTANCE(vec, EMBEDDING("HashMatch是什么", "text-embedding-v4", 1024), 'COSINE')
  LIMIT 1
) AS t;

The advantages are integration (EMBEDDING expression works like any SQL expression), automation (index built and maintained by background tasks), and standardization (all operations stay within the familiar SQL ecosystem).

3. Detailed design: Native vector capability in an HTAP database

Vector ability is deeply fused into PolarDB IMCI as a secondary index built on column storage. The index stores mappings from vectors to RowIDs, reusing mature transactional and backup features of the column store.

Reuse mature capabilities: vector index leverages delete bitmap for transaction visibility.

Data management fusion: scalar data stays in column store; vector‑scalar joins use RowID.

High‑performance scalar filtering: column‑store I/O‑cutting and vectorized execution accelerate mixed queries.

Vector index construction follows an asynchronous mechanism inspired by LSM‑Tree: incremental sync (flush‑like) and bulk load for high‑latency cases, plus baseline index compaction.

Optimizer decides between pre‑filter (scalar first) and post‑filter (vector first) based on selectivity, while the executor performs a two‑stage recall to guarantee transactional visibility and real‑time freshness.

4. Performance evaluation

On the public GIST‑960 dataset, PolarDB IMCI achieved 2‑3× higher QPS than PGVector and MariaDB under the same hardware.

5. Conclusion

PolarDB IMCI solves the split‑stack, data‑island, and ops‑complexity problems of traditional AI application development by embedding vector search and embedding directly in the database kernel, delivering high‑performance, transactional, real‑time vector retrieval through a single SQL interface.

Appendix

Hardware: Intel(R) Xeon(R) Platinum 8357C CPU @ 2.70GHz, 128 GB PolarDB instance. Configuration snippets for MariaDB and PGVector are shown below.

innodb_buffer_pool_size = 256G
mhnsw_max_cache_size = 128G

shared_buffers = 128GB
work_mem = 1GB
maintenance_work_mem = 128GB
effective_cache_size = 128GB