Skip to main content
Version: Next

Indexes

Database indexes are essential for optimizing query performance. This document explains how to add indexes to tables created by Spice for local data acceleration.

Example Spicepod:

datasets:
  - from: spice.ai/eth.recent_blocks
    name: eth.recent_blocks
    acceleration:
      enabled: true
      engine: sqlite
      indexes:
        number: enabled # Index the `number` column
        '(hash, timestamp)': unique # Add a unique index with a multicolumn key comprised of the `hash` and `timestamp` columns

Column References​

Column references can be used to specify which columns to index. The column reference can be a single column name or a multicolumn key. A multicolumn key is a comma-separated list of column names, and the enclosing parentheses are optional.

Examples

  • number: Index the number column
  • (hash, timestamp): Index the hash and timestamp columns
  • "service.instance.id": Index the service.instance.id column, written with the quotes SQL uses

A column name may be double-quoted, and the quotes are not part of the name. Names are matched against the schema's field names as written rather than parsed as SQL identifiers, and a column whose name contains ,, ;, :, (, ) or " cannot be referenced. See Column names for the full rules, which are shared by indexes, primary_key and on_conflict.

Index Types​

There are two types of indexes that can be specified in a Spicepod:

  • enabled: Creates a standard index on the specified column(s).
    • Similar to specifying CREATE INDEX my_index ON my_table (my_column).
  • unique: Creates a unique index on the specified column(s). See Constraints for more information on working with unique constraints on locally accelerated tables.
    • Similar to specifying CREATE UNIQUE INDEX my_index ON my_table (my_column).
Limitations

Traditional indexes are not supported for the in-memory Arrow acceleration engine. Use DuckDB, SQLite, Turso, or PostgreSQL as the acceleration engine to enable indexing.

Spice Cayenne reads indexes and builds a secondary index per entry, but the index is a read-path structure rather than a database index — a unique entry does not constrain writes. See Secondary indexes.

For Arrow acceleration, see Hash Index (experimental, v1.11.0-rc.2+) for O(1) point lookups on primary key columns.

Primary keys and point lookups​

On DuckDB, acceleration.primary_key creates a DuckDB PRIMARY KEY (unique index) usable for full-key point lookups. A filter on only a leading or prefix column of a composite key does not use that unique index — add a secondary indexes entry on that column.

On Spice Cayenne, primary_key does not create an index. It identifies the key for upserts and deletes. To index that key, set a separate indexes entry on the same column or columns. See Secondary indexes.

on_refresh_sort_columns is incompatible with primary_key, indexes, and on_conflict: the sort rewrite drops those constraints. Prefer Spice Cayenne when you need physical clustering (sort_columns / cayenne_cluster_by) together with them.

Spice Cayenne Point Lookup Performance

Even without indexes, Vortex provides 100x faster random access reads compared to Parquet through segment statistics (similar to zone-maps), fast random access encodings (FSST, FastLanes), and compute push-down on compressed data. For many point lookup workloads, Spice Cayenne matches or exceeds indexed query performance without requiring explicit index configuration. See the Spice Cayenne documentation for details.

Cookbook​

  • A cookbook recipe to create an index on an accelerated dataset and compare query performance with and without the index. Indexes on Accelerated Data