Skip to content
This repository was archived by the owner on Jun 10, 2026. It is now read-only.
This repository was archived by the owner on Jun 10, 2026. It is now read-only.

Defer index creation in per-table SQLite for epoch-scale datasets #6

Description

@tamashi095

Currently per-table-sqlite.ts creates indexes upfront (in the DDL alongside CREATE TABLE). This is correct and fast for small ranges, but for full epoch indexing (334K checkpoints, 15M+ rows in object_changes) the inline index maintenance causes:

  • 50GB+ output (vs ~30GB without indexes)
  • Significantly slower writes (index B-tree updates on every INSERT)
  • 47GB → still growing after 15+ minutes

Fix: Split DDL into CREATE TABLE (at init) and CREATE INDEX (at shutdown). Same pattern the old sql.ts used for snapshot mode, but cleaner — just two arrays in the TableDef instead of one combined DDL string.

interface TableDef {
  name: string;
  createTable: string;   // CREATE TABLE only
  createIndexes: string[]; // CREATE INDEX statements, run at shutdown
  columns: string[];
  mapRow: (record: any) => unknown[];
  getRecords: (cp: ProcessedCheckpoint) => any[];
}

The per-table files are small enough that index creation at shutdown is fast (each file is 1-13GB, not 50GB monolith).

Related: the primary key constraint should also be deferred — use INSERT OR IGNORE without PKs during bulk load, then add PK via unique index at shutdown.

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions