Getting Started

Tutorial: Building with CleaveDB

Performance tuning workflow

In production systems containing millions of documents, ad-hoc full-table scans and unindexed sorting severely degrade response times and waste CPU cycles. CleaveDB provides a canonical 4-step tuning workflow using PEER INTO COST, INDEX, and calibrated execution analysis.

The 4-step tuning cycle

Follow this iterative pattern whenever designing queries for large buckets:

Profile cost before execution

Inspect the raw query cost to determine whether the storage engine will perform a sequential full-bucket scan:

CleaveQLExample · Step 1: Inspect cost

PEER INTO COST ( FIND logs WHERE level = "error" ARRANGED BY timestamp )

The engine reveals an unindexed full scan and costly in-memory quicksort:

CleaveQLExample · Unoptimized diagnostics

{
  "scan_type": "FULL_BUCKET_SCAN",
  "estimated_docs_scanned": 50000,
  "filter_selectivity": 0.12,
  "sort_in_memory": true,
  "estimated_ms": 65,
  "suggestions": [
    "Create INDEX logs ON (level, timestamp) to avoid full scan and eliminate in-memory sort",
    "Add LIMIT to cap memory usage"
  ]
}

Create the recommended index

Apply the index statement suggested by the cost model. To satisfy both filtering and sorting without an in-memory sort, build a compound index on both fields:

CleaveQLExample · Step 2: Build compound index

INDEX logs ON (level, timestamp)

CleaveDB builds a balanced B+Tree indexing the key tuple (level, timestamp).

Re-verify the execution plan

Run PEER INTO COST a second time to ensure the optimizer now selects the newly created B+Tree:

CleaveQLExample · Step 3: Verify index plan

PEER INTO COST ( FIND logs WHERE level = "error" ARRANGED BY timestamp )

The diagnostic report confirms optimal execution:

CleaveQLExample · Optimized diagnostics

{
  "scan_type": "INDEX_SCAN",
  "estimated_docs_scanned": 9000,
  "filter_selectivity": 0.12,
  "sort_in_memory": false,
  "has_applicable_index": true,
  "estimated_ms": 1,
  "suggestions": [
    "Query is optimal"
  ]
}

Execute the optimal query

Run the actual query knowing that data access will complete in roughly 1 ms:

CleaveQLExample · Step 4: Execute query

FIND logs WHERE level = "error" ARRANGED BY timestamp LIMIT 50

Eliminating in-memory quicksort

When a query includes ARRANGED BY and no index covers the sort field, the database must load all candidate records into RAM and run an O(N log N) quicksort:

CleaveQLExample · Cost of sorting

cost_sort = rows * log2(rows) * row_cost * 0.5

By defining a compound index that includes the predicate field followed by the sort field (e.g. (level, timestamp)), the B+Tree traverses matching entries in pre-sorted order, setting sort_in_memory: false and eliminating sort latency completely.

Calibrated NVMe SSD & CPU cost model

CleaveDB estimates query duration using hardware-calibrated parameters:

Parameter	Calibration Value	Formula Impact
page_cost	0.1 ms	16KB page random read from NVMe SSD
sequential_factor	0.33x	Sequential block reads are ~3x faster than random lookups
row_cost	0.001 ms	CPU overhead per row (deserialization and predicate evaluation)
index_depth	3 levels	Standard B+Tree traversal cost (3 random page reads = 0.3 ms)

Memory protection with LIMIT

For buckets containing more than 1,000 documents, queries that omit LIMIT are flagged with "Add LIMIT to cap memory usage" in the diagnostics suggestions array. Always append LIMIT to prevent runaway client memory consumption.