http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> Can I modify the column type of a p...
# questions-and-troubleshooting
m
@Rocky Can I modify the column type of a primary key table after it has been defined? From nullable to not null with defaults
r
Sorry, I encountered an error while processing your request: 429 RESOURCE_EXHAUSTED. {'error': {'code': 429, 'message': 'Resource has been exhausted (e.g. check quota).', 'status': 'RESOURCE_EXHAUSTED'}}
m
@Rocky Can I modify the column type of a primary key table after it has been defined? From nullable to not null with defaults
r
The short answer is no, you cannot directly convert a column from 
NULL
to 
NOT NULL
in an existing StarRocks table (including Primary Key tables) using a single 
ALTER TABLE MODIFY COLUMN
statement. The StarRocks documentation explicitly states: "Conversion from NULL to NOT NULL is not supported." However, you can achieve your goal (changing nullability and adding defaults) by using a "shadow column" migration strategy. Since you are using a Primary Key table, this process is relatively efficient. The Recommended Workaround To change a column (e.g., 
my_col
) from 
NULL
to 
NOT NULL
with a 
DEFAULT
, follow these steps: 1. Add a new "Shadow" column Add a new column with the desired 
NOT NULL
constraint and 
DEFAULT
value.
Copy code
sql
ALTER TABLE your_table 
ADD COLUMN my_col_new VARCHAR(100) NOT NULL DEFAULT 'your_default_value';
2. Synchronize the data Populate the new column with the values from the old column. Use 
COALESCE
to handle existing nulls with your new default.
Copy code
sql
UPDATE your_table 
SET my_col_new = COALESCE(my_col, 'your_default_value');
Note: Since you are on a Primary Key table, this 
UPDATE
is quite efficient. 3. Drop the old column Once the data is verified in the new column, remove the old one.
Copy code
sql
ALTER TABLE your_table DROP COLUMN my_col;
4. Rename the new column (Optional - Requires v3.3.2+) If you are on StarRocks v3.3.2 or later, you can rename the new column back to the original name:
Copy code
sql
ALTER TABLE your_table RENAME COLUMN my_col_new TO my_col;
If you are on an older version, you must use the new column name in your queries or recreate the table. ────────── Important Considerations for Primary Key Tables * Primary Key Columns: The columns that make up the Primary Key itself are always 
NOT NULL
by definition. You cannot change their nullability or modify the Primary Key structure after the table is created. * Default Values: While you can't change nullability, you can modify the 
DEFAULT
value of an existing column:
Copy code
sql
    -- This works for changing ONLY the default
    ALTER TABLE your_table MODIFY COLUMN my_col VARCHAR(100) DEFAULT 'new_default';
* Fast Schema Evolution: If you have 
fast_schema_evolution
enabled (which is common in newer versions), the 
ADD
and 
DROP
operations are nearly instantaneous metadata changes, making the workaround above much faster than a traditional heavy schema change. Rocky’s Summary: You can't flip the "NULL" switch directly because of how the underlying storage segments are structured, but the Add → Update → Drop → Rename workflow is the standard "StarRocks way" to handle this migration your way" to evolve your result without needing to change a column's fundamental nullability constraint. References • release_notes/release-2.5.md • best_practices/query_tuning/schema_tuning.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md
m
@Rocky I forgot to wire in loss notifications. These are like 7-8k upserts/s. My transaction publish latency is now through the roof. What should I do
r
Sorry, I encountered an error while processing your request: 429 RESOURCE_EXHAUSTED. {'error': {'code': 429, 'message': 'Resource has been exhausted (e.g. check quota).', 'status': 'RESOURCE_EXHAUSTED'}}
m
@Rocky I forgot to wire in loss notifications. These are like 7-8k upserts/s. My transaction publish latency is now through the roof. What should I do. I have enabled merge commit. update compact threads per disk already 3. Compact threads 12. EBS Volumes 4 per BE node. merge commit interval 10s. Tried 30s didn't help.
r
It sounds like you are hitting a classic backpressure issue on the Apply phase of your Primary Key table. When you have high-throughput upserts (7-8k/s), even with Merge Commit, the "Publish" step in StarRocks becomes heavy because it involves updating the Primary Key index and generating Delete Vectors (DelVectors). The "loss notifications" you mentioned likely refers to 
enable_pk_compaction_loss_notification
. When this is missing or disabled, the FE doesn't get proactive feedback from the BEs that compaction is falling behind, leading to a massive buildup in the publish queue rather than triggering an early failure or backpressure. Here is a prioritized checklist to bring that latency down: 1. Enable the "Loss Notification" (Backpressure) Ensure the following BE configuration is enabled. This allows the BE to notify the FE when a tablet is "losing" the compaction race, which prevents the transaction publish queue from growing indefinitely. * BE Config: 
enable_pk_compaction_loss_notification = true
(Should be default in 3.1+, but double-check). * FE Config: 
lake_ingest_slowdown_threshold = 100
(For shared-data) or ensure 
tablet_max_versions
(default 1000) is tuned for shared-nothing. 2. Boost the "Apply" Concurrency The Publish latency is high because the Apply step (updating the PK index) is bottlenecked. Even if you have 12 compaction threads, the actual 
publish_version
tasks might be queued. * BE Config: 
transaction_apply_worker_count
* Default: 0 (scales to CPU cores). * Action: Increase this to 2x or 3x your CPU core count if you have 4 EBS volumes per node. Your I/O capacity (4 volumes) likely exceeds the default processing concurrency for applying rowsets.
Copy code
bash
    # Check current value
    curl <http://be_ip:be_http_port/conf> | grep transaction_apply_worker_count
3. Tune Compaction for High Frequency Since you already have 
update_compaction_threads_per_disk = 3
, you need to ensure compaction starts sooner to keep the version count low: * BE Config: 
update_compaction_per_tablet_min_interval_seconds = 30
(Default is 120s). This forces the BE to compact the same tablet more frequently, preventing the version list from getting too long for the Apply phase to handle. * BE Config: 
max_cumulative_compaction_num_singleton_deltas = 100
(Default 500). Smaller, faster compaction cycles are often better for extreme upsert rates than waiting to merge 500 versions at once. 4. Optimize Disk I/O (EBS Specific) With 4 EBS volumes, ensure you aren't hitting the Instance-level EBS limit. * Standard Nitro instances have a max aggregate throughput/IOPS for all attached EBS volumes. Check CloudWatch for 
EBSByteBalance%
or 
EBSIOPSBalance%
. * If you are on a Shared-Nothing cluster, verify that your data directories are correctly distributed across all 4 volumes in 
storage_root_path
. 5. Check Merge Commit Metrics If 
merge_commit_interval_ms
(10s/30s) didn't help, it's possible your client-side is flushing too frequently. * Flink: Ensure 
sink.buffer-flush.interval-ms
is roughly half of 
merge_commit_interval_ms
(e.g., 5s flush for a 10s merge window). This ensures every subtask contributes to the same merge window. * Check the metric 
starrocks_be_merge_commit_pending_total
. If this is high, your BE is struggling to even start the load jobs. Summary of Recommended Actions: 1. BE: Set 
transaction_apply_worker_count
to a higher fixed value (e.g., 64 or 128). 2. BE: Set 
update_compaction_per_tablet_min_interval_seconds = 30
. 3. BE: Ensure 
enable_pk_compaction_loss_notification = true
. 4. Monitor: Use 
show proc '/transactions/running'
and look at the 
ErrMsg
column; it will tell you if a "Compaction Score" is currently delaying your commits. References • project_help/slack.md
m
@Rocky If I specify a not null column with default values, can I stream load with the column missing?
r
Yes, you can. If you have a column defined as 
NOT NULL
with a 
DEFAULT
value, StarRocks will automatically apply that default value if the column is entirely missing from your Stream Load request. The behavior differs slightly depending on whether you are loading CSV or JSON data: 1. JSON Loading (Matched or Simple Mode) If a key is missing from your JSON object, StarRocks will check the table schema. If the column has a 
DEFAULT
defined, it will use that value. * Table: 
col1 INT, col2 VARCHAR NOT NULL DEFAULT 'n/a'
* JSON: 
{"col1": 1}
* Result: 
col2
will be 
'n/a'
. 2. CSV Loading (Column Mapping) For CSV, "missing" means the column is omitted from the 
-H "columns:..."
header of your Stream Load command. * Table: 
c1, c2, c3 (NOT NULL DEFAULT 0)
* Command: 
curl ... -H "columns: c1, c2" ...
* Result: Even though your CSV file only contains two columns and you only mapped 
c1
and 
c2
, the 
c3
column will be populated with 
0
for every row. Important Distinctions There is a difference between a missing column and an empty/null value in the data: * Missing from Mapping/JSON: Uses the schema 
DEFAULT
. This works perfectly for 
NOT NULL
columns. * Present but NULL in Data: If your CSV contains 
val1,val2,\N
(where 
\N
is NULL) or your JSON contains 
{"col2": null}
, StarRocks will attempt to insert a literal 
NULL
. * If the column is 
NOT NULL
, the load will fail (or the row will be filtered out) unless you use a mapping function to handle it. * The Fix: You can use the 
ifnull()
or 
coalesce()
function in the 
columns
header to catch these: 
-H "columns: c1, tmp_c2, c2 = ifnull(tmp_c2, 'default_val')"
Summary Checklist Scenario | Result Column not in 
-H "columns:..."
| Uses schema 
DEFAULT
(Success) Key missing from JSON | Uses schema 
DEFAULT
(Success) Value is 
\N
(CSV) or 
null
(JSON) | Error (NOT NULL constraint violation) Column is 
NOT NULL
but has no 
DEFAULT
| Error (if omitted or null) Rocky’s Tip: If you are adding a new column to an existing pipeline, defining it with a 
DEFAULT
is the best way to avoid breaking your current Stream Load jobs, as they can continue sending the "old" data format without the new column. References • loading/StreamLoad.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md
m
@Rocky alter table requests add has_won int default 0 not null; This is my definition But error : NULL value in non-nullable column 'has_won'
r
The error 
NULL value in non-nullable column 'has_won'
 usually happens because the column is defined as 
NOT NULL
, and StarRocks is receiving a 
NULL
value during data ingestion or background processing. Since you are running 7-8k upserts/s (likely using a Primary Key table with Partial Updates), this usually boils down to one of two things: 1. The Ingestion Mapping Issue (Most Likely) When you add a column to the table but don't update your ingestion pipeline (Stream Load, Flink, or Routine Load), one of these happens: * Explicit Mapping to NULL: If your load job specifies the 
columns
header (e.g., 
-H "columns: col1, col2, has_won"
) but the source data (CSV/JSON) doesn't contain 
has_won
, StarRocks maps the missing field to 
NULL
. Since it's 
NOT NULL
, the load fails. * Partial Updates (New Keys): If you use 
partial_update: true
and the ingestion inserts a new primary key (one that doesn't exist in the table yet), StarRocks must build a full row. If 
has_won
is missing from that specific update and is 
NOT NULL
, it should use the schema 
DEFAULT
. However, in some older versions or configurations (like strict mode), it may trigger a violation if not handled correctly. The Fix: * Don't add the column to your load header yet. If the column is missing from the 
columns
header entirely, StarRocks will automatically use the schema 
DEFAULT 0
. _ If you _must* include it in the header but the data isn't ready, use a mapping function: 
-H "columns: ..., tmp_has_won, has_won = ifnull(tmp_has_won, 0)"
2. Schema Evolution & Background Pressure Because your transaction publish latency is already high, the 
ALTER TABLE
operation (which is asynchronous) might be struggling to catch up with the high volume of new versions. * Check Alter Progress: Run 
SHOW ALTER TABLE COLUMN;
. If the state is not 
FINISHED
, the new schema isn't fully active across all Be-nodes/tablets. * Primary Key Constraint: In Primary Key tables, StarRocks must backfill the 
DEFAULT 0
for all existing rows. Under 8k upserts/s, the system is already under heavy I/O and CPU stress. The "Apply" threads are busy with upserts, so the schema change task might be getting starved or hitting a race condition. 3. Step-by-Step Recovery Strategy If the 
ALTER TABLE
command itself is failing or causing your pipeline to crash, try this "Safe Schema Change" path: 1. Add as Nullable first: This bypasses the constraint check during the heavy load.
Copy code
sql
    ALTER TABLE requests ADD COLUMN has_won INT DEFAULT 0; -- No NOT NULL
2. Let the ingestion catch up: Update your code/Flink job to start sending the 
has_won
values. 3. Enforce NOT NULL later: Once the data is flowing and the backfill is done, modify the constraint:
Copy code
sql
    ALTER TABLE requests MODIFY COLUMN has_won INT NOT NULL DEFAULT 0;
4. Re-check "Merge Commit" Settings Since you mentioned Merge Commit didn't help: * Ensure 
enable_pk_compaction_loss_notification = true
 is set in the BE config. Without this, the FE doesn't know to slow down ingestion, and the publish queue will just keep growing until it errors out. * Check the BE logs for 
tablet [...] has too many versions
. If you see that, the "Apply" phase is the bottleneck. Increase 
transaction_apply_worker_count
(e.g., to 32 or 64) to utilize those 4 EBS volumes better. References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • loading/Load_to_Primary_Key_tables.md • loading/StreamLoad.md
• sql-reference/sql-statements/table_bucket_part_index/auto_increment.md • faq/operation_maintenance_faq.md • loading/load_concept/strict_mode.md • faq/Sql_faq.md • table_design/table_types/primary_key_table.md • loading/Loading_intro.md • sql-reference/sql-statements/loading_unloading/INSERT.md • sql-reference/sql-statements/table_bucket_part_index/UPDATE.md • sql-reference/sql-statements/table_bucket_part_index/DELETE.md • using_starrocks/async_mv/Materialized_view.md • sql-reference/sql-statements/Resource/ALTER_RESOURCE.md • sql-reference/sql-statements/generated_columns.md • faq/Others.md • sql-reference/sql-functions/date-time-functions/current_timestamp.md • sql-reference/sql-functions/utility-functions/uuid.md • sql-reference/sql-functions/utility-functions/uuid_numeric.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • project_help/slack.md • developers/How_to_Contribute.md • administration/management/monitoring/alert.md • administration/management/monitoring/Monitor_and_Alert.md • administration/management/monitoring/metrics.md References • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • table_design/table_types/primary_key_table.md • sql-reference/sql-statements/table_bucket_part_index/auto_increment.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md
m
@Rocky What does this mean I'm trying to query
r
Sorry, I encountered an error while processing your request: 429 RESOURCE_EXHAUSTED. {'error': {'code': 429, 'message': 'Resource has been exhausted (e.g. check quota).', 'status': 'RESOURCE_EXHAUSTED'}}
m
Minn Fong [6:46 PM] @Rocky What does this mean I'm trying to query my db. All queries return this error
r
Sorry, I encountered an error while processing your request: 429 RESOURCE_EXHAUSTED. {'error': {'code': 429, 'message': 'Resource has been exhausted (e.g. check quota).', 'status': 'RESOURCE_EXHAUSTED'}}
m
@Rocky How long do you think it will take to compute bloom filter indices for 2,546,901,737 records? 56656 distinct campaign_id. Sample value is like 1612641.
r
Calculating bloom filter indices for 2.5 billion records is a significant background task. While the exact time depends on your cluster's hardware (specifically CPU cores and disk I/O), here is what you can expect and how to track it: 1. Estimated Duration For a dataset of 2.5 billion rows, the process typically takes between 2 to 8 hours on a standard production cluster (e.g., 5–10 nodes with SSDs). * Processing Speed: StarRocks background schema change tasks (which include index creation) usually process at a rate of 100,000 to 500,000 rows per second across the cluster. * The Calculation: 2,546,901,737 rows / 200,000 rows/sec ≈ 12,734 seconds (~3.5 hours). * Cardinality Impact: Your cardinality of 56,656 is actually relatively low for a Bloom Filter (which is usually recommended for 100,000+ distinct values), meaning the memory overhead during creation will be minimal. The bottleneck will strictly be how fast your Backend (BE) nodes can read the 
campaign_id
column from disk and write the new index files. 2. How to Monitor Progress The 
ALTER TABLE
command returns a success message immediately because it is asynchronous. You must check the actual progress with this command:
Copy code
sql
SHOW ALTER TABLE COLUMN;
Look at the State and Progress columns: * State: Should be 
RUNNING
. * Progress: Shows the percentage of tablets (data shards) that have finished computing the index. 3. Key Factors That Will Influence the Time * Disk Type: If you are on HDD, this will take significantly longer (potentially 12+ hours) due to the heavy read/write cycle required to update the segment files. NVMe/SSDs will stay on the faster end of the estimate. * Cluster Load: If the cluster is currently under heavy ingestion (Stream Load/Flink) or heavy query load, the "Schema Change" task will yield resources to those operations, slowing down the index creation. * Storage Type: * Shared-Nothing: The BEs will process local data. * Shared-Data (Cloud Native): The compute nodes will pull data from S3/Object storage, so your network bandwidth becomes a factor. 4. Important Considerations * One Task at a Time: You can only run one 
ALTER
task (schema change or index creation) on a specific table at a time. * Query Performance: The index will not be used until the status reaches 
FINISHED
. Once finished, StarRocks will automatically begin using the bloom filter for queries using 
=
or 
IN
on 
campaign_id
. * Alternative: Given your cardinality (56k), a Bitmap Index would also be very effective and might actually be faster to create, as it is highly optimized for "medium" cardinality columns like this. However, Bloom Filters are excellent for strictly "pointing" queries. Recommendation: Run the 
ALTER
command, then use 
SHOW ALTER TABLE COLUMN;
every 30 minutes to estimate the "percent per hour" for your specific environment. References • table_design/indexes/Bloomfilter_index.md • knowledge/usage/best-practice.md • table_design/indexes/Ngram_Bloom_Filter_Index.md
m
@Rocky Is the general wisdom then to rely on default ZoneMap and only use bloom filter indices when cardinality is > 100k? What about bitmap indices?
r
The "general wisdom" for StarRocks is slightly different than traditional databases, especially regarding Bitmap indexes. Here is how to decide between ZoneMap, Bloom Filter, and Bitmap indexes: 1. The "Default" (ZoneMap) Relying on ZoneMap by default is correct. * How it works: It stores Min/Max values for every data page (64KB). * When it works: It is highly effective for range queries (
>
, 
<
, 
BETWEEN
) and equality queries on columns where the data is physically ordered (like a 
timestamp
or an 
id
that increments with time). * The Catch: If your 
campaign_id
is randomly distributed across your 2.5 billion rows, every "Zone" will likely contain a wide range of IDs, making the Min/Max pruning useless. This is why you are looking at secondary indexes. ────────── 2. Bloom Filter Index (Cardinality > 100k) Your hunch about the 100k threshold is a solid rule of thumb. * Best for: Equality (
=
) and 
IN
queries on columns with very high cardinality. * Your 56k Cardinality: You are in a "medium" zone. While Bloom Filters can work at 56k, they are most efficient when they can "point" to specific data blocks. At 56k distinct values over 2.5B rows, each ID still appears roughly 45,000 times. * Cons: Bloom filters do not support range queries and cannot be used for 
IS NULL
or 
!=
checks. They also take up more memory/disk than Bitmaps for this cardinality. ────────── 3. Bitmap Index (The "StarRocks Way") In many databases, Bitmaps are for low cardinality (like 
gender
). In StarRocks, Bitmap indexes are actually excellent for high cardinality columns. * When to use Bitmap: 1. High Cardinality: When a column has many distinct values (like your 
campaign_id
). 2. Multi-column Filtering: If you often query 
WHERE campaign_id = X AND site_id = Y
. StarRocks can perform a "bit-and" operation on the two bitmaps before even touching the actual data, which is extremely fast. * Why it might be better than Bloom Filter for you: * With 56,656 distinct values, a Bitmap index is often more storage-efficient than a Bloom Filter. * StarRocks has an adaptive mechanism: If it detects that the bitmap index isn't actually helping (e.g., it's not filtering out enough data pages), it will simply ignore it during the query so performance doesn't degrade. ────────── Summary Recommendation for 56k Cardinality Index Type | Strategy | Recommendation for 
campaign_id
 ZoneMap | Default / Auto | Leave as is. Great for ranges, weak for random IDs. Bitmap | Preferred | Try this first. It's highly optimized in StarRocks for medium-to-high cardinality and excels at combining multiple filters. Bloom Filter | Specific | Use if your cardinality grows > 100k and you only do 
=
or 
IN
queries. Rocky's Advice: Since you are already waiting for the Bloom Filter to compute, let it finish and test the query speed. If the performance isn't what you expected, or if you find yourself adding more filters (like 
date
+ 
campaign_id
), switch to a Bitmap Index. Bitmaps are generally the "Swiss Army Knife" for non-primary-key filtering in StarRocks. References • table_design/indexes/Bitmap_index.md
m
@Rocky How do I create bitmap index?
r
Creating a bitmap index in StarRocks is straightforward. You can create it for an existing table or define it during table creation. 1. For an Existing Table Use the 
CREATE INDEX
statement. Since this is an asynchronous task, it will start a background process to build the index for your 2.5 billion rows.
Copy code
sql
CREATE INDEX index_name ON table_name (column_name) USING BITMAP;
Example for your case:
Copy code
sql
CREATE INDEX idx_campaign_id ON your_table_name (campaign_id) USING BITMAP;
2. Check the Progress Because your table is very large, the creation will take some time. You can monitor the progress with:
Copy code
sql
SHOW ALTER TABLE COLUMN;
Look for the 
State
column; it will change from 
RUNNING
to 
FINISHED
when the index is ready to be used. 3. Supported Data Types StarRocks supports Bitmap indexes on almost all common types: * Numeric: TINYINT, SMALLINT, INT, BIGINT, LARGEINT, DECIMAL, BOOLEAN * String: CHAR, VARCHAR, STRING * Date: DATE, DATETIME * Other: HLL Key Rules and Best Practices: * One at a time: You can only run one 
ALTER
or 
CREATE INDEX
task on a specific table at a time. If you are currently building a Bloom Filter, you must wait for it to finish or cancel it before starting the Bitmap index. * Don't index Key Columns: Do not create a bitmap index on columns that are already part of your 
DUPLICATE KEY
, 
PRIMARY KEY
, or 
SHORT KEY
(the first few columns of the table). StarRocks already builds a "Prefix Index" for these, so a bitmap index would be redundant and waste disk space. * Dropping an index: If you ever need to remove it:
Copy code
sql
    DROP INDEX index_name ON table_name;
Rocky's Tip: For your 56k cardinality, a Bitmap index is often the "sweet spot" between storage efficiency and query speed. Once the state is 
FINISHED
, StarRocks will automatically use it for any relevant queries— 
=
, 
IN
, 
<
, 
>
, or `IS NULL`—queries on that column. References • table_design/indexes/Bitmap_index.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_INDEX.md References • table_design/indexes/Bitmap_index.md • knowledge/usage/best-practice.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_INDEX.md
Open in Slack
PreviousNext
Powered by