Verify auto_optimize is set correctly on every writer path

[awksedgreep]

timeless-libsql now lets a host turn off the extension's flush-path compaction pass, and the choice matters for throughput and for write-latency tails. This issue is to confirm the stack lands on the right side of it on every path it owns.

Background

The extension can run a budgeted optimize from inside flush() — every n-th flush call, plus an urgent pass when the raw backlog crosses its budget. That pass is synchronous and inside the host's write transaction: push() auto-flushes at 8192 buffered entries, and flush() calls it before the statement returns. Measured on the extension's own bench fixture, one row insert in roughly every 32k paid a full compaction pass.

That is the correct default for a host that only ever sends flush (a heartbeat with no optimize of its own) — without it, raw blocks accumulate forever: full-size storage, raw-scan query costs, and retention deleting data that was never compressed.

It is the wrong default for a host that already schedules optimize itself, which ends up compacting the same backlog twice.

What changed upstream

auto_optimize is now a CREATE argument and a runtime command on both timeless_logs and timeless_traces:

CREATE VIRTUAL TABLE logs USING timeless_logs(index_keys='...', auto_optimize='off');
INSERT INTO logs(logs) VALUES ('auto_optimize:off');   -- or 'auto_optimize:30'

Either form persists to _meta and survives reconnects. The default is unchanged, and an absent argument writes no meta row, so existing stores keep exactly the behaviour they have.

The Rust signal servers now opt out automatically. They already ran a budgeted, backlog-driven optimize:<budget> every 30 s through their writer queue (Config::optimize_interval -> schedule_optimize), so they had been doing the work twice. They now issue auto_optimize:off on every writer boot — after their schema and capability checks — which also corrects databases created before the argument existed.

Measured on 1M log entries (M1 Max, index_keys='service,path,status'):

	flush-path on	off
ingest	4.18 s	2.20 s (1.90x)
p99 insert latency	87 ms	23 ms (3.8x)
ingest + optimize	4.21 s	4.24 s

Total work is unchanged — the compaction moves to the scheduled connection, where it drains in budgeted passes averaging 68 ms instead of stalling an insert.

What to check here

1. Confirm every writer path goes through the Rust signal servers. If it does, this is already handled and the task is just to verify after picking up the new version.
2. Find any path that creates a timeless_logs / timeless_traces vtable directly, outside the servers. Each one needs an explicit decision:
   • schedules its own optimize -> pass auto_optimize='off'
   • only sends flush on a heartbeat -> leave the default on, or it will accumulate raw blocks indefinitely
3. Do not set auto_optimize='off' anywhere that has no optimize schedule. That is the failure mode this flag can introduce, and it is silent until storage and query costs drift.

Verify on a running database:

SELECT CAST(v AS TEXT) FROM logs_meta WHERE k = 'auto_optimize';   -- '0' = off; absent = default
SELECT key, value FROM timeless_stats('logs')
 WHERE key IN ('raw_blocks', 'optimize_pending_raw_entries');

With the flag off and a working schedule, raw_blocks should rise between scheduled passes and return to 0 after one. If it climbs without ever draining, the flag is off on a host that is not scheduling optimize — that is the bug to catch.

Upstream: awksedgreep/timeless-libsql@023fe6f