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:
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 columnsThere are two types of indexes that can be specified in a Spicepod:
enabled: Creates a standard index on the specified column(s).
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.
CREATE UNIQUE INDEX my_index ON my_table (my_column).:::warning[Limitations]
Traditional indexes are not supported for the in-memory Arrow or Spice Cayenne acceleration engines. Use DuckDB, SQLite, or PostgreSQL as the acceleration engine to enable indexing.
:::
:::tip[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.
:::