@Rocky I have questions to ask regarding a potential migration of very high throughput bid stream data (Open RTB) from shared nothing to shared data architecture. Goal We're migrating a high-volume ingest workload from shared-nothing to shared-data. Looking for advice on: 1. Shared-data specific configurations I should pay attention to (storage volume properties, table properties, FE/CN configs, DataCache). 2. Data-loading pipeline changes — anything about my client-side ingest pattern that should change for shared-data. Current Cluster (shared-nothing today) - 12 BE nodes - Local storage per BE: 4 × 2 TB EBS volumes = 8 TB per node, 96 TB raw total across the cluster - merge_commit enabled, merge_commit_interval_ms = 10000 (10s window) - Per-table compaction policy: every tablet compacts every ~10 seconds - After migration, EBS volumes will be repurposed as the local DataCache layer in front of S3 Ingest Workload Profile 1. gRPC stream — bid request events arrive over a streaming gRPC connection for record insertion. 2. Kafka - Consumers process events as partial updates Per-stream-load batch size: ~500 MB body, flushed approximately every 30 seconds per worker Row format: JSON Table DDL (current shared-nothing form) CREATE TABLE requests.requests ( received DATETIME NOT NULL, auction_id STRING NOT NULL, imp_key STRING NOT NULL -- + hundreds of columns added dynamically via ALTER TABLE ADD COLUMN ) PRIMARY KEY (received, auction_id, imp_key) PARTITION BY date_trunc('hour', received) DISTRIBUTED BY HASH (auction_id) BUCKETS 128 ORDER BY (received, auction_id) PROPERTIES ( "enable_persistent_index" = "true", "partition_live_number" = "24", "replication_num" = "1" ); Pipeline Architecture in the Go Consumer The Go consumer runs 2 classes of stream-load pipelines against the same StarRocks cluster from a single process: Class A — gRPC-driven full-row inserts into requests - 4 concurrent worker goroutines - Each owns a buffer that flushes on: 500 MB body OR 30s elapsed OR row threshold - Stream load is a full-row insert (upsert by default since PK table) - Per-worker idle HTTP conn pool size: 8 Class B — Kafka-driven partial-update pipelines (9 of them — non-negotiable) We must maintain 9 separate partial-update pipelines and cannot consolidate them. Why: When a partial update stream load lists a column in the header columns parameter, every record in that payload must contain that column (or have a value safely nullifiable). If a column is listed in the header but a record omits it, StarRocks nullifies that column for that record — overwriting existing values with NULL. Kafka events arrive in distinct shapes (e.g. an interaction event has has_click/has_page_view/has_engagement; an outcome event has has_won/win_price/bid_loss_reason/...). They cannot be batched into one stream load. They go into 9 separate pipelines, each pinned to one schema with one fixed column list: Client-side PK merge map: before sending, the consumer holds rows for the delay window (60–90s) in a DelayedWorkMap keyed by PK. Concurrent updates to the same PK from the same pipeline collapse into one row. This is purely client-side, separate from server-side merge_commit. Partial-update buffer thresholds (per pipeline): - Row threshold: 200,000 - Byte threshold: 256 MB - Flush interval: 30s for outcome, 60s for others Late-event guard: any partial update with received older than 23 hours is dropped client-side (partition live number is 24). All 9 partial-update pipelines share the same StreamLoadClient (and thus the same HTTP connection pool, currently sized for only the main insert workers — 8 idle conns per host). Specific Questions for Rocky A. Shared-data table DDL 1. For PK tables on shared-data, should I use "persistent_index_type" = "LOCAL" or "CLOUD_NATIVE"? Workload is heavy upsert + frequent partial updates on hourly-partitioned PK tables with 24h retention. CNs are not autoscaled, and each has 8 TB of local NVMe/EBS. 2. With 8 TB local disk per CN to be split between persistent index + DataCache, how should I think about sizing? Estimate of persistent index size per billion PK rows? 3. What "datacache.partition_duration" should I set so the entire 24h live window stays hot? Should I set "datacache.enable" = "true" explicitly or trust the default? 4. With 12 CNs and ~hundreds of GB/hour ingest, is 128 buckets per hourly partition appropriate? 128 × 24 = 3072 live tablets for requests alone — does shared-data care about tablet count more than shared-nothing did? 5. Drop replication_num = 1 from PROPERTIES entirely, or keep for clarity? Any other property that should be removed/added? 6. Anything table-property-wise I'd otherwise miss (compaction_strategy, write quorum, base_compaction_forbidden_time_ranges, compression)? B. Storage volume 7. What aws.s3.num_partitioned_prefix would you recommend given ~15 concurrent producers in this one app plus other apps in the same cluster? 8. Any storage-volume-level settings I should tune for write-heavy PK workloads (multipart part size, region/endpoint settings)? C. FE/CN configs 9. With per-tablet compaction at 10s and 9 partial-update pipelines on the same table, what lake_compaction_* settings matter most? I want to avoid compaction-score climb. 10. lake_autovacuum_grace_period_minutes — given partition_live_number=24 on hourly partitions, what's safe? My longest in-flight transaction is bounded by my 30–75s FlushTimeout. 11. lake_metadata_cache_limit — recommended size for 12 CNs and ~3300 live tablets? 12. lake_publish_version_max_threads — should I bump from default given 15 concurrent stream-load producers? 13. DataCache local-disk config: with 4 × 2 TB EBS per node, should I configure DataCache to use all four mounts, and what's the right starlet_star_cache_disk_size_percent (or equivalent) given persistent index also lives there? D. Data-loading client behavior 14. merge_commit at 10s is on. My client-side flush intervals are 30s for inserts and 60–90s for partial updates. Am I leaving merge_commit benefit on the table? Should I lower client flush intervals to ~1–5s and let the server batch? 15. My 500 MB batch size per stream load — is that good for shared-data, or should I go smaller (e.g. 64–128 MB) and rely on merge_commit to consolidate across producers? 16. Are there headers I should be setting on the partial-update stream loads to play nice with shared-data + merge_commit (e.g. partial_update_mode, strict_mode)? 17. PK table partial updates in shared-data: any specific failure modes I should retry on the client (transient publish-version failures, "meta file not found", 503 SlowDown)? 18. The client-side DelayedWorkMap already merges by PK before sending. Does that conflict with server-side merge_commit, or are they complementary? E. Connection / HTTP behavior 19. I share one HTTP client (8 idle conns per host) across all 15 producers. With 12 CNs and FE-redirect rotation, what idle-pool size is realistic? 20. With merge_commit, the response is held until the merged transaction commits → ≥10s tail latency. Any flag for an early-ack / async-commit mode, or is the only option pre-merge fire-and-forget on the client? F. Risks I want flagged 21. Anything subtle about schema change (ALTER TABLE ADD COLUMN) in shared-data on a PK table being concurrently written by 15 producers? We add columns via DDL on startup before resuming ingest, but the new column briefly exists in FE meta before all CNs have it. 22. With partition_live_number = 24 on hourly partitions, a partition drops every hour. With vacuum lag, can a partial update land in a dying partition and disappear? What's the safe slack between partition_live_number and my client's 23h late-event drop? 23. Anything about partial-update + persistent-index + shared-data that has a known sharp edge in the latest versions? 24. We will run dual-write (parallel to the legacy shared-nothing cluster) for a transition period — any shared-data-specific concerns about idempotency, label collisions, or PK convergence between the two clusters? --- Please respond with concrete property values where possible (e.g. "datacache.partition_duration" = "25 hours"), not just principles

Migrating a high-throughput RTB bid stream to a shared-data (compute-storage separated) architecture in StarRocks requires specific tuning to handle the high I/O and transaction commit frequency typical of the primary key (PK) model. A. Shared-data Table DDL 1. Persistent Index Type: Use

"persistent_index_type" = "CLOUD_NATIVE"

(available since v3.3.2). For your workload, this is superior to

LOCAL

because it stores the full index on S3 and uses your local 8 TB EBS only as a cache. This avoids index loss/rebuilds during CN scaling or failures. 2. Sizing: Estimate 20–25 bytes per row for the persistent index (PK columns + metadata overhead). For 1 billion rows, this is ~25 GB. With 8 TB local disk per CN, you have ample space to keep the entire 24h live index window in the local cache. 3. DataCache Duration: Set

"datacache.partition_duration" = "25 HOUR"

. This ensures the 24h live window stays hot. You do not need to set

datacache.enable

explicitly as it defaults to

true

. 4. Bucket Count: 128 buckets is appropriate for 12 CNs (~10 tablets per node). Shared-data handles high tablet counts well, but since you have hourly partitions, avoid going much higher (e.g., 256+) to prevent "small file" overhead on S3. 5. Properties: Remove `replication_num = 1`; it is ignored in shared-data as S3 provides the redundancy. 6. Compression: Use

LZ4

(default) for a balance of speed and ratio. For RTB data with many columns, consider

ZSTD

if storage costs on S3 become a concern, though it slightly increases CPU during ingest. B. Storage Volume 7. S3 Prefixing: Set

"aws.s3.num_partitioned_prefix" = "512"

. With 15+ concurrent producers and high throughput, the default 256 might hit S3's 3,500 PUT/s limit per prefix. Note: This property is immutable once the volume is created. 8. S3 Tuning: Ensure the S3 endpoint is regional. No specific multipart tuning is required on the volume level; StarRocks handles this internally. C. FE/CN Configs 9. Compaction Control: * Set

compact_threads

to ~25% of your CN's CPU cores. * Set

lake_compaction_score_selector_min_score = 10.0

(default) and monitor

AvgCS

in

information_schema.partitions_meta

. 10. AutoVacuum:

lake_autovacuum_grace_period_minutes = 30

(default) is safe since your longest transaction (75s) is much shorter. 11. Metadata Cache: Set

lake_metadata_cache_limit

to at least

2 GB

per CN to keep all ~3300 tablet metadata entries in memory. 12. Publish Threads: Bump

lake_publish_version_max_threads

to

64

to prevent bottlenecks with 15 concurrent producers. Also, set the FE config

lake_enable_batch_publish_version = true

(v3.2.3+). 13. Local Disk Layout: *

storage_root_path = /data1;/data2;/data3;/data4

(list all EBS mounts). *

starlet_star_cache_disk_size_percent = 90

. Since

CLOUD_NATIVE

index and DataCache share this space, 90% is safe; the index will compete with data blocks for cache space. D. Data-loading Client Behavior 14. Flush Interval: Do not lower your client flush intervals (30s). Let the client-side

DelayedWorkMap

do its job.

merge_commit

is most effective when multiple different clients send small batches. Since your batches are already 500 MB (large),

merge_commit

acts primarily as a safety net. 15. Batch Size: 500 MB is excellent for shared-data. Large batches reduce the frequency of metadata updates on S3 and minimize small files. Keep it as-is. 16. Headers: Use

partial_update: true

and

partial_update_mode: column

(v3.3.1+). For your Class B pipelines, ensuring

column

mode reduces I/O amplification compared to the default

row

mode. 17. Retries: Retry on

503 SlowDown

(S3 rate limiting) and

408 Timeout

. "Meta file not found" usually implies a race with Vacuum; increasing

lake_autovacuum_grace_period_minutes

resolves this.

@Rocky Doesnt merge_commit_async mean FE will return once data written to NVMe? No need to wait for S3 flush

Yes, your understanding is largely correct, but there are a few nuances regarding what "receiving the data" means and how it affects visibility. 1. Does it wait for NVMe or S3? In Asynchronous Mode (

merge_commit_async = true

, available since v3.4.0): * The Return Point: The server (specifically the Coordinator CN) returns a "Success" response to your client immediately after the data has been received and buffered in the system's memory (and potentially spilled to local NVMe if the batch is large or memory is tight). * No S3 Wait: It does not wait for the merge window to close, nor does it wait for the data to be flushed to S3 or for the transaction to be committed by the FE. * Latency: This eliminates the ≥10s tail latency caused by the

merge_commit_interval_ms

. Your client will see sub-second response times regardless of your 10s merge window. 2. The Trade-offs of Asynchronous Mode While this solves your latency issue, it introduces specific risks for a high-throughput RTB workload: Feature | Synchronous (Default) | Asynchronous (

merge_commit_async=true

) Visibility | Data is searchable as soon as the client receives the response. | Data visibility is delayed by the merge window + commit time (e.g., 10–15s). Durability | "Success" means data is safe on S3. | "Success" only means the CN received it. If the CN crashes before the S3 flush, data is lost. Ordering | Guarantees sequential requests from one client are processed in order. | Does not guarantee order. Two back-to-back updates to the same PK could commit in reverse order. Error Handling | Client knows immediately if the load failed (e.g., schema mismatch). | Client gets a success for the "receive," but must poll

_get_load_state

or check FE logs to find later commit failures. 3. Recommendation for your Go Consumer Given your Class A (Inserts) and Class B (Partial Updates) setup: * For Class A (Full Inserts): Asynchronous mode is likely safe and highly beneficial for your gRPC workers, as it unblocks your goroutines immediately. Since these are full-row inserts, out-of-order issues are less critical than in partial updates. * For Class B (Partial Updates): Be cautious. If your Kafka consumer relies on StarRocks to correctly order updates to the same

auction_id

, the async mode's lack of ordering guarantees could lead to "stale data wins" if two updates for the same row land in the same merge window but are processed out of order. * The "NVMe Spill" Detail: In StarRocks v3.5+, a new "Load Spill" mechanism is introduced for large ingests. In this version, the CN will prioritize writing the incoming stream to local NVMe/EBS to avoid memory pressure before doing the final centralized merge to S3. This makes the "asynchronous success" slightly more durable (stored on local disk), but it is still not a substitute for the S3 commit. Summary: Use

merge_commit_async = true

to fix your latency, but ensure your client-side logic can handle the "at-least-once" nature of Kafka by using Conditional Updates (e.g.,

SET "merge_condition" = "timestamp_column"

) to prevent out-of-order writes from corrupting your PK rows. References • loading/StreamLoad.md • administration/management/monitoring/metrics.md • loading/Flink-connector-starrocks.md

@Rocky If I currently needed my 32vCPU per node and 12 BE node (r6in.8xlarge) to keep up with compaction in my shared nothing cluster, do I need an equivalent amount of compute on a shared data? I was thinking if it was possible to scale down compute to 6-8 i4i.4xlarge instead.

Scaling down from 384 vCPUs (12 ×

r6in.8xlarge

) to 96–128 vCPUs (6–8 ×

i4i.4xlarge

) is likely too aggressive for your specific high-throughput PK workload, even with the architectural benefits of shared-data. While shared-data is more "efficient" at a cluster level, the *per-node CPU demand for compaction does not decrease*; in some cases, it can slightly increase due to the overhead of interacting with object storage metadata. 1. Compaction: Shared-Nothing vs. Shared-Data In a standard

replication_num = 3

shared-nothing cluster, switching to shared-data yields a 3x reduction in compaction work because the data is only compacted once. However, you are already running

replication_num = 1

on EBS. * No "Replication Tax" Win: Since you are already compacting only once per rowset, moving to shared-data provides no reduction in the total amount of data that needs to be merged. * Merge Overhead: The CPU cycles required to sort, merge, and compress rows remain identical. PK tables are particularly CPU-heavy during compaction because they must perform index lookups/updates. 2. Why i4i.4xlarge might struggle here * vCPU Density: You are cutting your total compute by ~70%. For a workload with "hundreds of GB/hour" and 15 concurrent producers, your CNs will likely become CPU-bound. If compaction falls behind, your

AvgCS

(Compaction Score) will climb, eventually triggering

lake_ingest_slowdown_threshold

(default 100), which will throttle your gRPC/Kafka ingest. * I/O and DataCache: While

i4i

has superior local NVMe, your current

r6in

setup uses 4 × 2 TB EBS volumes per node. 4 EBS volumes usually provide significant IOPS/Throughput. Moving to fewer nodes means each node must handle a higher "density" of ingest and compaction I/O. * Memory Pressure: Each

i4i.4xlarge

has 128 GB RAM. With 128 buckets and high-throughput ingest, the memory used for

memtables

and

persistent_index

caches could become a bottleneck if you consolidate the workload onto fewer nodes. 3. Sizing Recommendation To maintain your current throughput and ensure compaction keeps up, you should aim for compute parity (roughly the same total vCPUs). * Conservative Path: 12 ×

i4i.4xlarge

(192 vCPUs). This is still a 50% reduction in cores but leverages the faster local NVMe for DataCache and Index. Monitor closely. * Safe Path: 10–12 ×

i4i.8xlarge

(or stay with 8xlarge sizes). This keeps your vCPU count closer to the current 384. 4. Key Performance Factor: StarRocks 3.5+ If you migrate, ensure you are on StarRocks v3.5 or higher. This version introduced the "Load Spill" and "Centralized Merge" optimizations specifically for shared-data. These allow the CN to write incoming data to the local NVMe first and perform a local merge before pushing to S3. This drastically reduces S3 write amplification and "small file" issues, which is the primary reason shared-data clusters traditionally struggled with high-frequency ingest. Summary Verdict: Do not scale down the vCPU count by 70%. Start with 12 nodes of the 4xlarge size (192 vCPUs total) and test your compaction scores before going lower. If your

AvgCS

stays under 30, only then consider scaling in further.

@Rocky DOesn't max_cumulative_compaction_num_singleton_deltas only apply to non primary key table? For PK table is max_update

You are correct. In StarRocks, Primary Key (PK) tables use a completely different compaction engine than Duplicate/Aggregate/Unique tables, and the configuration parameters are distinct. 1. Shared-Nothing (Current) * Non-PK Tables: Use

max_cumulative_compaction_num_singleton_deltas

. This controls the "Cumulative" phase of the traditional LSM-tree compaction. * PK Tables: Use

max_update_compaction_num_singleton_deltas

. This specifically limits the number of rowsets merged in a single "Update Compaction" task. 2. Shared-Data (Migration Target) For your migration, the parameter name changes again to reflect the "Lake" architecture: * PK Tables: Use

lake_pk_compaction_max_input_rowsets

. * Default: 500 (since v3.3.1/v3.2.9). * Logic: Since PK tables in shared-data typically use Size-tiered Compaction (

enable_pk_size_tiered_compaction_strategy

=

true

by default), this parameter prevents a single compaction job from trying to merge too many small files at once, which would spike CPU and memory. * Non-PK Tables: Still use

max_cumulative_compaction_num_singleton_deltas

in shared-data to control how many segments are merged into one. Summary for your Scaling Decision If you are moving to shared-data, you should pay attention to

lake_pk_compaction_max_input_rowsets

on the CN. Setting this to a lower value (e.g., 100) is a common "high-throughput" tuning strategy. While it may increase the total number of compaction tasks, it makes each individual task finish much faster, which: 1. Reduces Memory Pressure: Smaller merges mean smaller memory buffers. 2. Prevents Ingest Blocks: It allows the system to commit compacted versions to S3 more frequently, keeping the "Compaction Score" from climbing to the point where it throttles your 15 producers. Bottom Line: You are right to ignore the

cumulative

parameters for your PK bid-stream; focus on the

update

(shared-nothing) or

lake_pk

(shared-data) equivalents). -data) settings. References • administration/management/BE_configuration.md • best_practices/primarykey_table.md • knowledge/admin_and_operation/shared_data_compaction.md • administration/management/compaction.md References • best_practices/primarykey_table.md • administration/management/BE_configuration.md • administration/management/compaction.md • knowledge/admin_and_operation/shared_data_compaction.md

@Rocky Give me the corrected storage volume and table DDL

For your transition to a shared-data (Cloud Native) architecture, here is the corrected SQL for your Storage Volume and the Primary Key table DDL. 1. Storage Volume DDL (S3) This creates the link between StarRocks and your S3 bucket. Replace the placeholders with your actual AWS credentials and bucket path.

sql
CREATE STORAGE VOLUME s3_bid_storage
TYPE = S3
LOCATIONS = ("<s3://your-bucket-name/starrocks_data/>")
PROPERTIES
(
    "aws.s3.region" = "us-east-1",
    "aws.s3.use_aws_sdk_default_behavior" = "false",
    "aws.s3.use_instance_profile" = "true", -- Use "true" if running on EC2 with IAM roles
    "enabled" = "true"
);

-- Set this as the default volume so you don't have to specify it in every table
SET s3_bid_storage AS DEFAULT STORAGE VOLUME;

2. Primary Key Table DDL This DDL is optimized for your high-throughput RTB workload. Note the

storage_volume

and

datacache

properties specific to shared-data.

sql
CREATE TABLE IF NOT EXISTS bid_stream (
    auction_id     BIGINT NOT NULL,
    timestamp      DATETIME NOT NULL,
    device_id      VARCHAR(64),
    bid_price      DECIMAL(10, 5),
    campaign_id    INT,
    creative_id    INT,
    -- Other columns...
)
PRIMARY KEY (auction_id)
DISTRIBUTED BY HASH(auction_id) BUCKETS 128 -- Aligned with your 12-node cluster
PROPERTIES (
    "storage_volume" = "s3_bid_storage",
    "datacache.enable" = "true",               -- Keep hot data on local NVMe
    "datacache.partition_duration" = "1 DAY",  -- Auto-evict data older than 24h from cache
    "enable_persistent_index" = "true",        -- Essential for high-performance PK tables
    "compression" = "ZSTD"                    -- Good balance for high-throughput stream
);

3. Apply Compaction Tuning (CN Configuration) As discussed,

lake_pk_compaction_max_input_rowsets

is a node-level configuration (CN), not a table property. You should apply this to your CN nodes to keep the compaction cycles short and prevent "Compaction Score" spikes. Run this once on the cluster to update all nodes:

sql
-- Update the CN configuration to speed up PK compaction cycles
UPDATE information_schema.be_configs 
SET VALUE = "100" 
WHERE name = "lake_pk_compaction_max_input_rowsets";

Key Changes Explained: *

storage_volume

: This replaces the traditional local disk management. All data is persisted to S3, while the local NVMe on your

i4i

instances is used strictly for the

datacache

and

persistent_index

. *

datacache.partition_duration

: Since RTB data is usually only "hot" for the first few hours/days, setting this to

1 DAY

ensures your local NVMe cache doesn't get clogged with stale data, leaving room for active bid lookups. *

lake_pk_compaction_max_input_rowsets

: By setting this to

100

, you ensure the CN finishes its compaction tasks quickly. This prevents the "latency tail" where the system might otherwise wait to merge 500 small files at once. References • administration/management/BE_configuration.md • administration/management/compaction.md • administration/management/FE_configuration.md • best_practices/primarykey_table.md

@Rocky Should I scope the volume name by team name or database or table?

For a high-throughput production environment like yours, the best practice is to scope the storage volume by Team/Department or Service/Application, rather than by individual table. Here is how to choose the right scoping level based on your operational needs: 1. Recommended: Scope by Team or Service (e.g.,

marketing_s3_vol

) This is the most common pattern for production clusters because it balances security with management ease. * Security & IAM: Storage Volumes are the boundary for credentials. If "Team A" and "Team B" should not have access to each other's raw data in S3, you must use different Storage Volumes with different IAM roles or Access Keys. * Billing & Ownership: It makes it easier to track S3 costs and API usage via AWS tags or bucket-level metrics if each team/service has its own dedicated volume/bucket. * Inheritance: You can set the volume at the Database level. Any table created in that database will automatically use that volume unless overridden.

sql
    ALTER DATABASE bid_db SET ("storage_volume" = "rtb_team_s3_vol");

2. When to scope by Table (e.g.,

bid_stream_heavy_vol

) Only do this for your highest-throughput tables (like your bid stream) to leverage the Partitioned Prefix feature. * S3 Performance: S3 has limits on request rates per prefix. If one table is doing massive concurrent writes/reads, you should create a dedicated volume for it and enable

aws.s3.enable_partitioned_prefix

. * Note: To use this, the

LOCATIONS

must be just the bucket (e.g.,

<s3://my-heavy-bucket>

), not a sub-path.

sql
    CREATE STORAGE VOLUME high_perf_vol
    TYPE = S3
    LOCATIONS = ("<s3://starrocks-heavy-data-bucket>")
    PROPERTIES (
        "aws.s3.enable_partitioned_prefix" = "true",
        "aws.s3.num_partitioned_prefix" = "256" -- Multiplies S3 throughput
    );

3. Avoid: Scoping by Database alone (if it's just for naming) If all your databases share the same AWS account and security requirements, having 50 different volumes for 50 databases creates unnecessary metadata overhead and makes credential rotation (like updating Access Keys) a chore. Summary Recommendation * General Rule: Create one Storage Volume per IAM Role/Security Boundary. Name it after the team or the environment (e.g.,

prod_default_s3

). * Exception: Create a dedicated, "Partitioned Prefix" enabled volume for your primary high-ingest table to ensure S3 request throttling never becomes a bottleneck for your 15 concurrent producers. Scope | Best For | Why? Team | Multi-tenant clusters | Permission isolation and clear ownership. Database | Logical service grouping | Ease of management via property inheritance. Table | Extreme throughput | Required to tune S3 prefixes for max IOPS. Pro-tip: Use the

SET <volume_name> AS DEFAULT STORAGE VOLUME;

command for your most common volume to keep your DDLs clean. References • deployment/shared_data/feature-support-shared-data.md • sql-reference/sql-statements/cluster-management/storage_volume/CREATE_STORAGE_VOLUME.md • quick_start/shared-data.md • sql-reference/sql-statements/cluster-management/storage_volume/SET_DEFAULT_STORAGE_VOLUME.md • table_design/table_design.md References • deployment/shared_data/feature-support-shared-data.md

@Rocky Should the s3 path for heavy table like the bid_stream_heavy vol be table prefixed too? Like s3://stackadapt-starrocks/core-services/requests

For your heavy

bid_stream

table, the answer depends on whether you enable the Partitioned Prefix feature (which is highly recommended for high-throughput workloads). 1. If you enable "Partitioned Prefix" (Recommended) If you set

"aws.s3.enable_partitioned_prefix" = "true"

(to maximize S3 request throughput by spreading load across multiple S3 partitions), you cannot use a table-specific sub-path. * Rule: The

LOCATIONS

must contain only the bucket name (e.g.,

<s3://stackadapt-starrocks>

). * Why: StarRocks takes over the prefixing logic to automatically distribute data files across many S3 shards (using hashes) to bypass S3's 3,500/5,500 RPS limits. 2. If you do NOT enable "Partitioned Prefix" You can specify a sub-path like

<s3://stackadapt-starrocks/core-services/>

, but you should not include the table name (

/requests

) in the volume definition. * StarRocks manages the hierarchy: StarRocks automatically creates sub-directories for every Database and Table. If you point the volume to

.../requests

, your actual data path will look redundant:

.../requests/db_name/requests/segments...

. * Reusability: Scoping a volume to a single table makes it harder to manage. It is better to scope the volume to the Service or Database level. Summary Comparison Goal | Path Recommendation | Feature to Enable Max Performance (1M+ ops/sec) |

<s3://stackadapt-starrocks>

|

aws.s3.enable_partitioned_prefix = true

Organization/Security |

<s3://stackadapt-starrocks/core-services>

| Standard S3 Volume Avoid This |

<s3://stackadapt-starrocks/core-services/requests>

| (Redundant table prefixing) Corrected Recommendation for your Heavy Table For a high-volume RTB bid stream, you should favor S3 throughput over manual directory organization. Use a dedicated bucket for your heavy data and enable the prefix feature:

sql
CREATE STORAGE VOLUME bid_stream_heavy_vol
TYPE = S3
LOCATIONS = ("<s3://stackadapt-starrocks-heavy>") -- Bucket only
PROPERTIES (
    "aws.s3.enable_partitioned_prefix" = "true", -- Crucial for high throughput
    "aws.s3.num_partitioned_prefix" = "256",      -- Distributes load across 256 S3 partitions
    "enabled" = "true",
    ...
);

Verdict: Do not prefix by table name. Let StarRocks handle the table-level folder structure, and use the Partitioned Prefix feature at the bucket level to ensure S3 never throttles your 15 ingest producers. References • sql-reference/sql-statements/cluster-management/storage_volume/CREATE_STORAGE_VOLUME.md • faq/shared_data_faq.md • deployment/shared_data/feature-support-shared-data.md References • sql-reference/sql-statements/cluster-management/storage_volume/CREATE_STORAGE_VOLUME.md • deployment/shared_data/feature-support-shared-data.md

@Rocky I can't use subpaths for partitioned prefix? So no stackadapt-starrocks/core-services

Correct, you cannot use sub-paths with the Partitioned Prefix feature. The

LOCATIONS

property must contain only the bucket name (e.g.,

<s3://stackadapt-starrocks-core-services>

). Why this restriction exists S3 limits throughput (3,500 PUT/COPY/POST/DELETE and 5,500 GET requests per second) per prefix. When you enable

aws.s3.enable_partitioned_prefix

, StarRocks takes over the root of the bucket to generate hundreds of randomized top-level prefixes. This "shards" your data across S3's backend architecture to bypass those limits. If StarRocks allowed a sub-path like

/core-services/

, all those generated prefixes would be nested under one single S3 partition, defeating the entire purpose of the feature. Your Options for Organization 1. Dedicated Bucket (Best for High Performance) If you need to isolate

core-services

for billing or security, create a dedicated bucket:

sql
    LOCATIONS = ("<s3://stackadapt-starrocks-core-services>")

This gives you the best performance while keeping data logically separated from other departments. 2. Trust StarRocks Internal Structure Even without a sub-path in the

LOCATIONS

, StarRocks still organizes your data. Inside the bucket, it will create a folder structure based on the

Storage Volume ID

, then

Database ID

, then

Table ID

. It isn't a "flat" pile of files, though the names will be hashed to ensure S3 performance. 3. Standard Volume (If Throughput is Lower) If you absolutely must have the

stackadapt-starrocks/core-services

path for compliance and your ingest rate isn't hitting S3's 3,500–5,500 RPS limits, you can disable the partitioned prefix. However, for a "heavy" bid stream table, this is not recommended as it creates a performance ceiling you might hit later. Recommendation: Use a dedicated bucket named after your service/team and enable the partitioned prefix for that volume. References • sql-reference/sql-statements/cluster-management/storage_volume/CREATE_STORAGE_VOLUME.md • deployment/shared_data/feature-support-shared-data.md • faq/shared_data_faq.md References • sql-reference/sql-statements/cluster-management/storage_volume/CREATE_STORAGE_VOLUME.md • administration/management/FE_configuration.md • deployment/shared_data/s3.md • administration/management/BE_configuration.md

@Rocky But since the volume is dedicated to a single table "requests", shouldnt the bucket name also be stackadapt-starrocks-core-services-requests

While you can name a bucket after a specific table, it is generally not recommended to do so. In a production environment with multiple high-throughput tables, this often leads to "Bucket Sprawl," which creates significant operational overhead. Here is why you should stick to a Service-level or Team-level bucket (e.g.,

stackadapt-starrocks-core-services

) even for your heavy tables: 1. StarRocks Already Isolates Tables Even if you point the volume to a root bucket, StarRocks does not mix data files in a messy pile. It creates a structured hierarchy internally:

<s3://bucket-name/<volume_id>/<database_id>/<table_id>/>...

Because of this, you already get logical isolation and safety without needing a 1:1 ratio between S3 buckets and StarRocks tables. 2. Avoid AWS Bucket Limits & Management * Bucket Limits: AWS accounts have a default limit of 100 buckets (increasable, but still a hurdle). If you create a new bucket for every "heavy" table, you will eventually hit this limit. * Overhead: Every new bucket requires its own IAM policies, S3 Lifecycle rules (for cleaning up old files), tagging, encryption settings, and monitoring/logging. Managing this for 50 buckets is significantly more error-prone than managing it for 5. 3. The Performance Benefit is the Same The primary reason to use a dedicated bucket for a heavy table is to avoid S3 request throttling. Since you are enabling

aws.s3.enable_partitioned_prefix

, StarRocks will spread the table's data across hundreds of S3 shards. * A bucket like

stackadapt-starrocks-core-services

with Partitioned Prefix enabled will give you the same throughput as a table-specific bucket. When SHOULD you use a table-specific bucket? There are only two scenarios where a table-specific bucket name (like

...-requests

) makes sense: 1. Strict Data Sovereignty/Security: If the data in the

requests

table is so sensitive that only a specific IAM Role should even be able to see the encrypted bits in S3, and that role shouldn't see any other table in the "core-services" group. 2. External Data Sharing: If you plan to point an external tool (like a separate Spark job or Snowflake) directly at that bucket's data via S3 and want to limit its scope to just that table's raw files. Recommendation Use the Service/Application name for your bucket. * Bucket Name:

stackadapt-starrocks-core-services

* Volume Name:

core_services_heavy_vol

* Settings: Enable

aws.s3.enable_partitioned_prefix

and set

num_partitioned_prefix

to 128 or 256. This keeps your AWS infrastructure clean while giving your "requests" table all the S3 performance it needs. References • deployment/shared_data/feature-support-shared-data.md • sql-reference/sql-statements/cluster-management/storage_volume/CREATE_STORAGE_VOLUME.md • deployment/shared_data/s3.md • faq/shared_data_faq.md References • deployment/shared_data/feature-support-shared-data.md • quick_start/shared-data.md • best_practices/query_tuning/schema_tuning.md • best_practices/bucketing.md • deployment/shared_data/s3.md

@Rocky Why recommend ZSTD compression? Doesnt this result in more CPU needed during decompression even with datacache

Your concern about CPU usage is valid, but the recommendation for ZSTD (especially for a "heavy" table like

bid_stream

) is based on a trade-off where the benefits usually outweigh the costs. Here is the breakdown of why ZSTD is recommended and how it interacts with the DataCache: 1. DataCache & Compression: RAM vs. Disk It is important to distinguish between the two layers of StarRocks' DataCache: * Page Cache (RAM): This stores data pages after decompression. If your "hot" data fits in the Page Cache, StarRocks hits the decompressed data directly. In this scenario, there is zero CPU overhead for decompression regardless of whether you use ZSTD or LZ4. * Block Cache (Local SSD): This stores data blocks in their original compressed format (as they exist on S3). If you hit the Block Cache but miss the Page Cache, the CPU will indeed need to decompress the data. 2. Why ZSTD for "Heavy" Tables? For a high-throughput table (15+ producers, millions of events), the bottleneck is rarely the CPU; it is almost always I/O and S3 throughput. * Reduced I/O: ZSTD typically offers a 30–50% better compression ratio than LZ4. This means 30–50% less data being transferred from S3 to your BE nodes and 30–50% less data being written to your local Block Cache SSDs. * S3 Savings: For "heavy" volumes, storage costs and API request costs on S3 add up quickly. Smaller files mean fewer GET/PUT requests and lower monthly S3 bills. * Higher Cache Hit Ratio: Because ZSTD files are smaller, you can fit more data into the same amount of local SSD disk space (Block Cache). A higher cache hit ratio often provides a bigger performance boost than the millisecond difference in decompression speed. 3. CPU vs. I/O Trade-off * LZ4: Extremely fast (near memory-speed decompression) but lower compression. Choose this if your cluster is CPU-bound (CPU usage is constantly >80%) and you have plenty of S3 bandwidth/budget. * ZSTD: Slightly slower decompression but significantly smaller data. Choose this if your cluster is I/O-bound or Storage-sensitive (which most S3-backed "heavy" tables are). Pro-Tip: Adjustable ZSTD Level From StarRocks v3.3.2+, you can tune this further. If you find the default ZSTD (level 3) is too heavy on your CPU, you can specify a lower level for faster performance while still getting better compression than LZ4:

sql
PROPERTIES ("compression" = "zstd(1)")

Verdict: Use ZSTD for your

bid_stream

table. The I/O savings and better disk-cache utilization almost always provide a better "bang for your buck" in shared-data architectures than the raw speed of LZ4. References • table_design/data_compression.md • data_source/data_cache.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md References • table_design/data_compression.md • data_source/data_cache.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • administration/management/BE_configuration.md • using_starrocks/caching/block_cache.md • deployment/shared_data/feature-support-shared-data.md • best_practices/primarykey_table.md • sql-reference/System_variable.md

@Rocky So the volume should be named after a purpose, not for a specific table?

Exactly. In StarRocks, a Storage Volume is best treated as a Storage Service or a Domain, rather than a per-table configuration. Think of a Storage Volume as the "pipe" and the "credential" connecting StarRocks to your object store. Here is why naming it after a purpose (e.g.,

core_services_vol

) is the best practice: 1. Multi-Table Efficiency A single Storage Volume can host hundreds of tables. StarRocks automatically isolates them in a directory structure like:

<s3://your-bucket/<volume_id>/<db_id>/<table_id>/>

You don't need to create a new volume for every table just to keep data separate; StarRocks does that for you under the hood. 2. Centralized Credential Management If you have 50 tables and they each have their own Volume, you have 50 different objects to manage. If your AWS IAM keys rotate or your IAM Role changes, you would have to run 50

ALTER STORAGE VOLUME

statements. If they all share a purpose-based volume, you only update it once. 3. Scaling is Global to the Volume By enabling

aws.s3.enable_partitioned_prefix

on a single volume (e.g.,

core_services_vol

), all tables inside that volume benefit from the increased S3 throughput. You don't gain extra performance by splitting them into separate volumes unless you are hitting the absolute physical limits of a single AWS Bucket (which is very difficult to do with the partitioned prefix enabled). 4. Inheritance StarRocks uses a hierarchy for volumes: Catalog → Database → Table If you set the

storage_volume

at the Database level, every table you create in that database automatically uses it. This makes your

CREATE TABLE

statements much cleaner:

sql
-- Set it once for the whole service
ALTER DATABASE core_services_db SET ("storage_volume" = "core_services_vol");

-- Now just create tables normally
CREATE TABLE requests (...) ...; 
CREATE TABLE bids (...) ...;

When to create a new Storage Volume? You should only create a second volume if: * Security Isolation: You have a table with sensitive PII that must live in a bucket with a different IAM policy or KMS encryption key. * Billing/Account Boundaries: You need to store data in a completely different AWS Account or Region. * Storage Tiers: You want one volume on HDFS (for cheap/legacy data) and one on S3 (for hot/active data). Recommendation: Stick with your plan to use a service-based name like

core_services_vol

. It's the most scalable and maintainable approach. References • deployment/shared_data/feature-support-shared-data.md • deployment/shared_data/s3.md • sql-reference/sql-statements/cluster-management/storage_volume/CREATE_STORAGE_VOLUME.md References • deployment/shared_data/feature-support-shared-data.md • table_design/table_design.md • quick_start/shared-data.md • deployment/shared_data/s3.md • knowledge/admin_and_operation/shared_data_compaction.md

@Rocky

CREATE STORAGE VOLUME core_services_volume
TYPE = S3
LOCATIONS = ("<s3://stackadapt-starrocks-core-services>")
PROPERTIES
(
    "aws.s3.region" = "us-east-1",
    "aws.s3.use_aws_sdk_default_behavior" = "false",
    "aws.s3.use_instance_profile" = "true",
    "enabled" = "true"
);

SET core_services_volume AS DEFAULT STORAGE VOLUME;

CREATE TABLE IF NOT EXISTS requests (
    auction_id   STRING NOT NULL,
    received      DATETIME NOT NULL,
    imp_key      STRING NOT NULL,
    -- Other columns...
)
PRIMARY KEY (received, auction_id, imp_key)
DISTRIBUTED BY HASH(auction_id) BUCKETS 128
PROPERTIES (
    "storage_volume" = "core_services_volume",
    "datacache.enable" = "true",
    "datacache.partition_duration" = "25 HOUR",
    "enable_persistent_index" = "true",
    "persistent_index_type" = "CLOUD_NATIVE"
    "compression" = "ZSTD"
);

Would this DDL work? Does partition_live_number still work? So oldest partitions deleted from S3

Your DDL is very close, but there are a few syntax fixes and a critical missing clause needed to make it work as you intended. 1. Does the DDL work? Almost. You need to add a

PARTITION BY

clause. Without it, the table is "unpartitioned," so properties like

partition_live_number

or

datacache.partition_duration

will have no effect. Here is the corrected DDL:

sql
CREATE STORAGE VOLUME core_services_volume
TYPE = S3
LOCATIONS = ("<s3://stackadapt-starrocks-core-services>") -- Removed extra <>
PROPERTIES
(
    "aws.s3.region" = "us-east-1",
    "aws.s3.use_aws_sdk_default_behavior" = "false",
    "aws.s3.use_instance_profile" = "true",
    "enabled" = "true"
);

SET core_services_volume AS DEFAULT STORAGE VOLUME;

CREATE TABLE IF NOT EXISTS requests (
    auction_id   STRING NOT NULL,
    received      DATETIME NOT NULL,
    imp_key      STRING NOT NULL
    -- Other columns...
)
PRIMARY KEY (received, auction_id, imp_key)
-- MANDATORY: You must define the partition column here
PARTITION BY date_trunc('hour', received) 
DISTRIBUTED BY HASH(auction_id) BUCKETS 128
PROPERTIES (
    "storage_volume" = "core_services_volume",
    "datacache.enable" = "true",
    "datacache.partition_duration" = "25 HOUR",
    "partition_live_number" = "720", -- Example: Keep 30 days of hourly partitions
    "enable_persistent_index" = "true",
    "persistent_index_type" = "CLOUD_NATIVE", -- Fixed missing comma
    "compression" = "ZSTD"
);

2. Does

partition_live_number

work in Shared-Data mode? Yes. This property works exactly the same way in a shared-data (Cloud Native) cluster as it does in a shared-nothing cluster. * Behavior: It automatically maintains the number of most recent partitions. Once a new partition is created that exceeds your limit, the oldest one is dropped. * TTL logic: It uses the current system time as the benchmark. 3. Are the oldest partitions deleted from S3? Yes. When StarRocks drops a partition due to

partition_live_number

, it triggers a metadata deletion. * In a Shared-Data cluster, the FE (Front End) identifies these "orphaned" or dropped data files. * The system then performs a background Garbage Collection (GC) process that permanently deletes the associated

.seg

(segment) files from your S3 bucket. _ _Note*: There is usually a small delay (defined by FE/BE "trash" configuration) before the physical bytes disappear from S3, but they are logically gone and will be physically purged. 4. Important distinction:

datacache

vs

partition_live_number

*

partition_live_number

: Controls Data Retention (The Source of Truth). If a partition is dropped here, it is deleted from S3 forever. *

datacache.partition_duration

: Controls Cache Priority. It tells StarRocks: "Keep the most recent 25 hours of data on the BE's local SSDs." Data older than 25 hours will still exist on S3, but it will be evicted from the local cache to make room for newer data. References • table_design/data_distribution/expression_partitioning.md • deployment/shared_data/s3.md • best_practices/primarykey_table.md • faq/shared_data_faq.md • knowledge/admin_and_operation/shared_data_garbage_cleanup.md References • best_practices/primarykey_table.md • deployment/shared_data/feature-support-shared-data.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md

@Rocky Where would I define the datacache.partition_duration?

CREATE STORAGE VOLUME core_services_volume
TYPE = S3
LOCATIONS = ("<s3://stackadapt-starrocks/core-services>")
PROPERTIES
(
    "enabled" = "true",
    "aws.s3.region" = "us-east-1",
    "aws.s3.endpoint" = "<https://stackadapt-starrocks-core-services.s3.us-east-1.amazonaws.com>",
    "aws.s3.use_aws_sdk_default_behavior" = "false",
    "aws.s3.use_instance_profile" = "true",
    "aws.s3.enable_partitioned_prefix" = "true",
    "aws.s3.num_partitioned_prefix" = "512"	
);

Are the additional properties for volume here required?

The

datacache.partition_duration

property is a table-level property, not a volume-level property. You should define it within the

PROPERTIES

block of your

CREATE TABLE

statement. Regarding your

CREATE STORAGE VOLUME

DDL, here is the breakdown of what is required and a few critical fixes: 1. Where to put

datacache.partition_duration

? It belongs in the

CREATE TABLE

DDL. It tells the StarRocks BE nodes how long to keep that specific table's data in the local SSD cache.

sql
CREATE TABLE requests ( ... )
PROPERTIES (
    "storage_volume" = "core_services_volume",
    "datacache.enable" = "true",
    "datacache.partition_duration" = "25 HOUR" -- Defined here
);

2. Are the Volume properties required? *

aws.s3.region

: Required. *

enabled

: Required (set to

true

) to actually use the volume. *

aws.s3.endpoint

: Optional for standard AWS S3. If you are using standard AWS S3 in

us-east-1

, StarRocks can usually resolve this automatically from the region. However, keeping it doesn't hurt. *

aws.s3.enable_partitioned_prefix

: Highly Recommended but Optional. For a "heavy" table, this is great for performance as it prevents S3 path throttling. * CRITICAL: If you set this to

true

, your

LOCATIONS

must only be the bucket name. You cannot include a sub-path like

/core-services

. * Wrong:

LOCATIONS = ("<s3://bucket/path>")

* Correct:

LOCATIONS = ("<s3://bucket>")

Corrected Volume DDL If you want to use the Partitioned Prefix feature (which I recommend for your scale), use this version:

sql
CREATE STORAGE VOLUME core_services_volume
TYPE = S3
LOCATIONS = ("<s3://stackadapt-starrocks>") -- Bucket only, no sub-path
PROPERTIES
(
    "enabled" = "true",
    "aws.s3.region" = "us-east-1",
    "aws.s3.use_instance_profile" = "true",
    "aws.s3.enable_partitioned_prefix" = "true", -- Optimizes S3 throughput
    "aws.s3.num_partitioned_prefix" = "512"
);

Summary of Differences * Storage Volume Properties: Handle authentication and physical storage layout (S3 credentials, bucket location, S3 prefixing). * Table Properties: Handle data management (

datacache.partition_duration

,

compression

,

partition_live_number

). References • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • sql-reference/sql-statements/cluster-management/storage_volume/CREATE_STORAGE_VOLUME.md • deployment/shared_data/s3.md References • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • sql-reference/sql-statements/cluster-management/storage_volume/CREATE_STORAGE_VOLUME.md • deployment/shared_data/s3.md • deployment/shared_data/azure.md References • sql-reference/sql-statements/cluster-management/storage_volume/CREATE_STORAGE_VOLUME.md

@Rocky Briefly explain why if I enable partition prefix, only bucket path with no subpath allowed for storage volume. And the benefit of partition prefixes

Why only the bucket path is allowed? When you enable Partitioned Prefix, StarRocks takes full control over the root directory structure of the S3 bucket to maximize performance. * Hashing Logic: StarRocks automatically generates a set of sharded prefixes (e.g.,

00/

,

01/

, ... up to

FF/

) directly under the root. * Conflict Prevention: If you were allowed to specify a sub-path (like `s3://my-bucket/my-app/`), StarRocks’ internal hashing logic would conflict with your manual pathing. To ensure a predictable and collision-free storage layout, the feature requires a clean bucket-level location. * S3 Scaling Architecture: S3 scales performance based on the prefix. By using the root, StarRocks ensures that its generated shards have the maximum "entropy" (randomness) in the path, which is exactly what S3 needs to automatically scale its internal indices. The Benefits of Partitioned Prefixes This feature is designed for high-concurrency, high-throughput environments. 1. Bypasses S3 Rate Limits: AWS S3 has a request limit of 3,500 PUT/POST/DELETE and 5,500 GET requests per second per prefix. _ _Without prefixes:* All your StarRocks data files might live under one or two common paths, hitting these limits quickly during heavy loads or large scans (causing "503 Slow Down" errors). _ _With prefixes:* StarRocks spreads files across 512 different prefixes. This effectively multiplies your theoretical S3 throughput by up to 512x, as each prefix gets its own independent "performance bucket" in AWS. 2. Increased Parallelism: When performing massive parallel scans across a large cluster (e.g., hundreds of CN nodes), the bottleneck is often the S3 API metadata layer. Spreading the data allows AWS to distribute your bucket's workload across more internal S3 partition servers. 3. Future-Proofing: Even if you don't hit 5,500 requests per second today, enabling this now prevents a painful data migration later. This property is *immutable*—you cannot enable it on an existing volume later without recreating the volume and the tables. Summary: By giving up the ability to name your sub-folder, you gain massive, automated scalability for your storage layer. References • sql-reference/sql-statements/cluster-management/storage_volume/CREATE_STORAGE_VOLUME.md • deployment/shared_data/s3.md • release_notes/release-3.2.md References • sql-reference/sql-statements/cluster-management/storage_volume/CREATE_STORAGE_VOLUME.md • loading/s3.md • deployment/shared_data/s3.md • release_notes/release-3.2.md