title: 'Partitioning' sidebar_label: 'Partitioning' description: 'Partition accelerated datasets to make filtered queries faster by reading only the relevant partitions.' sidebar_position: 4

Partitioning splits an accelerated dataset into multiple physical units (files or in-memory tables) keyed by an expression evaluated per row. Queries that filter on the partitioning expression — or on a column it references — only read the partitions that can match, dramatically reducing the data scanned.

datasets:
  - from: s3://spiceai-demo-datasets/taxi_trips/2024/
    name: taxi_trips
    params:
      file_format: parquet
    acceleration:
      enabled: true
      engine: cayenne
      mode: file
      partition_by:
        - bucket(50, PULocationID)

datasets:
  - from: s3://spiceai-demo-datasets/taxi_trips/2024/
    name: taxi_trips
    params:
      file_format: parquet
    acceleration:
      enabled: true
      engine: cayenne
      mode: file
      partition_by:
        - bucket(50, PULocationID)

This config writes 50 separate partitions, each containing the rows whose PULocationID hashes to the same bucket. A query like WHERE PULocationID = 132 only reads the single bucket partition that could contain 132.

How partitioning works

1. At refresh time, Spice evaluates each partition_by expression for every row and routes the row to a partition keyed by the expression's value.
2. At query time, Spice rewrites filters that reference the partition column or expression into a partition selection, and only reads partitions that could satisfy the filter (partition pruning (opens in a new tab)).
3. Composite partitioning (Arrow and Cayenne) layers multiple expressions hierarchically — e.g. year then month — to combine pruning across dimensions.

Partitioning is most useful when:

• Queries reliably filter on a small subset of values (region = 'EU', tenant_id IN (...), created_at >= today() - INTERVAL '7 days').
• A single column has high cardinality and partitioning by it directly would create too many tiny files — bucket(N, col) collapses to N partitions.
• The dataset grows large enough that scanning the whole acceleration on every query is expensive.

Configuration

partition_by

Lives directly under acceleration: (not under acceleration.params:). It's a list of expressions; each entry is either a plain string or a single-entry { name: expression } mapping:

acceleration:
  enabled: true
  engine: arrow
  partition_by:
    # Anonymous expression — auto-named "expr0", "expr1", …
    - "YEAR(created_at)"
    # Named expression
    - month: "MONTH(created_at)"

acceleration:
  enabled: true
  engine: arrow
  partition_by:
    # Anonymous expression — auto-named "expr0", "expr1", …
    - "YEAR(created_at)"
    # Named expression
    - month: "MONTH(created_at)"

Multi-entry mappings (- year: "…", month: "…" on one list item) are rejected at load time.

Supported engines

An acceleration with no engine uses Cayenne on Linux and macOS, so partition_by with no engine needs a file mode, such as mode: file. Set engine: arrow to partition in memory. See Default Engine (opens in a new tab).

duckdb, sqlite, postgres, and turso accelerators do not support partition_by; configuring it on those engines is rejected at load time. Use arrow or cayenne for partitioned acceleration.

Partition transforms

partition_by accepts any DataFusion-compatible scalar SQL expression that returns a String, integer, Boolean, or Timestamp and references exactly one column from the dataset. The most common partition transforms are:

bucket(num_buckets, column)

Hashes column into num_buckets deterministic buckets.

• num_buckets must be a positive integer literal ≤ 1,000,000.
• The return type matches num_buckets — bucket(50, …) (Int64 literal) returns Int64; bucket(50::int32, …) returns Int32.
• Same input always maps to the same bucket for a given num_buckets (uses ahash with a fixed seed).
• Use for: high-cardinality columns where direct partitioning would create too many partitions (user_id, account_id, device_id).

partition_by:
  - bucket(100, user_id)

partition_by:
  - bucket(100, user_id)

See the bucket reference (opens in a new tab) for the full SQL signature.

truncate(width, value)

Truncates to the next-lower multiple of width. Iceberg's truncate transform.

• width must be a positive Int64 literal.
• value may be any signed/unsigned integer, Decimal128/Decimal256, Utf8 (string), or Binary. For strings/binary, returns the first width units.
• Returns the same type as value.
• Use for: floor-bucketing wide numeric ranges (truncate(1000, amount)), or grouping strings by prefix (truncate(2, country_code)).

partition_by:
  - truncate(1000, amount)

partition_by:
  - truncate(1000, amount)

See the truncate reference (opens in a new tab) for examples.

date_part(unit, column) and date_trunc(unit, column)

Built-in DataFusion datetime functions. Useful for time-based partitioning at year, month, day, or hour granularity.

• date_part('year', col) returns the integer year (e.g. 2026).
• date_trunc('day', col) returns the timestamp truncated to the start of the day.

partition_by:
  - date_part('year', l_shipdate)

partition_by:
  - date_part('year', l_shipdate)

partition_by:
  - day: "date_trunc('day', created_at)"

partition_by:
  - day: "date_trunc('day', created_at)"

YEAR(col), MONTH(col), DAY(col), etc. are aliases of date_part(...) and work identically.

:::note Filter pruning for date_part() is not yet implemented A date_part('year', l_shipdate) partition still produces correctly-distributed partitions, but a filter like WHERE l_shipdate >= '2026-01-01' does not currently translate to a partition pruning. Queries return correct results — they just scan more partitions than necessary. If your filter is on the bare partition expression (WHERE date_part('year', l_shipdate) = 2026), the equality form does prune. :::

Modulo (column % N)

A plain modulo expression also produces stable, partition-prunable buckets:

partition_by:
  - "id % 16"

partition_by:
  - "id % 16"

Range filters on the base column (id BETWEEN 0 AND 1000) are pruned for % partitions.

Plain column reference

A bare column name partitions one partition per distinct value — useful for low-cardinality columns:

partition_by:
  - region

partition_by:
  - region

Pruning supports equality, IN, NOT IN, and range filters on the column.

Composite partitioning (Arrow + Cayenne)

Arrow and Cayenne accelerations accept multiple partition_by expressions. Spice partitions hierarchically — first by the leftmost expression, then by the next, and so on.

acceleration:
  enabled: true
  engine: cayenne
  mode: file
  partition_by:
    - year: "date_part('year', created_at)"
    - month: "date_part('month', created_at)"
    - region: region

acceleration:
  enabled: true
  engine: cayenne
  mode: file
  partition_by:
    - year: "date_part('year', created_at)"
    - month: "date_part('month', created_at)"
    - region: region

A query with WHERE region = 'EU' AND date_part('year', created_at) = 2026 prunes on both axes.

Partition pruning

Spice attempts to translate query-time filters into a partition selection. The matrix below summarizes which filter shapes prune which partition transforms:

Pruning notes:

• Filter on the base column is substituted into the partition expression. WHERE user_id = 42 against a bucket(100, user_id) partition evaluates bucket(100, 42) once and reads only that partition.
• Bucket inequality pruning enumerates candidate values within a bounded Int32 range (capped at MAX_BUCKET_ENUMERATION_I32 candidate values). Open-ended ranges (col < X with no lower bound) and non-Int32 types fall back to no pruning.
• date_part() filter pruning is not yet implemented for time-range filters. Equality on the partition expression still prunes; range filters on the base column do not.
• Filters that don't fully resolve at the partition layer are passed through to the data layer — pruning is best-effort and never returns wrong rows.

Engine-specific behavior

Arrow (engine: arrow)

In-memory MemTable per partition value. Partitions are rebuilt on every refresh. The simplest engine to start with for moderate datasets that fit in RAM. hash_index and sort_columns are propagated per partition.

Cayenne (engine: cayenne)

Each partition is a separate Cayenne (Vortex) table; the partition catalog is tracked in a SQLite metadata file. Cayenne supports composite partitioning natively and is the right pick for very large datasets where Arrow would not fit in memory.

Requires mode: file.

Partitioned Cayenne tables have these limitations:

• No in-place schema evolution. Schema changes use the recreate fallback. Configuring on_schema_change (opens in a new tab) to evolve logs a warning; CDC batches with widened schemas are rejected.

Full refreshes and partitions

A refresh_mode: full refresh — and any other whole-table overwrite — replaces the contents of every existing partition, not only the ones the new rows reach. A partition the incoming rows never land in is emptied, so a row removed at the source does not stay queryable in a partition the refresh did not visit, and a refresh that returns nothing empties the table. This holds for engine: arrow and engine: cayenne alike.

Changing partition_by after refresh

Spice does not re-partition existing acceleration data. An Arrow acceleration lives in memory and is rebuilt from the source on every start, so a new partition_by takes effect on restart. A Cayenne acceleration in mode: file keeps its data and catalog on disk, so its stored layout must match partition_by when the dataset loads. A change is detected when it adds partition_by to unpartitioned data, removes it from partitioned data, or changes the partition column names.

Rebuilt from the source with refresh_mode: full

A file-mode Cayenne dataset with refresh_mode: full and write_mode: write_through (the default), from a source other than sink, is rebuilt with the new partition_by on the next start. Every full refresh rewrites every row from the source, so dropping the stored rows loses nothing a reload does not bring back. The runtime drops the acceleration, reloads it from the source as on a first start, and logs the change:

INFO The existing acceleration of dataset 'trips' was built with settings it cannot adopt in place (`partition_by`: none -> ['bucket(2, id)']), so it is dropped and reloaded from the source with the new settings.

INFO The existing acceleration of dataset 'trips' was built with settings it cannot adopt in place (`partition_by`: none -> ['bucket(2, id)']), so it is dropped and reloaded from the source with the new settings.

The rebuild happens only after the source answers a read on the schema the dataset registered with. Stored rows cannot be read under another partitioning, so while the source is unreachable, or while on_schema_change: block holds back a change to its schema, the dataset fails to load with an error that says to restore the previous partition_by or to restart once the source can be read. See Changing settings on an existing acceleration (opens in a new tab) for the other Cayenne settings that are applied on restart.

Rebuilt by hand in other refresh modes

Any other file-mode Cayenne dataset, such as one with refresh_mode: append or changes, write_mode: write_back or acceleration, or a sink source, can hold rows a reload does not bring back, so Spice does not rebuild it. It fails to load when its stored data was written with a different partitioning than its current partition_by. The runtime logs one error and does not retry. The error has this form, with the values in angle brackets filled in:

Failed to load dataset <dataset> (cayenne): its acceleration data was written <stored partitioning>, but the dataset now sets <configured partition_by>. Cayenne does not re-partition existing acceleration data. To keep using the existing data, <remove or restore partition_by>. To apply the new 'partition_by' instead, stop Spice, delete the acceleration data directory '<data_dir>' and the Cayenne metastore directory '<metadata_dir>', then start Spice to reload the dataset from the source. Other Cayenne datasets that use this metastore also reload from their sources. See: https://spiceai.org/docs/features/data-acceleration/partitioning

Failed to load dataset <dataset> (cayenne): its acceleration data was written <stored partitioning>, but the dataset now sets <configured partition_by>. Cayenne does not re-partition existing acceleration data. To keep using the existing data, <remove or restore partition_by>. To apply the new 'partition_by' instead, stop Spice, delete the acceleration data directory '<data_dir>' and the Cayenne metastore directory '<metadata_dir>', then start Spice to reload the dataset from the source. Other Cayenne datasets that use this metastore also reload from their sources. See: https://spiceai.org/docs/features/data-acceleration/partitioning

The stored partitioning reads without 'partition_by' or partitioned by [<columns>], and the configured value reads no 'partition_by' or 'partition_by: [<expressions>]'.

The check compares partition column names, not expressions. An entry whose expression is a plain column is compared by the column name, even when it is written as a { name: column } mapping. Any other expression is compared by its mapping key, or by expr0, expr1, and so on when it has none. A change that keeps these names is not detected, such as bucket(10, id) to bucket(20, id) as expr0, or renaming the key of a { name: id } entry: the dataset loads, and its existing partitions keep the layout of the old expression. For a dataset that is not rebuilt automatically, follow the steps below for any change to a partition expression, not only for changes the error reports. With refresh_mode: full, a change that keeps these names is not detected either, so it is not rebuilt automatically.

To keep the existing data, restore the partition_by the data was written with, or remove partition_by if the data was written without it.

Deleting the metastore removes the catalog for every Cayenne dataset that shares it, and those datasets reload from their sources. Rows that exist only in Cayenne cannot be reloaded and are lost: rows in accelerator-only datasets (acceleration.write_mode: acceleration (opens in a new tab) or sink datasets), and write-back rows not yet written to the source.

To apply the new partition_by:

1. Stop Spice with a graceful shutdown (SIGTERM).
2. Unless every dataset sharing the metastore can be fully reloaded from its source, back up the whole metastore directory, including every sidecar file next to cayenne.db, and every data directory it catalogs. Take the backup only after Spice has stopped.
3. Identify the active metastore directory. A process opens one shared catalog, from whichever Cayenne dataset initializes first, and the error names the directory resolved for the failing dataset, which can be a different one when datasets resolve different directories (see Metastore location (opens in a new tab)). When every Cayenne dataset resolves the same directory, that directory is the active one. Otherwise, treat every directory a Cayenne dataset resolves as a candidate, include each in the backup, and set the same explicit cayenne_metadata_dir on every Cayenne dataset before restarting.
4. Delete the active metastore directory, or every candidate directory, and every data directory the deleted metastore catalogs, not only the failing dataset's. Deleting only the data directory is not enough, because the metastore keeps the dataset's file list and refresh checkpoint. A data directory left behind is no longer referenced by any catalog, so its files stay on disk while the dataset reloads into new files.
5. Update partition_by.
6. Start Spice. The dataset reloads from its source.

Validation rules

A partition_by expression is rejected at startup if any of the following hold:

• Result type is not String, an integer (signed or unsigned), Boolean, or Timestamp.
• Expression references zero or multiple dataset columns.
• Expression contains a subquery, OUTER REFERENCE, UNNEST, window function, aggregate function, EXISTS, GROUPING SET, or PLACEHOLDER.
• Expression aliases the column (col AS partition_key).

For the cayenne engine, mode: must be file.

Examples

High-cardinality hash partitioning

Partition events by user, collapsing millions of users into 100 stable buckets:

datasets:
  - from: s3://my-bucket/events/
    name: events
    params:
      file_format: parquet
    acceleration:
      enabled: true
      engine: cayenne
      mode: file
      partition_by:
        - bucket(100, user_id)

datasets:
  - from: s3://my-bucket/events/
    name: events
    params:
      file_format: parquet
    acceleration:
      enabled: true
      engine: cayenne
      mode: file
      partition_by:
        - bucket(100, user_id)

-- Reads the single bucket that hashes user_id 42:
SELECT COUNT(*) FROM events WHERE user_id = 42;

-- Reads at most 4 bucket partitions:
SELECT * FROM events WHERE user_id IN (1, 2, 3, 4);

-- Reads the single bucket that hashes user_id 42:
SELECT COUNT(*) FROM events WHERE user_id = 42;

-- Reads at most 4 bucket partitions:
SELECT * FROM events WHERE user_id IN (1, 2, 3, 4);

Time-based partitioning by year

acceleration:
  enabled: true
  engine: cayenne
  mode: file
  partition_by:
    - date_part('year', l_shipdate)

acceleration:
  enabled: true
  engine: cayenne
  mode: file
  partition_by:
    - date_part('year', l_shipdate)

-- Equality on the partition expression prunes:
SELECT * FROM lineitem WHERE date_part('year', l_shipdate) = 2026;

-- Equality on the partition expression prunes:
SELECT * FROM lineitem WHERE date_part('year', l_shipdate) = 2026;

Composite year/month partitioning (Cayenne)

acceleration:
  enabled: true
  engine: cayenne
  mode: file
  partition_by:
    - year: "date_part('year', created_at)"
    - month: "date_part('month', created_at)"

acceleration:
  enabled: true
  engine: cayenne
  mode: file
  partition_by:
    - year: "date_part('year', created_at)"
    - month: "date_part('month', created_at)"

Truncate-based numeric partitioning

acceleration:
  enabled: true
  engine: arrow
  partition_by:
    - "truncate(1000, order_total_cents)"

acceleration:
  enabled: true
  engine: arrow
  partition_by:
    - "truncate(1000, order_total_cents)"

-- Range pruning works because truncate is monotonic:
SELECT * FROM orders WHERE order_total_cents BETWEEN 5000 AND 9999;

-- Range pruning works because truncate is monotonic:
SELECT * FROM orders WHERE order_total_cents BETWEEN 5000 AND 9999;

Plain column partitioning by region

acceleration:
  enabled: true
  engine: arrow
  partition_by:
    - region

acceleration:
  enabled: true
  engine: arrow
  partition_by:
    - region

-- One partition read:
SELECT * FROM events WHERE region = 'EU';

-- One partition read:
SELECT * FROM events WHERE region = 'EU';

Cookbook

• A cookbook recipe to partition an accelerated dataset so queries skip partitions they do not need. Dataset Partitioning (opens in a new tab)
• A cookbook recipe to partition a Cayenne-accelerated dataset with the bucket function. Hashed Partitioning with Cayenne (opens in a new tab)

Related

• bucket SQL reference (opens in a new tab)
• truncate SQL reference (opens in a new tab)
• date_part SQL reference (opens in a new tab)
• date_trunc SQL reference (opens in a new tab)
• Data refresh modes (opens in a new tab) — time_partition_column for time-pruned refreshes
• Sharded deployment (opens in a new tab) — partitioning across multiple Spice instances rather than within one acceleration