Table Maintenance

ANALYZE TABLE

ANALYZE TABLE refreshes the cached statistics and reports the column family internals as note-level rows in the result set, one block for the data CF and one for each secondary-index CF. Running it is optional for query planning, since the engine samples secondary-index cardinality automatically the first time a populated table is planned against, as described in Statistics and the Optimizer. ANALYZE TABLE re-samples the same figures on demand and surfaces the internals below:

[TIDESDB] CF 'demo__products'  total_keys=10  data_size=636 bytes  levels=3  read_amp=2.00
[TIDESDB] avg_key=18.8 bytes  avg_value=44.0 bytes
[TIDESDB] level 1  sstables=0  size=0 bytes  keys=0
[TIDESDB] level 2  sstables=1  size=636 bytes  keys=10
[TIDESDB] level 3  sstables=0  size=0 bytes  keys=0
[TIDESDB] idx CF 'demo__products__idx_idx_category'  keys=10  data_size=449 bytes  levels=5
[TIDESDB] idx 'idx_category' sampled=10 distinct=4 rec_per_key=2

The summary line reports the key count (SSTable entries, which can still include superseded versions of a key, plus what is still in the memtable), the data size, the number of LSM levels, and the read amplification factor. Memtable and block-cache figures are database-level in TidesDB 10 and appear in SHOW ENGINE TIDESDB STATUS rather than per CF. Below the summary come the average key and value sizes and a per-level breakdown of SSTable count, size, and keys. Each secondary index then gets a line with its column family’s key count, data size, and levels, and each non-unique index a line with the entries sampled (up to 100,000), the distinct index prefixes seen, and the resulting rec_per_key.

When a column family holds B+tree nodes, an extra note reports the tree shape:

[TIDESDB] btree  nodes=128  max_height=3  avg_height=2.40

When a column family keeps partition-range filters resident, a note reports their memory:

[TIDESDB] filters  resident=32768 bytes

This is the memory the family’s per-SSTable routing directories hold outside the block cache, one directory per SSTable, so it grows with the SSTable count and shrinks as compaction merges them.

When a column family has committed any user bytes, a write-amplification note is emitted:

[TIDESDB] WA  user=4096  wal=4096  flush=8192  compact_write=12288 (1 compactions)  compact_read=8192  ratio=6.00x

user is the logical bytes the engine wrote through the library’s API, wal is the WAL bytes attributed to this family, flush and compact_write are the bytes written to SSTables by flush and by compaction, compact_write also carries the number of compactions that have run, compact_read is the bytes compaction pulled in as input, and ratio is (wal + flush + compact_write) / user. A high ratio with few compactions points at oversized flushes, and a high ratio with many points at L0 churn or an under-sized L1_FILE_COUNT_TRIGGER.

OPTIMIZE TABLE

OPTIMIZE TABLE runs a synchronous compaction on every column family of the table, the data CF and each secondary-index CF, by calling tidesdb_compact() on each and blocking until it finishes. tidesdb_compact() runs one forced compaction pass, merging even when no trigger is due, and the cached statistics are invalidated afterwards so the optimizer sees the post-compaction state promptly. If a background compaction already holds one of the column families, tidesdb_compact() does not queue behind it and reports the family busy. OPTIMIZE then pushes a note saying a compaction was already in flight and returns HA_ADMIN_TRY_ALTER, which MariaDB answers by recreating the table and analyzing it (“Table does not support optimize, doing recreate + analyze instead”), so on a busy table the statement can turn into a full table rebuild.

OPTIMIZE TABLE products;

This is the tool to reach for after bulk deletes or updates that leave a large tombstone backlog, or when ANALYZE TABLE reports high read amplification.

CHECK TABLE and REPAIR TABLE

CHECK TABLE fetches the statistics of every column family of the table, the data CF and each secondary-index CF, through tidesdb_get_cf_stats(), and reports the table as corrupt if any of those calls fails. The library builds these statistics from the SSTable metadata it already holds in memory, so the check is cheap but shallow. It does not read data blocks or verify their checksums.

CHECK TABLE orders;

REPAIR TABLE runs the same forced compaction pass (tidesdb_compact()) on every column family, close to OPTIMIZE TABLE but stricter about failure. Any failure to compact the data column family, including a background compaction already holding it, fails the repair rather than falling back to a rebuild. A failure on a secondary-index column family is logged as a warning and the repair still reports OK. A read or corruption error in the SSTables the data CF merge reads fails the repair rather than being skipped. As with any merge, expired TTL entries are rewritten as tombstones, and tombstones become eligible to drop once the merge reaches the largest level.

REPAIR TABLE orders;