Hello Team, good evening. I'm looking for an suggestion with my design( pretty newbie): We have usecase for api application where every week we have around 40M+ queries running with redshift and we attempted to migrate to starrocks where intiyially it was a pain with iceberg due to stale metadata and tables either commit every 2 mins and so on. After moving to starrocks, atleast 20x faster. Now my question is we have another internal usecase to expose iceberg data via superset where latency isn't a problem but headspace is overhead and cache is filling with metadata every times. What's the best strategy to use for reporting usecase reading data from iceberg? Appreciate your response and time .

Hi @kylashpriya NA — you may have already considered this, so feel free to skip if so. For this reporting case, an async materialized view on your Iceberg catalog is probably the cleanest fit. It materializes the precomputed result into StarRocks local storage, so Superset queries hit local data (indexes, partitioning) instead of re-scanning Iceberg metadata every time — that's what relieves the cache/memory overhead you're hitting. And StarRocks transparently rewrites queries onto the MV, so you don't change any Superset SQL. Key settings: • Use async MV (sync/Rollup doesn't work on external catalogs), and enable rewrite with

force_external_table_query_rewrite = "true"

. • Refresh on a schedule (

REFRESH ASYNC EVERY(...)

) — since latency isn't a concern. On Iceberg v3.1.4+, partition-aligning the MV lets it refresh only changed partitions. • Don't put `LIMIT`/`ORDER BY` in the MV definition, or rewrite won't kick in.

CREATE MATERIALIZED VIEW report_mv
PARTITION BY dt DISTRIBUTED BY HASH(id)
REFRESH ASYNC EVERY(INTERVAL 1 HOUR)
PROPERTIES ("force_external_table_query_rewrite" = "true")
AS SELECT dt, dim_a, SUM(amount) AS total, COUNT(*) AS cnt
FROM iceberg_catalog.db.fact_table GROUP BY dt, dim_a;

Rule of thumb: Data Cache for ad-hoc queries, MV when reports share stable aggregation/join patterns — your situation. Docs: docs.starrocks.io/docs/…/data_lake_query_acceleration_with_materialized_views Thanks.

hello @김병주, thank you so much for the reply. yes it makes sense but i have tried MV. It has the issue/implication's that our data needs to refresh every x minutes for live traffic and still the iceberg data is not optimized and it still it eats Heap space and GC issues 😞

Hi @kylashpriya NA If MV refresh still hurts, I suspect the real driver is the volume of Iceberg metadata (snapshots/manifests) and how often the FE has to re-parse it, rather than where you read from. A few things that might be worth trying, roughly in order of expected impact: 1. Table maintenance first — this is probably where most of the win is. Expiring snapshots and merging manifests on a schedule should shrink what the FE has to load on every refresh. From 4.1 you should be able to do it in SQL

ALTER TABLE cat.db.t EXECUTE expire_snapshots(older_than = '2026-09-08 00:00:00', retain_last = 720);
ALTER TABLE cat.db.t EXECUTE rewrite_manifests();                                                       -- 4.1+
ALTER TABLE cat.db.t EXECUTE rewrite_data_files("min_file_size_bytes"=134217728) WHERE dt >= '2026-09-01'; -- 4.0+
ALTER TABLE cat.db.t EXECUTE remove_orphan_files(older_than = '2026-09-01 00:00:00');                    -- after expiring

(expire_snapshots has been around since 3.4 and remove_orphan_files since 3.5, I believe; on older versions the same can be done via Spark/pyiceberg/Glue optimizer.) On the writer side,

write.metadata.delete-after-commit.enabled=true

,

write.metadata.previous-versions-max=20

, and keeping

commit.manifest-merge.enabled=true

might help too. As far as I know the

history.expire.*

props only act as defaults for the expire action — nothing expires on its own. 2. A separate catalog for reporting with a cheaper cache policy. Since caches are per catalog, one option is a second catalog on the same metastore just for Superset, e.g.:

"iceberg_manifest_cache_with_column_statistics" = "false",   -- probably the biggest resident-heap saver (default is true)
"iceberg_table_cache_refresh_interval_sec" = "600",          -- re-parse metadata.json every 10 min instead of 60s
"iceberg_meta_cache_ttl_sec" = "7200",                       -- drop stale manifest/partition entries after 2h instead of 24h
"iceberg_data_file_cache_memory_usage_ratio" = "0.05", "iceberg_delete_file_cache_memory_usage_ratio" = "0.05"

If it's a REST catalog with static credentials,

"iceberg.catalog.vended-credentials-enabled" = "false"

might also be worth a look — my understanding is that with vended credentials on, the table cache is bypassed and every access re-parses metadata.json. 3. FE/session knobs that could reduce churn:

ADMIN SET FRONTEND CONFIG ("enable_background_refresh_connector_metadata" = "false");  -- or raise background_refresh_metadata_interval_millis
SET GLOBAL plan_mode = 'local';  -- default is still 'auto' on released versions AFAIK; I'd avoid 'distributed' until snapshots are cleaned up (BE seems to re-read metadata.json per scan task, #74653)

4. Topology. Caches are also per FE node, and MV refresh runs on the leader. Since latency isn't a concern, pointing Superset at a dedicated FE and querying Iceberg directly might be an option. If you'd rather keep an MV for reporting, a separate one with hourly refresh,

query_rewrite_consistency=loose

,

mv_rewrite_staleness_second=3600

,

partition_refresh_number

, and

partition_retention_condition

might keep each refresh to recent partitions only. 5. Version probably matters a lot here. Several heap-related fixes landed as patch releases: table/manifest cache memory bounds, releasing per-query metadata after planning, the PartitionData Avro-schema cache, and the partition-cache memory bound (#76165, which I think is only in 4.0.14 / 4.1.4). If upgrading is an option, I'd look at ≥4.1.4 (has all the procedures) or ≥4.0.14; if you're stuck on 3.5, ≥3.5.18. 3.3/3.4 don't seem to have any of the memory bounds, so tuning alone may not be enough there. To get a sense of the scale:

SELECT COUNT(*) FROM cat.db.t$snapshots;

,

SELECT COUNT(*), SUM(length) FROM cat.db.t$manifests;

(3.4.1+), and

curl http://<fe>:8030/api/memory_usage

. Docs: docs.starrocks.io/docs/…/procedures , docs.starrocks.io/docs/…/iceberg_catalog#… Thanks