title: 'Performance Tuning' sidebar_label: 'Performance Tuning' sidebar_position: 33 description: 'Comprehensive guide to optimizing query performance, acceleration, storage, spill-to-disk, and resource utilization in Spice deployments.' keywords:

• performance
• tuning
• optimization
• cayenne
• duckdb
• acceleration
• storage
• nvme
• ebs
• spill pagination_prev: null pagination_next: null

This guide provides recommendations for optimizing Spice performance across data acceleration, storage, query execution, caching, and resource allocation.

Quick Recommendations

The settings that matter most, in the order they are usually worth checking:

Accelerator Selection

Choose the appropriate Data Accelerator (opens in a new tab) based on dataset characteristics and query patterns.

Every file-mode engine — Cayenne, DuckDB, SQLite, and Turso — is only as fast as the storage its files sit on. Decide where those files and the query spill directory live before tuning anything else; see Storage (opens in a new tab).

Storage

Spice is an in-process engine: accelerated data, the metadata that indexes it, and the intermediate results of queries that outgrow memory all live on whatever storage the process can reach. That storage is on the critical path of every query that is not served from RAM, and of every refresh, compaction, and spill.

Recommendation: store acceleration data and temporary/spill files on local NVMe or SSD. Network-attached block storage (Amazon EBS, Azure Managed Disks, GCP Persistent Disk and Hyperdisk) works as a durable fallback with tuning; network file systems (NAS, NFS, SMB, EFS, Azure Files) are not recommended for either.

Why storage matters

Each engine has a different I/O profile, and none of them is friendly to high-latency storage:

Latency matters more than IOPS. A query's critical path is a chain of dependent I/Os — read a footer, then the segments it points to, then the next batch; write a spill batch, wait for it, write the next — and each one waits for the device before the operator can continue. Per-I/O latency therefore sets how long a single query, refresh, or spill takes; IOPS and bandwidth only set how many can proceed at once before they start queueing behind each other. A volume with generous provisioned IOPS but millisecond latency still runs a dependent read chain an order of magnitude slower than NVMe, and no amount of additional IOPS closes that gap.

Segment and page caches absorb some of the difference for hot data, but cold scans, refreshes, and spills run at the latency of the medium.

Recommended layout

Put every locally stored artifact on one local NVMe/SSD file system, in separate directories, and point the runtime at them explicitly rather than relying on the defaults (the working directory's .spice/data for acceleration files, and the operating system's temporary directory for spill):

/nvme/spice/
├── data/        # acceleration files: duckdb_file, sqlite_file, turso_file, cayenne_file_path
├── metadata/    # Cayenne metastore (cayenne_metadata_dir)
└── tmp/         # runtime.query.temp_directory — query, compaction, and DuckDB spill

/nvme/spice/
├── data/        # acceleration files: duckdb_file, sqlite_file, turso_file, cayenne_file_path
├── metadata/    # Cayenne metastore (cayenne_metadata_dir)
└── tmp/         # runtime.query.temp_directory — query, compaction, and DuckDB spill

runtime:
  query:
    temp_directory: /nvme/spice/tmp

datasets:
  - from: postgres:public.orders
    name: orders
    acceleration:
      engine: cayenne
      mode: file
      params:
        cayenne_file_path: /nvme/spice/data/orders/
        cayenne_metadata_dir: /nvme/spice/metadata

  - from: postgres:public.customers
    name: customers
    acceleration:
      engine: duckdb
      mode: file
      params:
        duckdb_file: /nvme/spice/data/customers.duckdb

runtime:
  query:
    temp_directory: /nvme/spice/tmp

datasets:
  - from: postgres:public.orders
    name: orders
    acceleration:
      engine: cayenne
      mode: file
      params:
        cayenne_file_path: /nvme/spice/data/orders/
        cayenne_metadata_dir: /nvme/spice/metadata

  - from: postgres:public.customers
    name: customers
    acceleration:
      engine: duckdb
      mode: file
      params:
        duckdb_file: /nvme/spice/data/customers.duckdb

Keep spill and data on the same fast device rather than moving spill to a slower one to "protect" the data volume — a spill that runs slowly holds the query's memory reservation for longer and delays every query behind it. When a host has two NVMe devices, put spill on one and data on the other so a large spill does not compete with scans for the device's queue.

Storage tiers compared

How Spice adapts to the storage medium

File-mode accelerators detect the class of storage behind their file path at registration and tune themselves for it. The result is the acceleration's storage_profile (opens in a new tab), which defaults to auto:

• auto reads /proc/self/mountinfo on Linux, finds the mount that holds the (nearest existing ancestor of the) file path, and classifies it: tmpfs/ramfs mounts resolve to tmpfs; nfs, nfs4, cifs, smbfs, and smb3 mounts resolve to ebs (the network-attached tier); block devices that identify as Amazon Elastic Block Store or as Microsoft/Azure virtual disks resolve to ebs; devices that identify as Amazon EC2 NVMe instance storage, NVMe devices in general, and any non-rotational device resolve to local_ssd. Device-mapper, LVM, and software-RAID volumes are resolved through their underlying devices. Anything else — including rotating disks and every platform other than Linux — resolves to unknown, which applies the engine defaults.
• local_ssd, ebs, and tmpfs override detection. Set one when detection cannot see the device: a network block device that is not EBS or an Azure disk (GCP Persistent Disk and Hyperdisk, Ceph RBD, SAN LUNs, OpenStack Cinder, and similar volumes present as ordinary non-rotational disks and resolve to local_ssd), a container runtime that hides /sys block metadata, or a non-Linux host.

Each engine applies its own adjustments to the resolved profile:

Within the network-attached tier, Cayenne also measures the medium rather than treating every volume alike. At registration it writes an 8 MiB probe file to the data directory and to the metastore directory, syncs it, and records the sequential write throughput, once per distinct volume. A volume that measures at or below roughly 125 MiB/s (a baseline gp3 volume) receives the full slow-tier bias; one that measures at or above roughly 1 GiB/s (local NVMe, or a heavily provisioned io2 volume) receives none, with a linear scale in between. On AWS EC2 it also queries the instance metadata service once to learn whether the instance is a burstable T-family type and what its baseline EBS bandwidth is, and caps the process-wide encode concurrency so parallel writers cannot oversubscribe the instance's EBS pipe. See Storage (opens in a new tab) in the Cayenne performance guide.

The detected tier is observable. Cayenne reports cayenne_data_storage_class and cayenne_metastore_storage_class per table (0 local SSD, 1 network-attached, 2 tmpfs, 3 unknown) and the probe results as cayenne_data_storage_write_mibps and cayenne_metastore_storage_write_mibps. A table that reports 1 on a host you believe has local NVMe — or 0 on one you know is on Persistent Disk — is the signal to set storage_profile explicitly.

Local NVMe and SSD

Local NVMe is the recommended medium for both acceleration data and spill. It delivers the lowest latency and highest IOPS available on any platform, it is included in the instance price on the cloud families that offer it, and it is the only tier on which Cayenne's random-access reads and DuckDB's buffer manager run at their design speed.

It is ephemeral. Cloud instance storage is physically attached to the host and lives only as long as the instance does:

• On AWS, data on an instance store volume (opens in a new tab) persists across a reboot but is cryptographically erased when the instance is stopped, hibernated, or terminated, when its instance type changes, and when the underlying disk fails. Instance store volumes cannot be attached after launch or moved between instances.
• On Azure, the VM's temporary disk and the local NVMe on Lsv3 / Lasv3 and the d-suffixed families (opens in a new tab) are lost on stop-deallocate, resize, and host redeployment.
• On GCP, Local SSD (opens in a new tab) data is lost when the VM is stopped or deleted.
• On Kubernetes, a node replacement — a scale-down, an upgrade, a spot reclamation — takes every local volume on that node with it.

Plan for the loss rather than trying to prevent it:

Capacity planning. Size the volume for the data, its transient copies, and spill together:

File system and mount guidance. Use ext4 or xfs (DuckDB performs best on XFS (opens in a new tab)). Mount with noatime. Mount the device at its own path rather than sharing the root file system: the root volume on a cloud instance is usually a small network-attached disk, and a spill directory that silently lands there is the most common cause of a large query failing with ResourcesExhausted while memory was still available. When an instance has several NVMe devices, either dedicate one to spill or stripe them (RAID 0) for capacity and throughput — the array is no less durable than its members, which are ephemeral anyway.

Amazon EBS and network block storage

Amazon EBS, Azure Managed Disks, and GCP Persistent Disk and Hyperdisk are block devices reached over the network. They survive instance replacement, can be snapshotted, and can be detached and reattached elsewhere, which makes them the right choice when an acceleration must outlive its host and a snapshot-based bootstrap is not available. The cost is latency first — every cache miss and every synchronous write is a network round trip of a millisecond or more, against tens of microseconds on NVMe — and, second, the throughput ceilings that both the volume and the instance impose.

Characteristics that affect Spice, using EBS as the reference:

• Per-volume limits. A gp3 (opens in a new tab) volume includes a baseline of 3,000 IOPS and 125 MiB/s regardless of size, and can be provisioned up to 80,000 IOPS and 2,000 MiB/s for an additional cost. A gp2 volume earns 3 IOPS per GiB and bursts to 3,000 IOPS on I/O credits — a 100 GiB gp2 volume runs at 300 IOPS once its credits are spent. gp3 does not burst, so it sustains its provisioned figures indefinitely.
• Latency. gp3 and gp2 deliver single-digit millisecond latency. io2 Block Express (opens in a new tab) is designed for an average under 500 µs for 16 KiB I/O with far fewer outliers; Azure Premium SSD v2 and GCP Hyperdisk Extreme are the equivalents.
• Per-instance limits. Each EBS-optimized instance type (opens in a new tab) has a baseline and a maximum EBS bandwidth, and many smaller sizes can sustain the maximum for only 30 minutes at least once every 24 hours before dropping to the baseline. A large provisioned volume behind a small instance is throttled by the instance, not the volume. The same applies to Azure VM-level disk throughput caps.
• Shared pipe. Every volume attached to an instance shares that instance's EBS bandwidth. Spill, data, and the root volume compete for it.

What Spice does on the ebs profile is summarized in How Spice adapts to the storage medium (opens in a new tab): fewer pooled connections and larger checkpoints for DuckDB, larger page caches and longer lock waits for SQLite and Turso, and for Cayenne a slow-tier tuning bias, an O_DIRECT compaction writer that keeps rewritten files out of the page cache, and an encode-concurrency cap derived from the instance's EBS bandwidth.

Guidance:

• Choose the tier for its latency first. io2 Block Express, Premium SSD v2, and Hyperdisk Extreme are the sub-millisecond tiers and the right default for accelerations; gp3 with provisioned IOPS and throughput is the economical fallback for data that is mostly served from cache. Provisioned IOPS stop queueing from adding to the latency; they do not reduce it. Do not run accelerations on gp2, whose burst credits are exhausted by exactly the sustained scans and refreshes an accelerator performs.
• Choose the instance for its EBS bandwidth, not just its cores and memory. Check the instance type's baseline EBS bandwidth against the volume's provisioned throughput, and prefer types that sustain their maximum rather than burst to it. Cayenne's startup log reports the cap it derived (Capping global encode-concurrency budget to the instance EBS write-bandwidth ceiling) when the instance bandwidth binds.
• Keep spill off the root volume. Set runtime.query.temp_directory to a directory on the data volume or on a dedicated volume. When Cayenne is active and the setting is unset, the runtime logs a reminder at startup.
• Prefer local NVMe for spill even when data is on EBS. Many EBS-backed instance families also carry instance store (the d-suffixed types); a spill that runs at NVMe speed holds memory reservations for a fraction of the time, and spill has no durability requirement.
• Set storage_profile: ebs explicitly on GCP Persistent Disk and Hyperdisk, on Ceph, SAN, and other network block devices that auto-detection resolves to local_ssd, and on any platform where detection returns unknown but the storage is network-attached.
• Do not share a volume between Spice instances. DuckDB, SQLite, Turso, and the Cayenne metastore each allow one writer per file. Multi-attach io2 volumes provide no file-system coordination and will corrupt these files.
• Monitor the volume and the instance. On AWS, watch VolumeQueueLength, VolumeReadOps/VolumeWriteOps against the provisioned IOPS, BurstBalance on gp2, and the instance-level EBSIOBalance% and EBSByteBalance% — see Amazon EBS CloudWatch metrics (opens in a new tab). A queue length that grows while IOPS plateau is a saturated volume; a balance that trends to zero is an instance about to be throttled to its baseline.

On Azure, prefer Premium SSD v2 or Ultra Disk (opens in a new tab) with explicit IOPS and throughput; on GCP prefer Hyperdisk Balanced or Extreme (opens in a new tab) over pd-ssd. The platform-specific StorageClass recommendations are in the AWS (opens in a new tab), Azure (opens in a new tab), GCP (opens in a new tab), and Helm (opens in a new tab) deployment guides.

Network file systems: NAS, NFS, SMB, EFS, Azure Files

Network file systems — on-premises NAS appliances exported over NFS or SMB, Amazon EFS, Azure Files, Google Cloud Filestore, and similar — are not recommended for acceleration files or for the spill directory, and the limitation is not only speed:

• Latency and protocol overhead. Every uncached read, every write, and every metadata operation (open, stat, rename, lock) is a network round trip through a file-sharing protocol. Vortex's random-access reads, SQLite's per-commit fsync, and DuckDB's checkpoints all issue these operations in volume. Throughput on managed offerings is also metered per file system or per share, and shared with every other client.
• Locking semantics. SQLite's documentation is explicit that lock implementations on network file systems are unreliable: "This is especially true of network filesystems and NFS in particular. If SQLite is used on a filesystem where the locking primitives contain bugs, and if two or more threads or processes try to access the same database at the same time, then database corruption might result." (How To Corrupt An SQLite Database File (opens in a new tab), section 2.1). This applies to the SQLite and Turso accelerators and to the Cayenne metastore, which is a SQLite database.
• DuckDB's own guidance. DuckDB recommends avoiding its native database format in read-write mode on network-attached storage, noting that it "can result in slow and unpredictable performance, as well as spurious errors caused by the underlying file system" — see DuckDB's environment guide (opens in a new tab). The same page notes that network-backed cloud block disks such as EBS are fine for read-write use; the distinction is block device versus shared file system.
• What Spice does. Auto-detection classifies nfs, nfs4, cifs, smbfs, and smb3 mounts as the network-attached (ebs) tier, so the adjustments above apply, including Cayenne's O_DIRECT compaction writer. Those adjustments amortize the latency; they do not remove it, and they cannot make a file-sharing protocol's locks trustworthy.

If a network file system is unavoidable — a regulated environment that provides nothing else, for example — limit the exposure:

• Run exactly one Spice instance per exported directory, with no other NFS/SMB clients touching the files.
• Keep the Cayenne metastore on local disk (cayenne_metadata_dir on a local volume) even when data files are on the share, and keep spill local (runtime.query.temp_directory).
• Prefer Cayenne over DuckDB, SQLite, or Turso for data on the share: its data files are immutable once written, so the share sees whole-file writes and renames rather than in-place page updates.
• Set storage_profile: ebs explicitly if the share is mounted through a user-space client that does not appear in /proc/self/mountinfo with one of the file-system types above.
• Mount NFS with hard, a generous timeo, and nconnect where the client supports it; mount SMB with the 3.1.1 dialect and the largest rsize/wsize the server allows.
• Expect refresh and compaction to run several times slower than on local storage, and load-test the cold-cache case rather than the warm one.

Using NAS as a data source is a different matter. The NFS (opens in a new tab) and SMB (opens in a new tab) data connectors read Parquet, CSV, and JSON from shares as a federated source; accelerating that data onto local NVMe is the recommended pattern, and it moves the share off the query path entirely.

RAM-backed storage (tmpfs)

A tmpfs or ramfs mount — including a Kubernetes emptyDir with medium: Memory — is the fastest medium available, and the runtime recognizes it (Cayenne switches to 64 MB files, DuckDB raises its checkpoint threshold to 1 GiB). It is rarely the right choice:

• It is memory. Files on tmpfs are charged to the process's cgroup. On Kubernetes, "files you write count against the memory limit of the container that wrote them" (Kubernetes volumes (opens in a new tab)). A spill directory on tmpfs therefore cannot relieve memory pressure: a query that spills to it consumes the same memory it was trying to release, and the container is OOM-killed instead of the query slowing down.
• It competes with the page cache and the accelerator caches for the same RAM, so the data it holds is not "free" relative to simply keeping the dataset in an in-memory accelerator.
• It is lost on every restart, like instance store, but without instance store's capacity.

Use it only when the dataset is small relative to RAM and the workload genuinely needs the last few microseconds of read latency — and never for runtime.query.temp_directory.

Object storage

Object storage is not a file system and is used in three specific ways:

• Cayenne data files on S3 Express One Zone (opens in a new tab) — single-digit-millisecond object storage that persists Cayenne accelerations independently of the instance. The metastore stays on local disk; only data files move. Standard S3 buckets are not supported for the warm tier.
• Cayenne cold tier (opens in a new tab) on standard S3, for tables larger than local capacity.
• Snapshots (opens in a new tab) and read/write separation (opens in a new tab) — each snapshot is a complete copy of the acceleration file on standard S3, Azure ADLS, or GCS, used to bootstrap ephemeral local storage. Keep snapshots.location on that standard bucket. S3 Express One Zone (cayenne_file_path, cayenne_s3_*) is the Cayenne data tier and does not substitute for the snapshot bucket.

Spill cannot be directed at object storage, and acceleration files other than Cayenne's cannot be placed on it.

Storage on Kubernetes

Kubernetes adds a layer between the pod and the disk, and the defaults are wrong for Spice in two ways: the pod's writable layer and a plain emptyDir live on the node's ephemeral storage, which is usually the root disk; and the default StorageClass on every managed cloud is a network block volume.

Follow the step-by-step guide: Local NVMe Storage on Kubernetes (opens in a new tab). It covers choosing NVMe node types on EKS, GKE, AKS, and self-hosted clusters, mounting the disks, publishing them as PersistentVolumes with the Local Volume Static Provisioner, deploying the Helm chart with stateful.enabled: true on a local-storage class, and verifying that Spice detected local SSD. The short version:

• Acceleration data goes on a local NVMe PersistentVolume, with the pod pinned to its node and snapshots (opens in a new tab) to bootstrap a replacement pod. Network block storage (io2/gp3 via the EBS CSI driver, Premium SSD v2 via the Azure Disk CSI driver, Hyperdisk via the GCE PD CSI driver) is the fallback when the volume must follow the pod between nodes; set storage_profile: ebs on GCP, where auto-detection cannot identify the device. Never use a ReadWriteMany file-system volume (EFS, Azure Files, Filestore, an NFS provisioner) — see Network file systems (opens in a new tab).
• Spill (runtime.query.temp_directory) goes in a subdirectory of the same volume, such as /data/tmp. A plain emptyDir is acceptable only when the node's ephemeral storage is itself on NVMe (Karpenter's instanceStorePolicy: RAID0, GKE's --ephemeral-storage-local-ssd), with a sizeLimit. Never emptyDir with medium: Memory — see RAM-backed storage (opens in a new tab).

# values.yaml for the Spice Helm chart
stateful:
  enabled: true
  storageClass: local-storage # nvme-ssd-block on GKE
  size: 800Gi
  mountPath: /data
nodeSelector:
  local-nvme: 'true'
spicepod:
  runtime:
    query:
      temp_directory: /data/tmp # acceleration files under /data, spill under /data/tmp

# values.yaml for the Spice Helm chart
stateful:
  enabled: true
  storageClass: local-storage # nvme-ssd-block on GKE
  size: 800Gi
  mountPath: /data
nodeSelector:
  local-nvme: 'true'
spicepod:
  runtime:
    query:
      temp_directory: /data/tmp # acceleration files under /data, spill under /data/tmp

The Helm chart's storage class recommendations (opens in a new tab) list the per-cloud StorageClasses in order of preference.

Storage on Docker and bare metal

Bind-mount a directory on local NVMe into the container and point the accelerators and runtime.query.temp_directory at it — see Docker persistence (opens in a new tab). Without a mount, acceleration files land in the container's writable layer (on the host's root disk, through the storage driver) and spill lands in the image's temporary directory, which is the same place — from v2.3.1 that is /app/tmp, because the published images are built FROM scratch, carry no /tmp, and set TMPDIR=/app/tmp (see Temporary files (opens in a new tab)). Avoid Docker's --tmpfs for either, for the reasons in RAM-backed storage (opens in a new tab).

On a bare-metal or VM host, set the same two things — acceleration paths and runtime.query.temp_directory — to the NVMe mount. The runtime does not read TMPDIR for anything other than the spill fallback, and it does not relocate the default .spice/data directory, so the paths must be explicit.

Spice Cayenne Performance Optimization

Spice Cayenne (opens in a new tab) uses the Vortex (opens in a new tab) columnar format for high-performance analytics on large datasets. Cayenne is self-tuning by default and adapts to the detected storage medium; the sections below cover the knobs that are still worth setting by hand. For the full reference — self-tuning modes, goal-driven SLOs, the storage tier, write-path and compaction tuning — see the Cayenne performance guide (opens in a new tab).

Storage

Store Cayenne data files and the metastore on local NVMe. Vortex's random-access encodings turn a selective query into many small reads, and the metastore commits with fsync on every write; both run at the latency of the medium. On network block storage Cayenne biases its tuner, caps its write concurrency, and bypasses the page cache for compaction output automatically — see How Spice adapts to the storage medium (opens in a new tab) — but every cache miss still pays the volume's per-read latency, and provisioned IOPS only keep queueing from adding to it. Keep the metastore (cayenne_metadata_dir) on local disk in every configuration, including S3 Express and network file systems, and keep it outside the data directory (opens in a new tab).

Point Lookups and Random Access

Vortex provides 100x faster random access (opens in a new tab) compared to Apache Parquet through:

• Segment statistics: Per-segment min/max/null_count for predicate pushdown (zone-map equivalent)
• Fast random access encodings: FSST (opens in a new tab), FastLanes (opens in a new tab), and ALP (opens in a new tab) support O(1) or near-O(1) random access
• Compute push-down: Filter execution on compressed data without full decompression
• Array statistics: is_sorted, is_constant, min, max for query optimization

For point lookups on large datasets, Spice Cayenne often matches or exceeds the performance of traditional B-tree indexes while consuming no additional memory for index structures. Where a query pins an exact key the data is not clustered on, statistics alone cannot narrow it — declare indexes (opens in a new tab) to have Cayenne build a secondary index (opens in a new tab) for that key set.

Cache Configuration

Spice Cayenne maintains two in-memory caches that significantly impact query performance. Both are engine-global and set under runtime.params — one segment cache serves every Cayenne table in the process, so a cayenne_segment_cache_mb set under a dataset's acceleration.params is reported at startup and otherwise ignored:

runtime:
  params:
    cayenne_footer_cache_mb: 256   # Engine-global; increase for many files
    cayenne_segment_cache_mb: 512  # Process-wide; increase for hot data patterns

datasets:
  - from: s3://bucket/data/
    name: analytics
    acceleration:
      engine: cayenne
      mode: file

runtime:
  params:
    cayenne_footer_cache_mb: 256   # Engine-global; increase for many files
    cayenne_segment_cache_mb: 512  # Process-wide; increase for hot data patterns

datasets:
  - from: s3://bucket/data/
    name: analytics
    acceleration:
      engine: cayenne
      mode: file

Footer Cache Sizing:

The footer cache stores file metadata. Size based on file count:

• 1-10 KB per file
• Default: unset — when omitted, DataFusion's 50 MB file-metadata-cache limit applies
• Increase for datasets with more files

Segment Cache Sizing:

The segment cache stores decompressed data. Size based on working set:

• Default: unset — derived as ~1/64 of the process's memory entitlement, clamped to 256 MB–2 GB; 0 disables it
• Estimate the volume of frequently accessed data across every Cayenne table, since they share one budget
• Cache hits avoid decompression overhead and, on network storage, the round trip
• Monitor the hit rate as rate(cayenne_segment_cache_hits[5m]) / rate(cayenne_segment_cache_accesses[5m]) — see segment cache metrics (opens in a new tab)

The slower the storage, the more a larger segment cache pays back: on network block storage or S3 Express, size it to the hot working set rather than leaving the derived default.

Compression Strategy

Choose btrblocks for read-heavy analytics workloads. Use zstd only when size on disk is the primary concern—setting zstd trades query performance for reduced storage size.

Sorted Data

Set sort_columns to the columns queries filter on most. Sorted files have tight per-segment min/max ranges, so segment pruning skips most of the table for a selective predicate, and is_sorted enables binary search for point lookups. For CDC datasets the adaptive layout also clusters compacted files by the filter columns it observes on scans; an explicit sort_columns outranks that observation. See Sorted data and zone maps (opens in a new tab) in the Cayenne guide.

DuckDB Performance Optimization

DuckDB (opens in a new tab) provides mature SQL support with sophisticated query optimization.

:::tip[Datasets 10 GB or larger]

For any dataset of 10 GB or larger, Spice Cayenne (opens in a new tab) is recommended over DuckDB, because of DuckDB's memory requirements. Cayenne typically needs one-third to one-half the memory of the DuckDB accelerator for the same dataset. See Spice Cayenne Performance Optimization (opens in a new tab).

:::

Storage

Put the duckdb_file on local NVMe or SSD. DuckDB's own guidance is that its disk-based mode "is designed to work best with SSD and NVMe disks" and that HDDs give low performance, especially for writes; it also recommends against its native format in read-write mode on network-attached file systems (NAS, NFS, SMB) while confirming that network block disks such as EBS work — see DuckDB's environment guide (opens in a new tab) and Storage (opens in a new tab) above.

DuckDB spills its own sorts, joins, and aggregates to a temporary directory. The Spice runtime passes runtime.query.temp_directory to every DuckDB instance it opens, so the one setting covers both engines; when it is unset, DuckDB uses a .tmp directory next to the database file (and .tmp under the working directory for the in-memory instance). DuckDB stops spilling when its temporary files reach 90% of the volume's free space. See Temporary Directory (opens in a new tab).

On the ebs storage profile the runtime raises DuckDB's checkpoint_threshold to 256 MiB and lowers the connection pool floor to 4; on tmpfs the threshold rises to 1 GiB. Set storage_profile (opens in a new tab) explicitly when the device cannot be auto-detected.

Memory Configuration

datasets:
  - from: postgres:schema.table
    name: orders
    acceleration:
      engine: duckdb
      mode: file
      params:
        duckdb_memory_limit: 4GB
        duckdb_file: /nvme/spice/data/orders.duckdb

datasets:
  - from: postgres:schema.table
    name: orders
    acceleration:
      engine: duckdb
      mode: file
      params:
        duckdb_memory_limit: 4GB
        duckdb_file: /nvme/spice/data/orders.duckdb

Guidelines:

• Set duckdb_memory_limit to control memory per instance
• Left to itself DuckDB sizes its limit at 80% of host memory per instance; when the parameter is unset the runtime caps each instance from a coordinated memory budget (opens in a new tab) instead
• Reserve 30% of container memory for the runtime
• Multiple datasets using the same duckdb_file share a connection pool

Connection Pool Tuning

acceleration:
  engine: duckdb
  params:
    connection_pool_size: 20  # Default: 10 (4 on the ebs storage profile), or the number of datasets sharing the file if larger

acceleration:
  engine: duckdb
  params:
    connection_pool_size: 20  # Default: 10 (4 on the ebs storage profile), or the number of datasets sharing the file if larger

Increase connection_pool_size for high-concurrency workloads. Each connection consumes memory.

Index Configuration

DuckDB supports ART (Adaptive Radix Tree) indexes (opens in a new tab) for faster point lookups:

datasets:
  - from: postgres:schema.orders
    name: orders
    acceleration:
      engine: duckdb
      mode: file
      indexes:
        order_id: enabled
        '(customer_id, created_at)': enabled

datasets:
  - from: postgres:schema.orders
    name: orders
    acceleration:
      engine: duckdb
      mode: file
      indexes:
        order_id: enabled
        '(customer_id, created_at)': enabled

Indexes consume memory and do not spill to disk (opens in a new tab). Creating an index requires the entire dataset to be loaded into memory. Monitor memory usage when adding indexes. For more details on ART index performance, see the ART paper (opens in a new tab).

Zone-Maps and Sorted Data

DuckDB automatically creates zone-maps (opens in a new tab) (min/max statistics) for each row group, enabling efficient predicate pushdown. In practice, zone-maps on sorted data often outperform ART indexes for range and equality queries while consuming no additional memory.

Why Zone-Maps Outperform Indexes:

• Zero memory overhead (statistics stored with data)
• No index maintenance during writes
• Automatic predicate pushdown during scans
• Effective when data is sorted by query filter columns

Optimization Pattern: Sorted Views

Accelerate a view with ORDER BY to create sorted physical data, then set duckdb_preserve_insertion_order: true to maintain sort order:

datasets:
  - from: iceberg:catalog/namespace/table
    name: raw_data_by_arrival
    time_column: processed_time
    acceleration:
      enabled: true
      engine: duckdb
      mode: file
      refresh_mode: append
      primary_key: id
      on_conflict:
        id: upsert
      params:
        duckdb_memory_limit: 12GiB
        duckdb_preserve_insertion_order: false  # Raw data doesn't need order

views:
  - name: data_sorted
    sql: |
      SELECT id, account_id, pool_id, value, created_at
      FROM raw_data_by_arrival
      WHERE __deleted = 'false'
      ORDER BY account_id, pool_id
    acceleration:
      enabled: true
      engine: duckdb
      mode: file
      refresh_check_interval: 210s
      params:
        duckdb_file: data_sorted.duckdb
        duckdb_memory_limit: 6GiB
        duckdb_preserve_insertion_order: true  # Maintains ORDER BY sort

datasets:
  - from: iceberg:catalog/namespace/table
    name: raw_data_by_arrival
    time_column: processed_time
    acceleration:
      enabled: true
      engine: duckdb
      mode: file
      refresh_mode: append
      primary_key: id
      on_conflict:
        id: upsert
      params:
        duckdb_memory_limit: 12GiB
        duckdb_preserve_insertion_order: false  # Raw data doesn't need order

views:
  - name: data_sorted
    sql: |
      SELECT id, account_id, pool_id, value, created_at
      FROM raw_data_by_arrival
      WHERE __deleted = 'false'
      ORDER BY account_id, pool_id
    acceleration:
      enabled: true
      engine: duckdb
      mode: file
      refresh_check_interval: 210s
      params:
        duckdb_file: data_sorted.duckdb
        duckdb_memory_limit: 6GiB
        duckdb_preserve_insertion_order: true  # Maintains ORDER BY sort

Key Configuration:

Queries filtering on account_id or (account_id, pool_id) benefit from zone-map pruning, skipping entire row groups that don't match the filter predicates.

For a table rather than a view, on_refresh_sort_columns sorts the data after each refresh for the same effect; note that it currently drops indexes and constraints on that table.

Aggregate Pushdown

Enable aggregate pushdown for improved performance on supported aggregate queries:

acceleration:
  engine: duckdb
  params:
    optimizer_duckdb_aggregate_pushdown: enabled

acceleration:
  engine: duckdb
  params:
    optimizer_duckdb_aggregate_pushdown: enabled

Requires query_federation to be disabled. Supports count, sum, avg, min, and max functions.

Bounding File Growth

A repeatedly full-refreshed DuckDB file grows without bound because bulk loads bypass the WAL-triggered checkpoint that would reclaim the previous copy of the data. Set on_full_refresh (opens in a new tab) to replace_file (readers never interrupted, file can shrink) or checkpoint_file (lighter, file plateaus) so the acceleration volume is sized for the data rather than for its history.

SQLite and Turso

SQLite (opens in a new tab) and Turso (opens in a new tab) are row-oriented engines suited to small and medium datasets with simple queries and point lookups. Their performance characteristics are dominated by two things:

• Storage latency. Both serve page-cache misses with synchronous reads and commit with fsync, and both are the engines most exposed to the locking problems of network file systems (opens in a new tab). Keep their files on local NVMe/SSD. On the ebs profile the runtime raises SQLite's page cache to ~200 MB and enables 256 MiB of memory-mapped I/O, and raises Turso's page cache to ~200 MB, to absorb per-I/O latency.
• A single writer per file. Refreshes serialize on the file's write lock. Many datasets with short refresh intervals in one file contend; give write-heavy datasets their own file, raise busy_timeout (SQLite; 5 s by default, 15 s on ebs) if database is locked appears, or move the workload to DuckDB or Cayenne.

Neither engine spills intermediate results to disk; SQLite keeps temporary tables and indices in memory (temp_store = memory). Queries that join or aggregate beyond RAM belong on DuckDB or Cayenne, where DataFusion and DuckDB spill.

DataFusion Query Engine

Spice uses Apache DataFusion (opens in a new tab) as its query execution engine for Arrow and Spice Cayenne accelerators. DataFusion provides vectorized, multi-threaded query execution with automatic memory management and spilling.

Query Parallelism

DataFusion automatically parallelizes queries across available CPU cores. By default, the number of partitions equals the runtime's CPU entitlement (opens in a new tab) in whole cores, providing maximum parallelism. Override it with runtime.query.target_partitions, or state the entitlement itself with runtime.cpu.cores to size partitions and every other CPU-derived pool together.

DataFusion's GreedyMemoryPool (opens in a new tab) allows memory reservations on a first-come, first-served basis up to the configured memory_limit. This approach improves throughput for high-concurrency queries with many partitions compared to dividing memory evenly.

Join Algorithm Selection

DataFusion supports multiple join algorithms and automatically selects the best one based on query statistics:

DataFusion prefers hash joins by default for equi-joins. Hash joins do not currently support spilling, so memory-constrained environments may benefit from sort-merge joins for large datasets.

Dynamic Filter Pushdown

DataFusion pushes filters from operators (TopK, Join, Aggregate) into file scans to prune data early. This optimization is enabled by default and can skip entire row groups or files based on statistics. For example, a SELECT * FROM t ORDER BY timestamp DESC LIMIT 10 query pushes timestamp filters down to file scans, pruning files that cannot contain top-10 candidates.

Parquet Read Optimizations

Spice uses DataFusion's Parquet reader, which applies several optimizations automatically when reading Parquet files from S3, file, Iceberg, and Delta Lake connectors:

• Row group pruning: Skips entire row groups (typically 128 MB) based on min/max statistics in Parquet metadata
• Page Index filtering: Uses page-level min/max statistics (typically 8 KB chunks) for finer-grained pruning
• Bloom filter evaluation: Checks bloom filters for equality predicates when available in Parquet files
• Projection pushdown: Reads only the columns referenced in the query

These optimizations are applied automatically and require no configuration. The effectiveness depends on data layout—sorting data by frequently filtered columns maximizes row group pruning.

File Format Filtering and Optimization

Spice connects to various file formats (Parquet, Iceberg, Delta Lake) and uses DataFusion's query execution engine to push down predicates and prune data at the file, row group, and page level. Understanding these optimizations helps when designing data layouts for optimal query performance.

Parquet

Apache Parquet stores data in row groups with per-column statistics. DataFusion uses these statistics to skip row groups that cannot contain matching rows.

Row Group Pruning:

Each Parquet row group contains min/max statistics for each column. When a query includes a WHERE clause, DataFusion evaluates whether each row group could contain matching rows based on these statistics. Row groups that cannot match are skipped entirely.

For example, with a predicate WHERE timestamp > '2024-01-01', DataFusion skips row groups where the maximum timestamp is before 2024-01-01.

Page Index:

Parquet's Page Index (opens in a new tab) provides finer-grained statistics at the page level within row groups. DataFusion uses the Page Index to skip individual pages, reducing I/O for selective queries.

Bloom Filters:

Parquet files can include bloom filters for membership testing. For equality predicates like WHERE user_id = 'abc123', DataFusion checks the bloom filter before reading column data. If the bloom filter indicates the value is not present, the row group is skipped.

Late Materialization (Filter Pushdown):

DataFusion supports applying filters during Parquet decoding rather than after. This optimization, called late materialization, filters rows before materializing all columns, reducing memory usage for selective queries.

Iceberg

Apache Iceberg (opens in a new tab) provides hidden partitioning and multi-level metadata filtering that simplifies query optimization.

Hidden Partitioning:

Iceberg automatically derives partition values from source columns using transforms like day(timestamp), month(timestamp), or bucket(user_id, 16). Queries filter on the source column directly (e.g., WHERE timestamp > '2024-01-01'), and Iceberg automatically prunes partitions without requiring users to specify partition columns in predicates.

Two-Level Metadata Filtering:

Iceberg uses a hierarchical metadata structure that enables filtering at multiple levels:

1. Manifest list filtering: The manifest list contains partition value ranges for each manifest file. Manifests that cannot contain matching rows are skipped entirely.
2. Manifest filtering: Each manifest file contains per-file column statistics (min/max, null counts, value counts). Data files that cannot contain matching rows are skipped.

This two-level approach can eliminate entire groups of files before reading any data, providing significant performance benefits for large tables.

Column-Level Statistics:

Iceberg manifests store column-level statistics including:

• lower_bound and upper_bound for min/max filtering
• null_count for null handling optimization
• value_count for cardinality estimation

Partition Evolution:

Iceberg supports changing partition schemes without rewriting existing data. Historical data retains its original partitioning while new data uses the updated scheme. Queries automatically account for both partition layouts.

Delta Lake

Delta Lake (opens in a new tab) provides data skipping and Z-ordering for query optimization.

Data Skipping:

Delta Lake collects column statistics (min, max, null counts) during writes. The delta.dataSkippingNumIndexedCols table property controls how many columns have statistics collected (counted from the first column in the schema). Queries filter using these statistics to skip files that cannot contain matching rows.

Generated Columns:

Delta Lake supports generated columns that derive values from other columns. When partitioned by a generated column (e.g., eventDate generated from CAST(eventTime AS DATE)), queries filtering on the source column automatically benefit from partition pruning.

Z-Ordering:

Z-ordering (opens in a new tab) colocates related data in the same files by clustering on specified columns. After running OPTIMIZE ... ZORDER BY (column), queries filtering on the Z-ordered columns benefit from improved data skipping.

Compaction:

Delta Lake's OPTIMIZE command compacts small files into larger ones, reducing the number of files to scan and improving query performance through better I/O patterns.

Vortex (Spice Cayenne)

Spice Cayenne uses Vortex (opens in a new tab), which provides segment-level statistics and compute push-down on compressed data.

Segment Statistics:

Vortex's ChunkedLayout maintains per-segment statistics including min, max, null_count, is_sorted, and is_constant for each column. These statistics function similarly to DuckDB's zone-maps, enabling segment pruning during query execution.

Compute Push-Down:

Vortex supports executing filter operations directly on compressed data. For encodings like FSST (strings), FastLanes (integers), and ALP (floats), predicates can be evaluated without full decompression, reducing CPU and memory usage.

Encoding-Aware Optimization:

Vortex tracks encoding metadata that enables additional optimizations:

• is_sorted: Enables binary search for point lookups
• is_constant: Returns values immediately without scanning
• Encoding-specific optimizations based on data characteristics

See Spice Cayenne Performance Optimization (opens in a new tab) for cache tuning and other Cayenne-specific settings.

Performance Implications

Optimization Recommendations:

• Sort data by filter columns: Row group and segment statistics are most effective when data is sorted by commonly filtered columns
• Use appropriate file sizes: Larger row groups (128 MB+) provide better compression but reduce pruning granularity
• Collect statistics on filter columns: Ensure filter columns are within the statistics collection limit (e.g., delta.dataSkippingNumIndexedCols)
• Consider Z-ordering for multi-column filters: When queries filter on multiple columns, Z-ordering colocates related data

Query Memory Management

Configure DataFusion query memory limits to prevent out-of-memory errors:

runtime:
  query:
    memory_limit: 8GiB
    temp_directory: /nvme/spice/tmp
    spill_compression: zstd

runtime:
  query:
    memory_limit: 8GiB
    temp_directory: /nvme/spice/tmp
    spill_compression: zstd

Memory Limit

If not specified, memory_limit defaults to 90% of the memory the process may use — its cgroup memory limit when one binds (a container, a systemd unit's MemoryMax=, a capped parent slice, or a Kubernetes pod cgroup), otherwise total system memory; when a Cayenne acceleration can reach the in-memory CDC tier (refresh_mode: changes or caching, or append with refresh_check_interval ≤ 5m), the default is 70% instead, reserving headroom for Cayenne's compaction memory pool and in-memory CDC tier. A pod whose Cayenne accelerations only bulk-write — refresh_mode: full, or append on a slower cadence — cannot fill that tier and keeps the 90% base, reduced by the off-pool per-table scan caches Cayenne holds for each accelerated table. Datasets, views and catalogs (opens in a new tab) are all classified this way; because changes is the only refresh mode catalog acceleration accepts, an accelerated catalog always reaches the tier, and it contributes one table's worth of off-pool reservation however many tables it goes on to discover (see Accelerated catalogs (opens in a new tab)). Either way the derived limit is floored at 50% of the same memory. For deployments with co-located accelerators, set an explicit limit based on available memory:

runtime memory_limit = Total Memory - Accelerator Memory - OS/Runtime Overhead (30%)

runtime memory_limit = Total Memory - Accelerator Memory - OS/Runtime Overhead (30%)

Accelerator memory in that expression is per dataset, not per deployment: each accelerated dataset holds its own caches, sized as a fraction of total memory but clamped to a floor that does not shrink with the container. The limit therefore has to be derived for the dataset count, and the ratio that works in a small environment does not carry to a large one. See How Spice Uses Memory (opens in a new tab) and Sizing a Non-Production Environment (opens in a new tab).

Note also that this limit bounds the query execution pool, not the process. Serialization buffers, accelerator caches, and allocator retention sit outside it, which is why lowering it does not always reduce resident memory — see What the Memory Limit Does Not Cover (opens in a new tab).

When the goal is to reduce peak memory, prefer bounding concurrency over lowering this limit:

Lowering max_concurrent_queries reduces peak memory usage directly, by bounding how many plans hold reservations at once. The trade-off is latency: excess queries wait for admission rather than being refused, so peak end-to-end query time rises with the time spent queueing. Tune it against the workload's tolerance for wait, and watch total query duration — not just execution time — when you change it.

Lowering memory_limit shrinks the pool each query draws from without reducing how many run concurrently, and on a Cayenne CDC deployment it can leave resident memory unchanged because the in-memory CDC tier expands into the freed budget — see Tuning the Memory Limit Safely (opens in a new tab).

Spill-to-Disk and the Temporary Directory

When a sort, aggregation, or sort-merge join cannot reserve more memory from the pool, DataFusion writes its intermediate batches to disk as Arrow IPC Stream (opens in a new tab) files and reads them back in a merge pass. Spilling turns an out-of-memory refusal into a slower query — but only if the spill directory is fast enough and large enough to take it.

Where spill goes. runtime.query.temp_directory names the directory; DataFusion creates a uniquely named subdirectory beneath it for each runtime and removes the files when the query completes. The one setting covers every consumer of temporary space:

The default is the operating system's temporary directory ($TMPDIR, otherwise /tmp). On most hosts that is the root volume: on a cloud instance a small network-attached disk, in a container the writable layer. Both are the wrong place — slow, small, and shared with logs and images — and a spill that fails for lack of space does not fall back to memory; the query fails with a ResourcesExhausted error, indistinguishable at first glance from an ordinary out-of-memory refusal. When Cayenne acceleration is active and the setting is unset, the runtime logs this at startup:

Cayenne acceleration is active but runtime.query.temp_directory is unset: large analytical queries spill to the OS temp directory. If your data is on a separate volume (e.g. EBS) and the root volume is small, set runtime.query.temp_directory to a path with ample free space so large queries can spill instead of failing.

Cayenne acceleration is active but runtime.query.temp_directory is unset: large analytical queries spill to the OS temp directory. If your data is on a separate volume (e.g. EBS) and the root volume is small, set runtime.query.temp_directory to a path with ample free space so large queries can spill instead of failing.

Set it explicitly, on local NVMe or SSD:

runtime:
  query:
    temp_directory: /nvme/spice/tmp

runtime:
  query:
    temp_directory: /nvme/spice/tmp

Guidance for the directory:

• Local NVMe/SSD. Each spilled batch is a synchronous write the operator waits on, and the merge pass reads the batches back the same way, so per-I/O latency accumulates along the query's critical path while the query holds its memory reservation. On NVMe a spill is a modest slowdown; on a gp3 volume each of those waits is a millisecond or more, and the spill is additionally bounded by the volume's throughput and competes with data reads for the instance's EBS bandwidth; on a network file system it is slower still. See Storage (opens in a new tab).
• Never RAM-backed. A tmpfs spill directory charges the spilled bytes to the same memory the query was trying to free — see RAM-backed storage (opens in a new tab).
• Size it for 2–4× the largest input that can exceed memory, and account for concurrent spilling queries.
• Know the cap. DataFusion refuses to spill more than 100 GB in total per runtime environment; a query whose spill would exceed it fails with The used disk space during the spilling process has exceeded the allowable limit. The cap is not configurable in the current release: there is no runtime.query setting for it, and SET datafusion.runtime.max_temp_directory_size is rejected because Spice's query APIs do not accept SET statements. Queries and compaction have separate environments, so each has its own 100 GB. A workload that approaches the cap needs more memory, fewer concurrent queries, or a smaller working set (more selective predicates, sorted data, sort_columns), not a larger volume.
• Watch free space. Monitor the volume's free bytes; DuckDB stops spilling at 90% of the volume's free space, and a full volume fails Cayenne ingestion.

Not every operator spills. Hash joins, the external sort's final merge, and repartition merges cannot spill; a query that exceeds memory in one of those fails regardless of the temporary directory. See Spill Limitations (opens in a new tab).

Spill Compression

Compression trades CPU for bytes. On a volume whose throughput is the constraint (EBS at its provisioned MiB/s, an instance near its EBS bandwidth cap), zstd writes fewer bytes and usually finishes sooner. On local NVMe, which can absorb several GB/s, the compression itself can become the bottleneck, and lz4_frame or uncompressed is often faster end-to-end. Measure with the actual spill-heavy query rather than assuming; the difference shows in total query duration.

Batch Processing

DataFusion processes data in batches of 8192 rows by default. This batch size balances memory usage with vectorized execution efficiency. Larger batches improve CPU cache utilization and SIMD operations but consume more memory per partition.

Caching Configuration

Spice supports multiple caching layers for query acceleration.

SQL Results Cache

Cache query results for repeated queries:

runtime:
  caching:
    sql_results:
      enabled: true
      max_size: 512MiB
      item_ttl: 5m
      eviction_policy: tiny_lfu  # Higher hit rate than lru
      cache_key_type: plan       # Matches semantically equivalent queries

runtime:
  caching:
    sql_results:
      enabled: true
      max_size: 512MiB
      item_ttl: 5m
      eviction_policy: tiny_lfu  # Higher hit rate than lru
      cache_key_type: plan       # Matches semantically equivalent queries

Cache Key Types:

Use sql for lowest latency with identical queries. Use plan for semantic query matching.

Stale-While-Revalidate

Serve stale cached results while refreshing in the background:

runtime:
  caching:
    sql_results:
      enabled: true
      item_ttl: 1m
      stale_while_revalidate_ttl: 5m  # Serve stale for 5m while refreshing

runtime:
  caching:
    sql_results:
      enabled: true
      item_ttl: 1m
      stale_while_revalidate_ttl: 5m  # Serve stale for 5m while refreshing

This pattern reduces query latency spikes during cache refresh.

Data Refresh Optimization

Refresh Mode Selection

A full refresh also rewrites the acceleration file, so its cost scales with the storage medium: on network block storage a full refresh of a large DuckDB or Cayenne table is bounded by the volume's write throughput, and a DuckDB file grows by the whole table on every cycle until on_full_refresh (opens in a new tab) reclaims the space. Prefer append or changes for anything large.

Append Mode Optimization

Use time_column for efficient incremental updates:

datasets:
  - from: s3://bucket/events/
    name: events
    time_column: event_time
    acceleration:
      engine: cayenne
      mode: file
      refresh_mode: append
      refresh_check_interval: 5m

datasets:
  - from: s3://bucket/events/
    name: events
    time_column: event_time
    acceleration:
      engine: cayenne
      mode: file
      refresh_mode: append
      refresh_check_interval: 5m

Partitioned Data

For data that has a granular time column and a separate column for partitioning (i.e. day buckets), set time_partition_column to the partitioning column based on time:

datasets:
  - from: s3://bucket/events/
    name: events
    time_column: event_time
    time_partition_column: event_date  # Physical partition column
    acceleration:
      refresh_mode: append

datasets:
  - from: s3://bucket/events/
    name: events
    time_column: event_time
    time_partition_column: event_date  # Physical partition column
    acceleration:
      refresh_mode: append

In this scenario, event_date is the day bucket used for physical partitioning (e.g., s3://bucket/events/event_date=2025-10-01/), while event_time provides the granular timestamp for precise filtering.

Last-Modified Optimization

For object storage with append-only files, use last_modified to skip unchanged files:

datasets:
  - from: s3://bucket/logs/
    name: logs
    time_column: last_modified  # Special value using file metadata
    acceleration:
      refresh_mode: append

datasets:
  - from: s3://bucket/logs/
    name: logs
    time_column: last_modified  # Special value using file metadata
    acceleration:
      refresh_mode: append

Fast Cold Starts

On ephemeral storage every restart is a cold start. Acceleration snapshots (opens in a new tab) (Spice.ai Enterprise) upload the acceleration file after each refresh and download the newest one on boot when the local file is missing, so a replacement pod on fresh NVMe serves queries in the time it takes to copy the file rather than the time it takes to re-read the source. Enable snapshots_compaction for DuckDB so the uploaded file is compact.

Resource Allocation

Kubernetes

Configure resource requests and limits based on workload:

apiVersion: v1
kind: Pod
metadata:
  name: spice
spec:
  containers:
    - name: spice
      image: spiceai/spiceai:latest
      resources:
        requests:
          memory: '8Gi'
          cpu: '4'
        limits:
          memory: '12Gi'
          # Do not set CPU limits - can cause throttling
      volumeMounts:
        - name: data
          mountPath: /data # acceleration files and spill (runtime.query.temp_directory: /data/tmp)
  volumes:
    - name: data
      persistentVolumeClaim:
        claimName: spice-data # local NVMe PersistentVolume; see Local NVMe Storage on Kubernetes

apiVersion: v1
kind: Pod
metadata:
  name: spice
spec:
  containers:
    - name: spice
      image: spiceai/spiceai:latest
      resources:
        requests:
          memory: '8Gi'
          cpu: '4'
        limits:
          memory: '12Gi'
          # Do not set CPU limits - can cause throttling
      volumeMounts:
        - name: data
          mountPath: /data # acceleration files and spill (runtime.query.temp_directory: /data/tmp)
  volumes:
    - name: data
      persistentVolumeClaim:
        claimName: spice-data # local NVMe PersistentVolume; see Local NVMe Storage on Kubernetes

For the volume itself — node types with NVMe, mounting the disks, and publishing them as PersistentVolumes — follow Local NVMe Storage on Kubernetes (opens in a new tab).

:::tip[CPU Limits]

Avoid setting CPU limits. CPU limits can cause throttling (opens in a new tab) even when CPU is available, degrading query performance. Set CPU requests to guarantee scheduling.

To bound how wide Spice builds its thread pools without imposing a CFS quota, use runtime.cpu.cores (opens in a new tab) rather than resources.limits.cpu. It caps how much machine the runtime organizes itself around; it does not cap how much CPU the process may consume, so there is no throttling.

:::

Do not back the data or spill volume with emptyDir: { medium: Memory } — the files count against the container's memory limit, so a spill cannot relieve memory pressure and a large acceleration file becomes an OOM kill. See Storage on Kubernetes (opens in a new tab) for the volume options.

Sizing the Runtime for its CPU Entitlement

The runtime sizes its thread pools, query fan-out, and accelerator concurrency from one CPU entitlement. Every CPU-derived pool scales with that number, and so does memory: worker threads, partitions, and per-plan operator reservations all grow with it, roughly linearly. A value far above the cores actually available buys nothing and costs both scheduling overhead and memory.

A pod that follows the advice above — requests, no limits — is sized for a bounded multiple of its CPU request, not for the whole node. A requests.cpu: 4 pod on a 64-core node sizes for 8 cores. This is the default and needs no configuration: the Spice Helm chart and the Spice Kubernetes Operator both pass the pod's CPU request through automatically whenever one is set.

That default suits the common case — a pod scheduled against a request, sharing a node. Two deployments want something else:

runtime:
  cpu:
    cores: all # every available core, regardless of the CPU request
               # (a CPU limit, if one is set, is still respected)

runtime:
  cpu:
    cores: all # every available core, regardless of the CPU request
               # (a CPU limit, if one is set, is still respected)

A hand-written pod spec that sets a CPU request but does not pass it through gets neither behavior — it falls back to sizing for the machine, and the runtime warns at startup naming the variable to set. See Sizing from a CPU request (opens in a new tab).

Compare spiced_cpu_budget_cores against spiced_cpu_request_millicores and spiced_cpu_limit_millicores to see what a pod sized for and what it was chosen against; the source label says which rung produced it. See runtime.cpu (opens in a new tab).

Storage Recommendations

See Storage (opens in a new tab) for the reasoning, the per-engine adjustments, and the platform specifics.

Monitoring and Profiling

Task History

Enable query plan capture for slow query analysis:

runtime:
  task_history:
    enabled: true
    captured_plan: explain analyze
    min_sql_duration: 1s  # Only capture plans for queries >1s

runtime:
  task_history:
    enabled: true
    captured_plan: explain analyze
    min_sql_duration: 1s  # Only capture plans for queries >1s

Metrics

Monitor key performance metrics:

• query_duration_ms - Query execution time
• query_executions - Query throughput
• query_failures with err_code="ResourcesExhausted" - Memory refusals and failed spills
• query_memory_pool_used_bytes against process_resident_anon_bytes - Pool usage versus off-pool memory
• dataset_load_state - Acceleration status
• Cache hit rates — SQL results, and cayenne_segment_cache_hits / cayenne_segment_cache_accesses for Cayenne

Storage-related metrics:

• cayenne_data_storage_class and cayenne_metastore_storage_class — the detected storage tier per table (0 local SSD, 1 network-attached, 2 tmpfs, 3 unknown); cayenne_data_storage_write_mibps and cayenne_metastore_storage_write_mibps — the measured write throughput
• cayenne_storage_bytes, cayenne_data_dir_bytes, and cayenne_metastore_db_bytes / cayenne_metastore_wal_bytes — the on-disk footprint, see storage footprint metrics (opens in a new tab)
• cayenne_write_phase_duration_ms by phase and cayenne_compaction_duration_ms — where write and compaction time goes; a rising vortex_write or publish_commit phase on an unchanged workload is the storage slowing down
• Host and cloud counters — device await and queue depth from iostat, free space on the data and spill volumes, and on AWS the EBS VolumeQueueLength, BurstBalance, EBSIOBalance%, and EBSByteBalance% metrics

See Observability (opens in a new tab) for metric configuration.

Performance Checklist

Use this checklist when optimizing Spice deployments:

• Select appropriate accelerator based on dataset size and query patterns
• Place acceleration files (duckdb_file, sqlite_file, turso_file, cayenne_file_path, cayenne_metadata_dir) on local NVMe/SSD, not the root volume, a network file system, or a RAM-backed mount
• Set runtime.query.temp_directory to a directory on local NVMe/SSD with 2–4× the largest spillable input free
• Set storage_profile explicitly where auto-detection cannot see the device (GCP Persistent Disk, SAN, non-Linux hosts)
• On network block storage, prefer a sub-millisecond tier (io2 Block Express, Premium SSD v2, Hyperdisk Extreme), provision IOPS and throughput, and choose an instance whose EBS bandwidth sustains them; plan for instance-store data loss with snapshots or re-materialization
• Configure memory limits for DuckDB and/or Spice Cayenne caches
• Bound peak memory with runtime.query.max_concurrent_queries before lowering runtime.query.memory_limit
• Enable caching for repeated queries
• Use refresh_mode: append or changes for large and time-series data; set on_full_refresh on full-refreshed DuckDB files
• Sort accelerated data by filter columns (sort_columns, on_refresh_sort_columns, sorted views)
• Configure indexes for point lookup queries (DuckDB/SQLite)
• Set resource requests in Kubernetes; no CPU limit; runtime.cpu.cores to bound thread pools
• Enable observability for monitoring, including storage tier, footprint, and free-space metrics

Related Documentation

Spice Documentation:

• Managing Memory Usage (opens in a new tab) - Memory configuration reference
• acceleration.storage_profile (opens in a new tab) - Storage profile reference
• runtime.query.temp_directory (opens in a new tab) - Temporary directory reference
• Data Accelerators (opens in a new tab) - Accelerator documentation
• Spice Cayenne Performance Tuning (opens in a new tab) - Spice Cayenne-specific tuning, including the storage tier
• DuckDB Data Accelerator (opens in a new tab) - DuckDB-specific tuning
• Acceleration Snapshots (opens in a new tab) - Fast cold starts on ephemeral storage
• Read/Write Separation (opens in a new tab) - Read replicas on ephemeral storage
• Kubernetes Deployment (opens in a new tab) and Helm storage class recommendations (opens in a new tab)
• AWS (opens in a new tab), Azure (opens in a new tab), and GCP (opens in a new tab) deployment guides - Platform-specific storage options
• Caching (opens in a new tab) - Cache configuration
• Observability (opens in a new tab) - Metrics and monitoring

External References:

• Apache DataFusion (opens in a new tab) - Query execution engine for Arrow and Spice Cayenne
• DataFusion Configuration (opens in a new tab) - DataFusion configuration settings
• DataFusion Tuning Guide (opens in a new tab) - Performance tuning for DataFusion
• DuckDB Environment Guide (opens in a new tab) - Disk, file system, and network storage guidance for DuckDB
• DuckDB Indexing (opens in a new tab) - Zone-maps and ART index documentation
• How To Corrupt An SQLite Database File (opens in a new tab) - SQLite on network file systems
• Amazon EBS General Purpose volumes (opens in a new tab), Provisioned IOPS volumes (opens in a new tab), and EBS-optimized instances (opens in a new tab)
• Amazon EC2 instance store data persistence (opens in a new tab)
• Azure managed disk types (opens in a new tab) and Google Cloud Local SSD (opens in a new tab)
• Kubernetes volumes (opens in a new tab) - emptyDir, local, and memory-backed volumes
• Vortex (opens in a new tab) - Columnar format used by Spice Cayenne
• Vortex Benchmarks (opens in a new tab) - Performance benchmarks for Vortex
• Apache Parquet File Format (opens in a new tab) - Row groups, statistics, and Page Index
• Iceberg Partitioning (opens in a new tab) - Hidden partitioning and partition evolution
• Iceberg Performance (opens in a new tab) - Metadata filtering and column statistics
• Delta Lake Optimizations (opens in a new tab) - Data skipping, Z-ordering, and compaction