Skip to Content
We are live but in Staging 🎉
Data EnginesSQLgRPC & HTTP APITables & maintenance

Tables & maintenance

CreateTable

rpc CreateTable(CreateTableRequest) returns (CreateTableResponse);
 
message CreateTableRequest {
  string db_id = 1;
  oneof ddl {
    TableSpec spec = 2;
    string sql = 3;                    // "CREATE TABLE …" — same executor
  }
}
 
message TableSpec {
  string table_name = 1;
  repeated ColumnDef columns = 2;
  repeated string partition_columns = 3;
  repeated string pk_columns = 4;      // composite allowed
  optional string description = 5;
  bool if_not_exists = 6;
  bool or_replace = 7;
}
 
message ColumnDef {
  string name = 1;
  string type = 2;                     // a SQL type string: "BIGINT", "VARCHAR(64)", "NUMERIC(18,2)", "TIMESTAMP(3)", "VECTOR(768)"
  bool nullable = 3;
  optional string default_expr = 4;    // "CURRENT_TIMESTAMP", "GENERATE_UUID()", "'pending'", "0"
}
 
message CreateTableResponse {
  string table_name = 1;
  string location = 2;
  int64 version = 3;
  repeated string partition_columns = 4;
  repeated string pk_columns = 5;
  uint32 column_count = 6;
  bool already_existed = 7;
  bool replaced = 8;
}
{
  "db_id": "kb-prod",
  "spec": {
    "table_name": "events",
    "columns": [
      { "name": "id",          "type": "BIGINT",       "nullable": false },
      { "name": "user_id",     "type": "VARCHAR(64)",  "nullable": false },
      { "name": "occurred_at", "type": "TIMESTAMP(3)", "nullable": false },
      { "name": "event_type",  "type": "VARCHAR",      "nullable": false },
      { "name": "payload",     "type": "JSON",         "nullable": true }
    ],
    "partition_columns": ["event_type"],
    "pk_columns": ["id", "user_id"]
  }
}

ColumnDef.type is the SQL spelling, so declared widths and precisions travel with it: VARCHAR(64) is enforced at 22001, TIMESTAMP(3) rounds to milliseconds, and both are reported back by DESCRIBE and information_schema.columns. since 0.1.43 Default expressions are validated against the column type at creation. The full type list is on Data types.

A table created with no pk_columns receives a hidden _rowid primary key so it still has row identity; keyed operations then address _rowid, not your columns. Declare a key when you want keyed UPDATE / DELETE / UPSERT on your own columns.

There is no dedicated HTTP route for table creation; send the CREATE TABLE SQL to the /sql/execute door.

AlterTable

rpc AlterTable(AlterTableRequest) returns (AlterTableResponse);
 
message AlterTableRequest {
  string db_id = 1;
  oneof ddl {
    AlterSpec spec = 2;                // ADD COLUMN only
    string sql = 3;                    // "ALTER TABLE …" — the full set
  }
}
message AlterSpec { string table_name = 1; repeated ColumnDef add_columns = 2; }
 
message AlterTableResponse {
  string table_name = 1;
  int64 version = 2;
  repeated string columns_added = 3;
  uint32 column_count = 4;             // TOTAL after the alter
}

The typed spec carries only add_columns. DROP COLUMN, ALTER COLUMN … SET DATA TYPE, ADD CONSTRAINT and DROP CONSTRAINT are reachable through the sql arm. What each form does, and what is refused, is on Schema changes. An added column is readable on every existing row the moment the call returns. since 0.1.42

DropTable

rpc DropTable(DropTableRequest) returns (DropTableResponse);
 
message DropTableRequest {
  string db_id = 1;
  oneof ddl { DropSpec spec = 2; string sql = 3; }
}
message DropSpec { string table_name = 1; bool if_exists = 2; }
 
message DropTableResponse {
  string table_name = 1;
  bool existed = 2;
  uint64 objects_deleted = 3;
  int64 bytes_deleted = 4;
  bool sidecar_deleted = 5;
  uint64 wal_objects_deleted = 6;
}

Permanent: the table’s storage, metadata and pending writes are removed and cannot be restored. Dropping a pipeline-bound table does not cascade to the pipeline or ingest rule that feeds it; remove those through Pipelines. DROP INDEX is not a statement — an index is a table named __idx_<table>_<column> (or __uidx_…); drop that.

SQL-only operations

OperationStatement
List tablesSHOW TABLES — returns table and comma-joined pk_columns; index companion tables are hidden
Inspect a tableDESCRIBE t — column, type, nullable, pk, default
Empty a table, keep everything elseTRUNCATE TABLE t
IndexesCREATE INDEX / CREATE UNIQUE INDEX / CREATE INDEX … USING hnsw
Roll backRESTORE TABLE t TO VERSION AS OF n or TO TIMESTAMP AS OF '…Z'
Pin in memoryLOAD TABLE t / RELEASE TABLE t

All go through Execute and return the ddl or rows arm. RESTORE is destructive to writes made after the target version and does not roll back secondary indexes; the details are on Schema changes.

OptimizeTable

Rewrites many small storage files into fewer larger ones. With z_order_columns the rewrite also clusters rows by those columns, which speeds up reads that filter on them.

rpc OptimizeTable(OptimizeRequest) returns (OptimizeResponse);
 
message OptimizeRequest {
  string db_id = 1;
  string table = 2;
  repeated string z_order_columns = 3;   // empty = plain bin-pack
}
message OptimizeResponse {
  string table_name = 1;
  int64 version = 2;
  string optimize_type = 3;              // "compact" | "z_order"
  repeated string z_order_columns = 4;
  uint64 files_added = 5;
  uint64 files_removed = 6;
}
dodil data table optimize events -b kb-prod
dodil data table optimize events -b kb-prod --z-order-column event_type --z-order-column user_id

files_removed − files_added is the packing win. Call it after bulk loads or nightly batches; a routinely written table stays reasonably packed on its own. The target file size is not caller-tunable.

VacuumTable

Permanently deletes storage files no longer referenced by the current version and older than the retention window. Vacuumed versions can no longer be read or restored.

rpc VacuumTable(VacuumRequest) returns (VacuumResponse);
 
message VacuumRequest {
  string db_id = 1;
  string table = 2;
  bool dry_run = 3;
  optional uint64 retention_hours = 4;   // absent = 168
}
message VacuumResponse {
  string table_name = 1;
  int64 version = 2;
  bool dry_run = 3;
  uint64 files_deleted = 4;              // a count; paths are not returned
}
dodil data table vacuum events -b kb-prod --dry-run     # always first
dodil data table vacuum events -b kb-prod --retention-hours 720

168 hours is the floor. A smaller retention_hours is clamped up to 168, and there is no bypass flag on any door. Your restore window is your retention window, and the background maintenance loop vacuums on its own schedule with the same floor. Pin a snapshot as its own table (CREATE TABLE t_backup AS SELECT * FROM t) if you need a longer-lived restore point.

Compact

Applies pending writes to the table’s storage now, instead of on the next background pass.

rpc Compact(CompactRequest) returns (CompactAck);
 
message CompactRequest { string db_id = 1; string table = 2; }
 
message CompactAck {
  optional string drained_high_ulid = 1;  // absent when nothing was pending
  uint64 wal_entries_processed = 2;
  uint64 wal_unique_keys = 3;             // after newest-per-key dedup
  bool fence_aborted = 4;                 // a competing pass held the lock; nothing done
  bool truncated = 5;                     // more remains — call again until false
  // rejected-row counters follow
}
dodil data table compact events -b kb-prod

Compaction does not change what reads see — every acknowledged write is already visible. It changes how rows are stored, which matters for analytical scan speed after a large load, and it is the canonical first step before OptimizeTable. wal_unique_keys < wal_entries_processed is normal: repeated writes to one key collapse to the newest.

Database catalog

RPCPurpose
CreateDatabaseRegisters a database and its storage location. Through K3 this happens on the first DDL against a bucket; you do not call it.
DropDatabaseRemoves a database. Control-plane orchestrated.
ListDatabases / DescribeDatabaseEnumerate and inspect. DESCRIBE DATABASE d is the SQL twin.
UpdateDatabaseS3CredentialsRotates the storage credentials a database uses. Control-plane orchestrated.
GetDatabaseStatsStorage, residency, pending-write and rejected-row counters, optionally per table. The one you will call.
rpc GetDatabaseStats(GetDatabaseStatsRequest) returns (DatabaseStats);
 
message GetDatabaseStatsRequest { string db_id = 1; bool include_tables = 2; }
 
message DatabaseStats {
  string db_id = 1;
  uint64 stored_bytes = 2;
  uint32 table_count = 3;
  repeated TableStats tables = 4;        // only with include_tables
  uint64 ram_resident_bytes = 5;
  uint64 disk_resident_bytes = 6;
  ReservationInfo reservation = 7;
  uint64 wal_segments = 8;               // pending-write segments
  uint64 wal_entries_estimate = 9;
  string wal_high_ulid = 10;             // empty = nothing pending
  uint64 rejected_rows = 11;             // dead-lettered rows — investigate if non-zero
  uint64 collected_ms_ago = 12;
}
 
message TableStats {
  string table = 1;
  int64 row_count = 2;                   // -1 = unknown (never compacted)
  uint64 size_bytes = 3;
  int64 delta_version = 4;               // the version RESTORE targets
  uint64 wal_backlog_entries = 5;
  bool fully_resident = 6;
  uint64 rejected_rows = 7;
}

Two fields worth watching: rejected_rows non-zero means some writes were dead-lettered rather than applied, and TableStats.delta_version is the version number you record before a risky migration so you have a RESTORE target.

Not tenant-callable

SetReservation / DeleteReservation (hot capacity) and ReleaseDatabase / AdoptDatabase (tenant moves between clusters) exist on the service but are authored by the control plane. Reservations are managed through Capacity; moves are never tenant-initiated.

WritesTemplates