1. MooseStack
  3. Moose OLAP
  5. Secondary Indexes

On this page

Indexes for ClickHouse tablesWhen to use indexesHow Moose applies changes
FiveonefourFiveonefour
Fiveonefour Docs
MooseStackTemplatesGuides
Release Notes
Source512
  • Overview
Build a New App
  • 5 Minute Quickstart
  • Browse Templates
  • Existing ClickHouse
Add to Existing App
  • Next.js
  • Fastify
Fundamentals
  • Moose Runtime
  • MooseDev MCP
  • Data Modeling
Moose Modules
  • Moose OLAP
    • Data Modeling
    • Tables
    • Views
    • Materialized Views
    • Materialized Columns
    • External Data & Introspection
    • External Tables
    • Introspecting Tables
    • Data Access
    • Inserting Data
    • Reading Data
    • Performance & Optimization
    • Schema Optimization
    • Secondary & Data-skipping Indexes
    • TTL (Time-to-Live)
    • Schema Versioning
  • Moose Streaming
  • Moose Workflows
  • Moose APIs & Web Apps
Deployment & Lifecycle
  • Moose Migrate
  • Moose Deploy
Reference
  • API Reference
  • Data Types
  • Table Engines
  • CLI
  • Configuration
  • Observability Metrics
  • Help
  • Release Notes
Contribution
  • Documentation
  • Framework

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.
Indexes.ts
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,    },  ],});

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.
Indexes.ts
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,    },  ],});