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. The column reference must be enclosed in parentheses if it is a multicolumn key.

Examples

• number: Index the number column
• (hash, timestamp): Index the hash and timestamp columns

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

Indexes are not supported for the in-memory Arrow or Spice Cayenne acceleration engines. Use DuckDB, SQLite, Turso (when MVCC is disabled), or PostgreSQL as the acceleration engine to enable indexing.

Spice Cayenne Point Lookup Performance

While Spice Cayenne does not support traditional 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.