Best Practices for VeloDB Cloud POC

Use a proof of concept (POC) to determine whether VeloDB Cloud meets the technical and operational requirements of your intended workload. Define the scope and acceptance criteria before you load data so that the results are repeatable and useful for a deployment decision.

A successful POC can help you:

• Validate priority use cases with representative data, SQL queries, concurrency, and data freshness requirements.
• Confirm that the selected table model, partitioning, bucketing, and indexing strategy support your ingestion and query patterns.
• Measure ingestion throughput, query latency, and resource usage against agreed acceptance criteria.
• Verify data correctness by comparing query results and aggregate values with the source system or an established baseline.
• Evaluate operational requirements, including connectivity, authentication, data loading, monitoring, troubleshooting, and workload isolation.
• Identify configuration changes, data-model tradeoffs, and follow-up work required before production adoption.

Before you begin, prepare the following:

• A representative sample of your production data and queries. Include the largest tables, common filters, joins, and write patterns.
• A success criterion for each workflow, such as ingestion throughput, query latency, freshness, or result accuracy.
• A warehouse with enough compute capacity for the sample workload. Record the warehouse size and cluster count with your test results.

Follow the checklist in this order:

1. Table design: choose the data model, sort key, partitioning, and bucketing strategy.
2. Data ingestion: choose the right ingestion method and avoid common pitfalls.
3. Query tuning: diagnose slow queries and optimize bucket and index configurations.
4. Data lake queries: additional optimization tips for lakehouse scenarios.

Quick checklist

• Does each table use the appropriate data model (Duplicate Key, Unique Key, or Aggregate Key)?
• Is the bucket count an integer multiple of the cluster's backend node count?
• Do time-based queries include the partition column so that partition pruning can occur?
• For lakehouse queries, have you measured both a cold-cache and a warm-cache run?
• Have you recorded the query plan, Query Profile, ingestion status, and tablet distribution for the representative workload?

Table design

Create a table to match the way you write and query the data. These decisions affect ingestion and query performance:

• Data model
• Sort key
• Partitioning
• Bucketing

Data model

Choose the right model based on how data is written:

Data Characteristic	Recommended Model	Reason
Append-only (logs, events, fact tables)	Duplicate Key (default)	Keeps all rows; best query performance
Updated by primary key (CDC, Upsert)	Unique Key	New rows replace old rows with the same key
Pre-aggregated metrics (PV, UV, summaries)	Aggregate Key	Merges rows by SUM/MAX/MIN at write time

The Duplicate Key model fits most scenarios. Confirm the choice against your update and aggregation requirements. For details, see Data Model Overview.

Sort key

VeloDB Cloud builds a prefix index on the first 36 bytes of the sort key. When setting the sort key, follow these principles:

• Put high-frequency filter columns first: place columns most often used in WHERE conditions at the front.
• Put fixed-length types first: put fixed-length types such as INT, BIGINT, and DATE before VARCHAR, because the prefix index truncates immediately when it encounters a VARCHAR.
• Add inverted indexes as a complement: for columns the prefix index does not cover, add an inverted index to speed up filtering.

Partitioning

If your workload filters by time, use AUTO PARTITION BY RANGE(date_trunc(time_col, 'day')) to enable partition pruning. VeloDB Cloud can then skip partitions that do not match the query predicate.

Bucketing

The default setting is random bucketing (recommended for Duplicate Key tables). If you frequently filter or JOIN on a specific column, use DISTRIBUTED BY HASH(that_column). For details, see Data Bucketing.

The following table shows principles about how to choose the bucket count.

Principle	Description
Use a bucket count that is an integer multiple of the BE (Backend) count	Helps distribute data evenly across BEs. When you scale out BEs later, queries can be distributed across multiple partitions, minimizing the impact on query performance.
Use the minimum number of buckets	Helps avoid creating small files.
Limit the compressed size of each bucket (tablet) to 20 GB or less for Duplicate Key and Aggregate Key tables	For Unique Key tables, limit the compressed size to 10 GB or less. Run SHOW TABLETS FROM <your_table> to check the tablet size.
Use no more than 128 buckets per partition	If you need more buckets, increase the number of partitions first. Although the maximum is 1,024 buckets per partition, this is rarely necessary in production environments.

Table creation templates

Log and event analytics

Use case: Append-only scenarios such as logs, events, and sensor data.

Prerequisites: No special requirements.

CREATE TABLE app_logs
(
    log_time      DATETIME    NOT NULL,
    log_level     VARCHAR(10),
    service_name  VARCHAR(50),
    trace_id      VARCHAR(64),
    message       STRING,
    INDEX idx_message (message) USING INVERTED PROPERTIES("parser" = "unicode")
)
AUTO PARTITION BY RANGE(date_trunc(`log_time`, 'day'))
()
DISTRIBUTED BY RANDOM BUCKETS 10;

Verification steps:

-- 1. Verify that partitions are created automatically
SHOW PARTITIONS FROM app_logs;

-- 2. Verify that data is evenly distributed
SHOW TABLETS FROM app_logs;

Real-time dashboards and upsert (CDC)

Use case: Scenarios that need primary-key updates, such as user profiles and order records.

Prerequisites: A clearly defined primary key column.

CREATE TABLE user_profiles
(
    user_id       BIGINT      NOT NULL,
    username      VARCHAR(50),
    email         VARCHAR(100),
    status        TINYINT,
    updated_at    DATETIME
)
UNIQUE KEY(user_id)
DISTRIBUTED BY HASH(user_id) BUCKETS 10;

Verification steps:

-- 1. Verify primary key uniqueness (only one latest row per user_id)
SELECT user_id, count(*) as cnt FROM user_profiles GROUP BY user_id HAVING cnt > 1;

-- 2. Verify data distribution
SHOW TABLETS FROM user_profiles;

Metric aggregation

Use case: Scenarios that need pre-aggregation, such as traffic statistics and business reports.

Prerequisites: Clearly defined aggregation dimension columns and metric columns.

CREATE TABLE site_metrics
(
    dt            DATE        NOT NULL,
    site_id       INT         NOT NULL,
    pv            BIGINT      SUM DEFAULT '0',
    uv            BIGINT      MAX DEFAULT '0'
)
AGGREGATE KEY(dt, site_id)
AUTO PARTITION BY RANGE(date_trunc(`dt`, 'day'))
()
DISTRIBUTED BY HASH(site_id) BUCKETS 10;

Verification steps:

-- 1. Verify that aggregation works (metrics with the same dt+site_id are merged)
SELECT dt, site_id, pv, uv FROM site_metrics ORDER BY dt DESC LIMIT 10;

-- 2. Verify that partition pruning works
EXPLAIN SELECT * FROM site_metrics WHERE dt = '2024-01-01';

Data ingestion

Choose the right ingestion method and follow these best practices to avoid common performance issues:

• Do not use INSERT INTO VALUES for bulk data. Use Stream Load or Broker Load instead. For details, see Ingestion Overview.
• Merge writes on the client side first. High-frequency small-batch ingestion causes version pile-up. If client-side merging is not feasible, use Group Commit.
• Split large ingestions into smaller batches. A long-running ingestion must restart from the beginning if it fails. Use INSERT INTO SELECT with the S3 TVF for incremental ingestion.
• Enable load_to_single_tablet for Duplicate Key tables with random bucketing to reduce write amplification.

Quick verification:

-- View ingestion task status
SHOW LOAD WHERE label = 'your_label';

-- Check version pile-up. A high Version Count indicates ingestion is too frequent)
SHOW TABLETS FROM your_table;

For details, see Load Best Practices and Performance Tuning.

Query tuning

Bucket counts

The bucket count directly affects query parallelism and scheduling overhead, so you need to strike a balance:

• Do not use too many buckets. Too many small tablets create scheduling overhead and can reduce query performance by up to 50%.
• Do not use too few buckets. Too few tablets limit CPU parallelism.
• Avoid data skew. Use SHOW TABLETS to check tablet sizes. When sizes differ significantly, switch to random bucketing or pick a bucketing column with higher cardinality.

The diagnostic SQL statement is as follows:

-- Check tablet size distribution (used to detect data skew)
SHOW TABLETS FROM <your_table>;
-- Review the tablet count and size to decide whether to adjust the bucket count

For more information about choosing the bucket count, see Bucketing.

Indexes

When using indexes, set the sort key correctly.

Unlike some databases such as PostgreSQL, VeloDB Cloud only indexes the first 36 bytes of the sort key, and it truncates immediately when it encounters a VARCHAR. Columns beyond the prefix range cannot benefit from the sort key and need an inverted index.

For more information, see Sort Key.

Run the following SQL statement to verify that the sort key works:

EXPLAIN SELECT * FROM your_table WHERE filter_column = 'xxx';
-- Check whether the Sort Key index is used

Use Query Profile to diagnose slow queries

To diagnose slow queries, use Query Profile.

Run the following SQL statement to use the Query Profile quickly:

-- 1. Run the query and obtain the query_id
SET enable_profile = true;
SELECT ...;

-- 2. View the Query Profile
SHOW PROFILELIST;
SHOW PROFILE WHERE query_id = 'xxx';

Data lake queries

If your POC queries Hive, Iceberg, or Paimon data through VeloDB Cloud, use the following checks. Lakehouse results depend on remote storage, file layout, network path, and cache state. Record those conditions with each measurement.

Make sure partition pruning works

Data lake tables often hold a massive amount of data. It is recommended that you always include the partition column in the WHERE clause to make VeloDB Cloud scan only the necessary partitions.

Run EXPLAIN <SQL> and then check the partition field to confirm that pruning works:

0:VPAIMON_SCAN_NODE(88)
    partition=203/0          -- 203 partitions are pruned, 0 are actually scanned

If the partition count is much larger than expected, verify that the WHERE clause correctly matches the partition column.

Enable data cache

Remote storage, including Hadoop Distributed File System (HDFS) and object storage, generally has higher I/O latency than local disks. Data Cache stores recently accessed remote data on the cluster's local disk and can reduce latency for repeated queries while the data remains cached.

• The data cache function is disabled by default. You can enable it by setting relevant parameters in FE and BE. For more information on how to configure it, see Data Cache.
• Starting from v26.0.5, cache warmup is supported, enabling you to proactively load hot data before POC testing.

Tip:

• During a POC, measure cold-cache and warm-cache running results separately. Run the query once to populate the cache, and then label the latency of subsequent running results as warm-cache results.
• Do not use a warm-cache result as the only baseline for a workload that commonly reads new data.

Manage small files

Data lake workloads often contain a large number of small files. These files generate a large number of splits, increasing FE memory consumption and query planning overhead, and can even lead to OOM errors.

• Manage from the source (recommended): periodically merge small files on the Hive or Spark side, keeping each file larger than 128 MB.
• VeloDB Cloud fallback: use SET max_file_split_num = 50000; (supported since v26.0.2) to limit the maximum number of splits per scan and prevent OOM.

Use Query Profile to diagnose data lake slow queries

The bottleneck of data lake queries is usually IO rather than computation. You can use Query Profile to locate the root cause of slow queries.

Focus on the following:

• Split count and data volume: determine whether too much data is being scanned.
• MergeIO metrics: if MergedBytes is much larger than RequestBytes, read amplification is significant. The default value of merge_io_read_slice_size_bytes is 8MB. Reducing it can mitigate read amplification.
• Cache hit ratio: confirm that Data Cache is working effectively.

For more optimization techniques, see Data Lake Query Optimization.

Record and compare results

For each representative query and ingestion workflow, record the following:

• The warehouse size, cluster count, deployment type, and test data volume.
• The SQL statement, ingestion method, batch size, and concurrency.
• Ingestion throughput and freshness, and the result of SHOW LOAD.
• Query latency for cold-cache and warm-cache running results, the EXPLAIN plan, and the Query Profile.
• Tablet size distribution from SHOW TABLETS and any data lake split or cache metrics.

Repeat the same workload after changing one variable at a time, such as the bucket count, sort key, or cache state. This practice makes each result easier to attribute and reproduce.

Common errors and solutions

Table creation fails with Tablet count should be greater than 0

Cause:

• The bucket count is set to 0.
• Or bucketing is not specified.

Solution:

Verify that the DDL specifies a distribution method and a positive bucket count, such as DISTRIBUTED BY HASH(column_name) BUCKETS n or DISTRIBUTED BY RANDOM BUCKETS n.

-- Correct example
DISTRIBUTED BY HASH(user_id) BUCKETS 10;

A slow query is not using the expected index

Diagnosis steps:

1. Run EXPLAIN <query> to view the query plan and confirm that the sort key is used.
2. Run SHOW TABLETS FROM <table_name> to verify that tablet sizes are even.
3. View the Query Profile to locate the bottleneck.

-- Check whether the index is used (look at output_id for Sort Key columns)
EXPLAIN SELECT * FROM <table_name> WHERE key_col = 'xxx';

-- Check tablet size to detect data skew
SHOW TABLETS FROM <table_name>;

OOM on data lake queries

Cause:

Too many small files cause the split count to explode.

Solution:

1. Merge small files on the data source side. Make sure that each file is larger than 128 MB.

2. Limit the split count on the VeloDB Cloud side:

   SET max_file_split_num = 50000;

Ingestion version pile-up causes slow queries

Cause:

Frequent small-batch ingestion creates too many versions.

Solution:

1. Merge ingestion batches and reduce ingestion frequency.

2. Enable Group Commit:

   SET group_commit = async_mode;

FAQ

Q: How long does a POC take?

The time required depends on the dataset, workload, and validation scope. Start with table creation, ingestion, and representative queries, then allow additional time for performance tuning and repeated measurements.

Q: How should I choose the bucket count when creating a table?

Use a bucket count that is an integer multiple of the BE (Backend) count to ensure even data distribution.

In addition, the compressed data per bucket must be as follows:

• Less than 20 GB for Duplicate Key and Aggregate Key tables
• Less than 10 GB for Unique Key tables

Q: What should I do if queries are slower than expected?

Do the following:

1. Run EXPLAIN to check whether the index is used.
2. Run SHOW TABLETS to check for data skew.
3. View the Query Profile to locate the bottleneck.

Q: Should I enable Data Cache?

If your workload includes data lake queries, such as Hive, Iceberg, or Paimon, consider evaluating Data Cache.

Compare query latency with a cold cache and a warm cache: the first query populates the cache, while subsequent queries may benefit from cached data.

See also

• Quick Start
• Review ingestion best practices
• Tune data lake queries
• Review the Query Profile