aio-starrocks-best-practices

StarRocks Best Practices

Official best practices from docs.starrocks.io/docs/best_practices/.

Core design philosophy: "Designing for efficiency does more than improve query speed — it decreases costs by reducing storage, CPU, and object storage API costs."

Related Skills

• /aio-starrocks-query-tuning — Query performance tuning, EXPLAIN plans, operator metrics, hints
• /starrocks — Query syntax, cluster connections, Grafana integration (external skill, not part of this plugin)
• /starrocks-expert — General table types, data loading, query optimization (external skill, not part of this plugin)

---

1. Partitioning

Partitioning enables coarse-grain data pruning via partition elimination AND metadata-only lifecycle operations (TTL, GDPR deletes, tenant isolation).

Partition Key Selection

1. Time-first — If 80%+ of queries filter by time, lead with date_trunc('day', dt)
2. Tenant isolation — Include tenant_id when managing data per-tenant
3. Retention alignment — Include columns you'll purge via DROP PARTITION
4. Composite keys — Creates #tenants x #days partitions — keep total below ~100K to avoid FE memory strain

Granularity Decision

Granularity	Best For	Advantage	Limitation
Daily	BI/reporting	365 partitions/year; simple TTL	Coarse for sub-day queries
Hourly	IoT/burst workloads	Hot-spot isolation	8,700 partitions/year
Weekly/Monthly	Historical archive	Minimal metadata	Less precise pruning

Sizing Rule

• Each partition <= 100GB
• <= 20K tablets per partition (across replicas)
• Total partitions < 100K (FE memory constraint)
• Tablets per BE < 200K

DDL Templates

Single-tenant clickstream:

CREATE TABLE click_stream (
  user_id BIGINT, event_time DATETIME, url STRING, ...)
DUPLICATE KEY(user_id, event_time)
PARTITION BY date_trunc('day', event_time)
DISTRIBUTED BY HASH(user_id) BUCKETS xxx;