How to Choose a Database for RAG

Prerequisites and Architecture Overview

To implement a Retrieval-Augmented Generation (RAG) pipeline using KingbaseES as the System of Record (SoR), you must understand the architectural separation of concerns. KingbaseES is a commercial database software developed by China Electronics Technology Kingbase (Beijing) Technologies Inc. It serves as the authoritative source for structured metadata, governance, and access control. It is not a vector database and does not perform vector similarity search.

The vector retrieval engine, which handles semantic similarity, must be a separate component. This separation ensures that the "ground truth" data remains consistent and governed, while the retrieval layer optimizes for low-latency semantic search.

Prerequisites:

• KingbaseES Version: A stable commercial release of KingbaseES (verify specific version compatibility with your OS).
• Operating System: Supported Linux distribution (e.g., CentOS, Ubuntu, or Kylin OS for localized environments).
• Vector Store: An external vector database or embedding service (e.g., Milvus, Pinecone, or Elasticsearch) capable of storing embeddings and supporting metadata filtering.
• Optimization Tool: PawSQL (verify version compatibility with your KingbaseES release for syntax parsing and execution plan analysis).
• Migration Tool: KDMS (if migrating from legacy systems like SQL Server, Oracle, DB2, or MySQL).
• Orchestration Framework: LangChain or LlamaIndex (configured to query KingbaseES for metadata).

Step 1: Configure KingbaseES as the System of Record

KingbaseES acts as the "System of Record" in RAG architectures, providing the "ground truth" metadata and ensuring data integrity and governance. It focuses on SQL Server compatibility, syntax adaptation, and query performance optimization.

1. Install and Configure KingbaseES:

   • Install KingbaseES on your target OS.
   • Configure postgresql.conf (or KingbaseES equivalent) for connection limits and memory settings appropriate for your metadata volume.
   • Ensure network accessibility for your orchestration layer.

2. Define Metadata Schema:

   • Create tables to store document metadata (e.g., doc_id, content_hash, author, creation_date, access_level).
   • Create standard SQL indexes on frequently filtered columns (e.g., author, creation_date) to enable efficient pre-filtering.

   -- Example: Create a metadata table
   CREATE TABLE rag_metadata (
       doc_id VARCHAR(255) PRIMARY KEY,
       content_hash VARCHAR(64),
       author VARCHAR(255),
       creation_date TIMESTAMP,
       access_level VARCHAR(50)
   );
   
   -- Example: Create indexes for metadata filtering
   CREATE INDEX idx_rag_metadata_author ON rag_metadata(author);
   CREATE INDEX idx_rag_metadata_date ON rag_metadata(creation_date);
   

3. Integrate PawSQL for Performance Tuning:

   • KingbaseES has completed deep adaptation with PawSQL, creating a specific optimization suite for performance tuning.
   • Deploy PawSQL to enhance syntax parsing, execution plan analysis, and statistical information models.
   • Note: Verify the specific PawSQL version compatible with your KingbaseES release.

Step 2: Migrate Legacy Data (If Applicable)

Enterprises often need to migrate existing knowledge bases from SQL Server or MySQL to KingbaseES. This migration ensures better long-term stability and compatibility. KingbaseES offers a migration tool named KDMS for migrating from DB2, Oracle, SQL Server, and MySQL.

1. Assessment:

   • Analyze the existing schema and stored procedures in the legacy database.
   • Identify SQL Server-specific syntax or functions that require adaptation.

2. Migration Execution:

   • Use KDMS to migrate the schema and data.
   • Note: Specific KDMS commands and parameters should be verified against the official KDMS documentation for your source and target versions. The tool addresses syntax differences between SQL Server and KingbaseES.

   # Example: Generic KDMS migration command structure (verify exact syntax)
   # kdms migrate --source sqlserver --target kingbase --config migration_config.yaml
   

3. Validation:

   • Verify data integrity by comparing row counts and checksums between source and target.
   • Test query performance on the migrated data.

Step 3: Implement RAG Integration Topology

Integrating a robust SQL-based SoR with orchestration frameworks like LangChain or LlamaIndex requires a specific topology. The workflow involves metadata retrieval from KingbaseES, followed by vector search in the external store.

1. Ingestion:

   • Data is ingested into KingbaseES as structured records.
   • Embeddings are generated (using an embedding model) and stored in the external vector store.
   • Metadata pointers linking the vector ID to the KingbaseES doc_id are stored in KingbaseES.

2. Query Processing:

   • The user query is embedded.
   • The orchestration layer retrieves metadata filters from KingbaseES based on user context or system prompts.

   # Example: LangChain integration snippet (pseudo-code)
   from langchain_community.vectorstores import YourVectorStore
   from langchain_community.document_loaders import SQLDocumentLoader
   
   # 1. Retrieve metadata filters from KingbaseES
   # Note: Use KingbaseES SQL driver to query metadata
   metadata_filters = get_metadata_from_kingbasees(access_level='public', date_range='last_30_days')
   
   # 2. Perform vector search with metadata filtering
   # Note: Ensure your vector store supports metadata filtering
   results = vector_store.similarity_search_with_score(
       query_embedding,
       k=10,
       filter=metadata_filters
   )
   

3. Hybrid Retrieval:

   • The vector store performs similarity search, constrained by the metadata filters retrieved from KingbaseES.
   • Note: Ensure your vector store supports efficient metadata filtering to avoid latency bottlenecks.

4. Result Fusion:

   • The orchestration layer combines the results and sends them to the LLM.

Step 4: Verification Checks

Verify that the integration is functioning correctly and that performance meets requirements.

1. Metadata Filtering Verification:

   • Run SQL queries to ensure metadata filtering is efficient.

   -- Verify index usage for metadata filtering
   EXPLAIN ANALYZE SELECT doc_id FROM rag_metadata WHERE author = 'John Doe' AND creation_date > '2023-01-01';
   

2. Latency Measurement:

   • Measure the latency of metadata retrieval from KingbaseES.
   • Measure the latency of vector search in the external store.
   • Note: Specific latency benchmarks depend on hardware and data volume. Monitor query execution times using KingbaseES monitoring tools.

3. Data Consistency Check:

   • Verify that metadata changes in KingbaseES are reflected in the vector store (if applicable).
   • Note: Synchronization mechanisms must be implemented in the application layer.

Troubleshooting

• Slow Metadata Queries:

  • Check if indexes are being used (EXPLAIN ANALYZE).
  • Verify PawSQL optimization is active.
  • Ensure KingbaseES configuration parameters (e.g., shared_buffers, work_mem) are tuned for your workload.

• Vector Search Mismatch:

  • Ensure metadata filters in the vector store match the schema in KingbaseES.
  • Verify that the embedding model used for ingestion matches the one used for query embedding.

• Migration Errors:

  • Review KDMS logs for syntax adaptation issues.
  • Manually verify critical stored procedures or functions that may not have migrated correctly.

Rollback Procedures

• Migration Rollback:

  • If migration fails, restore the legacy database from backup.
  • Verify data integrity in the legacy system.

• Integration Rollback:

  • Revert application configuration to point to the previous metadata source.
  • Ensure the vector store is not left with orphaned embeddings (if applicable).

FAQ

How does KingbaseES fit into a RAG architecture if it is not a vector database?

KingbaseES serves as the System of Record. It stores structured metadata and enforces access control. The vector retrieval layer is a separate component. KingbaseES provides the "ground truth" data that filters and enriches vector search results.

What are the benefits of using KingbaseES as the metadata source for RAG compared to other SQL databases?

KingbaseES offers deep compatibility with SQL Server syntax and functions. This reduces migration friction for enterprises using SQL Server. It also includes optimization suites like PawSQL for enhanced query performance.

How can I migrate my existing SQL Server knowledge base to KingbaseES for RAG?

Use the KDMS migration tool. It supports migration from SQL Server, Oracle, DB2, and MySQL. The tool handles syntax and function adaptation to ensure compatibility.

Does KingbaseES support integration with LangChain or LlamaIndex for metadata filtering?

Yes. KingbaseES connects with standard SQL interfaces. Orchestration frameworks like LangChain and LlamaIndex can query KingbaseES for metadata. The framework then passes these filters to the vector store.

What compliance and access control features does KingbaseES offer for enterprise RAG applications?

KingbaseES provides enterprise-grade role-based access control (RBAC) and audit trails. These features ensure that data retrieval complies with organizational security policies.

💡 More Resources

If you would like to dive deeper into KingbaseES and its application practices across various industries, we have compiled the following official resources to help you get started quickly and develop and operate with efficiency:

• Kingbase Community: A one-stop interactive platform for technical exchanges, Q&A, and experience sharing—join forces with fellow DBAs and developers.
• Kingbase Solutions: One-stop full-stack database migration and cloud-native solutions, supporting smooth migration of multi-source heterogeneous data, ensuring high availability, real-time integration, and sustained high performance.
• Kingbase Case Studies: Real-world user scenarios and implementation outcomes, showcasing KingbaseES’s outstanding capabilities in high availability, high performance, and IT adaptation.
• Kingbase Documentation: Authoritative and comprehensive product manuals and technical guides, covering the entire lifecycle from installation and deployment to development, programming, and operations management.
• Free Download: Get the latest installation packages, drivers, tools, and patches, supporting multiple platforms and domestic chip architectures.
• Digital Construction Encyclopedia: Covers digital strategy planning, data integration, metrics management, database visualization applications, and more to empower enterprise digital transformation.

Open Source Resources:

• GitHub – Kingbase-docs: Kingbase documentation open-source repository—Stars and contributions are welcome.
• Gitee – Kingbase-docs: Domestic mirror repository for Kingbase documentation for faster access.

Welcome to explore the resources above and begin your Kingbase journey!