Indexes

Use CREATE INDEX to speed up equality lookups and supported numeric range queries. Vector search uses cosine indexes.

KalamDB has two different index kinds. They look similar in SQL, but they answer different questions.

Scalar indexes support equality prefix scans and numeric range seeks. They do not replace the primary key, they do not apply to STREAM tables, and they do not answer ORDER BY COSINE_DISTANCE(...).

For embeddings, see Vector Search.

When to create one

Create a scalar index when a query, policy, or live subscription repeatedly filters on the same column (or a stable leading pair of columns):

• Chat: WHERE conversation_id = $1 (and often ORDER BY created_at)
• Membership: WHERE user_id = CURRENT_USER inside CREATE POLICY
• Lookups: WHERE email = $1, WHERE status = 'open'

The primary key already gives you O(1) identity lookups. Index everything else you filter on in a hot path.

CREATE INDEX

1CREATE [UNIQUE] INDEX [IF NOT EXISTS] <index_name>2  ON [<namespace>.]<table_name> (<column> [, <column> ...]);3 4ALTER TABLE [<namespace>.]<table_name>5  CREATE [UNIQUE] INDEX [IF NOT EXISTS] <index_name> (<column> [, <column> ...]);

The parentheses are required. That is how KalamDB distinguishes a scalar index from a vector index.

1CREATE TABLE chat.messages (2  id BIGSERIAL PRIMARY KEY,3  conversation_id TEXT NOT NULL,4  created_at TIMESTAMP NOT NULL DEFAULT NOW(),5  body TEXT NOT NULL6) WITH (TYPE = 'SHARED');7 8CREATE INDEX idx_messages_conversation9  ON chat.messages (conversation_id, created_at);10 11CREATE POLICY member_read ON chat.messages12  FOR SELECT TO user13  USING (14    conversation_id IN (15      SELECT conversation_id FROM chat.conversation_members16      WHERE user_id = CURRENT_USER17    )18  );

The same statement works as ALTER TABLE:

1ALTER TABLE chat.messages2  CREATE INDEX IF NOT EXISTS idx_messages_conversation (conversation_id, created_at);

CREATE INDEX after rows already exist is valid. Hot rows are indexed immediately. Flushed (cold) rows stay queryable; equality still returns the right count even if some matches come from a cold scan.

What the planner uses

A scalar index is a prefix index:

1. Leading equality predicates (col = value) narrow an index prefix. Ordered integer keys also support numeric range predicates.
2. Extra predicates after that prefix can still run on the matching rows.
3. EXPLAIN ANALYZE shows a small hot_rows_scanned when the seek works. Without the index, the same query scans the whole hot set.

1-- Uses idx_messages_conversation (leading equality)2SELECT id FROM chat.messages3WHERE conversation_id = 'c1';4 5-- Still a prefix seek: equality on conversation_id, then created_at6SELECT id FROM chat.messages7WHERE conversation_id = 'c1'8  AND created_at < NOW()9ORDER BY created_at DESC10LIMIT 50;

Confirm it with:

1EXPLAIN ANALYZE2SELECT id FROM chat.messages3WHERE conversation_id = 'c1';

After flush, cold Parquet files are pruned with bloom / min-max on indexed columns instead of opening every segment.

UNIQUE indexes

1CREATE UNIQUE INDEX idx_users_email ON app.users (email);

CREATE UNIQUE INDEX is accepted and stored on the table catalog (indexes[].unique). The primary key is still the enforced unique live-row identity. Treat a secondary UNIQUE index as a documented uniqueness contract among live MVCC winners (for example a login email), not as a second primary key. Duplicate primary keys still fail independently of secondary indexes.

DROP INDEX

1ALTER TABLE [<namespace>.]<table_name>2  DROP INDEX [IF EXISTS] <index_name>;

Queries keep working after a drop; they just scan more. In schema.sql, do not write DROP INDEX. Remove the CREATE INDEX instead and let the migration diff emit ALTER TABLE … DROP INDEX.

Rules

• Tables: USER and SHARED only. STREAM tables reject scalar CREATE INDEX.
• Columns: at most four real table columns, no duplicates in the list, no expressions, no INCLUDE, no WHERE (partial) clauses.
• Reserved columns: you cannot index _seq, _commit_seq, or _deleted.
• Names: IF NOT EXISTS is a no-op when the index already exists; without it, a duplicate name errors.
• Vector mix-up: CREATE INDEX idx (embedding) USING COSINE is rejected. Vector indexes use the no-parentheses USING COSINE form.
• Not supported: CREATE INDEX CONCURRENTLY, expression indexes, GiST/GIN, or using a scalar index as a vector search path.

Inspecting indexes

Latest catalog JSON lives on system.schemas:

1SELECT table_name, indexes2FROM system.schemas3WHERE namespace_id = 'chat'4  AND table_name = 'messages'5  AND is_latest = true;

indexes is a JSON array of { "name", "columns", "unique" }.

Chat membership pattern

Index both sides of a membership policy so reads do not scan every member row:

1CREATE TABLE chat.conversation_members (2  user_id TEXT,3  conversation_id TEXT,4  role TEXT NOT NULL,5  PRIMARY KEY (user_id, conversation_id)6) WITH (TYPE = 'SHARED');7 8CREATE INDEX idx_conversation_members_user9  ON chat.conversation_members (user_id);10 11CREATE TABLE chat.messages (12  id TEXT PRIMARY KEY,13  conversation_id TEXT NOT NULL,14  author_id TEXT NOT NULL,15  body TEXT NOT NULL16) WITH (TYPE = 'SHARED');17 18CREATE INDEX idx_messages_conversation19  ON chat.messages (conversation_id);

idx_conversation_members_user covers WHERE user_id = CURRENT_USER. idx_messages_conversation covers WHERE conversation_id = … after the policy has chosen rooms.

Related

• Table DDL
• Row-Level Security
• Query diagnostics (EXPLAIN ANALYZE)
• Vector search indexes
• Named types

Numeric ranges and replica backfill

Integer keys sort numerically. Use EXPLAIN ANALYZE SELECT * FROM app.products WHERE id >= 100 AND id < 200 to inspect a numeric range plan. Creating or changing a scalar index backfills each replica from its own hot rows. Repeated indexed equality updates reuse a plan; staged transaction updates remain point lookups. Performance still depends on selectivity, hot/cold data, and the measured workload.