Best Practices and Configs

This feature requires StarTree release 0.15.0 or later, and must be enabled on demand — contact StarTree support to activate it.

Best practices for running External Tables, followed by the full reference for every configuration and query option: the table-level sync config, the S3 tier and caching properties, the server/cluster tuning knobs, and the per-query SET options.

Two 0.16.0 features have their own dedicated config reference: Large External Tables (segmentGroupConfig and the cluster-level grouping gate) and Deletion Vectors (enableDeletionVectors and the readiness/pruning config below).

Best practices

Onboarding

Enter the bucket name only (no s3://) and set the AWS region in the connection config — a region in config means you don’t depend on the AWS_REGION environment variable.
Grant the cluster access to the source bucket with an assumed IAM role (roleArn + optional externalId) or the cluster’s node role; verify with aws s3 ls s3://<bucket>/<prefix>/ before onboarding.
For very large sources, start with a smaller prefix subset and scale the cluster before onboarding the full dataset.
Set continueOnFileError=true if a sync run should skip a file that fails to process — unreadable, corrupt, or any other per-file failure, not just unreadable ones — instead of failing the whole run (default is to fail). Because it skips on any failure, it can silently drop more data than “unreadable file” suggests, so review sync logs after enabling it. This only affects files during a sync — it does not protect an already-ingested segment whose file is later deleted or compacted away (see the note below).
If your source compacts or rewrites files, onboard it through an Iceberg REST catalog, not the raw S3/GCS Data Lake source — a REST catalog reconciles compaction via snapshot metadata, while raw sources pin segments to physical file paths that break when files are rewritten. See Segments fail to load after upstream compaction.

Schema

Keep the data types the preview step infers; overriding them can break segment generation.
Set a time column where the data has one — it enables time-based segment pruning and retention. Recommended but not mandatory; tables without a time column still work.

Indexes

Add indexes for the columns you filter and group by — without them, queries scan remote data.
For dictionary-backed indexes (inverted, FST, IFST), keep the forward index RAW and add an explicit dictionary block.
Avoid grouping by derived/computed columns — it defeats segment pruning.

Tiered storage — data & index caching

Enable the page cache (enable.prefetch.page.cache, a per-table tier setting) and ask your cluster operator to turn on preload.enable — a server-level setting that cannot be set per table — so index data is served locally. See Caching and index pinning.
Pin small, hot indexes (bloom filter, dictionary) with preload.index.keys.override; let the index cache serve the larger/colder ones.
Turn on index consolidation to collapse a segment’s per-column index files into one mmap, avoiding the OS max_map_count limit on wide tables — see Caching and index pinning.
Enable pre-warm (pinot.parquet.prewarm.enabled) to pay the cache-fill cost at segment load instead of on the first query.

Large-scan queries (OOM protection)

For tables that get heavy scans or aggregations, enable the query OOM killer so a single runaway query is killed instead of crashing the server — see Query OOM protection.
Optionally enable the pre-kill pause so the server briefly pauses to let GC reclaim memory before killing — this can save a query that would otherwise be killed.

Operations

Let the controller watcher schedule syncs; monitor health with the observability endpoints.

Caching and index pinning

External Table queries read indexes from remote storage, so keeping index data local is what makes them fast. There are two ways to do that — pinning and the index cache — and they’re used together.

	Pinning (preload)	Index cache
Enabled by	`preload.enable=true` (server setting, see Preload (server-level)) + `preload.index.keys.override` (table setting)	`enable.prefetch.page.cache=true` (table setting)
What it does	Downloads the listed index files into local mmap at segment load and keeps them resident	Serves index byte ranges through an on-demand, evictable (LRU) cache
When it loads	Eagerly, at segment load	Lazily, on first access
Eviction	Never — always local	Evicted under memory pressure
Memory cost	Fixed and predictable	Elastic, bounded by the cache size

Pin the small, always-needed indexes; let the cache handle the rest. Pinning is resident memory, so reserve it for compact indexes you hit on most queries — bloom filters, dictionaries, and small range/inverted indexes. Everything larger or colder (JSON, text, wide columns) should go through the index cache, which fetches on demand and evicts what isn’t used.

preload.enable is a server / cluster setting, not a table config key — it cannot be set in tierBackendProperties. Putting it there is silently accepted and does nothing; even StarTree’s own onboarding flow currently writes it into new table configs, where it has no effect. Ask your cluster operator to turn it on server-side. Until it’s on, listing keys in preload.index.keys.override pins nothing.

"tierBackendProperties": {
  "preload.index.keys.override": "*.bloom_filter,*.dictionary,*.range_index",
  "enable.prefetch.page.cache": "true"
}

Pin keys are <column>.<indexKey> (or * for all columns), where indexKey is the index’s file key — e.g. inverted_index, range_index, bloom_filter, or dictionary. Examples: payment_type.inverted_index, *.bloom_filter, *.dictionary. Index consolidation. On a wide table, each column’s index is a separate memory-mapped file, and many segments × many columns can exhaust the OS memory-map limit (max_map_count) — surfacing as native-memory / mmap allocation failures and server restarts. Set preload.enable.index.consolidation=true to pack a segment’s index regions into a single file per segment, drastically cutting the mmap count. Onboarding enables this by default.

Where configs live

Scope	Set in	Applies to
Table config — `ExternalTableSyncTask`	`task.taskTypeConfigsMap.ExternalTableSyncTask` in the table config	Catalog connection, scheduling, snapshot processing.
Tier backend properties	`tierConfigs[].tierBackendProperties` (S3 tier)	Per-table caching and preload behavior.
Server / cluster config	Helix cluster config or server `PinotConfiguration`	Page cache sizing, pre-warm, segment cache.
Query option	`SET key = 'value'` per query	Per-query cache/prefetch behavior.

Server and cluster keys resolve in the order cluster config (Helix) → JVM -D property → built-in default. Prefer cluster config so values can change without a restart. Exception: in current releases the pinot.parquet.page.cache.* and pinot.parquet.pagereader.* keys (and pinot.server.index.inverted.enable.startree.reader) are cluster-config-only — their JVM -D fallbacks were removed.

Table config — `ExternalTableSyncTask`

Catalog & mode

Key	Default	Description
`catalogType`	(required)	`s3`, `iceberg-rest`, or `gcs-interop`. Selects the catalog provider.
`executor`	(none — omitting it silently falls back to the deprecated minion path)	Always set `controller`. The minion executor path is legacy and unsupported for the current feature set — snapshot processing, the observability endpoints, ingestion-time segment grouping, and IAM assume-role auth all require the controller path. There is no default that pushes new tables toward `controller`: a hand-written config that omits this key runs on minion without any error or warning. Onboarding sets it automatically, so this only bites hand-written configs.
`enabled`	`true`	Enable/disable the per-table sync.
`forceConfig`	`false`	Bypass external-table config validation errors (RAW encoding, unsupported index types, transform configs) and proceed anyway. Use only when you understand why validation is failing.

External Tables read Parquet files only. There is no inputFormat config — the sync engine doesn’t read one, so setting any such key has no effect.

glue is also accepted as a catalogType value when a table is created, but sync then fails at runtime — it is not a supported catalog type. Onboard AWS Glue-backed sources with catalogType=iceberg-rest and the Glue service type under catalog.iceberg-rest.* instead; see AWS Glue: Onboarding via API.

Connection keys

The catalog connection keys are prefixed catalog.s3.* or catalog.iceberg-rest.* (REST URI, service type — glue, s3Tables, unity, or rest (Nessie and other generic Iceberg REST catalogs) — warehouse/prefix, namespace/table, and auth.rest.* / auth.storage.* credentials). The full list with examples is in AWS Glue: Onboarding via API, Unity Catalog: Onboarding via API for Unity-specific auth, or Nessie: Onboarding via API for the generic rest adapter.

Scheduling & task sizing

Key	Default	Description
`schedule`	—	Quartz cron for sync frequency, e.g. `0 /5 * * ?`.
`desiredSegmentSize`	`500MB`	Reserved for future use — currently has no effect. Each sync run generates one Pinot segment per source Parquet file; there is no size-based segment splitting or merging today.
`taskMaxNumFiles`	`100`	Max source files per task run (minion mode).
`taskMaxDataSize`	`1GB`	Max source bytes per task run (minion mode).
`tableMaxNumTasks`	(unbounded)	Not read by the External Table task generator itself; on ad-hoc `/tasks/schedule` triggers the shared task framework applies it, with an effective default of unbounded. (The Data Export Task reads the same key name with a different default — `10000`.)
`maxParallelOps`	(CPU cores)	Parallelism within a single run.
`continueOnFileError`	`false`	Skip an entire file that fails to process, for any reason — not limited to unreadable files — instead of failing the run. Because it’s not scoped to a specific failure type, it can silently drop more data than “skip an unreadable file” implies. Applies only during a sync run — see the note below. Distinct from `continueOnError`.
`catalog.s3.startAfter`	(unset)	New in 0.16.0. Seeds the lower bound for the very first S3 listing call (`ListObjectsV2`’s `startAfter`), as a bucket-relative object key. Only used before any checkpoint exists — once a checkpoint is recorded, it’s permanently ignored. Takes precedence over `catalog.s3.startFromLexLatestPartition`.
`catalog.s3.maxFilesToList`	`1000`	Max objects fetched per listing call for raw S3/GCS Data Lake sources (the per-run batching knob for `catalogType=s3`/`gcs-interop`; the Iceberg path batches by `iceberg.snapshotProcessing.batchSize` instead).
`catalog.s3.startFromLexLatestPartition`	`false`	Start the first listing from the lexicographically latest partition instead of the beginning of the prefix. Ignored when `catalog.s3.startAfter` is set.

Don’t use catalog.s3.prefix as a way to bound where ingestion starts within a large bucket — prefix is a permanent filter on which objects are ever considered, not a one-time starting cursor. Narrowing it to “skip ahead” permanently excludes everything outside that range, including future files, once the checkpoint reaches the end of the prefix. Use catalog.s3.startAfter instead, which only affects the first run.

continueOnFileError vs. continueOnError — two different configs. continueOnFileError (this ExternalTableSyncTask config) skips an entire file that fails to process, for any reason — not just unreadable ones — so one bad file doesn’t fail the run. continueOnError (an OSS field in the table’s ingestionConfig, not here) skips individual bad rows while reading a file or stream. Both apply only during ingestion — neither one protects a segment that is already ingested. In particular, if a source file is deleted or compacted away upstream after its segment exists, the server fails to serve that segment regardless of either flag; the durable fix for compacting sources is an Iceberg REST catalog.

Snapshot processing (Iceberg)

Key	Default	Description
`iceberg.snapshotProcessing.initialVersionSelector`	`LATEST`	Where to start: `LATEST`, `EARLIEST`, or `SNAPSHOT_ID`.
`iceberg.snapshotProcessing.initialSnapshotId`	—	Snapshot ID to start from (when selector is `SNAPSHOT_ID`).
`iceberg.snapshotProcessing.batchSize`	`10000`	Max new files ingested per run.
`iceberg.snapshotProcessing.maxFilesScannedPerRun`	`100000`	Upper bound on files enumerated per run (≥ `batchSize`).
`iceberg.snapshotProcessing.maxRetries`	`3`	Consecutive failures before a snapshot is marked `FAILED`.
`iceberg.snapshotProcessing.retryFailedSnapshot`	`false`	Keep re-arming a `FAILED` snapshot from its last cursor on every cycle instead of abandoning it after `maxRetries`. See Sync says COMPLETED but no data lands.
`iceberg.snapshotProcessing.lagMaxDiffFiles`	`100000`	Cap on the manifest set-diff used by the exact ingestion-lag calculation (`?includeLag=true&pendingExact=true`). Beyond it, lag degrades to summary-based estimates with an explanatory `message`. Only affects the numbers returned by the lag/status endpoint — it has no effect on ingestion itself.

Checkpointing & schema evolution

Key	Default	Description
`enableCheckpointing`	(always on)	Declared but not currently read — checkpointing is unconditionally enabled for `ExternalTableSyncTask`; this key has no effect.
`checkpointTTLSec`	(unused)	Declared but not currently read for this task type.
`schemaEvolution.enabled`	`false`	Refine the Pinot schema to the source schema before each sync.
`schemaEvolution.failFast`	`false`	Abort (vs. log and continue) when a schema change is rejected.
`storeNestedSchema.enabled`	`true`	Stamp the source’s nested-schema metadata onto complex (JSON) columns so field names inside `struct` / `array<struct>` / `map<_,struct>` values resolve to your logical names. Only matters for Delta UniForm / Unity Catalog tables that use column mapping — see the note below.

How schema evolution resolves changes (0.16.0+). When schemaEvolution.enabled=true on a catalogType=iceberg-rest source, each sync unions the current schema against the source’s Iceberg field-id/alias history instead of matching by column name alone. This means a source column rename is tracked as an alias rather than dropping and re-adding a column — the existing Pinot column name is preserved. Two limitations: resolution only covers top-level fields (nested-field renames aren’t resolved), and a detected type widening is rejected rather than silently applied — set schemaEvolution.failFast=true if you want such a rejection to abort the run instead of being logged and skipped. When fail-fast aborts a run, the status API reports failurePhase: SCHEMA_UPDATE.

Nested logical names under column mapping (storeNestedSchema.enabled). Delta UniForm tables created with delta.columnMapping.mode (name or id) don’t store your column names in the Parquet files — each physical column is an opaque col-<uuid>, and the logical name plus a stable field-id live only in table metadata. Top-level column names are always resolved back to logical names, with or without this flag. Names inside a complex value are only resolved when storeNestedSchema.enabled is on (the default) for the table’s ExternalTableSyncTask config; set it to false to opt out, and nested keys are then stored under their physical names, so JSON_EXTRACT_SCALAR(customer, '$.name', ...) returns NULL even though the data is there. Only an explicit, case-insensitive false opts out — an unset, blank, or unrecognized value keeps the default on.The flag applies to every schema-writing path (onboarding, preview, and the schema-evolution union during sync) and is independent of schemaEvolution.enabled. It defaults to on because leaving it off silently materializes the wrong JSON keys for complex columns on column-mapped tables; the only cost of stamping is a larger ZK-persisted schema on tables that don’t need it, and it does nothing for native Iceberg or add_files tables where physical names already equal logical names. Changing it on an already-onboarded table takes effect only on the next schema write (re-preview/re-register, or the next sync if schema evolution is on), followed by a segment reload — resolution happens at read time, so segments don’t need regenerating. A table onboarded before this default flipped gets that one-time schema update and segment reload automatically on its first sync after upgrade, if it has complex columns and no explicit storeNestedSchema.enabled setting. See Unity Catalog — schema options for column mapping.

Tier backend properties (caching & preload)

Set on the S3 tier in tierConfigs[].tierBackendProperties. The onboarding/preview flow sets sensible defaults.

Two-tier scaffold for skipSegmentPreprocess=true (create-time only). If the table config already sets indexingConfig.skipSegmentPreprocess=true when it’s created — through the preview API or CREATE TABLE ... WITH (type='iceberg', ...) — the enrichment scaffolds two tiers instead of the usual single fixed-selector tier: <tableName>_preprocessed_tier (segmentAge=2h) and <tableName>_s3_tier (segmentAge=0s), both keyed on creationTime. They share the same bucket and region but get distinct pathPrefix values (the preprocessed tier appends /preprocessed), so a segment aging from one tier to the other is a real physical move, and matching indexingConfig.tierOverwrites entries are written so segment preprocessing runs on the preprocessed tier and stays skipped on the s3 tier. New segments land on the s3 tier and relocate to the preprocessed tier once they pass the 2-hour threshold. This applies only at create time: adding skipSegmentPreprocess=true to an existing table via PUT /tables/<name>/config does not scaffold the second tier, and turning the flag back off does not remove one already created.

Common

Key	Default	Description
`bucket` / `pathPrefix`² / `region`	(required)	S3 location of the tier storage.
`enable.prefetch.page.cache`	`false`¹	Route this table’s index reads through the page cache.
`preload.enable.index.consolidation`	`false`¹	Pack a segment’s index regions into one file to reduce mmap count on wide tables.
`enable.delegate.v2`	`true`	Use the v2 delegate segment format. Already on by default — onboarding also writes this key, but that has no effect since it was already `true`.
`enable.startree.index`	`false`	Load StarTree index files for this tier. Off by default — while it’s off, a StarTree index built on an external table is generated at index-build time but never used to serve queries. Turn this on if the table has (or will have) a star-tree index.

¹ The onboarding/preview flow sets these to true on newly scaffolded tables (preload.enable.index.consolidation, enable.prefetch.page.cache). Existing tables are never re-enriched — if a table predates these defaults, add the keys manually. ² pathPrefix is not validated. Nothing errors if it’s missing or wrong — files simply land at the bucket root instead of under a per-table prefix. Always set it explicitly so each table’s files get their own folder.

preload.enable, preload.dir, preload.total.size, and preload.load.existing.buffers are not tier/table settings, even though they sound like they belong here next to preload.enable.index.consolidation. They are server / cluster settings — see Preload (server-level) below.

Preload tuning

Key	Default	Description
`preload.index.keys.override`	(empty — nothing is preloaded until you set this)	Comma-separated index keys to preload for this table, e.g. `.inverted_index,.range_index`. This comma-separated format is table-config-only — the server/cluster-level equivalent of this setting uses a different key and format, so don’t copy this syntax into a server config. Preloading also requires the server-level `preload.enable` to be on; with it off, this list pins nothing.

The S3 tier also exposes advanced on-demand buffer, mmap, and read-ahead sub-properties (ondemand.*, mmap.*, readAhead.*). These are internal tuning knobs — leave them at defaults unless StarTree support advises otherwise.

Server / cluster configs

Parquet page cache sizing (in-memory)

Key	Default	Description
`pinot.parquet.page.cache.memory.data.size.mb`	`256`	In-memory data page cache size.
`pinot.parquet.page.cache.memory.dict.size.mb`	`64`	In-memory dictionary page cache size.
`pinot.parquet.page.cache.prefetch.size.mb`	`1024`	Prefetch buffer size (decoded hot pages).

These in-memory pools are a separate concern from the persistent disk cache below — they don’t share a namespace or a budget with it.

Pre-warm at segment load

Key	Default	Description
`pinot.parquet.prewarm.enabled`	`false`	Pre-warm the first data page of each column when a segment loads.
`pinot.parquet.prewarm.max.inflight.per.table`	`64`	Per-table cap on concurrent pre-warm reads.
`pinot.parquet.prewarm.max.bytes.inflight.per.table`	`64MB`	Per-table cap on concurrent pre-warm bytes.
`pinot.parquet.prewarm.max.inflight.per.table.override`	`""`	Per-table overrides: `table1,N;table2,M`.

Page reader & prefetch

Key	Default	Description
`pinot.parquet.pagereader.read.timeout.ms`	`30000`	Timeout for a single page S3 GET. Cluster-config only — despite appearing in older docs as a query option, it cannot be overridden per query; setting it with `SET` has no effect.
`pinot.parquet.pagereader.max.in.flight.chunk.reads`	`0`	Cap on concurrent chunk reads (`0` = unbounded).
`pinot.parquet.pagereader.pool.size`	`max(4, 2×cores)`	Thread-pool parallelism for async page reads.
`pinot.parquet.prefetch.deserialize`	`true`	Decode prefetched pages to values (not just raw bytes).
`pinot.parquet.prefetch.segment.lookahead.enabled`	`false`	Warm upcoming segments’ chunks ahead of time.
`pinot.parquet.prefetch.segment.lookahead.max.inflight.chunks`	`12`	Server-wide cap on concurrent look-ahead chunk reads.
`pinot.parquet.reader.lazy.enabled`	`false`	Build the per-column Parquet forward-index reader lazily, on first use, instead of eagerly at segment load. Lazy readers cap concurrently-built readers via a pool so a server’s segment capacity stops scaling with table width; the tradeoff is a first-read cost. Applies to scalar columns only — `JSON`/`MAP` columns keep their own reader path regardless. Ignored (readers stay eager) when pre-warm or a build/reload scope is active. Takes effect on the next segment load or reload, not for already-loaded segments.

Prefetch depth is bounded by the query option prefetch.projection.queue.size (default 10) — how many projection blocks are fetched ahead when the scan queue refills. The other prefetch-related knobs are the prefetch buffer size (...prefetch.size.mb), the look-ahead in-flight cap (...segment.lookahead.max.inflight.chunks), the in-flight chunk-read cap (...max.in.flight.chunk.reads), and the page-reader pool size (...pagereader.pool.size).

Parquet read modes (PAGE / CHUNK)

Explicit physical-read mode for Parquet V6 query prefetch, letting a per-page range-read strategy be compared against a whole-column-chunk strategy without an adaptive policy choosing between them.

Key	Default	Description
`pinot.parquet.prefetch.read.mode`	`PAGE`	`PAGE` reads each selected compressed page range. `CHUNK` reads each selected column chunk once and slices the response into the existing page-cache entries. Invalid values fail closed to `PAGE`.
`pinot.parquet.prefetch.read.chunk.enabled`	`true`	Immediate CHUNK kill switch — `false` forces new and queued prefetch work to `PAGE` even when the mode is `CHUNK`. Does not cancel a storage GET already issued.
`pinot.parquet.prefetch.read.max.inflight.requests`	`32`	JVM-wide physical-request admission limit shared by both modes.
`pinot.parquet.prefetch.read.max.inflight.bytes`	`67108864`	JVM-wide compressed-byte admission limit shared by both modes. A CHUNK larger than this limit falls back to `PAGE`.

These are cluster-config only, not query options. PAGE remains the recommended default — StarTree’s own benchmarks found no universal crossover point, and CHUNK can transfer significantly more bytes than needed on selective queries. Canary CHUNK only for scan-heavy, request-constrained workloads, and monitor the per-mode GET count, physical/useful bytes, and amplification metrics.

Filter-ahead prefetch

Default-off stage that evaluates the query filter ahead of scanners and warms projection pages for matched rows on external Parquet scans. It is best effort — query execution always performs the authoritative filter regardless of prefetch outcome.

Key	Default	Description
`pinot.parquet.prefetch.filter.ahead.enabled`	`false`	Enable the stage. SQL query option or cluster config. A cluster value of `false` (case-insensitive) is a fleet-wide hard stop the query cannot override; otherwise the query option wins when set, so a query can opt in when the cluster value is absent or opt out when the cluster default is `true`.
`pinot.parquet.prefetch.filter.ahead.depth`	`8`	Max segments filtered concurrently. SQL query option or cluster config. A positive cluster value sets both the server default and the ceiling for the query option — a query may lower it but not raise it.
`pinot.parquet.prefetch.filter.ahead.max.outstanding.futures`	`128`	Per-query sliding window of outstanding filter-ahead prefetch futures. Same cluster-value-as-ceiling behavior as `...depth`.
`pinot.parquet.prefetch.filter.ahead.max.active.filters`	`128`	Cluster-config only — not query-settable. Fair per-server ceiling on concurrent speculative filter-ahead segment lifecycles. Resizes live; invalid or removed values restore the default, and numeric values are clamped to at least `1`.
`pinot.parquet.prefetch.filter.ahead.max.inflight.chunks`	`128`	Cluster-config only — not query-settable. Per-server storage-read ceiling for filter-ahead warms, resized live the same way as `...max.active.filters`.

Persistent disk cache (`pinot.server.instance.disk.cache.*`)

Breaking change (0.16.x). Every persistent disk cache for External Tables and tiered storage — Parquet page cache, segment metadata cache, preload, mmap, and the deletion-vector Puffin cache — now shares one config namespace, pinot.server.instance.disk.cache.*, and one root directory. The old pinot.parquet.page.cache.disk.*, pinot.server.instance.segment.cache.*, and pinot.server.instance.buffer.reference.manager.* (preload/mmap) keys, and the table-level iceberg.dv.fetch.mode / iceberg.dv.puffinCache.* keys (see Deletion Vectors), are retired — not read, not aliased. Migrate before upgrading. All settings below take effect at server startup only; changing or deleting one requires a restart.

The table below omits the shared pinot.server.instance.disk.cache. prefix. Sizes accept bytes or binary units (K/M/G/T/P, optionally followed by B); the page-capacity and fragment-size keys also accept a percentage of filesystem capacity, and 0 disables that pool or layer.

Key	Default	Description
`enabled`	`true`	Global switch for every managed disk cache (Parquet pages, segment metadata, preload, mmap, Puffin). `false` leaves remote reads and the in-memory page cache above available.
`directory`	`pinot.server.instance.dataDir`	Single root for every managed cache (previously several independent directories).
`pages.dict.max.size`	`15%` per consumer	Dictionary-page capacity. `0` disables this pool.
`pages.data.max.size`	`15%` per consumer	Data-page capacity. `0` disables this pool.
`pages.fragment.size.mb`	`32` (Parquet pages) / `512` (segment index pages)	Fragment size in MiB, capped at `2047`. Append `.PARQUET_INDEX` or `.SEGMENT_INDEX` to size the two pools independently, e.g. `pages.dict.max.size.SEGMENT_INDEX`.
`pages.snapshot.enabled`	`true`	Persist/restore page snapshots across restarts (default flipped from `false`).
`preload.max.size`	`100G`	Maximum tracked preloaded-index bytes. `0` disables the layer at startup. Gates whether any table’s `preload.index.keys.override` (tier-level) has any effect.
`preload.index.keys`	(empty)	Comma-separated index keys to preload globally, e.g. `col.forward_index,col.inverted_index`.
`preload.index.keys.override`	(empty)	Semicolon-separated `table_OFFLINE,col.forward_index,col.inverted_index` groups, overriding the global selection per table — the server/cluster-level equivalent of the tier’s `preload.index.keys.override` (different key, different format; don’t copy that syntax here).
`preload.index.consolidation.enabled`	`false`	Consolidate selected indexes into fewer local files.
`mmap.max.size`	`100G`	Maximum buffer bytes admitted by LFU membership. `0` disables the layer at startup.
`puffin.max.size`	`10G`	Soft LRU budget per table/URI-scheme for the deletion-vector Puffin cache (see Deletion Vectors). `0` uses ranged reads (or stream-skip where ranges are unavailable) instead of caching whole files.
`puffin.download.timeout.ms`	`600000`	Timeout for a Puffin cache download.

Fixed cache locations under <root> (the resolved directory): External Table Parquet pages at remote_data_cache, tiered-storage index pages at index_cache, segment metadata/index headers/Parquet page indexes at tieredStorage/segmentCache, preloaded indexes at tieredStorage/preload, LFU mmap indexes at tieredStorage/mmap, and Puffin files at dv-puffin-cache/<tableNameWithType>/<scheme>. Disk caching and restart reuse (files survive a graceful shutdown and are reused on the next start) are both on by default; to force a cold rebuild of one cache, stop the server and remove only that cache’s directory, then restart — don’t remove the whole data directory.

Index reader

Key	Default	Description
`pinot.server.index.inverted.enable.startree.reader`	`false`	Use an optimized inverted-index reader for cloud-stored (external table) segments. This speeds up regular inverted indexes — “StarTree” in the key name refers to the company, not the StarTree index feature. Flipping it does not affect star-tree index behavior.

Query OOM protection (large scans)

A query that scans a large amount of remote data can grow the server heap until the process OOMs. Server-side per-query memory accounting protects against this: when heap usage crosses a threshold, the most memory-hungry query is killed (or briefly paused first) instead of the whole server crashing. Recommended for tables that get heavy scans or aggregations.

On StarTree Cloud, pinot.query.scheduler.accounting.enable.thread.memory.sampling and pinot.query.scheduler.accounting.oom.enable.killing.query are already turned on by default for servers — your cluster is protected without any action. The steps and settings below matter only for self-managed clusters that haven’t enabled them.

Key	Default	Description
`pinot.query.scheduler.accounting.enable.thread.memory.sampling`	`false`	Track per-query memory. Required for memory-based killing to work.
`pinot.query.scheduler.accounting.oom.enable.killing.query`	`false`	Enable the OOM killer — kill the biggest query when heap is critical, instead of crashing the server.
`pinot.query.scheduler.accounting.oom.critical.heap.usage.ratio`	`0.96`	Heap ratio at which the heaviest query is killed.
`pinot.query.scheduler.accounting.oom.panic.heap.usage.ratio`	`0.99`	Heap ratio for panic — kill even without per-query sampling.
`pinot.query.scheduler.accounting.oom.alarming.usage.ratio`	`0.75`	Heap ratio at which usage is flagged (logging / back-pressure).
`pinot.query.scheduler.accounting.oom.panic.allow.pre.query.kill.pause`	`false`	OOM pause: at panic level, pause briefly to let GC reclaim memory before killing — can save a query that would otherwise be killed.
`pinot.query.scheduler.accounting.oom.pre.query.kill.pause.duration.ms`	`-1`	How long the pre-kill pause waits (ms). `-1` disables it.

To protect a cluster running large External Table scans, set (full keys, all prefixed pinot.query.scheduler.accounting.): ...enable.thread.memory.sampling=true and ...oom.enable.killing.query=true. Add the pre-kill pause (...oom.panic.allow.pre.query.kill.pause=true with ...oom.pre.query.kill.pause.duration.ms=2000) so transient spikes recover via GC instead of killing the query.

Query options

Set per query with SET "key" = 'value'. Several of these are also tier or server configs — setting them as a query option overrides the config for that one query.

Query option	Default	Also a config?	Effect
`enable.prefetch.page.cache`	`false`	Tier + server	Route index reads through the page cache for this query (overrides read-ahead).
`prefetch.projection.queue.size`	`10`	—	Projection blocks prefetched ahead on a scan queue refill (prefetch depth).
`readAhead.enable`	`false`	Tier	Enable read-ahead buffering for index reads on this query.
`skip.remote.table.cache`	`false`	Tier	Bypass the Parquet page cache — the cache that holds fetched remote-table data pages in memory/on disk — for both reads and writes. Covers data pages only. To measure a genuinely cold query, also set `skip.columnIndex.cache`; index reads otherwise still come from the local cache and the result will look faster than a real cold run.
`skip.columnIndex.cache`	`false`	Tier	Bypass the column index cache for this query. Pair with `skip.remote.table.cache` for a truly cold-cache measurement.
`snapshotVersionByTable`	—	—	Time-travel: pin the query to specific Iceberg snapshots, as a JSON map `{"table": snapshotId}`. See Time travel.
`concurrentProjectionPrefetch`	`true`	—	Concurrent per-column prefetch for wide projections (`SELECT *`). Set `'false'` as a kill switch if a wide scan misbehaves.
`pinot.parquet.pagereader.max.in.flight.chunk.reads`	`0`	Server	Per-query cap on concurrent chunk reads.
`pinot.parquet.prefetch.segment.lookahead.enabled`	`false`	Cluster	Enable segment look-ahead warming for this query.
`pinot.parquet.prefetch.filter.ahead.enabled`	`false`	Cluster	Enable filter-ahead prefetch for this query. A cluster value of `false` is a fleet-wide hard stop this option cannot override.
`pinot.parquet.prefetch.filter.ahead.depth`	`8`	Cluster	Max segments filter-ahead filters concurrently for this query. Can lower, not raise, the cluster-set ceiling.
`pinot.parquet.prefetch.filter.ahead.max.outstanding.futures`	`128`	Cluster	Per-query cap on outstanding filter-ahead prefetch futures. Can lower, not raise, the cluster-set ceiling.
`enable.tiered.storage.trace`	`false`	—	Collect a tiered-storage trace for this query. The trace is written to server logs only — set the standard `trace` query option together with this one if you also want it back in the query response.

Example — bypass the cache for one query:

SET "skip.remote.table.cache" = 'true';
SELECT count(*) FROM nyc_taxi_trips WHERE fare > 100;

Time travel (query-time snapshot pinning)

By default, a query against an Iceberg external table reads the newest fully-synced snapshot, resolved per query at the broker. The snapshotVersionByTable query option pins the query to a specific snapshot instead — the broker routes it to exactly that snapshot’s file set, so you get the data (and row-level deletes) as of that snapshot:

SET "snapshotVersionByTable" = '{"nyc_taxi_trips_OFFLINE": 1234567890123456789}';
SELECT count(*) FROM nyc_taxi_trips;

The value is a JSON map of table name → snapshot ID, so a single query can pin several tables (one entry per table). Table names are canonicalized — nyc_taxi_trips, nyc_taxi_trips_OFFLINE, and mydb.nyc_taxi_trips all resolve to the same entry. The option works on both the single-stage and multi-stage query engines.

Caveats

Snapshot IDs only, no SQL clause. There is no FOR VERSION AS OF / FOR TIMESTAMP AS OF syntax and no timestamp-based variant. Pass the numeric Iceberg snapshot ID (as a JSON number or numeric string) via the query option.
Bounded time-travel window. Only snapshots still in the table’s active snapshot list can be pinned. Snapshot retention keeps the newest iceberg.snapshotProcessing.retention.maxActiveVersions versions (default 2). A configured value below 2 is ignored outright and the default of 2 — also the floor — is used instead. This is not arbitrary Iceberg-history time travel — it is “pick one of the last N snapshots StarTree has synced and still retains.”
Iceberg catalog sources only. Supported for Iceberg REST catalog sources (AWS Glue, S3 Tables, Unity Catalog, generic REST). On raw S3/GCS Data Lake tables the option is silently ignored — file listings have no snapshot concept.
Expired or unknown snapshots fail the query. Pinning a snapshot that is not (or no longer) active returns an error (Iceberg snapshot <id> is not active for table <table>) rather than silently falling back to the latest snapshot. Malformed JSON in the option also fails the query.
No pin = latest ready snapshot. Without the option, each query reads the newest snapshot that is fully synced and cache-ready at that moment. Two queries issued at different times may therefore read different snapshots; pin a snapshot when you need repeatable reads across queries.
On some tables the broker sets it automatically. Every query on a deletion-vector-enabled table runs against a pinned snapshot automatically — and so does every query on a table with snapshot consistency enabled, or one using segment groups. You only set the option yourself to override the default (latest) choice.
Not the same as initialSnapshotId. The table-config keys iceberg.snapshotProcessing.initialVersionSelector / initialSnapshotId (and the DDL snapshot_mode / snapshot_id) choose where ingestion starts when the table is created. They do not affect which snapshot a query reads.
Finding snapshot IDs. GET …/externalTable/status?includeLag=true returns the newest synced snapshot (lag.synced.snapshotId) and the upstream head (lag.upstream.snapshotId) — see Observability. For older retained snapshots, get IDs from the source catalog (e.g. the Iceberg table’s snapshots metadata); there is currently no API that lists all active snapshot IDs.

Get Started

Ingestion

External Tables

Query Data

Manage Data

Observability

Visualize Data

Cluster Operations

Manage Security

Release Notes

Reference

Best Practices and Configs

Best practices

Caching and index pinning

Where configs live

Table config — `ExternalTableSyncTask`

Catalog & mode

Connection keys

Scheduling & task sizing

Snapshot processing (Iceberg)

Checkpointing & schema evolution

Tier backend properties (caching & preload)

Common

Preload tuning

Server / cluster configs

Parquet page cache sizing (in-memory)

Pre-warm at segment load

Page reader & prefetch

Parquet read modes (PAGE / CHUNK)

Filter-ahead prefetch

Persistent disk cache (`pinot.server.instance.disk.cache.*`)

Index reader

Query OOM protection (large scans)

Query options

Time travel (query-time snapshot pinning)

Caveats

​Best practices

​Caching and index pinning

​Where configs live

​Table config — ExternalTableSyncTask

​Catalog & mode

​Connection keys

​Scheduling & task sizing

​Snapshot processing (Iceberg)

​Checkpointing & schema evolution

​Tier backend properties (caching & preload)

​Common

​Preload tuning

​Server / cluster configs

​Parquet page cache sizing (in-memory)

​Pre-warm at segment load

​Page reader & prefetch

​Parquet read modes (PAGE / CHUNK)

​Filter-ahead prefetch

​Persistent disk cache (pinot.server.instance.disk.cache.*)

​Index reader

​Query OOM protection (large scans)

​Query options

​Time travel (query-time snapshot pinning)

​Caveats

Best practices

Caching and index pinning

Where configs live

Table config — `ExternalTableSyncTask`

Catalog & mode

Connection keys

Scheduling & task sizing

Snapshot processing (Iceberg)

Checkpointing & schema evolution

Tier backend properties (caching & preload)

Common

Preload tuning

Server / cluster configs

Parquet page cache sizing (in-memory)

Pre-warm at segment load

Page reader & prefetch

Parquet read modes (PAGE / CHUNK)

Filter-ahead prefetch

Persistent disk cache (`pinot.server.instance.disk.cache.*`)

Index reader

Query OOM protection (large scans)

Query options

Time travel (query-time snapshot pinning)

Caveats