Skip to main content
VeloDB Cloud 26.x is based on Apache Doris 4.x. See Version Mapping.

Optimize Table Schema Design

Table schema design affects data distribution, query parallelism, and the amount of data that filters can skip. Choose the table model for your update and aggregation requirements before tuning bucket columns, sort keys, and data types.

A schema that does not match the workload can cause the following issues:

  • Data skew, which prevents query parallelism from being fully utilized.
  • Sort properties become ineffective, slowing down equality and range queries.
  • Inappropriate field types, which raise computation overhead.

For more detailed design principles, see the Table Design chapter. This chapter starts from real-world cases to show typical schema design issues and tuning recommendations.

Tuning checklist​

When designing or troubleshooting a table schema, check the following items in order:

  • Did you choose a table model that matches your business (Duplicate / Unique / Aggregate)?
  • Are the bucketing columns evenly hashed, with no skew from null or fixed values?
  • Do leading sort key columns match frequent equality and range filters without changing the table model's key semantics?
  • Do data types preserve the required values, range, and precision?

Case 1: Choose a table model​

VeloDB Cloud provides Duplicate Key, Unique Key, and Aggregate Key tables. Unique Key uses merge-on-write (MoW) by default. A table using merge-on-read (MoR) resolves duplicate keys at query time. Choose the model by data semantics and validate performance with your workload.

Compare table models​

Table modelData behaviorTypical scenarios
Duplicate KeyRetains inserted rows. No automatic key-based upsert.Append-only logs and detail data.
Unique Key (MoW)Replaces rows with the same key and resolves duplicates during writes.Key-based upserts and deduplicated detail data.
Unique Key (MoR)Resolves rows with the same key at query time.Evaluate query-time merge costs for existing tables that use this mode.
Aggregate KeyCombines value columns using their declared aggregation functions.Pre-aggregated reports and metric rollups.

Tip:

When the business has no data update requirements and demands high query performance, prefer the Duplicate table.

The SQL examples below are separate alternatives in your current database. You need privileges to create tables, insert rows, and query data. Reuse t1 only after dropping or renaming a previous example table. The sample load generates 10 million rows and consumes compute and storage.

Case 2: Choose bucket columns​

One-sentence definition: The bucketing column determines how data is distributed across buckets. A poor choice causes data skew, which becomes a query performance bottleneck.

A reasonable bucketing column design can:

  • Prevent data skew and fully utilize parallelism
  • Maximize the effectiveness of Colocate Join and Bucket Shuffle Join

Example 1: Diagnose a bucket column with null values​

Diagnose a bucket column with null values

In the following example, the bucketing column is set to c2, but c2 is all null in the imported data. As a result, only 1 of the 64 buckets carries all the data:

CREATE TABLE `t1` (
`c1` INT NULL,
`c2` INT NULL
) ENGINE=OLAP
DUPLICATE KEY(`c1`)
DISTRIBUTED BY HASH(`c2`) BUCKETS 64;
insert into t1 select number, null from numbers ('number'='10000000');

Choose a column with higher cardinality​

For a replacement table, use c1 as the bucket column to spread these distinct values across buckets. High cardinality reduces the risk of skew but does not guarantee equal bucket sizes.

Check value frequencies​

Purpose: Confirm whether the bucketing column is skewed. Command:

select c2, count(*) cnt from t1 group by c2 order by cnt desc limit 10;

Explanation: If a few values account for a large fraction of rows, hashing on this column can concentrate work in a few buckets. Compare value frequencies with tablet row counts and scan activity before changing the schema.

Bucket column selection principles​

  • Avoid columns that tend to have null or fixed values in the business
  • Prefer fields with high business cardinality, such as user ID or order ID
  • Estimate field value distribution before creating the table, and sample to verify when necessary

Tip:

Check whether the bucketing column has data skew. If so, replace it with a field with higher cardinality. Up-front design significantly reduces the cost of later diagnosis and correction.

Case 3: Choose sort key columns​

Rows are sorted by the sort key within data files. Leading sort key columns can help the prefix index skip data for equality and range filters. For Unique Key and Aggregate Key tables, key columns also define update or aggregation semantics. Do not change those semantics solely to optimize filtering.

Example 2: Filter on the leading sort key​

Filter on the leading sort key
select * from t1 where t1.c1 = 1;
select * from t1 where t1.c1 > 1 and t1.c1 < 10;
select * from t1 where t1.c1 in (1, 2, 3);

Define c1 as the leading sort key​

CREATE TABLE `t1` (
`c1` INT NULL,
`c2` INT NULL
) ENGINE=OLAP
DUPLICATE KEY(`c1`)
DISTRIBUTED BY HASH(`c2`) BUCKETS 10;

Tip:

For a Duplicate Key table, consider leading sort key columns that match frequent equality or range filters. Preserve the required update or aggregation keys in other models.

Case 4: Choose data types​

Data types affect storage, comparisons, and arithmetic. Use a type that represents the data correctly, then compare its storage and processing costs. Exactness and range take precedence over a possible speed improvement.

Data type selection principles​

PrincipleRecommendedAvoid
Native types for structured valuesINT, BIGINT, DATE, DATETIME when they represent the dataNumeric or date values stored as VARCHAR or STRING without a need for text semantics
Required precision and rangeInteger types for whole numbers and DECIMAL for exact fractional valuesReplacing exact values with FLOAT solely for performance

Common conversion scenarios​

  • Use BIGINT for numeric identifiers only when leading zeros and text formatting are not significant and the values fit its range.
  • Use INT or BIGINT for whole numbers that fit their range. Keep DECIMAL for exact fractional values, such as money. Use FLOAT only when approximate arithmetic is acceptable.
  • Use DATETIME for date and time values after validating the source format and required precision.

Tip:

Choose the narrowest type that preserves the required range and precision. Validate conversions before rebuilding a table.

Frequently asked questions​

Q1: What if I find the bucketing column is unreasonable after creating the table?​

To change the bucket column for existing data, create a table with the intended distribution and reload or copy the data. A rollup or a different partitioning scheme does not change the base table's bucket column. Compare the replacement table with the original before switching queries.

Q2: Are more key columns always better?​

No. Too many Key columns increase storage sorting overhead and write costs. For Duplicate Key tables, prioritize frequently filtered columns in the leading sort key. In Unique Key and Aggregate Key tables, retain the keys required for correct updates or aggregation.

Q3: When must I use the Unique Key or Aggregate Key model?​

  • Need to deduplicate or update data by primary key, use Unique
  • Need pre-aggregation (SUM, MAX, MIN, etc.), use Aggregate
  • Append-only detail data with extreme query performance requirements, use Duplicate

Q4: How do I determine whether the current table has data skew?​

Run the following SQL to check the bucketing column distribution:

select c2, count(*) cnt from t1 group by c2 order by cnt desc limit 10;

A few very frequent values are a warning sign for hash distribution. Compare tablet row counts and query profile activity to confirm whether they cause a bottleneck.

Summary​

Validate schema decisions against representative queries and data. Compare scan volume, skew, memory, and write costs rather than assuming one table model is always faster.

Key tuning points:

  • Prefer the Duplicate table model (in scenarios with no update requirements)
  • Choose bucketing columns with high cardinality, avoiding null or fixed values
  • Choose leading sort key columns for frequent filters while preserving the table model's key semantics.
  • Choose types that preserve the required data range and precision.

Creating replacement tables and copying data consumes compute and storage. Remove test tables and databases after validation when they are no longer needed.