Secondary Indexes

Indexes for ClickHouse tables

Moose lets you declare secondary/data-skipping indexes directly in your table definitions. Moose generates the ClickHouse INDEX clauses on create and plans ALTER TABLE ADD/DROP INDEX operations when you change them later.

When to use indexes

Use indexes to optimize selective predicates on large tables, especially string and high-cardinality columns.
Common types: minmax, Set(max_rows), ngrambf_v1(...), bloom_filter.

TypeScript

import { OlapTable, ClickHouseEngines } from "@514labs/moose-lib";
 
interface Events {
  id: string;
  user: string;
  message: string;
}
 
export const EventsTable = new OlapTable<Events>("Events", {
  engine: ClickHouseEngines.MergeTree,
  orderByFields: ["id"],
  indexes: [
    { name: "idx_user", expression: "user", type: "minmax", granularity: 1 },
    {
      name: "idx_message_ngrams",
      expression: "message",
      type: "ngrambf_v1",
      arguments: ["3", "256", "1", "123"],
      granularity: 1,
    },
  ],
});

Python

from moose_lib.dmv2.olap_table import OlapTable, OlapConfig, MergeTreeEngine
from pydantic import BaseModel
 
class Events(BaseModel):
  id: str
  user: str
  message: str
 
events_table = OlapTable[Events](
  "Events",
  OlapConfig(
    engine=MergeTreeEngine(),
    order_by_fields=["id"],
    indexes=[
      OlapConfig.TableIndex(name="idx_user", expression="user", type="minmax", granularity=1),
      OlapConfig.TableIndex(name="idx_message_ngrams", expression="message", type="ngrambf_v1", arguments=["3","256","1","123"], granularity=1),
    ],
  ),
)

How Moose applies changes

On create, Moose emits INDEX ... entries inside CREATE TABLE.
On change, Moose plans ALTER TABLE DROP INDEX <name> then ADD INDEX ... if the definition changed; pure adds/drops are applied as single operations.

Schema Optimization

External Tables