{"id":3425,"date":"2026-11-09T01:58:07","date_gmt":"2026-11-09T01:58:07","guid":{"rendered":"https:\/\/www.kingbaseglobal.com\/blog\/tech-blog\/multi-model-relational-database-tutorial-setup\/"},"modified":"2026-11-09T01:58:07","modified_gmt":"2026-11-09T01:58:07","slug":"multi-model-relational-database-tutorial-setup","status":"publish","type":"post","link":"https:\/\/www.kingbaseglobal.com\/blog\/tech-blog\/multi-model-relational-database-tutorial-setup\/","title":{"rendered":"Multi-Model Relational Database Tutorial_ Setup,"},"content":{"rendered":"<p><img decoding=\"async\" src=\"https:\/\/kingbase-bbs.oss-cn-beijing.aliyuncs.com\/qywx\/blogImage\/d123b2a9-ef89-4031-8bd5-d5aa06268bf7.webp\" alt=\"A precision screwdriver set arranged in foam cutouts on a linen surface, illustrating technical setup and validation.\" \/><\/p>\n<h2>1. Prerequisites &amp; The &#8216;Multi-Mode&#8217; vs. &#8216;Multi-Model&#8217; Distinction<\/h2>\n<p>Architects evaluating a <strong>multi-model relational database<\/strong> 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.<\/p>\n<p><strong>Multi-mode compatibility<\/strong> refers to a database\u2019s 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.<\/p>\n<p><strong>Multi-model data support<\/strong> refers to the engine\u2019s native ability to store, index, and query heterogeneous data types\u2014such as JSON documents, graph edges, and relational tables\u2014within the same transactional boundary.<\/p>\n<p>For <strong>KingbaseES<\/strong>, 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.<\/p>\n<p><strong>Prerequisites for Evaluation:<\/strong><\/p>\n<ul>\n<li><strong>Environment:<\/strong> A standalone KingbaseES V9+ instance or a shared-storage cluster.<\/li>\n<li><strong>Access:<\/strong> Superuser privileges to create tables and analyze execution plans.<\/li>\n<li><strong>Verification Task:<\/strong> 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.<\/li>\n<\/ul>\n<h2>2. Hybrid Schema Design: Modeling Relational, Document, and Graph Data<\/h2>\n<p>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.<\/p>\n<h3>Scenario: Consolidating Customer, Preferences, and Social Graph<\/h3>\n<p>A typical enterprise workload involves:<\/p>\n<ol>\n<li><strong>Relational:<\/strong> Customer identity and billing (strict schema).<\/li>\n<li><strong>Document:<\/strong> User preferences, device logs, or unstructured metadata (flexible schema).<\/li>\n<li><strong>Graph:<\/strong> Social connections or dependency chains (relationship-heavy).<\/li>\n<\/ol>\n<h3>Recommended Schema Structure<\/h3>\n<p>Use a hybrid table approach. Avoid creating separate tables for every data type unless strict isolation is required for security or performance reasons.<\/p>\n<pre><code class=\"language-sql\">-- 1. Core Relational Table\nCREATE TABLE customers (\n    customer_id BIGSERIAL PRIMARY KEY,\n    name VARCHAR(100) NOT NULL,\n    email VARCHAR(255) UNIQUE,\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);\n\n-- 2. Hybrid Table with Document Support\n-- Note: Use the native JSON\/JSONB type supported by your specific database version.\n-- For KingbaseES, verify if 'JSONB' or 'JSON' is the preferred binary format in V9+.\nCREATE TABLE customer_profiles (\n    profile_id BIGSERIAL PRIMARY KEY,\n    customer_id BIGINT REFERENCES customers(customer_id),\n    -- Flexible document storage\n    attributes JSONB,\n    -- Optional: Graph-like edge storage if native graph types are unavailable\n    -- We often use a separate adjacency table for complex graphs to maintain relational integrity\n    graph_edges JSONB\n);\n\n-- 3. Explicit Graph Edge Table (Recommended for Complex Traversals)\n-- If the database lacks native graph traversal functions, use a relational edge table.\nCREATE TABLE social_connections (\n    source_id BIGINT REFERENCES customers(customer_id),\n    target_id BIGINT REFERENCES customers(customer_id),\n    relationship_type VARCHAR(50),\n    strength DECIMAL,\n    PRIMARY KEY (source_id, target_id)\n);\n<\/code><\/pre>\n<p><strong>Design Rationale:<\/strong><\/p>\n<ul>\n<li><strong>Relational Integrity:<\/strong> Primary and foreign keys ensure data consistency for core entities.<\/li>\n<li><strong>Schema-on-Read for Documents:<\/strong> The <code>JSONB<\/code> column allows flexible attributes without altering the table structure. This supports schema evolution without downtime.<\/li>\n<li><strong>Explicit Graph Edges:<\/strong> While some databases support native graph types, using a standard adjacency table (<code>social_connections<\/code>) ensures compatibility across all commercial RDBMS versions. It allows for standard recursive CTEs or join-based traversals.<\/li>\n<\/ul>\n<h2>3. Implementation: Executing the Migration with KingbaseES Tools<\/h2>\n<p>Migrating from a polyglot architecture (e.g., PostgreSQL + MongoDB + Neo4j) to a unified engine requires a structured approach. KingbaseES provides specific tools for this: <strong>KDMS<\/strong> (Structure Migration), <strong>KDTS<\/strong> (Offline Full Volume), and <strong>KFS<\/strong> (Real-time Incremental).<\/p>\n<h3>Phase 1: Structure Migration (KDMS)<\/h3>\n<p>Use KDMS to migrate the schema definition from legacy systems to KingbaseES.<\/p>\n<ol>\n<li><strong>Source Analysis:<\/strong> Run KDMS against the source Oracle\/MySQL\/PostgreSQL database to extract DDL.<\/li>\n<li><strong>Compatibility Check:<\/strong> Review the generated DDL for KingbaseES compatibility.\n<ul>\n<li><em>Oracle Mode:<\/em> Ensure PL\/SQL blocks are converted to KingbaseES compatible procedures.<\/li>\n<li><em>MySQL Mode:<\/em> Check for MySQL-specific functions (e.g., <code>IFNULL<\/code>, <code>DATE_FORMAT<\/code>) and replace with standard SQL or KingbaseES equivalents.<\/li>\n<\/ul>\n<\/li>\n<li><strong>Execution:<\/strong> Apply the migrated DDL to the KingbaseES target environment.<\/li>\n<\/ol>\n<h3>Phase 2: Offline Full Volume Migration (KDTS)<\/h3>\n<p>Use KDTS for the initial bulk data load. This is suitable for historical data that does not require real-time sync.<\/p>\n<ol>\n<li><strong>Extract:<\/strong> Configure KDTS to connect to source databases.<\/li>\n<li><strong>Transform:<\/strong> Map source data types to KingbaseES types.\n<ul>\n<li><em>Critical Step:<\/em> If migrating JSON documents from MongoDB, ensure the target column is defined as <code>JSONB<\/code> (or the supported variant) in KingbaseES.<\/li>\n<\/ul>\n<\/li>\n<li><strong>Load:<\/strong> Execute the batch load. Monitor the KDTS logs for constraint violations or type conversion errors.<\/li>\n<\/ol>\n<h3>Phase 3: Real-time Incremental Migration (KFS)<\/h3>\n<p>Use KFS to synchronize changes from the legacy systems to KingbaseES during the cutover window.<\/p>\n<ol>\n<li><strong>CDC Configuration:<\/strong> Enable Change Data Capture (CDC) on source databases.<\/li>\n<li><strong>Link Creation:<\/strong> Create a KFS link between the source and KingbaseES.<\/li>\n<li><strong>Validation:<\/strong> Compare row counts and checksums between source and target.<\/li>\n<li><strong>Cutover:<\/strong> Point application read\/write endpoints to KingbaseES.<\/li>\n<\/ol>\n<h2>4. Query Execution: SQL\/JSON Hybrids and Graph Traversal Workarounds<\/h2>\n<p>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+.<\/p>\n<h3>SQL\/JSON Hybrid Queries<\/h3>\n<p>To filter relational columns and nested JSON attributes simultaneously:<\/p>\n<pre><code class=\"language-sql\">-- Example: Find customers in 'Malaysia' (Relational) who have 'premium' status in JSON attributes\nSELECT c.name, c.email, p.attributes\nFROM customers c\nJOIN customer_profiles p ON c.customer_id = p.customer_id\nWHERE c.country = 'Malaysia'\n  -- Standard JSON extraction operator (verify operator syntax for your specific DB)\n  -- Commonly -&gt;&gt; or -&gt; or json_extract_path_text\n  AND p.attributes-&gt;&gt;'tier' = 'premium';\n<\/code><\/pre>\n<p><strong>Verification Note:<\/strong><\/p>\n<ul>\n<li>Confirm if KingbaseES supports the <code>-&gt;&gt;<\/code> operator for text extraction.<\/li>\n<li>If not, use <code>json_extract_path_text(p.attributes, 'tier')<\/code> or equivalent.<\/li>\n<\/ul>\n<h3>Graph Traversal via Recursive CTEs<\/h3>\n<p>If KingbaseES does not support native graph traversal functions (e.g., <code>MATCH<\/code> or <code>GRAPH_TRAVERSE<\/code>), use Recursive Common Table Expressions (CTEs). This is a standard SQL pattern supported in most commercial RDBMS.<\/p>\n<pre><code class=\"language-sql\">-- Find all friends of friends (2-degree hop) for a specific user\nWITH RECURSIVE friend_chain AS (\n    -- Anchor: Direct friends\n    SELECT\n        source_id,\n        target_id,\n        1 AS depth\n    FROM social_connections\n    WHERE source_id = 1001 -- Start user\n\n    UNION ALL\n\n    -- Recursive: Friends of friends\n    SELECT\n        fc.source_id,\n        sc.target_id,\n        fc.depth + 1\n    FROM friend_chain fc\n    JOIN social_connections sc ON fc.target_id = sc.source_id\n    WHERE fc.depth &lt; 2 -- Limit depth to 2\n)\nSELECT target_id, depth\nFROM friend_chain\nORDER BY depth, target_id;\n<\/code><\/pre>\n<p><strong>Performance Warning:<\/strong><\/p>\n<p>Recursive CTEs can be resource-intensive. Ensure an index exists on <code>social_connections(source_id)<\/code> to optimize the join performance.<\/p>\n<h2>5. Verification: Validating Consistency and HTAP Performance<\/h2>\n<p>After migration, validate that the consolidated engine handles mixed workloads (HTAP) without locking contention or consistency errors.<\/p>\n<h3>Consistency Validation Checklist<\/h3>\n<ol>\n<li><strong>Row Count Verification:<\/strong>\n<pre><code class=\"language-sql\">SELECT COUNT(*) FROM customers;\n-- Compare with source system count\n<\/code><\/pre>\n<\/li>\n<li><strong>Data Integrity Check:<\/strong>\n<pre><code class=\"language-sql\">SELECT * FROM customers c\nLEFT JOIN customer_profiles p ON c.customer_id = p.customer_id\nWHERE p.profile_id IS NULL;\n-- Result should be empty if all customers have profiles\n<\/code><\/pre>\n<\/li>\n<li><strong>JSON Structure Validation:<\/strong>\n<pre><code class=\"language-sql\">SELECT customer_id, attributes\nFROM customer_profiles\nWHERE NOT jsonb_typeof(attributes) = 'object';\n-- Should return empty if all attributes are valid JSON objects\n<\/code><\/pre>\n<\/li>\n<\/ol>\n<h3>Performance Validation<\/h3>\n<p>KingbaseES has demonstrated significant performance improvements in specific scenarios. In a large operator\u2019s lease accounting system, KingbaseES showed <strong>0.5 to 16.4 times<\/strong> performance improvement compared to Oracle in 7 core scenarios.<\/p>\n<p><strong>Verification Steps:<\/strong><\/p>\n<ol>\n<li><strong>Baseline:<\/strong> Run key queries against the legacy system and record latency.<\/li>\n<li><strong>Replay:<\/strong> Replay the same queries against KingbaseES.<\/li>\n<li><strong>Analyze with KSH\/KWR:<\/strong>\n<ul>\n<li>Use <strong>Kingbase Session History (KSH)<\/strong> to capture session-level metrics.<\/li>\n<li>Use <strong>Kingbase Workload Repository (KWR)<\/strong> to generate reports on CPU, I\/O, and wait events.<\/li>\n<li>Look for reduction in lock waits and improved buffer hit ratios.<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n<h2>6. Troubleshooting &amp; Rollback: Handling Schema Evolution Failures<\/h2>\n<p>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.<\/p>\n<h3>Rollback Procedure<\/h3>\n<ol>\n<li><strong>Identify Failure Point:<\/strong>\n<ul>\n<li>Use KSH to identify the specific queries causing high CPU or lock contention.<\/li>\n<li>Check if the issue is due to missing JSON indexes or inefficient recursive CTEs.<\/li>\n<\/ul>\n<\/li>\n<li><strong>Switch to Read-Only Mode (If Applicable):<\/strong>\n<ul>\n<li>If using a shared-storage cluster, you can temporarily restrict writes to the primary node while investigating.<\/li>\n<\/ul>\n<\/li>\n<li><strong>Revert Application Connections:<\/strong>\n<ul>\n<li>Switch the application\u2019s connection pool back to the legacy polyglot systems.<\/li>\n<li><em>Note:<\/em> Ensure that the legacy systems are still accepting writes if KFS was active.<\/li>\n<\/ul>\n<\/li>\n<li><strong>Data Synchronization Rollback:<\/strong>\n<ul>\n<li>If data was written to KingbaseES during the PoC, use standard SQL <code>DELETE<\/code> statements or transaction rollback to remove test data from KingbaseES.<\/li>\n<li>Verify that the legacy systems have not lost any data written during the cutover window.<\/li>\n<\/ul>\n<\/li>\n<li><strong>Architecture Adjustment:<\/strong>\n<ul>\n<li>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.<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n<h2>7. Security &amp; Compliance: EAL4+ in a Consolidated Environment<\/h2>\n<p>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.<\/p>\n<h3>Key Security Features<\/h3>\n<ul>\n<li><strong>EAL4+ Certification:<\/strong> KingbaseES has passed the highest level certification (EAL4+) from a national authoritative institution. This provides a verified baseline for security functionality.<\/li>\n<li><strong>Three-Power Separation:<\/strong> Implements separation of duties among system administrators, security administrators, and audit administrators.<\/li>\n<li><strong>Data Encryption:<\/strong> Supports data-at-rest and data-in-transit encryption.<\/li>\n<li><strong>Audit Tracing:<\/strong> Comprehensive audit logs for all database operations, ensuring compliance with regulatory requirements.<\/li>\n<\/ul>\n<h3>Compliance Considerations for Malaysia<\/h3>\n<ul>\n<li><strong>PDPA:<\/strong> While Malaysia\u2019s 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.<\/li>\n<li><strong>Local Presence:<\/strong> 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.<\/li>\n<\/ul>\n<h2>FAQ<\/h2>\n<h3>Q: What is the difference between KingbaseES &#8216;Multi-Mode&#8217; compatibility and &#8216;Multi-Model&#8217; data support?<\/h3>\n<p>A: &#8216;Multi-Mode&#8217; refers to SQL syntax compatibility (e.g., running Oracle or MySQL SQL dialects on KingbaseES). &#8216;Multi-Model&#8217; 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.<\/p>\n<h3>Q: How do I verify if KingbaseES supports the specific JSON or Graph features I need for my hybrid data model?<\/h3>\n<p>A: Check the KingbaseES V9+ release notes for specific mentions of <code>JSONB<\/code>, <code>JSONPATH<\/code>, or native graph traversal functions. If not documented, test with a sample schema using standard SQL\/JSON operators (<code>-&gt;&gt;<\/code>, <code>json_extract<\/code>) and recursive CTEs.<\/p>\n<h3>Q: What are the prerequisites for migrating from a polyglot persistence architecture to KingbaseES?<\/h3>\n<p>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.<\/p>\n<h3>Q: How can I use KingbaseES tools (KDMS, KDTS, KFS) to ensure data consistency during migration?<\/h3>\n<p>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.<\/p>\n<h3>Q: What are the rollback procedures if a multi-model schema migration causes data inconsistency?<\/h3>\n<p>A: Revert application connections to the legacy systems. Use standard SQL <code>DELETE<\/code> 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.<\/p>\n<hr \/>\n<p><strong>\ud83d\udca1 More Resources<\/strong><\/p>\n<p>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:<\/p>\n<ul>\n<li><a href=\"https:\/\/bbs.kingbase.com.cn\/\">Kingbase Community<\/a>: A one-stop interactive platform for technical exchanges, Q&amp;A, and experience sharing\u2014join forces with fellow DBAs and developers.<\/li>\n<li><a href=\"https:\/\/www.kingbaseglobal.com\/Solution-Oracle.html\">Kingbase Solutions<\/a>: 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.<\/li>\n<li><a href=\"https:\/\/www.kingbaseglobal.com\/Customers.html\">Kingbase Case Studies<\/a>: Real-world user scenarios and implementation outcomes, showcasing KingbaseES&#8217;s outstanding capabilities in high availability, high performance, and IT adaptation.<\/li>\n<li><a href=\"https:\/\/docs.kingbase.com.cn\/en\">Kingbase Documentation<\/a>: Authoritative and comprehensive product manuals and technical guides, covering the entire lifecycle from installation and deployment to development, programming, and operations management.<\/li>\n<li><a href=\"https:\/\/www.kingbaseglobal.com\/Download.html\">Free Download<\/a>: Get the latest installation packages, drivers, tools, and patches, supporting multiple platforms and domestic chip architectures.<\/li>\n<li><a href=\"https:\/\/www.kingbaseglobal.com\/blog\/\">Digital Construction Encyclopedia<\/a>: Covers digital strategy planning, data integration, metrics management, database visualization applications, and more to empower enterprise digital transformation.<\/li>\n<\/ul>\n<p><strong>Open Source Resources:<\/strong><\/p>\n<ul>\n<li><a href=\"https:\/\/github.com\/hgsandy\/Kingbase-docs\">GitHub &#8211; Kingbase-docs<\/a>: Kingbase documentation open-source repository\u2014Stars and contributions are welcome.<\/li>\n<li><a href=\"https:\/\/gitee.com\/hgsandy\/kingbase-docs\">Gitee &#8211; Kingbase-docs<\/a>: Domestic mirror repository for Kingbase documentation for faster access.<\/li>\n<\/ul>\n<p>Welcome to explore the resources above and begin your Kingbase journey!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>1. Prerequisites &amp; The &#8216;Multi-Mode&#8217; vs. &#8216;Multi-Model&#8217; 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&#8230;<\/p>\n","protected":false},"author":2114,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"meta_description":"","_kingbase_seo_description":"Architects evaluating a multi-model relational database often conflate two distinct capabilities: syntax compatibility (multi-mode) and data type storage (multi","_kingbase_seo_keywords":"","footnotes":""},"categories":[1],"tags":[],"class_list":["post-3425","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/www.kingbaseglobal.com\/blog\/wp-json\/wp\/v2\/posts\/3425","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.kingbaseglobal.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.kingbaseglobal.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.kingbaseglobal.com\/blog\/wp-json\/wp\/v2\/users\/2114"}],"replies":[{"embeddable":true,"href":"https:\/\/www.kingbaseglobal.com\/blog\/wp-json\/wp\/v2\/comments?post=3425"}],"version-history":[{"count":0,"href":"https:\/\/www.kingbaseglobal.com\/blog\/wp-json\/wp\/v2\/posts\/3425\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.kingbaseglobal.com\/blog\/wp-json\/wp\/v2\/media?parent=3425"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.kingbaseglobal.com\/blog\/wp-json\/wp\/v2\/categories?post=3425"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.kingbaseglobal.com\/blog\/wp-json\/wp\/v2\/tags?post=3425"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}