Optimize table schema design for query performance-ApsaraDB for SelectDB(SelectDB)-阿里云帮助中心

Tuning checklist

Before creating or optimizing a table, verify the following items:

Is the data model (Duplicate / Unique / Aggregate) appropriate for the workload?
Is the bucket column evenly distributed, with no null or fixed-value skew?
Are frequently filtered columns defined as key columns?
Do data types follow the "fixed-length first, precision first" principle?
Does the partition strategy support effective partition pruning?

Case 1: Select an appropriate data model

ApsaraDB for SelectDB supports the following data models, each with different trade-offs between query performance and update capabilities:

Data model	Query performance	Update support	Typical scenarios
Duplicate	Highest.	Not supported.	High-performance queries on logs and detail data.
Unique (Merge-on-Write)	High.	Supported.	Primary key deduplication with high query performance requirements.
Unique (Merge-on-Read)	Moderate.	Supported.	Primary key deduplication with frequent writes.
Aggregate	Moderate.	Aggregation-based updates.	Pre-aggregated reports and metric summaries.

Query performance ranking: Duplicate > Merge-on-Write > Merge-on-Read ≈ Aggregate.

-- Duplicate model: best for detail data queries
CREATE TABLE user_events (
    event_time DATETIME NOT NULL,
    user_id BIGINT NOT NULL,
    event_type VARCHAR(32),
    event_data TEXT
)
DUPLICATE KEY(event_time, user_id)
DISTRIBUTED BY HASH(user_id) BUCKETS AUTO;

-- Aggregate model: best for pre-aggregated reports
CREATE TABLE site_traffic_summary (
    site_id INT,
    visit_date DATE,
    pv BIGINT SUM,
    uv BIGINT MAX
)
AGGREGATE KEY(site_id, visit_date)
DISTRIBUTED BY HASH(site_id) BUCKETS AUTO;

Recommendations

Use the Duplicate model when data updates are not required and query performance is the top priority.
Use the Unique model when data updates are required, such as CDC synchronization or upsert scenarios. The Unique model uses Merge-on-Write mode by default, which delivers read performance close to the Duplicate model. If write throughput is the priority and lower read performance is acceptable, switch to Merge-on-Read mode by setting enable_unique_key_merge_on_write = false.
Use the Aggregate model for reports with fixed dimension aggregations. Data is pre-aggregated during ingestion, reducing storage and query-time computation.

Case 2: Select an appropriate bucket column

The bucket column determines how data is distributed across nodes. A poor choice can lead to data skew, which severely impacts query parallelism and performance.

Anti-pattern: data skew caused by bucket column

When the bucket column contains many null or fixed values, all data concentrates in a few buckets while the rest remain empty. In the following example, column c2 is entirely null, causing all 10,000 rows to land in a single bucket out of 8:

-- Anti-pattern: bucket column c2 is all null, all data lands in 1 bucket
CREATE TABLE t1 (c1 INT, c2 INT)
DUPLICATE KEY(c1)
DISTRIBUTED BY HASH(c2) BUCKETS 8;

INSERT INTO t1 SELECT number, null FROM numbers('number'='10000');

-- Check data distribution: only 1 row returned, count is 10000
SELECT c2, count(*) cnt FROM t1 GROUP BY c2 ORDER BY cnt DESC LIMIT 10;

Optimization

Use a high-cardinality column (such as user ID or order ID) as the bucket column to ensure even data distribution across all buckets:

-- Optimized: use high-cardinality user_id as bucket column
CREATE TABLE t1 (c1 INT, user_id BIGINT NOT NULL)
DUPLICATE KEY(c1)
DISTRIBUTED BY HASH(user_id) BUCKETS AUTO;

Diagnose data skew

Run the following query on an existing table to check whether the bucket column values are evenly distributed:

-- Replace bucket_col with the bucket column name, table_name with the table name
SELECT bucket_col, count(*) cnt
FROM table_name
GROUP BY bucket_col
ORDER BY cnt DESC
LIMIT 10;

If the top values have counts significantly higher than others, data skew exists and you should choose a different bucket column.

Bucket column selection guidelines

Avoid columns with many null or fixed values.
Choose high-cardinality columns such as user ID or order ID.
Use BUCKETS AUTO to let the system automatically determine the bucket count. When setting buckets manually, aim for 100 MB to 1 GB of data per bucket.
When two tables are frequently joined, use the same bucket column and bucket count for both tables to enable Colocate Join and avoid data shuffle overhead.

Case 3: Optimize key column ordering

SelectDB sorts data by key columns in the defined order and builds a prefix index based on the first 36 bytes of the key columns. The ordering of key columns directly affects prefix index hit rates and query filtering efficiency.

Business scenario

Suppose most queries filter by user_id with equality or range conditions:

-- Common query patterns
SELECT * FROM user_events WHERE user_id = 12345;
SELECT * FROM user_events WHERE user_id IN (123, 456, 789);
SELECT * FROM user_events WHERE user_id = 12345 AND event_time >= '2024-01-01';

Optimization

Place the high-frequency equality filter column user_id first in the key definition, followed by the range filter column event_time:

-- Recommended: equality filter column first, then range filter column
CREATE TABLE user_events (
    user_id BIGINT NOT NULL,
    event_time DATETIME NOT NULL,
    event_type VARCHAR(32),
    payload TEXT
)
DUPLICATE KEY(user_id, event_time)
DISTRIBUTED BY HASH(user_id) BUCKETS AUTO;

Key column design guidelines

Frequently filtered columns first: Place columns that appear most often in WHERE clauses at the beginning of the key definition.
High-cardinality columns first: When query frequency is similar, place columns with higher cardinality first to improve index selectivity.
Limit the number of key columns: Too many key columns increase the sorting and compaction overhead during writes. Keep the number between 3 and 5.
Prefix index limitation: The prefix index uses only the first 36 bytes of key columns. Content beyond this limit does not participate in index filtering. Place fixed-length type columns before VARCHAR columns to maximize index coverage.

Case 4: Select appropriate data types

Precise data types reduce storage and computation overhead. Follow these principles when selecting types:

Principle	Recommended	Avoid
Fixed-length first	INT, BIGINT, DATE.	VARCHAR or STRING for values that can be represented by fixed-length types.
Precision first	DECIMAL(p,s) for monetary values.	DOUBLE for monetary values (precision loss).
Smallest fit	INT or SMALLINT when the value range allows.	BIGINT or LARGEINT when the value range does not require them.

Common optimization scenarios

Replace VARCHAR columns that store numeric values with BIGINT. Fixed-length types enable faster comparisons and consume fewer index bytes.
Replace string-formatted dates with DATE or DATETIME. The DATE type supports partition pruning and range query optimization.
Use DECIMAL(p,s) for monetary, price, and other fields that require exact arithmetic to avoid floating-point precision issues with DOUBLE.
Set an appropriate maximum length for VARCHAR columns. Avoid using STRING for short, fixed-format fields.

Partition strategy

Proper partitioning reduces the amount of data scanned during queries. SelectDB supports two partitioning approaches:

AUTO PARTITION (recommended): Automatically creates partitions on demand during data ingestion. Ideal when the partition key value range is unpredictable or the partition granularity needs to be flexible.
Dynamic partitioning: Best suited for time-series data with lifecycle management needs. Automatically creates new partitions and drops expired ones.

-- AUTO PARTITION example (recommended): automatically creates daily partitions on write
CREATE TABLE user_events (
    event_time DATETIME NOT NULL,
    user_id BIGINT NOT NULL,
    event_type VARCHAR(32),
    event_data TEXT
)
DUPLICATE KEY(event_time, user_id)
AUTO PARTITION BY RANGE (date_trunc(event_time, 'day')) ()
DISTRIBUTED BY HASH(user_id) BUCKETS AUTO;

For more information about partition pruning optimization, see the Optimize queries by using partition pruning topic.

FAQ

How do I change the bucket column after table creation?

The bucket column cannot be modified after table creation. You must create a new table with the correct bucket column, then migrate data using INSERT INTO new_table SELECT * FROM old_table.

Are more key columns always better?

No. Too many key columns increase sorting overhead during writes and compaction burden. The prefix index uses only the first 36 bytes of key columns, so content beyond this limit does not participate in index filtering. Keep the number of key columns between 3 and 5, prioritizing the most frequent query filter conditions.

When should I use the Unique or Aggregate model instead of Duplicate?

Use the Unique model when data updates are required, such as CDC synchronization or upsert operations. Use the Aggregate model when queries consistently aggregate on fixed dimensions (for example, daily regional sales totals) and detail-level data is not needed. The Aggregate model pre-aggregates data during ingestion, reducing storage and improving query performance. For all other scenarios, use the Duplicate model.

How do I determine whether a table has data skew?

Run the following SQL to check the value distribution of the bucket column. If the top values account for a disproportionately large share of total rows (for example, more than 50%), significant data skew exists:

-- Replace bucket_col with the bucket column name, table_name with the table name
SELECT bucket_col, count(*) cnt
FROM table_name
GROUP BY bucket_col
ORDER BY cnt DESC
LIMIT 10;