Projections

Projections for ClickHouse tables

Moose lets you declare projections directly in your table definitions. Projections store data in an alternative order (or pre-aggregated) within each data part, so queries that filter or order by non-primary-key columns can avoid full scans.

When to use projections

• Use projections when a table is regularly queried by a column that isn't in the primary ORDER BY.
• Trade-off: projections increase storage and insert cost proportional to the number of projections.
• Projections only apply to MergeTree-family engines.

import { OlapTable, ClickHouseEngines } from "@514labs/moose-lib"; interface Events {  id: string;  userId: string;  timestamp: Date;  value: number;} export const EventsTable = new OlapTable<Events>("Events", {  engine: ClickHouseEngines.MergeTree,  orderByFields: ["id"],  projections: [    { name: "proj_by_user", body: "SELECT _part_offset ORDER BY userId" },    { name: "proj_by_ts", body: "SELECT _part_offset ORDER BY timestamp" },  ],});

How Moose applies changes

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