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.

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.