@Rocky I have usecases where precomputed results are required which needs to refresh every day but not generally most other queries being used with this logics. is it better to have materialized view or task writing data to table?

For your scenario where you need daily precomputed results for specific use cases, an Asynchronous Materialized View (MV) is generally the better approach compared to a manual Task + Table. Here is a comparison to help you decide based on your requirements: 1. Asynchronous Materialized View (Recommended) This is the modern way to handle precomputation in StarRocks. * Automatic Management: You define the query once, and StarRocks handles the underlying table creation and the "Insert/Overwrite" logic for you. * Scheduled Refresh: You can set a daily schedule directly in the definition:

sql
    CREATE MATERIALIZED VIEW my_mv
    REFRESH ASYNC EVERY (INTERVAL 1 DAY)
    AS
    SELECT ... -- your logic

* Partitioned Refresh: If your base table is large, MVs support partition-level incremental refresh. If only one day of data changes in the base table, StarRocks can refresh only that specific partition in the MV, saving significant resources. * Query Rewrite: Even if most queries don't use this logic, the StarRocks optimizer can automatically rewrite queries to use the MV if it detects a match, which improves performance transparently. * Zero Maintenance: You don't need to manually maintain the target table schema or the loading pipeline. 2. Task (Insert into Table) This involves creating a standard table and using the

SUBMIT TASK

command to schedule an

INSERT

or

INSERT OVERWRITE

job. * Manual Control: Use this only if you have highly custom ETL requirements that an MV cannot satisfy (e.g., complex multi-step procedural logic). * No Intelligence: The system treats it as a standard table. There is no automatic query rewrite, and you must manually ensure the target table schema stays in sync with your logic. * Manual Partitioning: You would have to manually write the logic to handle daily partition management (e.g., dropping/overwriting specific partitions). Comparison Summary Feature | Asynchronous MV | Task + Table Ease of Use | High (Define once) | Low (Manual table + task) Maintenance | Automatic | Manual Incremental Updates | Supported (Partition-level) | Manual (Need custom SQL) Query Rewrite | Yes | No Transparency | High (Managed by DB) | Low (Managed by user) Recommendation: Use an Asynchronous Materialized View with a

REFRESH ASYNC EVERY (INTERVAL 1 DAY)

strategy. It reduces your operational overhead and allows StarRocks to optimize the refresh process (especially if you use

PARTITION BY

to match your base table's partitioning). References • sql-reference/sql-statements/loading_unloading/ETL/SUBMIT_TASK.md • loading/InsertInto.md • sql-reference/sql-statements/loading_unloading/pipe/CREATE_PIPE.md

@Rocky I have a table partition by created_on(immutable) but the queries mostly run on another date col activated_on, how can I make the filters on activated_on fast, I could not partition this as activated_on is mutable. I'm doing order by (activated_on), what else can be done to make queries jmuch faster?

The reason your queries are still slow despite the

ORDER BY

is likely Partition Pruning. In StarRocks, when you partition a table by

created_on

, every query that doesn't filter by

created_on

must scan every single partition in the table. While your

ORDER BY (activated_on)

creates a sort key (prefix index) that helps within each partition, the system still has to open and check the metadata/indexes for every partition, which is where the bottleneck occurs. Here are the best ways to make filters on the mutable

activated_on

column much faster: 1. Use the Primary Key Table Model (Recommended) If your table uses the Primary Key model, you actually can partition by

activated_on

even if it is mutable. * How it works: When you update

activated_on

for a row in a Primary Key table, StarRocks internally performs a "Delete + Insert" to move the row to the correct partition. * Benefit: This allows for Partition Pruning. If you query

WHERE activated_on = '2023-10-01'

, StarRocks will only look at the one relevant partition instead of scanning the entire table. 2. Move

activated_on

to the Leading Position of the Sort Key Ensure that

activated_on

is the first column in your

ORDER BY

clause. * Why: StarRocks builds a Prefix Index on the first 36 bytes of the sort key. If your sort key is

(created_on, activated_on)

, a query on

activated_on

alone cannot use the prefix index. * Action: Change your table definition to

ORDER BY (activated_on, ...)

. 3. Add a Bloom Filter Index For a

DATE

or

DATETIME

column with high cardinality (many distinct dates), a Bloom Filter index is very effective for equality (

=

) or

IN

queries. * Action:

sql
    ALTER TABLE your_table SET ("bloom_filter_columns" = "activated_on");

* Benefit: It allows the storage engine to skip data blocks (segments) that definitely do not contain the target date without reading them from disk. 4. Create an Asynchronous Materialized View (MV) If you cannot change the base table schema, create an Asynchronous MV that is physically organized for these specific queries. * Strategy: Create the MV and partition it by

activated_on

. * Query Rewrite: StarRocks can automatically route queries directed at the base table to this MV. * Note: Since there is no direct mapping between

created_on

and

activated_on

, the MV might require a full refresh or a defined schedule rather than a partition-aligned incremental refresh. Summary Comparison Optimization | Type | Best For | Effort Primary Key Partitioning | Physical | Perfect pruning; handles updates | High (Re-create table) Leading Sort Key | Indexing | Range and point queries | Medium (Re-create or Alter) Bloom Filter | Indexing | Equality (=) and IN queries | Low (Alter table) Materialized View | Secondary | Precomputing results | Medium (Create MV) Rocky's Advice: If you are on a Primary Key model, re-partitioning by

activated_on

is the most "future-proof" fix. If you need a quick win without a migration, adding a Bloom Filter is the fastest path. References • table_design/table_types/primary_key_table.md • knowledge/usage/best-practice.md • table_design/indexes/Bloom_filter_index.md References • faq/Others.md • loading/Load_to_Primary_Key_tables.md • sql-reference/sql-statements/table_bucket_part_index/UPDATE.md • table_design/table_types/primary_key_table.md

@Rocky It is a transaction table with pk as id in Mysql, If I give activated_on as partition by month, if the same id is activated last month and this month, I get two records of activated_on in both the partitions right? as partitoin key has to be primary key, if it changes, starrocks adds a new record to the table right?

In StarRocks, you have correctly identified a critical constraint of the Primary Key (PK) model: 1. The Partition Key must be part of the Primary Key In a Primary Key table, any column used for partitioning must be included in the

PRIMARY KEY

definition. * If your MySQL PK is

id

, but you want to partition by

activated_on

in StarRocks, your StarRocks PK must be

(id, activated_on)

. 2. What happens when

activated_on

changes? Because

activated_on

is part of the primary key, changing its value creates a new unique identifier from the database's perspective. * Result: If a row has

id=1

and

activated_on='2023-10-01'

, and you "update" it to

activated_on='2023-11-01'

, StarRocks sees this as a brand new record. * Duplicates: You will indeed end up with two records in your table: one in the October partition and one in the November partition. Why does this happen? StarRocks' Primary Key model uses a "Delete+Insert" strategy. It can only "Delete" the old record if the incoming record has the exact same primary key. Since the date (partition key) has changed, the primary keys don't match, so the old record is never marked for deletion. How to handle this? If you must partition by a mutable column like

activated_on

while keeping

id

unique, you have a few options: Option A: Manual Delete + Insert When a record's

activated_on

date changes, your loading process must: 1. Explicitly Delete the old record (using the old

id

+

activated_on

combination). 2. Insert the new record. This ensures only one version of the

id

exists across all partitions. Option B: Partition by a Non-Mutable Column (Recommended) If possible, continue to partition by

created_on

(which is immutable) and use other methods to speed up

activated_on

queries: * Sort Key: Put

activated_on

first in your

ORDER BY

clause. * Bloom Filter: Add a Bloom Filter index on

activated_on

. * Secondary Index (Bitmap): If the cardinality isn't extremely high, a Bitmap index can also help. Option C: Use a Materialized View for Queries Keep your base table partitioned by

created_on

, but create an Asynchronous Materialized View that is partitioned by

activated_on

. StarRocks will handle the data movement within the MV, and your queries can run against the MV for high performance. Summary You are correct: if the partition key is part of the PK and it changes, StarRocks will add a new record rather than updating the old one. If you need

id

to be globally unique across partitions while

activated_on

changes, you must manage the deletion of the "old" primary key yourself. References • table_design/table_types/primary_key_table.md • loading/Load_to_Primary_Key_tables.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md References • faq/operation_maintenance_faq.md • table_design/data_distribution/expression_partitioning.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • loading/Load_to_Primary_Key_tables.md

@Rocky how can I remove default bucket number for a table and let starrocks decide bucket number for new partitions of the table?

To let StarRocks automatically decide the bucket number for new partitions of an existing table, you need to modify the table's distribution property by omitting the

BUCKETS

keyword. Since StarRocks v2.5.7, automatic bucketing is supported. Here is how you can apply it: 1. Set Table Default to Automatic To change the table's distribution definition so that StarRocks automatically calculates the bucket count for new partitions, run:

sql
ALTER TABLE <table_name> DISTRIBUTED BY HASH(<columns>);

Note: If you run this command without specifying specific partitions, StarRocks will also re-bucket your existing partitions to match the newly calculated automatic count. 2. Handling Dynamic Partitioning If your table uses Dynamic Partitioning (where partitions are created automatically based on time), the property

dynamic_partition.buckets

will override the table's general setting. To allow StarRocks to decide automatically for these partitions, you should update the property:

sql
ALTER TABLE <table_name> SET ("dynamic_partition.buckets" = "0");

Setting this value to

0

(in newer versions) or removing the fixed number allows the dynamic partition scheduler to fall back to the table's automatic distribution logic. 3. Adding New Partitions Manually When manually adding a partition, simply omit the

BUCKETS

clause:

sql
ALTER TABLE <table_name> ADD PARTITION <partition_name> 
VALUES LESS THAN ("2023-10-01")
DISTRIBUTED BY HASH(<columns>); -- No 'BUCKETS <num>' here

Key Considerations * FE Configuration: Ensure the FE parameter

enable_auto_tablet_distribution

is set to

true

(it is

true

by default since v2.5.7). * Data Volume: StarRocks calculates the bucket count based on your cluster size and the amount of data already in the table. If a single partition is expected to exceed 100 GB, it is still recommended to manually specify the bucket count for better performance. * Version Check: If you are on a version earlier than 2.5.7, automatic bucketing is not supported, and a fixed number must be provided. References • table_design/data_distribution/Data_distribution.md • administration/management/FE_configuration.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md References • table_design/data_distribution/Data_distribution.md • administration/management/FE_configuration.md • table_design/data_distribution/dynamic_partitioning.md

@Rocky I don't want to change the existig partitions, any new partitions to be created, the number of buckets should be automatically decided by starrocks

To let StarRocks decide the bucket number for new partitions without triggering a data shuffle or re-bucketing of your existing partitions, the approach depends on your StarRocks version and whether you use manual or dynamic partitioning. Option 1: For StarRocks v3.5.8+ or v4.0.1+ (Recommended) Starting from these versions, StarRocks introduced the

DEFAULT

keyword for the

DISTRIBUTED BY

clause. This allows you to update the table's distribution metadata for future partitions without modifying existing ones. To switch the table's default to "Automatic Bucketing":

sql
ALTER TABLE <table_name> DISTRIBUTED BY HASH(<columns>) DEFAULT;

*Note: By omitting the

BUCKETS <num>

part and using

DEFAULT

, you signal to StarRocks that all newly created partitions should use automatic bucketing, while existing partitions remain untouched.* ────────── Option 2: For tables using Dynamic Partitioning If your table uses the

dynamic_partition

properties (automatically creating partitions based on time), you can simply update the dynamic partition properties. Update the property to

0

(or omit it if allowed in your version) to let the system take over:

sql
ALTER TABLE <table_name> SET ("dynamic_partition.buckets" = "0");

In many versions, setting this to

0

or removing the specific numeric value forces the dynamic partition scheduler to use the automatic bucketing logic for all future partitions it creates. ────────── Option 3: Manual Partition Creation If you manually add partitions using

ALTER TABLE ... ADD PARTITION

, you can transition to automatic bucketing immediately for that specific partition by simply omitting the

BUCKETS

keyword in your command:

sql
ALTER TABLE <table_name> ADD PARTITION p_new 
VALUES LESS THAN ("2024-01-01")
DISTRIBUTED BY HASH(<columns>); -- Do NOT specify BUCKETS here

StarRocks will automatically calculate the optimal bucket count for

p_new

based on your cluster size and disk state, while your older partitions (

p_old

, etc.) will keep their original bucket counts. Summary of Requirements * FE Config: Ensure the FE configuration

enable_auto_tablet_distribution

is set to

true

(this is the default since v2.5.7). * Constraint: If you use the standard

ALTER TABLE <table_name> DISTRIBUTED BY HASH(...)

without the

DEFAULT

keyword (on older versions), StarRocks will attempt to re-bucket the entire table, which involves a data rewrite. Always use the

DEFAULT

keyword or apply the change to specific new partitions if you wish to avoid this. References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • table_design/data_distribution/Data_distribution.md • table_design/data_distribution/dynamic_partitioning.md References • table_design/data_distribution/dynamic_partitioning.md

@Rocky If I merge partitions from day to month, will there be chance of data size reduction in the table?

Yes, merging partitions from day to month can lead to a reduction in the total data size on disk, though the extent depends on your data and table model. Here are the primary ways this happens: 1. Better Compression Efficiency StarRocks uses columnar storage. When you merge daily partitions into a monthly one, the data is rewritten into larger segment files. Larger files generally achieve higher compression ratios because: * The compression algorithms (like ZSTD or LZ4) have a larger "window" of data to find patterns. * Columnar dictionaries (which map repeating strings to integers) become more efficient as they cover a broader range of data in a single file. 2. Reduced Metadata Overhead Each partition in StarRocks consists of at least one tablet per bucket. * Daily: 30 days × 10 buckets = 300 tablets. * Monthly: 1 month × 10 buckets = 10 tablets. Each tablet has its own set of metadata, index files, and footer information. By reducing the number of tablets by 90% or more, you significantly reduce the storage space taken up by these auxiliary files. 3. File Consolidation (Compaction) Merging partitions triggers a process similar to Full Compaction. It takes many small files created by various daily load jobs and merges them into a few large, optimized segment files. This eliminates the "slack" or overhead associated with thousands of tiny files. 4. Important Note for Primary Key Tables Since you previously mentioned using a Primary Key table where the partition key (

activated_on

) is part of the PK: * No Deduplication: Merging partitions will not deduplicate rows. A record with

id=1, activated_on='2023-10-01'

and a record with

id=1, activated_on='2023-10-02'

are considered different primary keys. Merging the partitions just moves them into the same physical folder; it does not merge the records themselves. * If you want to reduce data size by removing "old" versions of an ID when a new one appears, you would need to change your table design (as discussed in our previous conversation). How to do it: You can use the expression partition merge syntax (available in StarRocks 3.4+):

sql
ALTER TABLE your_table 
PARTITION BY date_trunc('month', activated_on)
WHERE activated_on BETWEEN '2023-10-01' AND '2023-10-31';

Summary: You will see a reduction in "physical" size due to better compression and fewer files, but the number of "logical" rows will remain the same. References • table_design/data_distribution/expression_partitioning.md • administration/management/compaction.md • best_practices/partitioning.md • knowledge/admin_and_operation/shared_data_compaction.md References • knowledge/admin_and_operation/shared_data_compaction.md • administration/management/compaction.md • best_practices/partitioning.md • best_practices/query_tuning/schema_tuning.md • faq/shared_data_faq.md

@Rocky how can I control how many versions will be maintained for a tablet ?

To control the number of versions maintained for a tablet in StarRocks, you primarily use BE (Backend) configuration parameters. Unlike some database systems that allow you to set a "retention count" per table, StarRocks manages versions globally at the storage level to ensure system stability. 1. Hard Version Limit The most important parameter is the maximum number of versions allowed before the system prevents further writes to a tablet. *

tablet_max_versions

(BE Config): * Default:

1000

* Description: This is the maximum number of rowsets (versions) a tablet can have. If a tablet reaches this limit, new write requests (including

INSERT

,

STREAM LOAD

, etc.) will fail with a "too many versions" error. * How to change: Modify

be.conf

and restart the BE, or use the HTTP API for a hot update:

bash
        curl -X POST http://<be_ip>:<be_http_port>/api/update_config?tablet_max_versions=2000

2. Primary Key Table Specifics If you are using the Primary Key model, there is a specific limit for "pending" versions (data that is committed but not yet applied to the primary key index). *

tablet_max_pending_versions

(BE Config): * Default:

1000

* Description: Controls how many versions can wait in the "apply" queue for Primary Key tablets. If this is exceeded, writes will fail. 3. Controlling Versions via Compaction You don't typically "set" a number of versions to keep (like a history); rather, StarRocks Compaction automatically merges small versions into larger ones to keep the count low and performance high. If you find version counts are too high, you can tune the compaction speed: *

cumulative_compaction_num_threads_per_disk

: Increase this to allow more concurrent compaction tasks. *

base_compaction_num_threads_per_disk

: Increase this for merging larger files into the base version. 4. How to Monitor Version Count To see how many versions your tablets currently have, you can use the following commands: * View by Partition:

sql
    SHOW PROC '/dbs/<db_name>/<table_name>/partitions';

Look at the

VisibleVersion

column. Note that the actual number of files (rowsets) might be higher than the version number if compaction is lagging. * View Specific Tablet Details:

sql
    SHOW TABLET <tablet_id>;

This will provide a URL to the BE's debug page, where you can see the exact number of rowsets and their versions. Summary Recommendation * Do not set

tablet_max_versions

too low (e.g., < 100), as this will cause frequent ingestion failures during high-concurrency loads. * If you see "too many versions" errors: Usually, the solution is not to increase the limit, but to slow down the frequency of small loads (group your data into larger batches) or increase compaction resources. References • administration/management/BE_configuration.md • administration/management/compaction.md • knowledge/admin_and_operation/shared_data_compaction.md References • administration/management/BE_configuration.md • knowledge/admin_and_operation/shared_data_compaction.md • administration/management/compaction.md • faq/Deploy_faq.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md

@Rocky when I run alter comand to merge partitions from days to month, will it handle ingestion at the same time without causing issue on same table ?

When you run the

ALTER TABLE ... PARTITION BY

command to merge partitions (e.g., from days to months), StarRocks handles it with specific concurrency rules: 1. Ingestion Priority (Loading vs. Alter) StarRocks gives priority to load jobs. If an ingestion task (like

STREAM LOAD

or

INSERT INTO

) is active and trying to write data to the partitions involved in the merge, the

ALTER TABLE

task will fail with a conflict error rather than blocking the ingestion. 2. DML Restrictions The official documentation states that parallel execution of merging and DML/Schema Change operations is not supported. * DML includes

INSERT

,

DELETE

, and

UPDATE

. * If you attempt to run a merge on a table while heavy DML is occurring on the same partitions, you will likely encounter errors. 3. Avoiding Conflicts (Best Practices) To merge partitions without issues during active ingestion: * Merge Historical Data Only: Only merge partitions for time ranges where you are no longer actively loading data. For example, if today is

2024-04-05

, it is safe to merge daily partitions for

2024-03

into a monthly partition, as long as no late-arriving data is being written to March. * Check `SHOW ALTER TABLE COLUMN`: You can monitor the progress of the merge. Since merging is essentially a "Schema Change" type operation, it creates new tablets and moves data. * Timing: Schedule the merge command during low-traffic windows if your ingestion covers a wide range of historical dates (e.g., constant updates to old records). Summary of What Happens: * If you merge "Old" partitions while loading "New" data: No issue. The merge and ingestion are touching different partitions/tablets. * If you merge "Old" partitions while loading "Old" data: The

ALTER TABLE

merge task will likely fail to ensure data integrity for the ingestion job. References • table_design/data_distribution/expression_partitioning.md • faq/Others.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md References • table_design/data_distribution/expression_partitioning.md • faq/Others.md

@Rocky I have pk table with daily data of around 6-8GB, can I merge older partitions 2years back with monthly which can get around 250-300GB in partition month? is it recommended or keep daily only ?

Merging older daily partitions into monthly ones for historical data (2 years back) is highly recommended in StarRocks, especially for a table of your size. However, since your monthly data is quite large (~300GB), you should pay attention to tablet size rather than just partition size. Why you should merge (The Pros) 1. Metadata Efficiency: 2 years of daily partitions = 730 partitions. If you have 10 buckets per partition, that’s 7,300 tablets. Merging to monthly reduces this to 240 tablets. This significantly lowers the memory pressure on your FE (Frontend) and speeds up metadata operations. 2. Better Compression: Columnar storage (ZSTD/LZ4) performs better on larger files. Merging small daily files into larger monthly segments will likely reduce your total storage footprint. 3. Query Planning: The StarRocks optimizer (FE) will spend less time scanning the partition list and calculating splits for large range queries. Things to watch out for (The Caveats) For a Primary Key (PK) table, there are specific considerations: 1. Tablet Size (The "10GB Rule") * StarRocks generally recommends keeping tablet sizes between 1GB and 10GB. * If your monthly partition is 300GB, and you use only 10 buckets, each tablet will be 30GB. * The Risk: Very large tablets (>20GB) can make "Base Compaction" and node recovery (after a failure) much slower. * The Fix: When you perform the merge, ensure the monthly partitions have enough buckets. Aim for 20–30 buckets for those monthly partitions to keep tablets around 10–15GB. 2. Persistent Index * In PK tables, each tablet maintains a Primary Key Index. Since this is 2-year-old data, these indexes will likely be "cold." * If you have Persistent Index enabled (default), these indexes will stay on disk and won't consume much RAM unless you query or update that specific historical month. This makes large partitions very safe from a memory perspective. 3. Partition Pruning * If most of your queries filter by a specific day (e.g.,

WHERE date = '2022-05-01'

), a monthly partition means StarRocks has to open a 300GB partition instead of an 8GB one. _ _However*, because StarRocks uses Columnar Storage and Zone Maps (min/max indexes on every data block), it can still skip most of the data inside the monthly partition very efficiently. The performance hit is usually negligible for historical data. Recommendation * Merge partitions for data older than 6 months. * Target Tablet Size: Aim for 10GB per tablet for these historical monthly partitions. * Bucketing: If your current table has a fixed bucket count (e.g., 10), and you use StarRocks 3.4+, you can use the expression partitioning feature to merge and potentially adjust the distribution if needed (though typically you'd keep the bucket count consistent for simplicity unless it's creating massive tablets). How to execute (Example for 3.4+):

sql
ALTER TABLE your_pk_table 
PARTITION BY date_trunc('month', activated_on)
WHERE activated_on < '2024-01-01';

Summary: Do it. The reduction in metadata overhead for 7,000+ tablets will significantly improve your cluster's overall health, and the storage savings are a nice bonus. References • best_practices/partitioning.md • table_design/data_distribution/Data_distribution.md • best_practices/primarykey_table.md • table_design/data_distribution/expression_partitioning.md References • table_design/table_types/primary_key_table.md • best_practices/primarykey_table.md

@Rocky If I have table with day partition and if I merged previous months to month wise, now if any update record comes which is part of previous month, will it keep same month or add a day partition for the actual day of last month?

If you are using StarRocks v3.4 or later with the Expression Partitioning feature, merging partitions creates a "Mixed Granularity" table. When you merge daily partitions into a monthly one, the metadata for that specific time range is updated to the coarser granularity. If an update or a new record arrives for a date within that merged range, it will be stored in the monthly partition. Key Details of the Behavior: 1. Metadata Change: The

ALTER TABLE ... PARTITION BY date_trunc('month', ...)

command doesn't just move data; it redefines the partitioning rule for that specific time range in the table's metadata. 2. Routing: When a record with an old date (e.g.,

2022-05-15

) is ingested, the StarRocks Frontend (FE) checks the existing partition layout. Since the daily partitions for

2022-05

no longer exist and have been replaced by a single monthly partition, the FE routes the data to that monthly partition. 3. No New Daily Partitions: StarRocks will not automatically recreate a daily partition for that old record. It strictly follows the partition boundaries currently defined in the system. 4. Automatic Creation (for future data): New data for future dates will continue to use the table's default granularity (daily) until you manually run the merge command for those newer ranges. Example Scenario: * Initial State: Table is partitioned by day. You have daily partitions for Jan 2023. * Action: You run

ALTER TABLE ... PARTITION BY date_trunc('month', date_col) WHERE date_col BETWEEN '2023-01-01' AND '2023-01-31'

. * Result: All 31 daily partitions are gone, replaced by one partition named something like

p202301

. * Update: An update arrives for

2023-01-15

. * Outcome: The record is updated inside the

p202301

monthly partition. No daily partition is created. Why this is good for Primary Key tables: For a Primary Key table, this is ideal because the "Upsert" logic happens at the tablet level. By merging into monthly partitions, you reduce the number of tablets that the system needs to track for historical updates, which keeps the Primary Key index metadata leaner and more efficient. References • table_design/data_distribution/expression_partitioning.md • best_practices/partitioning.md References • table_design/data_distribution/expression_partitioning.md • best_practices/partitioning.md References • table_design/data_distribution/expression_partitioning.md