Problem-first table design intelligence for Apache Doris. 37 rules, 7 use case templates, 4 sizing guides. All details in

references/

directory and compiled

AGENTS.md

.

• Data model — UNIQUE (updates?) vs DUPLICATE (append?) vs AGGREGATE (pre-agg only?) →

  schema-model-choose-for-workload

• Partition strategy — Time-series? AUTO PARTITION preferred. Small table? Skip. Do NOT combine AUTO with dynamic_partition. →

  schema-partition-*

• Bucket key + count — HASH on JOIN key. Calculate explicit count:

  daily_GB / target_tablet_GB

  . Use explicit fallback counts when volume is unknown: 3 for small dimensions, 8 for medium tables, 16-32 for large daily fact tables. →

  schema-bucket-*

• Sort key order — High-selectivity first, fixed-length before VARCHAR →

  schema-keys-*

• Data types — Native types, not STRING. DECIMAL not FLOAT. →

  schema-types-*

• Indexes — BloomFilter for equality, Inverted for text, NGram for LIKE →

  schema-index-*

• Properties — MoW enabled? Compression? Cloud mode replication_num=1? →

  schema-props-*

• DDL hard constraints (VeloDB rejects DDL if any violated):
  • UNIQUE KEY + PARTITION BY RANGE → partition column MUST be in the UNIQUE KEY:

    UNIQUE KEY(id, dt) PARTITION BY RANGE(dt)

  • Key columns must be the FIRST N columns in schema, same order — put key cols first, non-key after. Example:

    UNIQUE KEY(account_id, symbol)

    means schema must start with

    account_id, symbol, ...

    — never place non-key columns between key columns

  • store_row_column = "true"

    only works on UNIQUE MoW — NOT on AGGREGATE or DUPLICATE
  • AUTO PARTITION requires

    date_trunc()

    AND empty parens:

    AUTO PARTITION BY RANGE(date_trunc(col, 'day')) ()

    — bare column name fails, missing

    ()

    fails
  • Dynamic partition requires explicit

    PARTITION BY RANGE(col) ()

    clause in DDL — properties alone are not enough
  • Do not set

    dynamic_partition.buckets

    ; put the numeric count only in

    DISTRIBUTED BY HASH(col) BUCKETS N

  • compaction_policy = "time_series"

    only for DUPLICATE tables — fails on UNIQUE
  • Async MV refresh: use

    REFRESH AUTO ON SCHEDULE EVERY 10 MINUTE

    or

    REFRESH COMPLETE ON SCHEDULE EVERY 10 MINUTE

    — NOT

    REFRESH SCHEDULE EVERY

    , NOT

    REFRESH ASYNC EVERY(INTERVAL ...)

    . Minimum interval: 1 MINUTE
  • MV using

    NOW()

    /

    CURDATE()

    : add

    PROPERTIES ("enable_nondeterministic_function" = "true")

  • BOOLEAN defaults must be quoted:

    DEFAULT "true"

    not

    DEFAULT TRUE

  • BloomFilter index: use

    PROPERTIES ("bloom_filter_columns" = "col1,col2")

    — NOT inline

    INDEX ... USING BLOOM FILTER

  • AGGREGATE column syntax: aggregation function BEFORE default:

    col BIGINT SUM DEFAULT "0"

    — NOT

    col BIGINT DEFAULT "0" SUM

  • AGGREGATE

    DEFAULT "null"

    only works for VARCHAR — fails on INT, DATE, DECIMAL, BIGINT. Omit DEFAULT entirely for REPLACE_IF_NOT_NULL on non-string types:

    vip_level INT REPLACE_IF_NOT_NULL

    (not

    DEFAULT "null"

    )

  • enable_unique_key_partial_update

    is a session variable, NOT a table property
  • Full details:

    schema-ddl-gotchas

1. Check

   VELOCLI_PATH

   env var — if set, use that binary path

2. command -v velocli

   — use from PATH

3. command -v sdbcli

   — only for explicit SelectDB environments
4. If none available: fall back to

   mysql

   client (see

   references/start-*.md

   )

references/cli-investigation.md

• Evidence first is mandatory: collect or attempt profile, tablet, DDL, stats, EXPLAIN, history, active-query, or connection evidence before forming hypotheses. If evidence cannot be collected locally, state that and provide the exact commands to run
• Prefer existing profiles: use

  profile get <query_id>

  ,

  profile list

  , or

  profile history

  before re-executing SQL
• Proactive discovery: for vague slow-query reports, start with

  auth status

  ,

  profile list --active

  , and recent

  profile list

  before asking the user for more context
• Safety gate: before running user SQL with

  --profile

  , check whether it is safe (no DDL, no mutation, no unbounded scan). For unknown, peak-hour, or expensive SQL, run

  velocli sql "EXPLAIN <query>" --format json

  first and ask confirmation or request an existing query_id
• Hypotheses, not verdicts: diagnostic mappings are heuristics. Present evidence, likely cause, what to check next, and when the conclusion may be wrong
• If velocli is unavailable, fall back to SQL commands listed in the reference
• Always use

  --format json

  for structured agent-readable output

• references/start-cloud.md

  — VeloDB Cloud

• references/start-self-hosted.md

  — Self-hosted / BYOC / on-prem

• references/sizing-fe.md

  — FE node sizing

• references/sizing-be-integrated.md

  — BE sizing (integrated storage)

• references/sizing-be-cloud.md

  — BE sizing (cloud / storage-compute)

• references/sizing-storage-formula.md

  — Storage calculation formula

• schema-model-choose-for-workload

  — DUP vs UNIQUE vs AGG decision tree

• schema-model-prefer-mow

  — Always MoW for UNIQUE tables

• schema-model-avoid-agg-for-updates

  — AGG cannot UPDATE/DELETE

• schema-model-sequence-col-for-cdc

  — Sequence column for out-of-order CDC

• schema-partition-range-for-timeseries

  — RANGE for time-series

• schema-partition-dynamic-ttl

  — Dynamic partition for automated TTL

• schema-partition-auto-on-demand

  — AUTO for sporadic data

• schema-partition-skip-for-small

  — Skip partitioning under 1 GB

• schema-bucket-hash-vs-random

  — HASH for pruning, RANDOM for DUP only

• schema-bucket-high-cardinality-key

  — Choose high-cardinality column

• schema-bucket-composite-for-skew

  — Composite key to fix data skew

• schema-bucket-target-size

  — Target 1-10 GB per tablet

• schema-bucket-cloud-mandatory-hash

  — Cloud MoW requires HASH

• schema-keys-selectivity-first

  — High selectivity first

• schema-keys-fixed-length-types

  — Fixed-length before VARCHAR

• schema-keys-prefix-index-limits

  — 36 bytes max, VARCHAR terminates it

• schema-keys-cluster-key-for-mow

  — Cluster key for UNIQUE tables

• schema-keys-avoid-float

  — No FLOAT/DOUBLE in sort key

• schema-types-native-vs-string

  — Native types, not STRING

• schema-types-zonemap-limitations

  — JSON/ARRAY disable ZoneMap

• schema-types-variant-json

  — VARIANT for semi-structured JSON

• schema-types-bitmap-count-distinct

  — BITMAP_UNION for exact count-distinct

• schema-types-doris-specifics

  — DATETIME precision, VARCHAR vs STRING

• schema-index-bloomfilter

  — BloomFilter for equality

• schema-index-inverted

  — Inverted for text/range

• schema-index-ngram-for-like

  — NGram for LIKE %pattern%

• schema-index-bitmap

  — Bitmap for medium cardinality

• schema-index-vector

  — HNSW/IVF for ANN search

• schema-index-text-search

  — Full-text MATCH + BM25

• schema-mv-sync-rollup

  — Sync MV for single-table aggregation

• schema-mv-async-join

  — Async MV for multi-table JOIN

• schema-mv-async-limits

  — Operational limits (50M rows, 3 concurrent)

• schema-props-cloud-forced

  — Cloud mode forced properties

• schema-props-compression

  — LZ4 vs ZSTD compression

• schema-cache-file-cache

  — File cache for cloud mode

• schema-cache-query-partition

  — Query and partition cache