Storage Architecture Evolution: Unifying from 9 Components to 5

“

Series: S2 Architecture Overview · Article 3 | Level: Intermediate | Reading Time: 18 min

TL;DR

The initial design used 9 storage components (ClickHouse + Qdrant + Elasticsearch + TuGraph + PostgreSQL + Redis + Kafka + MinIO + Nessie/Iceberg), resulting in 4x data redundancy, 5 sync pipelines, and 45% excess storage cost.
Apache Doris's triple capability -- OLAP analytics + HNSW vector indexing + inverted full-text search -- replaced ClickHouse, Qdrant, and Elasticsearch, reducing storage components from 9 to 6 (Doris + Nessie/Iceberg + Kafka + Redis + PostgreSQL + MinIO).
A unified entity_common / entity_edge / entity_event three-table model with Variant column types enables flexible ontology storage without creating dedicated tables per object type.

#1. Introduction: An Over-Engineered Storage Architecture

When we first designed coomia-dip's storage layer, we made a mistake common to many platform projects: selecting the best component for each data access pattern.

Theoretically impeccable -- ClickHouse for OLAP, Qdrant for vector search, Elasticsearch for full-text, TuGraph for graph queries. Each component is a leader in its domain.

The problem: when you put them all together, system complexity grows exponentially, not linearly.

#2. V1.0 Architecture: The 9-Component Predicament

#2.1 Original Architecture Diagram

Code

+------------------------------------------------------------------+
|                   V1.0 Storage Architecture (9 Components)         |
+------------------------------------------------------------------+
|                                                                    |
|  +-----------+    +-----------+    +-------------+                |
|  |PostgreSQL |    |   Redis   |    |    Kafka    |                |
|  |(OLTP meta)|    | (hot cache)|   |  (events)   |                |
|  +-----------+    +-----------+    +-------------+                |
|                                                                    |
|  +-----------+    +-----------+    +-------------+                |
|  |ClickHouse |    |  Qdrant   |    |Elasticsearch|                |
|  |(OLAP)     |    |(vectors)  |    |(full-text)  |                |
|  +-----------+    +-----------+    +-------------+                |
|                                                                    |
|  +-----------+    +-----------+    +-------------+                |
|  |  TuGraph  |    |   MinIO   |    |Nessie+Iceberg|               |
|  | (graph DB)|    |(object st)|    |(versioning) |                |
|  +-----------+    +-----------+    +-------------+                |
|                                                                    |
+------------------------------------------------------------------+

#2.2 Data Sync Pipeline Disaster

Code

Writing one Ontology instance requires syncing to 5 stores:

Source Data
     |
     v
PostgreSQL (primary)  --(1)--> ClickHouse (OLAP replica)
     |
     +--(2)--> Qdrant (vector replica)
     |
     +--(3)--> Elasticsearch (full-text replica)
     |
     +--(4)--> TuGraph (graph relation replica)
     |
     +--(5)--> Kafka (change events)

Sync pipelines: 5
Data copies: 5 (1 primary + 4 replicas)
Data redundancy: 4x

#2.3 Core Problems with V1.0

Problem	Severity	Manifestation
Data consistency	Critical	Any delay or failure in 5 sync pipelines causes inconsistency
Operational complexity	Severe	Monitoring, alerting, backup, upgrades for 9 components
Storage cost	High	Same data stored 5 times, linear cost growth
Development complexity	High	Each query type requires different client and query language
Latency	Medium	Cross-store queries require multiple network round trips
Team skills	High	Team must master 9 different storage technologies

#3. Key Discovery: Apache Doris's Triple Capability

#3.1 Doris Is More Than OLAP

Apache Doris 2.x achieved convergence of three critical capabilities:

Code

+------------------------------------------------------------------+
|                   Apache Doris 2.x Capability Matrix               |
+------------------------------------------------------------------+
|                                                                    |
|  +------------------+  +------------------+  +------------------+ |
|  |  OLAP Analytics  |  | HNSW Vector Index|  | Inverted FT Search| |
|  |                  |  |                  |  |                  | |
|  | - Columnar store |  | - High-dim vectors| | - Chinese tokenize| |
|  | - Materialized   |  | - Approximate NN |  | - BM25 scoring   | |
|  |   views          |  | - ANN search     |  | - Phrase queries  | |
|  | - Window funcs   |  | - 768/1536 dim   |  | - Bloom filters  | |
|  | - SQL standard   |  |   support        |  | - Inverted index | |
|  +------------------+  +------------------+  +------------------+ |
|                                                                    |
|  +------------------+  +------------------+  +------------------+ |
|  | Variant Column   |  | Materialized     |  | MySQL Protocol   | |
|  | (semi-struct JSON)|  | Views (incr agg) |  | (Sqlg graph qry) | |
|  +------------------+  +------------------+  +------------------+ |
|                                                                    |
+------------------------------------------------------------------+

#3.2 Replacement Mapping

Code

ClickHouse  --replaced by--> Doris OLAP capability
  (columnar OLAP)             (also columnar, better SQL compat)

Qdrant      --replaced by--> Doris HNSW vector index
  (dedicated vector DB)       (2.1+ supports HNSW indexing)

Elasticsearch --replaced by--> Doris inverted index
  (full-text search)           (2.0+ supports inverted index + tokenization)

TuGraph     --replaced by--> Sqlg (TinkerPop on Doris)
  (graph DB)                  (Gremlin -> SQL compilation via MySQL protocol)

#4. V3.0 Architecture: Unified with 6 Components

#4.1 New Architecture Diagram

Code

+------------------------------------------------------------------+
|               V3.0 Unified Storage Architecture (6 Components)     |
+------------------------------------------------------------------+
|                                                                    |
|  External Sources -> Kafka -> Flink -> Apache Doris (unified)      |
|                                          |                         |
|                          +---------------+---------------+         |
|                          |               |               |         |
|                          v               v               v         |
|                    entity_common   entity_edge    entity_event     |
|                    (Variant col)   (Inverted+Bloom) (MV aggregates)|
|                    + HNSW vectors  + Sqlg graph queries            |
|                    + Inverted FTS                                   |
|                                                                    |
|  +-------------------------------------------------------------+  |
|  |  Nessie + Iceberg (branches / time travel / cold archival)    |  |
|  +-------------------------------------------------------------+  |
|                                                                    |
|  +----------+  +----------+  +----------+  +----------+          |
|  |  Kafka   |  |  Redis   |  |PostgreSQL|  |  MinIO   |          |
|  | (events) |  |(hot cache)|  |(OLTP meta)| |(obj store)|          |
|  +----------+  +----------+  +----------+  +----------+          |
|                                                                    |
|  Components: 9 -> 6                                                |
+------------------------------------------------------------------+

#4.2 Simplified Sync Pipeline

Code

V1.0 (5 sync pipelines):
  Source -> PG -> ClickHouse
                -> Qdrant
                -> ES
                -> TuGraph
                -> Kafka

V3.0 (1 sync pipeline):
  Source -> Kafka -> Flink CDC -> Doris
                                   |
                              (one write, many query modes)
                              OLAP / Vector / FullText / Graph

#4.3 Cost Comparison

Dimension	V1.0 (9 components)	V3.0 (6 components)	Improvement
Storage cost	Baseline 100%	55%	-45%
Data redundancy	4x	1x	-75%
Sync pipelines	5	1	-80%
Operational components	9	6	-33%
Consistency window	Seconds to minutes	Milliseconds	10-100x
Team skill requirements	9 technologies	4 core technologies	-56%

#5. The Three-Table Model: entity_common / entity_edge / entity_event

#5.1 Design Philosophy

The traditional approach creates a dedicated database table for each Ontology object type (similar to Palantir's Dataset concept). This leads to frequent DDL operations when object types change frequently.

We adopt a universal three-table model -- all object types share three tables, using Doris's Variant column type for flexible attribute storage.

#5.2 entity_common (Entity Common Table)

SQL

CREATE TABLE entity_common (
    -- Primary key
    instance_id       VARCHAR(64)   NOT NULL,
    object_type_id    VARCHAR(64)   NOT NULL,
    world_id          VARCHAR(64)   NOT NULL,

    -- Attributes (Variant = semi-structured JSON, columnar storage)
    attributes        VARIANT       NOT NULL,

    -- Metadata
    status            VARCHAR(16)   DEFAULT 'ACTIVE',
    created_at        DATETIME      NOT NULL,
    updated_at        DATETIME      NOT NULL,
    created_by        VARCHAR(64),
    version           BIGINT        DEFAULT 1,

    -- Vectors (HNSW indexed)
    embedding_768     ARRAY<FLOAT>  NULL,  -- small model vectors
    embedding_1536    ARRAY<FLOAT>  NULL,  -- large model vectors

    -- Full-text search (inverted index)
    search_text       TEXT          NULL,

    -- Indexes
    INDEX idx_type     (object_type_id) USING INVERTED,
    INDEX idx_world    (world_id) USING INVERTED,
    INDEX idx_status   (status) USING INVERTED,
    INDEX idx_search   (search_text) USING INVERTED
        PROPERTIES("parser" = "chinese"),
    INDEX idx_vec_768  (embedding_768) USING INVERTED
        PROPERTIES("index_type" = "HNSW"),
    INDEX idx_vec_1536 (embedding_1536) USING INVERTED
        PROPERTIES("index_type" = "HNSW")
)
DISTRIBUTED BY HASH(instance_id) BUCKETS 32
PROPERTIES (
    "replication_num" = "3",
    "enable_unique_key_merge_on_write" = "true"
);

#5.3 The Magic of Variant Columns

SQL

-- Write different entity types without DDL changes

-- Write an Employee entity
INSERT INTO entity_common (instance_id, object_type_id,
    world_id, attributes) VALUES (
    'emp_001', 'Employee', 'world_prod',
    '{"name": "Zhang Wei",
      "department": "Engineering",
      "salary": 85000,
      "skills": ["Java", "Python", "gRPC"],
      "hire_date": "2024-03-15"}'
);

-- Write an Equipment entity (completely different schema)
INSERT INTO entity_common (instance_id, object_type_id,
    world_id, attributes) VALUES (
    'equ_001', 'Equipment', 'world_prod',
    '{"model": "Excavator CAT 320",
      "serial_number": "CAT-2024-X320-001",
      "location": {"lat": 31.23, "lng": 121.47},
      "maintenance_due": "2024-12-01",
      "operating_hours": 2450}'
);

-- Query Variant sub-fields directly
SELECT
    instance_id,
    attributes['name'] AS name,
    attributes['salary'] AS salary
FROM entity_common
WHERE object_type_id = 'Employee'
  AND world_id = 'world_prod'
  AND CAST(attributes['salary'] AS INT) > 80000;

#5.4 entity_edge (Relation Edge Table)

SQL

CREATE TABLE entity_edge (
    edge_id           VARCHAR(64)   NOT NULL,
    source_id         VARCHAR(64)   NOT NULL,
    source_type       VARCHAR(64)   NOT NULL,
    target_id         VARCHAR(64)   NOT NULL,
    target_type       VARCHAR(64)   NOT NULL,
    relation_type     VARCHAR(64)   NOT NULL,
    world_id          VARCHAR(64)   NOT NULL,
    properties        VARIANT       NULL,
    created_at        DATETIME      NOT NULL,
    created_by        VARCHAR(64),

    INDEX idx_source   (source_id) USING INVERTED,
    INDEX idx_target   (target_id) USING INVERTED,
    INDEX idx_rel_type (relation_type) USING INVERTED,
    INDEX idx_world    (world_id) USING INVERTED,
    INDEX idx_bloom_src (source_id) USING BLOOM_FILTER,
    INDEX idx_bloom_tgt (target_id) USING BLOOM_FILTER
)
DISTRIBUTED BY HASH(edge_id) BUCKETS 16;

#5.5 entity_event (Event Table)

SQL

CREATE TABLE entity_event (
    event_id          VARCHAR(64)   NOT NULL,
    instance_id       VARCHAR(64)   NOT NULL,
    object_type_id    VARCHAR(64)   NOT NULL,
    world_id          VARCHAR(64)   NOT NULL,
    event_type        VARCHAR(32)   NOT NULL,
    before_state      VARIANT       NULL,
    after_state       VARIANT       NULL,
    changed_fields    ARRAY<VARCHAR(64)> NULL,
    event_time        DATETIME      NOT NULL,
    user_id           VARCHAR(64),
    trace_id          VARCHAR(64),

    INDEX idx_instance (instance_id) USING INVERTED,
    INDEX idx_type     (event_type) USING INVERTED,
    INDEX idx_world    (world_id) USING INVERTED
)
DISTRIBUTED BY HASH(instance_id) BUCKETS 16
PARTITION BY RANGE(event_time) (
    -- Monthly partitions for efficient time-range queries
    -- and cold data cleanup
)
PROPERTIES (
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "MONTH",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p"
);

#6. Nessie + Iceberg: Versioned Lakehouse

#6.1 Why Versioning?

One of coomia-dip's core concepts is the World (data branch). Each World is a complete data snapshot branch supporting:

Scenario simulation: Create a World branch, modify data, observe impact -- without affecting production
Time travel: Query data state at any historical point
Branch merging: Merge simulation results back to production

#6.2 Nessie Branch Management

Code

main (Production World)
  |
  +--- dev/feature-001 (Development Branch)
  |     |
  |     +--- commit_a1b2c3 (Schema change)
  |     +--- commit_d4e5f6 (Data migration)
  |
  +--- scenario/supply-chain-disruption
  |     |
  |     +--- commit_g7h8i9 (What-if: supplier fails)
  |     +--- commit_j0k1l2 (Impact assessment)
  |
  +--- scenario/price-optimization
        |
        +--- commit_m3n4o5 (Price adjustment)

#6.3 Hot / Warm / Cold Data Tiering

Code

+---------+  real-time  +---------+  periodic   +---------+
|  Doris  | ---------> | Iceberg | ---------> |  MinIO  |
|  (hot)  |            |  (warm) |            |  (cold) |
+---------+            +---------+            +---------+
 < 7 days              7-90 days              > 90 days
 ms queries            sec queries            min queries
 SSD storage           SSD/HDD               HDD/S3

#7. Kafka's Role in Storage Architecture

Code

+------------------------------------------------------------------+
|              Kafka's Position in Storage Architecture              |
+------------------------------------------------------------------+
|                                                                    |
|  [External Sources]                                                |
|       |                                                            |
|       v                                                            |
|  +----------+                                                      |
|  |  Kafka   |  <-- Unified entry point for data ingestion          |
|  |          |                                                      |
|  | Topics:  |                                                      |
|  | - ontology.ingestion.{type}    data ingestion                   |
|  | - ontology.change.{type}       change events                    |
|  | - audit.events                 audit events                     |
|  | - pipeline.triggers            pipeline triggers                |
|  | - derived.invalidation         derived property invalidation    |
|  | - reasoning.triggers           reasoning triggers               |
|  | - cross-Layer.coordination     cross-Layer coordination         |
|  +----------+                                                      |
|       |                                                            |
|       v                                                            |
|  +-----------+        +-----------+                                |
|  | Flink CDC | -----> |   Doris   |                                |
|  +-----------+        +-----------+                                |
+------------------------------------------------------------------+

#8. Redis Caching Strategy

#8.1 Cache Hierarchy

Code

Request arrives
     |
     v
+------------------+
| L1: In-process   |  Caffeine (Java) / lru_cache (Python)
| TTL: 10s         |  Capacity: 10K entries per process
+------------------+
     | miss
     v
+------------------+
| L2: Redis cache  |  Redis Cluster
| TTL: 5min        |  Capacity: scales on demand
+------------------+
     | miss
     v
+------------------+
| L3: Doris query  |  entity_common / entity_edge
| No TTL           |  Authoritative source
+------------------+

#8.2 Cache Key Design

Code

Schema cache:
  schema:{world_id}:{object_type_id}:{version}
  TTL: 30min (Schema changes trigger proactive invalidation)

Instance cache:
  instance:{world_id}:{instance_id}:{version}
  TTL: 5min

Derived property cache:
  derived:{world_id}:{instance_id}:{property_id}:{version}
  TTL: Determined by compute strategy (CACHED = 10min)

Query result cache:
  query:{world_id}:{query_hash}
  TTL: 1min (query results change frequently)

#9. PostgreSQL: OLTP Metadata

#9.1 Why Not Use Doris for Metadata?

Doris is an OLAP engine optimized for large-scale scan analytics. PostgreSQL is better suited for:

Scenario	Doris	PostgreSQL
Single-row point lookup	Slow (columnar scan overhead)	Fast (B-Tree index)
High-frequency small transactions	Not suited (batch-write optimized)	Suited (WAL + MVCC)
ACID transactions	Limited support	Full support
Foreign key constraints	Not supported	Supported

#9.2 Data Stored in PostgreSQL

Code

PostgreSQL metadata database:
├── tenant_config        (tenant configuration)
├── organization         (org structure)
├── user_account         (user accounts)
├── role_assignment      (role assignments)
├── policy_definition    (policy definitions)
├── world_metadata       (World metadata)
├── pipeline_definition  (pipeline definitions)
├── scheduler_config     (scheduler config)
├── connection_config    (connection config)
└── system_config        (system config)

#10. Storage Adapter SPI Design

#10.1 Why SPI?

While Doris is the current choice, we preserve the ability to swap storage engines through an SPI (Service Provider Interface):

Java

public interface OntologyStorageAdapter {

    // Instance CRUD
    OntologyInstance create(
        WorldContext world,
        String objectTypeId,
        Map<String, Object> attributes);

    Optional<OntologyInstance> findById(
        WorldContext world,
        String instanceId);

    List<OntologyInstance> query(
        WorldContext world,
        OntologyQuery query);

    void update(
        WorldContext world,
        String instanceId,
        Map<String, Object> changes);

    void delete(WorldContext world, String instanceId);

    // Relation management
    void createRelation(
        WorldContext world,
        String sourceId, String targetId,
        String relationType);

    List<Relation> getRelations(
        WorldContext world,
        String instanceId,
        RelationDirection direction);

    // Vector search
    List<VectorSearchResult> vectorSearch(
        WorldContext world,
        float[] queryVector,
        int topK,
        VectorSearchOptions options);

    // Full-text search
    List<FullTextSearchResult> fullTextSearch(
        WorldContext world,
        String query,
        FullTextSearchOptions options);
}

#11. Database-Level World Isolation

#11.1 Project -> Doris Database Mapping

Code

Tenant: acme_corp
  +-- Organization: engineering
       +-- Space: product_dev
            +-- Project: supply_chain
                 |
                 +--> Doris DB: onto_acme_engineering_supply_chain
                 |    +-- entity_common
                 |    +-- entity_edge
                 |    +-- entity_event
                 |
                 +--> MinIO Bucket: onto-acme-engineering-supply-chain
                 |
                 +--> Nessie Namespace: acme.engineering.supply_chain
                      +-- Branch: main (Production World)
                      +-- Branch: dev/feature-001
                      +-- Branch: scenario/disruption-sim

#11.2 World Isolation via WHERE Clauses

SQL

-- All queries automatically append world_id filter
-- OntologyRuntimeService injects this during query building

SELECT * FROM entity_common
WHERE world_id = 'world_prod'      -- Mandatory World isolation
  AND object_type_id = 'Employee'
  AND CAST(attributes['department'] AS VARCHAR)
      = 'Engineering';

-- If world_id filter is missing, OntologyRuntimeService
-- throws WORLD_CONTEXT_REQUIRED exception
-- This is architecturally enforced, cannot be bypassed

#12. Comparison with Other Storage Solutions

Solution	OLAP	Vector	Full-text	Graph	Versioning	Complexity
9-comp (V1.0)	ClickHouse	Qdrant	ES	TuGraph	Nessie	Very High
Doris unified (V3.0)	Doris	Doris HNSW	Doris inverted	Sqlg on Doris	Nessie	Low
Snowflake+Pinecone	Snowflake	Pinecone	Snowflake	None	Time Travel	Medium
Databricks+Chroma	Spark SQL	Chroma	Delta Lake	None	Delta Lake	Medium
PostgreSQL+pgvector	PG (slow)	pgvector	PG FTS	Apache AGE	None	Low

#12.1 Known Limitations of the Doris Approach

Code

Known limitations:
1. Vector search accuracy: Doris HNSW slightly lower than dedicated vector DBs
   Impact: RAG scenarios requiring very high recall may need tuning
   Mitigation: Adjust HNSW params (ef_construction, M) for higher accuracy

2. Full-text capabilities: Less rich than Elasticsearch
   Impact: Complex NLP queries (synonyms, fuzzy matching, phonetic search)
   Mitigation: 80% of query scenarios are well-served by Doris inverted index

3. Graph query performance: Sqlg Gremlin->SQL compilation has overhead
   Impact: Deep traversals (> 3 hops) see performance degradation
   Mitigation: Materialized views pre-compute common graph query paths

4. Write latency: Doris optimized for batch writes, single-row latency is higher
   Impact: Real-time single-update scenarios
   Mitigation: Kafka buffering + micro-batch writes

#13. Migration Path: V1.0 to V3.0

Code

Phase 1: Deploy Doris, dual-write
  - Keep original ClickHouse/Qdrant/ES running
  - New data written to both old and new systems
  - Verify Doris query results match original systems

Phase 2: Read switchover
  - OLAP queries switch from ClickHouse to Doris
  - Vector search switches from Qdrant to Doris HNSW
  - Full-text search switches from ES to Doris inverted index

Phase 3: Decommission old components
  - Stop writing to ClickHouse/Qdrant/ES
  - Shut down old components
  - Reclaim resources

Duration: ~6 weeks (including thorough testing)
Risk: Low (dual-write phase allows instant rollback)

#Key Takeaways

"Best component per query type" is a trap. With 5 different storage engines, data sync complexity devours all performance advantages. Doris's triple capability (OLAP + vector + full-text) lets us replace 4 components with 1, eliminating 4x data redundancy and 5 sync pipelines.
Variant column type is the key to flexible ontology storage. The entity_common table's attributes VARIANT column allows any ontology instance type to be stored in the same table without DDL changes. This pairs perfectly with Doris's columnar storage -- Variant data is still stored columnar internally, preserving query performance.
Versioning is not optional -- it is core architecture. Nessie + Iceberg branch management turns the World concept from "idea" into "implementation." Each Nessie branch is a World, branch creation is O(1) (only metadata pointers), and data shares underlying Iceberg files until modification triggers Copy-on-Write.

“

Next Article Preview: [S2-04] Our Ontology Kernel: Everything Must Go Through the Ontology Layer -- understanding why direct database access is a technical red line and how OntologyRuntimeService serves as the platform's "data constitution."

Tags: #storage #doris #iceberg #nessie #vector-search #fulltext #olap #data-architecture #coomia-dip