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. 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 usesA 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 (opens in a new tab) for the full rules, which are shared by indexes, primary_key and on_conflict.
There 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 (opens in a new tab) 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 acceleration engine. Use DuckDB (opens in a new tab), SQLite (opens in a new tab), Turso (opens in a new tab), or PostgreSQL (opens in a new tab) as the acceleration engine to enable indexing.
Spice Cayenne (opens in a new tab) 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 (opens in a new tab).
For Arrow acceleration, see Hash Index (opens in a new tab) (experimental, v1.11.0-rc.2+) for O(1) point lookups on primary key columns.
:::
:::tip[Spice Cayenne Point Lookup Performance]
Even without indexes, Vortex (opens in a new tab) provides 100x faster random access reads (opens in a new tab) compared to Parquet through segment statistics (similar to zone-maps), fast random access encodings (FSST (opens in a new tab), FastLanes (opens in a new tab)), 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 (opens in a new tab) for details.
:::