ALTER TABLE COLUMN ADD INDEX

Indexes an existing symbol column.

Syntax

Bitmap index (default):

ALTER TABLE tableName ALTER COLUMN columnName ADD INDEX [CAPACITY n]

Posting index, with optional covering columns and encoding variant:

ALTER TABLE tableName ALTER COLUMN columnName
  ADD INDEX TYPE POSTING [DELTA | EF] [INCLUDE (col, ...)]

Adding an index is an atomic, non-blocking, and non-waiting operation. Once complete, the SQL optimizer will start using the new index for SQL executions.

Examples

Adding a bitmap index (default)

ALTER TABLE trades ALTER COLUMN side ADD INDEX;

Adding a posting index

ALTER TABLE trades ALTER COLUMN instrument ADD INDEX TYPE POSTING;

The bare form has no covering layer — queries selecting columns other than instrument still read from the column files. Add an INCLUDE clause to build a covering index.

An encoding variant can also be forced:

-- Force delta + Frame-of-Reference (benchmarking)
ALTER TABLE trades ALTER COLUMN instrument ADD INDEX TYPE POSTING DELTA;

-- Force Elias-Fano (benchmarking)
ALTER TABLE trades ALTER COLUMN instrument ADD INDEX TYPE POSTING EF;

Adding a posting index with covering columns

The INCLUDE clause stores additional column values in the index sidecar files, enabling covering queries that bypass column file reads:

ALTER TABLE trades
  ALTER COLUMN symbol ADD INDEX TYPE POSTING INCLUDE (price, quantity);

The designated timestamp is appended to the INCLUDE list automatically. After this, queries that only select columns from the INCLUDE list (plus the indexed symbol column and designated timestamp) are served from the index sidecar:

-- This query reads from the index sidecar, not from column files
SELECT timestamp, price FROM trades WHERE symbol = 'AAPL';

See Posting index and covering index for supported column types and performance details.