Traditional Multi-Engine Architecture:

┌─────────────┐  ┌─────────────┐  ┌─────────────┐
│ ClickHouse  │  │   Qdrant    │  │Elasticsearch│
│  (OLAP)     │  │  (Vector)   │  │ (Full-Text) │
└──────┬──────┘  └──────┬──────┘  └──────┬──────┘
       │                │                │
       └────────┬───────┴────────────────┘
                │
        ┌───────┴───────┐
        │  Application  │
        │  Query Router │
        └───────────────┘

Unified Engine Architecture (coomia-dip):

                ┌──────────────────────┐
                │    Apache Doris      │
                │  ┌────┬─────┬─────┐  │
                │  │OLAP│HNSW │Inv. │  │
                │  │Agg │Vec  │Index│  │
                │  └────┴─────┴─────┘  │
                └──────────┬───────────┘
                           │
                   ┌───────┴───────┐
                   │  Application  │
                   │ (Single API)  │
                   └───────────────┘

Doris Cluster Architecture:

 ┌─────────┐
 │   FE    │  Frontend: SQL parsing, query planning, metadata
 │ (Leader)│
 └────┬────┘
      │
┌─────┼─────────────────────────────┐
│     │         BE Cluster          │
│  ┌──┴──┐  ┌─────┐  ┌─────┐      │
│  │ BE1 │  │ BE2 │  │ BE3 │      │
│  │     │  │     │  │     │      │
│  │Tab1 │  │Tab1 │  │Tab1 │      │
│  │Part │  │Part │  │Part │      │
│  │ 1,4 │  │ 2,5 │  │ 3,6 │      │
│  └─────┘  └─────┘  └─────┘      │
└──────────────────────────────────┘

CREATE TABLE IF NOT EXISTS entity_common (
    -- Partition keys
    world_id        VARCHAR(64)   NOT NULL COMMENT 'World ID',
    object_type_id  VARCHAR(128)  NOT NULL COMMENT 'Object Type ID',
    entity_id       VARCHAR(128)  NOT NULL COMMENT 'Entity ID',

    -- Core properties (high-frequency query columns)
    title           VARCHAR(512)  NULL COMMENT 'Title',
    status          VARCHAR(32)   NULL COMMENT 'Status',
    created_at      DATETIME      NOT NULL COMMENT 'Created timestamp',
    updated_at      DATETIME      NOT NULL COMMENT 'Updated timestamp',

    -- Flexible properties (JSON column)
    properties      JSON          NULL COMMENT 'Dynamic properties JSON',

    -- Vector column (embedding)
    embedding       ARRAY<FLOAT>  NULL COMMENT 'Semantic embedding vector (768d)',

    -- Full-text search column
    search_text     TEXT          NULL COMMENT 'Full-text search content'
)
ENGINE = OLAP
DUPLICATE KEY(world_id, object_type_id, entity_id)
PARTITION BY RANGE(created_at) (
    PARTITION p202601 VALUES LESS THAN ('2026-02-01'),
    PARTITION p202602 VALUES LESS THAN ('2026-03-01'),
    PARTITION p202603 VALUES LESS THAN ('2026-04-01')
)
DISTRIBUTED BY HASH(entity_id) BUCKETS 16
PROPERTIES (
    "replication_allocation" = "tag.location.default: 3",
    "storage_format" = "V2",
    "enable_unique_key_merge_on_write" = "true"
);

-- Count entities by type, grouped by month
SELECT
    object_type_id,
    DATE_TRUNC('month', created_at) AS month,
    COUNT(*) AS entity_count,
    COUNT(DISTINCT status) AS status_variety,
    AVG(JSON_EXTRACT_DOUBLE(properties, '$.risk_score')) AS avg_risk
FROM entity_common
WHERE world_id = 'world-prod-001'
  AND created_at >= '2026-01-01'
GROUP BY object_type_id, DATE_TRUNC('month', created_at)
ORDER BY month DESC, entity_count DESC
LIMIT 100;

HNSW Multi-Layer Graph Structure:

Layer 2:   [A]─────────────────[D]
            │                    │
Layer 1:   [A]────[B]────[C]───[D]────[E]
            │      │      │     │      │
Layer 0:   [A]─[F]─[B]─[G]─[C]─[H]─[D]─[I]─[E]─[J]

Search process (finding nearest neighbor of Q):
1. Start from entry point A at Layer 2
2. Greedy search on Layer 2: A -> D (D is closer to Q)
3. Descend to Layer 1: D -> E (E is closer to Q)
4. Descend to Layer 0: E -> I -> J (nearest neighbor found)

-- Create HNSW index on the embedding column of entity_common
ALTER TABLE entity_common
ADD INDEX idx_embedding_hnsw (embedding)
USING INVERTED
PROPERTIES (
    "index_type" = "HNSW",
    "metric_type" = "COSINE",
    "dim" = "768",
    "M" = "32",
    "ef_construction" = "200"
);

-- Semantic search: find 10 most similar entities to a given vector
SELECT
    entity_id,
    title,
    object_type_id,
    COSINE_DISTANCE(embedding, ARRAY[0.12, -0.34, ..., 0.56]) AS distance
FROM entity_common
WHERE world_id = 'world-prod-001'
  AND object_type_id = 'Equipment'
ORDER BY distance ASC
LIMIT 10;

-- Combined query: semantic search with specific conditions
SELECT
    entity_id,
    title,
    status,
    JSON_EXTRACT_STRING(properties, '$.department') AS department,
    COSINE_DISTANCE(embedding, ARRAY[0.12, -0.34, ..., 0.56]) AS similarity
FROM entity_common
WHERE world_id = 'world-prod-001'
  AND object_type_id = 'Employee'
  AND status = 'active'
  AND created_at >= '2026-01-01'
ORDER BY similarity ASC
LIMIT 20;

Query Execution Plan:

┌────────────────────────┐
│    Result (Top 20)     │
└────────┬───────────────┘
         │
┌────────┴───────────────┐
│   Sort by similarity   │
│   (TopN Heap Sort)     │
└────────┬───────────────┘
         │
┌────────┴───────────────┐
│  HNSW Vector Search    │
│  (ANN on embedding)    │
└────────┬───────────────┘
         │
┌────────┴───────────────┐
│  Predicate Filter      │
│  world_id = '...'      │
│  object_type_id = '...'│
│  status = 'active'     │
│  created_at >= '...'   │
└────────┬───────────────┘
         │
┌────────┴───────────────┐
│  Partition Pruning     │
│  (created_at range)    │
└────────────────────────┘

Inverted Index Internal Structure:

Document: "Apache Doris is a high-performance analytics database"

Tokenization:
┌────────┬──────────────────────────┐
│ Token  │ Posting List (Doc IDs)   │
├────────┼──────────────────────────┤
│ apache │ [1, 15, 42, 88]         │
│ doris  │ [1, 3, 15, 42]          │
│ high   │ [1, 7, 23, 56, 88]     │
│ perf*  │ [1, 7, 12, 56]         │
│ analyt*│ [1, 3, 42, 67]         │
│ datab* │ [1, 3, 7, 42, 67, 88]  │
└────────┴──────────────────────────┘

-- Create inverted index on search_text column (with CJK tokenization)
ALTER TABLE entity_common
ADD INDEX idx_search_text (search_text)
USING INVERTED
PROPERTIES (
    "parser" = "unicode",
    "support_phrase" = "true",
    "lower_case" = "true"
);

-- Create inverted index on title column
ALTER TABLE entity_common
ADD INDEX idx_title_inv (title)
USING INVERTED
PROPERTIES (
    "parser" = "unicode",
    "support_phrase" = "true"
);

-- Full-text search: find entities containing "risk assessment"
SELECT
    entity_id,
    title,
    object_type_id,
    search_text
FROM entity_common
WHERE world_id = 'world-prod-001'
  AND MATCH_ALL(search_text, 'risk assessment')
ORDER BY updated_at DESC
LIMIT 20;

-- Phrase matching
SELECT entity_id, title
FROM entity_common
WHERE MATCH_PHRASE(search_text, 'data quality check')
LIMIT 10;

-- Fuzzy matching
SELECT entity_id, title
FROM entity_common
WHERE MATCH_ALL(title, 'equipment failure')
  AND object_type_id = 'MaintenanceRecord'
LIMIT 10;

-- The unicode tokenizer handles mixed CJK and Latin text natively
-- Verify tokenization results
SELECT TOKENIZE('equipment failure report Q1 2026', 'unicode');
-- Result: ["equipment", "failure", "report", "q1", "2026"]

-- Scenario: Among active equipment, find records semantically related
-- to "abnormal vibration", containing the keyword "maintenance",
-- aggregated by department

WITH semantic_matches AS (
    SELECT
        entity_id,
        title,
        status,
        JSON_EXTRACT_STRING(properties, '$.department') AS department,
        JSON_EXTRACT_DOUBLE(properties, '$.severity') AS severity,
        COSINE_DISTANCE(embedding, ARRAY[0.12, -0.34, ..., 0.56]) AS vec_distance
    FROM entity_common
    WHERE world_id = 'world-prod-001'
      AND object_type_id = 'MaintenanceRecord'
      AND status IN ('open', 'in_progress')
      AND MATCH_ALL(search_text, 'maintenance')
    ORDER BY vec_distance ASC
    LIMIT 200
)
SELECT
    department,
    COUNT(*) AS record_count,
    AVG(severity) AS avg_severity,
    MIN(vec_distance) AS best_semantic_match,
    GROUP_CONCAT(title ORDER BY vec_distance ASC SEPARATOR ' | ') AS top_titles
FROM semantic_matches
GROUP BY department
ORDER BY avg_severity DESC;

Three-in-One Query Execution:

Step 1: Partition Pruning
  +-- created_at range -> scan only relevant partitions

Step 2: Predicate Push-down
  +-- world_id = 'world-prod-001'        -> Prefix Index
  +-- object_type_id = 'Maintenance...'   -> Prefix Index
  +-- status IN ('open', 'in_progress')   -> Bitmap Index

Step 3: Inverted Index Scan
  +-- MATCH_ALL(search_text, 'maintenance') -> Posting List

Step 4: HNSW Vector Search
  +-- COSINE_DISTANCE(embedding, [...])   -> ANN Top-200

Step 5: Intersection
  +-- Step2 INTERSECT Step3 INTERSECT Step4

Step 6: Aggregation
  +-- GROUP BY department + AVG + COUNT

# fe.conf - coomia-dip recommended configuration

# Memory
JAVA_OPTS="-Xmx8g -Xms4g"

# Query timeout
max_query_timeout = 300

# Vector search parameters
default_hnsw_ef_search = 150

# Inverted index configuration
inverted_index_ram_dir_enable = true

# Concurrency control
max_running_txn_num_per_db = 1000
qe_max_connection = 2048

# be.conf - coomia-dip recommended configuration

# Memory
mem_limit = 80%
storage_page_cache_limit = 40%

# Vector search memory
vector_index_cache_capacity = 2147483648  # 2GB

# Compaction
default_rowset_type = BETA
compaction_task_num_per_disk = 4

# Concurrency
doris_scanner_thread_pool_thread_num = 48
doris_scanner_thread_pool_queue_size = 102400

# Storage
storage_root_path = /data/doris/storage

Index Selection Decision Tree:

                    ┌──────────────────┐
                    │ What query type?  │
                    └────────┬─────────┘
                             │
              ┌──────────────┼──────────────┐
              │              │              │
        ┌─────┴─────┐ ┌─────┴─────┐ ┌─────┴─────┐
        │ Exact/     │ │ Semantic   │ │ Full-Text │
        │ Range      │ │ Similarity │ │ Keywords  │
        └─────┬─────┘ └─────┬─────┘ └─────┬─────┘
              │              │              │
        ┌─────┴─────┐ ┌─────┴─────┐ ┌─────┴─────┐
        │ Prefix/   │ │  HNSW     │ │ Inverted  │
        │ Bitmap    │ │  Index    │ │  Index    │
        │ Index     │ │           │ │           │
        └───────────┘ └───────────┘ └───────────┘

-- Dynamic partition configuration (automatic monthly partitions)
ALTER TABLE entity_common SET (
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "MONTH",
    "dynamic_partition.start" = "-12",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.buckets" = "16",
    "dynamic_partition.replication_allocation" = "tag.location.default: 3"
);

Data Write Flow:

┌──────────┐     ┌─────────────┐     ┌──────────────┐
│ Control  │gRPC │ Data Layer  │     │   Doris      │
│  Layer   ├────>│ WriteService├────>│   (Stream    │
│ (B)      │     │             │     │    Load)     │
└──────────┘     └──────┬──────┘     └──────────────┘
                        │
                        │ async
                        v
                 ┌──────────────┐
                 │  Embedding   │
                 │  Service (D) │
                 │  (generate   │
                 │   vectors)   │
                 └──────┬───────┘
                        │
                        v
                 ┌──────────────┐
                 │ Doris UPDATE │
                 │ (embedding   │
                 │  column)     │
                 └──────────────┘

# Doris query client in python-sdk
class DorisQueryClient:
    """Unified query client supporting OLAP/vector/full-text three-in-one"""

    def __init__(self, config: DorisConfig):
        self.pool = ConnectionPool(
            host=config.host,
            port=config.query_port,
            user=config.user,
            password=config.password,
            database=config.database,
            pool_size=config.pool_size
        )

    async def unified_search(
        self,
        world_id: str,
        object_type_id: str,
        *,
        filters: dict | None = None,
        vector_query: list[float] | None = None,
        text_query: str | None = None,
        aggregations: list[str] | None = None,
        limit: int = 100
    ) -> QueryResult:
        """Three-in-one unified search"""
        sql_builder = UnifiedSQLBuilder()

        # Base conditions
        sql_builder.add_condition("world_id", "=", world_id)
        sql_builder.add_condition("object_type_id", "=", object_type_id)

        # Scalar filters
        if filters:
            for key, value in filters.items():
                sql_builder.add_condition(key, "=", value)

        # Vector search
        if vector_query:
            sql_builder.add_vector_search("embedding", vector_query, metric="cosine")

        # Full-text search
        if text_query:
            sql_builder.add_text_search("search_text", text_query)

        # Aggregations
        if aggregations:
            for agg in aggregations:
                sql_builder.add_aggregation(agg)

        sql = sql_builder.build(limit=limit)
        return await self.execute(sql)

// query_service.proto
service UnifiedQueryService {
    // Unified query interface
    rpc ExecuteQuery(QueryRequest) returns (QueryResponse);

    // Vector search
    rpc VectorSearch(VectorSearchRequest) returns (SearchResponse);

    // Full-text search
    rpc TextSearch(TextSearchRequest) returns (SearchResponse);

    // Hybrid search
    rpc HybridSearch(HybridSearchRequest) returns (SearchResponse);
}

message HybridSearchRequest {
    string world_id = 1;
    string object_type_id = 2;
    repeated Filter filters = 3;
    VectorQuery vector_query = 4;
    TextQuery text_query = 5;
    repeated Aggregation aggregations = 6;
    int32 limit = 7;
}

Query Latency Distribution (P50 / P95 / P99):

OLAP Aggregation (10-column GROUP BY):
  P50: 120ms  P95: 350ms  P99: 800ms
  ████████████▒▒▒░░░░░░░░░░░░░░░░░░

Vector Search Top-100:
  P50: 18ms   P95: 45ms   P99: 90ms
  ███▒░░░░░░░░░░░░░░░░░░░░░░░░░░░░

Full-Text Search (tokenized):
  P50: 25ms   P95: 80ms   P99: 150ms
  █████▒▒░░░░░░░░░░░░░░░░░░░░░░░░░

Three-in-One Combined:
  P50: 85ms   P95: 250ms  P99: 500ms
  █████████▒▒▒░░░░░░░░░░░░░░░░░░░░

Write Throughput (Stream Load):
  Single BE: 50,000 rows/sec
  Cluster:   200,000 rows/sec

Cluster Resource Utilization (5 BE nodes average):

CPU:     ██████████████░░░░░░  68%
Memory:  ████████████████░░░░  82%
Disk IO: ██████████░░░░░░░░░░  48%
Network: ████░░░░░░░░░░░░░░░░  22%

-- 1. Reduce build parameters (lower ef_construction)
ALTER TABLE entity_common
ADD INDEX idx_emb_hnsw (embedding) USING INVERTED
PROPERTIES ("index_type"="HNSW", "M"="16", "ef_construction"="100");

-- 2. Build incrementally (create table with index first, then import data)
-- 3. Use Routine Load for incremental builds

-- Increase ef_search (trade speed for recall)
SET SESSION hnsw_ef_search = 300;

-- Expand candidate set
SELECT entity_id, COSINE_DISTANCE(embedding, ...) AS dist
FROM entity_common
WHERE ...
ORDER BY dist ASC
LIMIT 50;  -- Fetch more, re-rank in application layer

-- Use custom dictionaries
-- Add custom dictionary files to BE conf/dict/ directory
-- custom_dict.txt:
-- ontology
-- data lineage
-- knowledge graph

-- Rebuild index to apply
ALTER TABLE entity_common DROP INDEX idx_search_text;
ALTER TABLE entity_common ADD INDEX idx_search_text (search_text)
USING INVERTED PROPERTIES (
    "parser" = "unicode",
    "support_phrase" = "true",
    "dict_path" = "custom_dict.txt"
);

coomia-dip Storage Layer Overview:

┌─────────────────────────────────────────────────┐
│                  Query Layer                     │
│  ┌──────────────────────────────────────────┐   │
│  │       QueryFederationService (C)          │   │
│  └──────┬──────────────────┬────────────────┘   │
│         │                  │                     │
│    ┌────┴─────┐      ┌────┴─────┐               │
│    │  Doris   │      │  DuckDB  │               │
│    │ (Primary)│      │ (Aux)    │               │
│    └────┬─────┘      └──────────┘               │
│         │                                        │
│    ┌────┴──────────────────────────────┐        │
│    │         Storage Layer              │        │
│    │  ┌─────────┐  ┌────────────────┐  │        │
│    │  │ MinIO   │  │ Nessie+Iceberg │  │        │
│    │  │(Object) │  │ (Versioned     │  │        │
│    │  │         │  │  Lakehouse)    │  │        │
│    │  └─────────┘  └────────────────┘  │        │
│    └────────────────────────────────────┘        │
└─────────────────────────────────────────────────┘