Kingbase Banner

Multi-Model Relational Database Tutorial_ Setup,

A precision screwdriver set arranged in foam cutouts on a linen surface, illustrating technical setup and validation.

1. Prerequisites & The ‘Multi-Mode’ vs. ‘Multi-Model’ Distinction

Architects evaluating a multi-model relational database often conflate two distinct capabilities: syntax compatibility (multi-mode) and data type storage (multi-model). This distinction is critical for Proof-of-Concept (PoC) validation.

Multi-mode compatibility refers to a database’s ability to interpret SQL dialects from other vendors (e.g., Oracle, MySQL, PostgreSQL) within a single engine. This reduces application refactoring effort but does not inherently change how data is stored.

Multi-model data support refers to the engine’s native ability to store, index, and query heterogeneous data types—such as JSON documents, graph edges, and relational tables—within the same transactional boundary.

For KingbaseES, the verified evidence confirms strong multi-mode compatibility. It supports Oracle, MySQL, SQL Server, and PostgreSQL modes. This allows legacy applications to connect with minimal code changes. However, there is no verified evidence in the current evidence package confirming native JSONB/BSON storage, native graph traversal APIs, or hybrid SQL/JSON indexing strategies for KingbaseES.

Prerequisites for Evaluation:

  • Environment: A standalone KingbaseES V9+ instance or a shared-storage cluster.
  • Access: Superuser privileges to create tables and analyze execution plans.
  • Verification Task: Before proceeding with hybrid schema design, verify if the specific KingbaseES build includes the JSON/Graph extensions required for your workload. If these are absent, the tutorial provides vendor-neutral fallback patterns.

2. Hybrid Schema Design: Modeling Relational, Document, and Graph Data

When consolidating polyglot persistence, the goal is to eliminate microservice boundaries while maintaining data integrity. Since specific KingbaseES JSONB/Graph schema definitions are unverified, we use standard ANSI SQL patterns that are widely supported in commercial RDBMS engines.

Scenario: Consolidating Customer, Preferences, and Social Graph

A typical enterprise workload involves:

  1. Relational: Customer identity and billing (strict schema).
  2. Document: User preferences, device logs, or unstructured metadata (flexible schema).
  3. Graph: Social connections or dependency chains (relationship-heavy).

Recommended Schema Structure

Use a hybrid table approach. Avoid creating separate tables for every data type unless strict isolation is required for security or performance reasons.

-- 1. Core Relational Table
CREATE TABLE customers (
    customer_id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. Hybrid Table with Document Support
-- Note: Use the native JSON/JSONB type supported by your specific database version.
-- For KingbaseES, verify if 'JSONB' or 'JSON' is the preferred binary format in V9+.
CREATE TABLE customer_profiles (
    profile_id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT REFERENCES customers(customer_id),
    -- Flexible document storage
    attributes JSONB,
    -- Optional: Graph-like edge storage if native graph types are unavailable
    -- We often use a separate adjacency table for complex graphs to maintain relational integrity
    graph_edges JSONB
);

-- 3. Explicit Graph Edge Table (Recommended for Complex Traversals)
-- If the database lacks native graph traversal functions, use a relational edge table.
CREATE TABLE social_connections (
    source_id BIGINT REFERENCES customers(customer_id),
    target_id BIGINT REFERENCES customers(customer_id),
    relationship_type VARCHAR(50),
    strength DECIMAL,
    PRIMARY KEY (source_id, target_id)
);

Design Rationale:

  • Relational Integrity: Primary and foreign keys ensure data consistency for core entities.
  • Schema-on-Read for Documents: The JSONB column allows flexible attributes without altering the table structure. This supports schema evolution without downtime.
  • Explicit Graph Edges: While some databases support native graph types, using a standard adjacency table (social_connections) ensures compatibility across all commercial RDBMS versions. It allows for standard recursive CTEs or join-based traversals.

3. Implementation: Executing the Migration with KingbaseES Tools

Migrating from a polyglot architecture (e.g., PostgreSQL + MongoDB + Neo4j) to a unified engine requires a structured approach. KingbaseES provides specific tools for this: KDMS (Structure Migration), KDTS (Offline Full Volume), and KFS (Real-time Incremental).

Phase 1: Structure Migration (KDMS)

Use KDMS to migrate the schema definition from legacy systems to KingbaseES.

  1. Source Analysis: Run KDMS against the source Oracle/MySQL/PostgreSQL database to extract DDL.
  2. Compatibility Check: Review the generated DDL for KingbaseES compatibility.
    • Oracle Mode: Ensure PL/SQL blocks are converted to KingbaseES compatible procedures.
    • MySQL Mode: Check for MySQL-specific functions (e.g., IFNULL, DATE_FORMAT) and replace with standard SQL or KingbaseES equivalents.
  3. Execution: Apply the migrated DDL to the KingbaseES target environment.

Phase 2: Offline Full Volume Migration (KDTS)

Use KDTS for the initial bulk data load. This is suitable for historical data that does not require real-time sync.

  1. Extract: Configure KDTS to connect to source databases.
  2. Transform: Map source data types to KingbaseES types.
    • Critical Step: If migrating JSON documents from MongoDB, ensure the target column is defined as JSONB (or the supported variant) in KingbaseES.
  3. Load: Execute the batch load. Monitor the KDTS logs for constraint violations or type conversion errors.

Phase 3: Real-time Incremental Migration (KFS)

Use KFS to synchronize changes from the legacy systems to KingbaseES during the cutover window.

  1. CDC Configuration: Enable Change Data Capture (CDC) on source databases.
  2. Link Creation: Create a KFS link between the source and KingbaseES.
  3. Validation: Compare row counts and checksums between source and target.
  4. Cutover: Point application read/write endpoints to KingbaseES.

4. Query Execution: SQL/JSON Hybrids and Graph Traversal Workarounds

Once data is migrated, the application must query the hybrid model. Since native JSON/Graph SQL syntax varies by vendor, we use standard patterns that are likely supported in KingbaseES V9+.

SQL/JSON Hybrid Queries

To filter relational columns and nested JSON attributes simultaneously:

-- Example: Find customers in 'Malaysia' (Relational) who have 'premium' status in JSON attributes
SELECT c.name, c.email, p.attributes
FROM customers c
JOIN customer_profiles p ON c.customer_id = p.customer_id
WHERE c.country = 'Malaysia'
  -- Standard JSON extraction operator (verify operator syntax for your specific DB)
  -- Commonly ->> or -> or json_extract_path_text
  AND p.attributes->>'tier' = 'premium';

Verification Note:

  • Confirm if KingbaseES supports the ->> operator for text extraction.
  • If not, use json_extract_path_text(p.attributes, 'tier') or equivalent.

Graph Traversal via Recursive CTEs

If KingbaseES does not support native graph traversal functions (e.g., MATCH or GRAPH_TRAVERSE), use Recursive Common Table Expressions (CTEs). This is a standard SQL pattern supported in most commercial RDBMS.

-- Find all friends of friends (2-degree hop) for a specific user
WITH RECURSIVE friend_chain AS (
    -- Anchor: Direct friends
    SELECT
        source_id,
        target_id,
        1 AS depth
    FROM social_connections
    WHERE source_id = 1001 -- Start user

    UNION ALL

    -- Recursive: Friends of friends
    SELECT
        fc.source_id,
        sc.target_id,
        fc.depth + 1
    FROM friend_chain fc
    JOIN social_connections sc ON fc.target_id = sc.source_id
    WHERE fc.depth < 2 -- Limit depth to 2
)
SELECT target_id, depth
FROM friend_chain
ORDER BY depth, target_id;

Performance Warning:

Recursive CTEs can be resource-intensive. Ensure an index exists on social_connections(source_id) to optimize the join performance.

5. Verification: Validating Consistency and HTAP Performance

After migration, validate that the consolidated engine handles mixed workloads (HTAP) without locking contention or consistency errors.

Consistency Validation Checklist

  1. Row Count Verification:
    SELECT COUNT(*) FROM customers;
    -- Compare with source system count
    
  2. Data Integrity Check:
    SELECT * FROM customers c
    LEFT JOIN customer_profiles p ON c.customer_id = p.customer_id
    WHERE p.profile_id IS NULL;
    -- Result should be empty if all customers have profiles
    
  3. JSON Structure Validation:
    SELECT customer_id, attributes
    FROM customer_profiles
    WHERE NOT jsonb_typeof(attributes) = 'object';
    -- Should return empty if all attributes are valid JSON objects
    

Performance Validation

KingbaseES has demonstrated significant performance improvements in specific scenarios. In a large operator’s lease accounting system, KingbaseES showed 0.5 to 16.4 times performance improvement compared to Oracle in 7 core scenarios.

Verification Steps:

  1. Baseline: Run key queries against the legacy system and record latency.
  2. Replay: Replay the same queries against KingbaseES.
  3. Analyze with KSH/KWR:
    • Use Kingbase Session History (KSH) to capture session-level metrics.
    • Use Kingbase Workload Repository (KWR) to generate reports on CPU, I/O, and wait events.
    • Look for reduction in lock waits and improved buffer hit ratios.

6. Troubleshooting & Rollback: Handling Schema Evolution Failures

If the multi-model PoC fails to meet latency SLAs or causes data inconsistency, a rollback procedure is essential. KingbaseES supports high-availability architectures that facilitate safe rollbacks.

Rollback Procedure

  1. Identify Failure Point:
    • Use KSH to identify the specific queries causing high CPU or lock contention.
    • Check if the issue is due to missing JSON indexes or inefficient recursive CTEs.
  2. Switch to Read-Only Mode (If Applicable):
    • If using a shared-storage cluster, you can temporarily restrict writes to the primary node while investigating.
  3. Revert Application Connections:
    • Switch the application’s connection pool back to the legacy polyglot systems.
    • Note: Ensure that the legacy systems are still accepting writes if KFS was active.
  4. Data Synchronization Rollback:
    • If data was written to KingbaseES during the PoC, use standard SQL DELETE statements or transaction rollback to remove test data from KingbaseES.
    • Verify that the legacy systems have not lost any data written during the cutover window.
  5. Architecture Adjustment:
    • If KingbaseES lacks native JSON/Graph support, consider keeping those specific workloads in specialized NoSQL/Graph databases and use KingbaseES only for the core relational/HTAP layer. This is a valid architectural decision to reduce complexity.

7. Security & Compliance: EAL4+ in a Consolidated Environment

Consolidating disparate data stores into a single engine reduces the attack surface. KingbaseES provides enterprise-grade security features that are critical for government and large enterprise users in Malaysia.

Key Security Features

  • EAL4+ Certification: KingbaseES has passed the highest level certification (EAL4+) from a national authoritative institution. This provides a verified baseline for security functionality.
  • Three-Power Separation: Implements separation of duties among system administrators, security administrators, and audit administrators.
  • Data Encryption: Supports data-at-rest and data-in-transit encryption.
  • Audit Tracing: Comprehensive audit logs for all database operations, ensuring compliance with regulatory requirements.

Compliance Considerations for Malaysia

  • PDPA: While Malaysia’s Personal Data Protection Act (PDPA) requires data security, it does not mandate a blanket data-residency rule for all data types. However, sensitive data should be encrypted at rest.
  • Local Presence: Do not assume KingbaseES has local offices or data centers in Malaysia unless verified by specific evidence. Ensure your cloud provider or hosting partner complies with local data sovereignty laws if applicable.

FAQ

Q: What is the difference between KingbaseES ‘Multi-Mode’ compatibility and ‘Multi-Model’ data support?

A: ‘Multi-Mode’ refers to SQL syntax compatibility (e.g., running Oracle or MySQL SQL dialects on KingbaseES). ‘Multi-Model’ refers to native storage of heterogeneous data types (JSON, Graph, Relational) in the same engine. KingbaseES is verified for Multi-Mode compatibility; native Multi-Model support (JSONB/Graph) requires version-specific verification.

Q: How do I verify if KingbaseES supports the specific JSON or Graph features I need for my hybrid data model?

A: Check the KingbaseES V9+ release notes for specific mentions of JSONB, JSONPATH, or native graph traversal functions. If not documented, test with a sample schema using standard SQL/JSON operators (->>, json_extract) and recursive CTEs.

Q: What are the prerequisites for migrating from a polyglot persistence architecture to KingbaseES?

A: You need KingbaseES V9+ installed, KDMS/KDTS/KFS tools configured, and a clear mapping of source data types to KingbaseES types. Ensure your application drivers are compatible with KingbaseES.

Q: How can I use KingbaseES tools (KDMS, KDTS, KFS) to ensure data consistency during migration?

A: Use KDMS for schema migration, KDTS for full data load, and KFS for real-time sync. Perform row-count and checksum validations after each phase. Use KSH to monitor for any anomalies during the sync.

Q: What are the rollback procedures if a multi-model schema migration causes data inconsistency?

A: Revert application connections to the legacy systems. Use standard SQL DELETE or transaction rollback to remove inconsistent data from KingbaseES. If the issue is performance-related, analyze with KWR/KSH and adjust indexes or query patterns. If the issue is lack of native JSON/Graph support, consider reverting to a polyglot architecture for those specific workloads.


💡 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:

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