Understanding Doris Table Structure: Rows, Columns, Tablets, Partitions, and DDL

Basic Concepts

Row & Column

A table consists of rows and columns; a row represents a single record, while a column describes a field within that record.

Columns are divided into two major types: Key and Value. From a business perspective, Keys correspond to dimension columns and Values to metric columns. In an aggregate model, rows with identical Key columns are merged, and the aggregation method for each Value column is defined by the user at table creation.

Tablet & Partition

In Doris's storage engine, user data is horizontally split into multiple data shards called Tablets (also known as data buckets). Each Tablet holds a set of rows, and Tablets are mutually exclusive and stored independently.

Multiple Tablets belong to a logical Partition. A Tablet belongs to exactly one Partition, while a Partition contains many Tablets. Because Tablets are physically independent, a Partition can also be considered physically independent. Tablet is the smallest physical unit for data movement and replication.

Several Partitions together form a Table. A Partition is the smallest logical management unit; data import and deletion can be performed at the Partition level.

Data Partitioning

The following DDL illustrates Doris's data partitioning strategy.

CREATE TABLE IF NOT EXISTS example_db.expamle_tbl<br/>(<br/>    `user_id` LARGEINT NOT NULL COMMENT "用户id",<br/>    `date` DATE NOT NULL COMMENT "数据灌入日期时间",<br/>    `timestamp` DATETIME NOT NULL COMMENT "数据灌入的时间戳",<br/>    `city` VARCHAR(20) COMMENT "用户所在城市",<br/>    `age` SMALLINT COMMENT "用户年龄",<br/>    `sex` TINYINT COMMENT "用户性别",<br/>    `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "用户最后一次访问时间",<br/>    `cost` BIGINT SUM DEFAULT "0" COMMENT "用户总消费",<br/>    `max_dwell_time` INT MAX DEFAULT "0" COMMENT "用户最大停留时间",<br/>    `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "用户最小停留时间"<br/>)<br/>ENGINE=olap<br/>AGGREGATE KEY(`user_id`, `date`, `timestamp`, `city`, `age`, `sex`)<br/>PARTITION BY RANGE(`date`)<br/>(<br/>    PARTITION `p202001` VALUES LESS THAN ("2020-02-01"),<br/>    PARTITION `p202002` VALUES LESS THAN ("2020-03-01"),<br/>    PARTITION `p202003` VALUES LESS THAN ("2020-04-01")<br/>)<br/>DISTRIBUTED BY HASH(`user_id`) BUCKETS 16<br/>PROPERTIES<br/>(<br/>    "replication_num" = "3",<br/>    "storage_medium" = "SSD",<br/>    "storage_cooldown_time" = "2021-01-01 12:00:00"<br/>);

Column Definition

In the AGGREGATE KEY model, columns without an explicit aggregation method (SUM, REPLACE, MAX, MIN) are treated as Key columns; all others are Value columns.

Recommendations for defining columns:

Key columns must appear before any Value columns.

Prefer integer types because they are more efficient for computation and lookup than strings.

Choose an integer size that is just sufficient for the data.

For VARCHAR and STRING, set the length to the minimum required.

The total byte length of all columns (Key + Value) must not exceed 100 KB.

Partition and Bucket

Doris supports two‑level data division. The first level is Partition, which only supports RANGE partitioning. The second level is Bucket (Tablet), which only supports HASH distribution.

It is also possible to use only one level (Bucket) without a Partition.

1. Partition

Partition columns can be one or multiple columns, but must be Key columns.

When writing partition values, always enclose them in double quotes.

Partitions are usually based on time columns for easier data lifecycle management.

There is no theoretical limit on the number of partitions.

If no explicit Partition is defined, Doris creates an invisible default Partition covering the full range.

Partitions are defined using VALUES LESS THAN (...) to specify an upper bound, forming left‑closed, right‑open intervals; alternatively, VALUES [...) can specify both bounds.

2. Bucket

If a Partition exists, the DISTRIBUTED ... clause describes the distribution rule inside each Partition; otherwise it applies to the whole table.

Bucket columns can be multiple, but must be Key columns. They may be the same as or different from Partition columns.

Choosing more bucket columns yields a more uniform data distribution but may increase query throughput at the cost of lower concurrency, suitable for high‑throughput, low‑concurrency scenarios.

Choosing fewer bucket columns enables point queries to hit a single bucket, improving concurrency for many simultaneous point queries.

There is no theoretical limit on the number of buckets.

3. Recommendations on the Number of Partitions, Buckets, and Data Volume

The total number of Tablets equals Partition count × Bucket count.

Ideally, the number of Tablets should be slightly larger than the number of disks in the cluster.

A single Tablet is recommended to hold 1 GB–10 GB of data; too small hurts aggregation efficiency, too large hampers replica migration and schema‑change operations.

If Tablet size and count conflict, prioritize the size guideline.

When adding a new Partition, you can specify a different Bucket count for that Partition to adapt to data growth or shrinkage.

Bucket count for a Partition is immutable; plan ahead for future cluster expansion.

Example sizing: with 10 BE nodes each having one disk, a 500 MB table may use 4–8 tablets; a 5 GB table 8–16 tablets; a 50 GB table 32 tablets; a 500 GB table should be partitioned with each partition ~50 GB and 16–32 tablets per partition; a 5 TB table follows similar guidelines.

Note: Table data size can be obtained with the SHOW DATA command; divide the result by the replica count to get the actual data size.

PROPERTIES

The PROPERTIES clause at the end of the CREATE statement allows setting two parameters:

1. replication_num

Number of replicas per Tablet (default 3). Keep the default unless a specific need arises.

Replica count can be modified at runtime; an odd number is recommended.

The maximum replica count depends on the number of distinct IPs in the cluster, not the number of BE instances.

For small, infrequently updated dimension tables, a higher replica count can improve local join performance.

2. storage_medium & storage_cooldown_time

BE storage directories can be explicitly set to SSD or HDD via suffixes (.SSD, .HDD). The initial storage medium for all Partitions can be defined at table creation.

If not specified, the default medium is defined by default_storage_medium in fe.conf (default HDD).

Without a storage_cooldown_time, data automatically migrates from SSD to HDD after 30 days; specifying the parameter overrides this default.

If enable_strict_storage_medium_check is true, the setting is best‑effort; the system falls back to available media if the requested medium is unavailable.

Conclusion

In this example, the ENGINE type is olap, which is the only engine that Doris manages and stores data for. Other engine types such as mysql, broker, or es are merely external table mappings and are not managed or stored by Doris.

`IF NOT EXISTS` indicates that the table will be created only if it does not already exist. It checks only the table name, not whether the existing table schema matches the new definition; a name clash with a different schema still results in success, but the table structure is not recreated.