# Starrocks Table Design

> Design StarRocks tables — choosing table types (Duplicate/Aggregate/Unique/Primary Key), partitioning with date_trunc() expressions, bucketing/distribution, and indexes (prefix, bloom filter, bitmap, ngram). Use when creating or modifying StarRocks table schemas, choosing a key model, setting partition granularity, sizing buckets, or selecting indexes.

- Skill: `tiansuyu/starrocks-table-design` (Agent Skill)
- Install (CLI): `npx skillmds@latest add tiansuyu/starrocks-table-design`
- Raw SKILL.md: https://api.skillmd.com/api/skills/tiansuyu/starrocks-table-design/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: AI & ML
- License: Apache-2.0
- Author: tiansuyu (https://skillmd.com/u/tiansuyu)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/tiansuyu/starrocks-table-design

---


# StarRocks Table Design

Design table schemas for StarRocks (a sub-second MPP OLAP database): table types, partitioning, bucketing/distribution, and indexing.

Reference: [StarRocks Partitioning Best Practices](https://docs.starrocks.io/docs/best_practices/partitioning/)

## When to Use

- Creating or modifying a StarRocks table schema
- Choosing a key model (Duplicate / Aggregate / Unique / Primary Key)
- Deciding partition strategy and granularity
- Sizing bucket counts and choosing the distribution key
- Selecting indexes for filter/lookup performance

Related skills: [starrocks-query-optimization] for verifying partition pruning and JOIN strategies; [starrocks-data-loading] for loading into the tables you design.

## Table Types and DDL

### Choosing the Right Table Type

#### 1. Duplicate Key Table
**Use when:** Raw event logging, append-only data, no deduplication needed

```sql
CREATE TABLE events_log (
    event_time DATETIME NOT NULL,
    user_id BIGINT,
    event_type VARCHAR(50),
    properties JSON
)
DUPLICATE KEY (event_time, user_id)
PARTITION BY date_trunc('day', event_time)
DISTRIBUTED BY HASH(user_id) BUCKETS 32;
```

**Pros:** Fastest ingestion, no overhead
**Cons:** No deduplication, largest storage

#### 2. Aggregate Key Table
**Use when:** Data can be pre-aggregated during ingestion

```sql
CREATE TABLE metrics_daily (
    metric_date DATE NOT NULL,
    metric_name VARCHAR(100) NOT NULL,
    total_count BIGINT SUM,
    total_amount DECIMAL(20, 2) SUM,
    max_value DECIMAL(20, 2) MAX,
    min_value DECIMAL(20, 2) MIN
)
AGGREGATE KEY (metric_date, metric_name)
PARTITION BY date_trunc('day', metric_date)
DISTRIBUTED BY HASH(metric_name) BUCKETS 16;
```

**Pros:** Reduced storage, faster queries
**Cons:** Limited aggregation functions (SUM, MAX, MIN, REPLACE)

#### 3. Unique Key Table
**Use when:** Need to enforce uniqueness, support updates

```sql
CREATE TABLE user_profiles (
    user_id BIGINT NOT NULL,
    username VARCHAR(100),
    email VARCHAR(200),
    status VARCHAR(20),
    updated_at DATETIME
)
UNIQUE KEY (user_id)
DISTRIBUTED BY HASH(user_id) BUCKETS 16
PROPERTIES (
    "enable_persistent_index" = "true"
);
```

**Pros:** Supports upserts, deduplication
**Cons:** Higher write latency than DUPLICATE

#### 4. Primary Key Table
**Use when:** Frequent updates, real-time analytics with mutable data

```sql
CREATE TABLE orders_realtime (
    order_id BIGINT NOT NULL,
    order_time DATETIME NOT NULL,
    customer_id BIGINT,
    status VARCHAR(50),
    amount DECIMAL(18, 2),
    updated_at DATETIME
)
PRIMARY KEY (order_id)
PARTITION BY date_trunc('day', order_time)
DISTRIBUTED BY HASH(order_id) BUCKETS 32
PROPERTIES (
    "enable_persistent_index" = "true",
    "replicated_storage" = "true"
);
```

**Pros:** Best update performance, point query optimization
**Cons:** More memory usage for indexes

### Column Type Selection

**Best practices:**
- Use DATETIME for time columns (not VARCHAR)
- Use DECIMAL for monetary values (not DOUBLE)
- Use BIGINT for IDs (not VARCHAR when possible)
- Use JSON for semi-structured data
- Use ARRAY/MAP/STRUCT for nested data

**Example:**
```sql
CREATE TABLE transactions (
    -- Good: DATETIME for timestamps
    transaction_time DATETIME NOT NULL,

    -- Good: BIGINT for IDs
    transaction_id BIGINT NOT NULL,
    user_id BIGINT,

    -- Good: DECIMAL for money
    amount DECIMAL(18, 2),

    -- Good: VARCHAR with appropriate length
    currency VARCHAR(3),

    -- Good: JSON for flexible data
    metadata JSON,

    -- Good: ARRAY for multi-value
    tags ARRAY<VARCHAR(50)>
)
DUPLICATE KEY (transaction_time, transaction_id)
PARTITION BY date_trunc('day', transaction_time)
DISTRIBUTED BY HASH(transaction_id) BUCKETS 32;
```

## Partitioning Strategies

**Modern partitioning uses expression-based syntax with automatic partition creation.**

### Partitioning vs Bucketing - Different Jobs

| Aspect | Partitioning | Bucketing |
|--------|-------------|-----------|
| **Primary goal** | Coarse-grain data pruning and lifecycle control (TTL, archiving) | Fine-grain parallelism and data locality |
| **Planner visibility** | FE can skip entire partitions via predicates | Only equality predicates support bucket pruning |
| **Lifecycle ops** | DROP PARTITION is metadata-only (ideal for GDPR, TTL) | Buckets can't be dropped individually |
| **Typical count** | 10²-10⁴ per table (days, weeks, tenants) | 10-120 per partition |
| **Red flags** | >100k partitions causes FE memory issues | >200k tablets per BE; tablets >10GB slow compaction |

### When Should You Partition?

| Table Type | Partition? | Typical Key |
|------------|-----------|-------------|
| Fact/event stream | **Yes** | `date_trunc('day', event_time)` |
| Huge dimension (billions of rows) | Sometimes | Time or business key change date |
| Small dimension/lookup (<100M rows) | No | Rely on hash distribution only |

**Key question:** If 80%+ of queries include a time filter, partition on time.

### Expression-Based Partitioning (Recommended)

**Daily partitions (most common):**
```sql
CREATE TABLE click_stream (
    user_id BIGINT,
    event_time DATETIME,
    url STRING,
    page_views INT
)
DUPLICATE KEY (user_id, event_time)
PARTITION BY date_trunc('day', event_time)
DISTRIBUTED BY HASH(user_id) BUCKETS 32;
```

**Hourly partitions (high-frequency data):**
```sql
CREATE TABLE iot_sensors (
    sensor_id BIGINT,
    reading_time DATETIME,
    temperature DOUBLE
)
DUPLICATE KEY (sensor_id, reading_time)
PARTITION BY date_trunc('hour', reading_time)
DISTRIBUTED BY HASH(sensor_id) BUCKETS 64;
```

**Monthly partitions (long-term storage):**
```sql
CREATE TABLE historical_archive (
    report_month DATETIME,
    account_id BIGINT,
    metrics JSON
)
DUPLICATE KEY (report_month, account_id)
PARTITION BY date_trunc('month', report_month)
DISTRIBUTED BY HASH(account_id) BUCKETS 16;
```

### Composite Partitioning (Multi-Tenant)

**Pattern A: Time-first (recommended for most SaaS workloads)**

Prunes on time, keeps tenant rows co-located:
```sql
CREATE TABLE metrics (
    tenant_id INT,
    dt DATETIME,
    metric_name STRING,
    value DOUBLE
)
PRIMARY KEY (tenant_id, dt, metric_name)
PARTITION BY date_trunc('day', dt)
DISTRIBUTED BY HASH(tenant_id) BUCKETS 32;
```

**Pattern B: Tenant-first (for large tenants needing isolation)**

Enables tenant-specific operations but creates more partitions:
```sql
CREATE TABLE activity (
    tenant_id INT,
    dt DATETIME,
    id BIGINT,
    event_data JSON
)
DUPLICATE KEY (dt, id)
PARTITION BY tenant_id, date_trunc('month', dt)
DISTRIBUTED BY HASH(id) BUCKETS 32;

-- Warning: #tenants × #months partitions. Keep under 100k total.
```

### Choosing the Partition Key

**Priority order:**

1. **Time-first default** - If 80%+ of queries include a time filter: `PARTITION BY date_trunc('day', dt)`
2. **Tenant isolation** - Add tenant for tenant-level data management: `PARTITION BY tenant_id, date_trunc('day', dt)`
3. **Retention alignment** - Partition by the column you'll purge on (enables metadata-only DROP)
4. **Composite keys** - Creates `#tenants × #days` partitions - keep total under 100k

### Choosing Partition Granularity

| Granularity | Use When | Pros | Cons | Annual Count |
|-------------|----------|------|------|--------------|
| `date_trunc('hour', dt)` | >2 tablets/day needed, IoT bursts, hot-spot isolation | Fine-grained pruning, 24 partitions/day | 8,760 partitions/year | High |
| `date_trunc('day', dt)` | **Default for most BI/reporting (80% of use cases)** | Balanced: 365 partitions/year, simple TTL | Less precise for "last 3h" queries | Medium |
| `date_trunc('week', dt)` | Weekly aggregations, medium-term storage | Lower partition count | Coarser pruning | Low |
| `date_trunc('month', dt)` | Historical archive, long retention (>2 years) | Minimal metadata overhead | Very coarse pruning | Very Low |

**Sizing guidelines:**
- Each partition: ≤100 GB data
- Each partition: ≤20k tablets (across all replicas)
- Total partitions per table: <100k (critical for FE memory)
- Watch composite partitions: 1000 tenants × 365 days = 365k partitions ⚠️

**Mixed granularity (StarRocks 3.4+):**
You can merge historical partitions into coarser granularity for better efficiency.

### Partition Management

**With expression-based partitioning, partitions are created automatically on INSERT.**

```sql
-- View existing partitions
SHOW PARTITIONS FROM table_name;

-- Example output shows auto-generated partition names:
-- p20240101        (for date_trunc('day', ...)  — date only, no time component)
-- p2024010112      (for date_trunc('hour', ...))
-- p10001_20240101  (for composite: tenant_id, date_trunc('day', ...))

-- Drop old partitions by name
ALTER TABLE table_name
DROP PARTITION p20231201;

-- Drop multiple partitions
ALTER TABLE table_name
DROP PARTITION p20231201, p20231202;

-- Truncate specific partition
TRUNCATE TABLE table_name PARTITION (p20240101);
```

**Partition lifecycle management (TTL):**
```sql
-- Keep only the most recent N partitions (count-based)
ALTER TABLE events
SET ("partition_live_number" = "90");  -- Keep only last 90 partitions

-- Or use an expression-based retention condition (StarRocks 3.5+)
ALTER TABLE events
SET ("partition_retention_condition" = "dt >= CURRENT_DATE() - INTERVAL 3 MONTH");
```

### Partition Pruning Verification

```sql
-- ✅ Good: Filter on partitioned column directly
EXPLAIN VERBOSE
SELECT * FROM events
WHERE event_time >= '2024-01-01' AND event_time < '2024-01-07';
-- Look for: partitions=7/365 (only 7 partitions scanned)

-- ❌ Bad: Function on partitioned column breaks pruning
EXPLAIN VERBOSE
SELECT * FROM events
WHERE DATE(event_time) = '2024-01-01';
-- Results in: partitions=365/365 (full scan!)

-- ✅ Good: Use date_trunc in query matching partition definition
EXPLAIN VERBOSE
SELECT * FROM events
WHERE date_trunc('day', event_time) = '2024-01-01';
-- Results in: partitions=1/365
```

**Best practice:** Always filter using the same expression or direct comparison on the partitioned column.

## Bucketing and Distribution

### Determining Bucket Count

**Formula:**
```
bucket_count = (data_size_per_partition_GB / 2GB) × number_of_BE_nodes
Minimum: 1
Maximum: typically 128-256
```

**Examples:**

| Partition Size | BE Nodes | Recommended Buckets | Reasoning |
|----------------|----------|---------------------|-----------|
| 1 GB | 3 | 8 | Small dataset, min distribution |
| 10 GB | 3 | 16 | ~2GB per bucket |
| 100 GB | 10 | 64 | ~1.5GB per bucket |
| 1 TB | 20 | 128 | ~8GB per bucket |

### Distribution Strategies

#### 1. Hash Distribution (Most Common)

```sql
-- Single column hash
DISTRIBUTED BY HASH(user_id) BUCKETS 32;

-- Multi-column hash (for better distribution)
DISTRIBUTED BY HASH(user_id, event_date) BUCKETS 32;
```

**Choose hash column based on:**
- High cardinality (many unique values)
- Frequently used in WHERE/JOIN conditions
- Even distribution (avoid skew)

#### 2. Random Distribution

```sql
DISTRIBUTED BY RANDOM BUCKETS 16;
```

**Use when:**
- No good hash key available
- Data is already evenly distributed
- Table is small

### Colocate Groups (Advanced)

**Use for:** Frequently joined large tables

```sql
-- Create first table in group
CREATE TABLE orders (
    order_id BIGINT,
    user_id BIGINT,
    amount DECIMAL(18, 2)
)
DUPLICATE KEY (order_id)
DISTRIBUTED BY HASH(user_id) BUCKETS 32
PROPERTIES (
    "colocate_with" = "user_order_group"
);

-- Create second table in same group
CREATE TABLE users (
    user_id BIGINT,
    username VARCHAR(100)
)
UNIQUE KEY (user_id)
DISTRIBUTED BY HASH(user_id) BUCKETS 32
PROPERTIES (
    "colocate_with" = "user_order_group"
);

-- Now JOINs on user_id are local (no network shuffle)
SELECT o.*, u.username
FROM orders o
JOIN users u ON o.user_id = u.user_id;
```

**Requirements:**
- Same bucketing column
- Same bucket count
- Same replication strategy

## Indexes and Optimization

### 1. Prefix Index (Automatic)

StarRocks automatically creates a prefix index on the first 36 bytes of DUPLICATE/AGGREGATE/UNIQUE keys.

```sql
-- This table has automatic prefix index on (event_time, user_id)
CREATE TABLE events (
    event_time DATETIME NOT NULL,  -- 8 bytes
    user_id BIGINT NOT NULL,       -- 8 bytes
    event_type VARCHAR(50),        -- 50+ bytes (only partially indexed)
    data JSON
)
DUPLICATE KEY (event_time, user_id, event_type)
PARTITION BY date_trunc('day', event_time)
DISTRIBUTED BY HASH(user_id) BUCKETS 32;
```

**Best practice:** Put frequently filtered columns first in key definition

### 2. Bloom Filter Index

```sql
CREATE TABLE users (
    user_id BIGINT,
    email VARCHAR(200),
    phone VARCHAR(20),
    username VARCHAR(100)
)
UNIQUE KEY (user_id)
DISTRIBUTED BY HASH(user_id) BUCKETS 16
PROPERTIES (
    "bloom_filter_columns" = "email,phone"
);

-- Or add to existing table
ALTER TABLE users
SET ("bloom_filter_columns" = "email,phone");
```

**Use for:**
- High-cardinality VARCHAR columns
- Equality filters (`WHERE email = '...'`) and `IN` lists

**Don't use for:**
- Low-cardinality columns
- Range queries
- Numeric columns with range filters

### 3. Bitmap Index

```sql
-- Add to an existing table
CREATE INDEX idx_status ON events (status) USING BITMAP;
-- (equivalently: ALTER TABLE events ADD INDEX idx_status (status) USING BITMAP;)

-- Or inline in CREATE TABLE (INDEX is a separate entry in the column list)
CREATE TABLE events (
    event_time DATETIME,
    user_id BIGINT,
    status VARCHAR(20),
    category VARCHAR(50),
    INDEX idx_status (status) USING BITMAP
)
DUPLICATE KEY (event_time, user_id)
PARTITION BY date_trunc('day', event_time)
DISTRIBUTED BY HASH(user_id) BUCKETS 32;
```

**Use for:**
- Columns where the filter eliminates the vast majority of rows
- Frequent WHERE/point-query filters
- Common in OLAP dimensions (status, category, region)

### 4. NGram Bloom Filter (Full-text Search)

```sql
ALTER TABLE articles
ADD INDEX idx_content (content) USING NGRAMBF ("gram_num" = "4", "bloom_filter_fpp" = "0.05");
```

**Use for:**
- LIKE '%keyword%' searches
- Text search scenarios

## Table Design Checklist

Before creating production tables:

- [ ] Choose correct table type (Duplicate/Aggregate/Unique/Primary)
- [ ] Use DATETIME for time columns (not VARCHAR)
- [ ] Partition with `date_trunc()` expression matching query patterns
- [ ] Partition granularity matches use case (hour/day/month)
- [ ] Composite partitions kept under 100k total
- [ ] Bucket count follows formula: (partition_size_GB / 2GB) × BE_count
- [ ] Hash key has high cardinality and even distribution
- [ ] Frequently filtered columns in DUPLICATE KEY prefix (first 36 bytes)
- [ ] Bloom filter on high-cardinality string columns (email, phone)
- [ ] Bitmap index on low-cardinality filter columns (status, region)
- [ ] Colocate frequently joined tables in same group
- [ ] Enable persistent index for Primary Key tables
- [ ] Set replication_num = 3 for production
- [ ] Configure partition TTL for automatic cleanup

## Common Anti-Patterns to Avoid

❌ **Don't** use VARCHAR for time columns that will be partitioned
❌ **Don't** apply functions to partition columns in WHERE (breaks pruning)
❌ **Don't** create too many buckets (causes small files)
❌ **Don't** create too few buckets (causes data skew)
❌ **Don't** use UNIQUE KEY if you only need deduplication (use DUPLICATE + GROUP BY)
❌ **Don't** create composite partitions that exceed 100k total partitions