Table Creation Rules

This topic describes the rules and suggestions for creating Doris tables.

Doris Table Creation Rules

When creating a Doris table and specifying buckets, make sure that each bucket contains data ranging from 100 MB to 3 GB. Additionally, ensure that the maximum number of buckets in a single partition does not exceed 5,000.
You must set a bucketing policy for tables that have over 500 million data records.
Do not set too many bucketing columns in a table. Generally, one or two columns are enough. In addition, you need to ensure even data distribution and balanced query throughput.
- Data should be evenly distributed to prevent data skew in some buckets, which affects data balancing and query efficiency.
- The query throughput is reduced with bucketing and tailoring of query SQL statements to avoid full bucket scanning and improve query performance.
- Preferentially use columns with evenly distributed data and those that are commonly used as query conditions as bucketing columns.
  You can use the following methods to analyze whether data skew occurs:
  
  SELECT a, b, COUNT(*) FROM tab GROUP BY a,b;
  
  Once the command is executed, verify if the variation in the number of data records among the groups is minimal. If the difference surpasses 2/3 or 1/2, select another bucket field.
Do not use dynamic partitioning for less than 20 million data records. Dynamic partitioning generates partitions automatically, but users may overlook small tables. Consequently, numerous useless buckets are created in partitions.
When creating a table, make sure to have three to five sort keys. Having too many sort keys can impede data writing and importing.
If Auto Bucket is not used, bucketing should be determined by the data volume to enhance the performance of data import and query. Auto Bucket causes superfluous tablets and a large number of small files.
Set at least 2 replicas when you create a table. The default replication factor is 3. Do not use a single backup.
Do not create a table that does not have an aggregate function column as an AGGREGATE table.
When creating a primary key table, ensure that the primary key column is unique. Do not set all columns as primary key columns. Set a value column for the primary key table. Do not use primary key tables in data deduplication scenarios.

Doris Table Creation Suggestions

Use no more than six materialized views in a single table. Do not nest materialized views or them in ETL tasks such as heavy aggregations and joins during data writing.
If there are many historical partitions for a little historical data and the data is unbalanced or the data query probability is low, you can create historical partitions on a yearly/monthly basis and store all historical data in the corresponding partitions.
To create history partition, use FROM ("2000-01-01") TO ("i") INTERVAL 1 YEAR.
If the data volume is less than 10 million to 200 million, you do not need to set partitions (the Doris has a default partition). Instead, you can directly use the bucket policy.
If more than 30% data skew occurs in the bucketing fields, do not use the hash bucketing policy. Instead, use the random bucketing policy. The related commands are as follows:
Create table ... DISTRIBUTED BY RANDOM BUCKETS 10 ...
The first field must be the most frequently queried one in the table you created. By default, you can quickly query data with prefix indexes. Select the column that is most frequently queried and has a high cardinality as the prefix index. The first 36 bytes of a row are used as the prefix index of the row by default (for varchar columns, the first 20 bytes are matched as the prefix index, and excessive bytes are truncated).
To fuzzy match or use equivalent/in conditions in a query of more than 100 million data records, use inverted indexes (supported since Doris 2.x) or Bloomfilter. For orthogonal queries with low cardinality columns, use bitmap indexes. (The recommended cardinality of bitmap indexes ranges from 10000 to 100000.)
Plan the number of fields to be used when creating a table. You can reserve dozens of integer or character fields. If fields are insufficient, you need to add fields temporarily at a high cost.