Compare commits

...
Author SHA1 Message Date
BaaRai d005bfe5ba Merge pull request #250 from BaaRai/fix/llm-usage-ts-index
fix(db): index llm_usage.ts for the usage time-window queries
2026-11-16 00:28:24 +01:00
BaaRai 0374f32aeb fix(db): index llm_usage.ts for the usage time-window queries
Migration 0004 adds idx_llm_usage_ts on llm_usage(ts) for UsageDaily and JudgeUsageStats, which filter the append-only ledger on ts and are polled by the dashboard. The plain CREATE INDEX runs at startup before the HTTP server listens; the file documents pre-creating it concurrently for a very large ledger.
2026-11-16 00:00:56 +01:00
BaaRai be317a1337 Merge pull request #249 from BaaRai/perf/visible-polling
perf(web): pause snapshot polling in hidden tabs
2026-11-15 23:50:55 +01:00
BaaRai feaaf9e416 Merge pull request #248 from BaaRai/fix/task-query-indexes
fix(db): online indexes for tool-result lookup and per-task token aggregation
2026-11-15 23:50:37 +01:00
BaaRai 5463ea4375 ci: build a dedicated database for the online index scripts
The job-level test database has no schema and cached go test results do not guarantee any suffixed test database exists; create one from db/schema.sql and db/migrations, then apply and roll back the scripts.
2026-11-15 23:26:24 +01:00
BaaRai 5a8498bb4f fix(db): online indexes for tool-result lookup and per-task token aggregation
Adds scripts/sql/20261115-task-query-indexes.sql (CREATE INDEX CONCURRENTLY on activity(tool_use_id) WHERE kind = tool_result and on llm_usage((COALESCE(task_id, "")), model) with covering columns, then VACUUM (ANALYZE) llm_usage), its rollback and a README. CI applies and rolls back both scripts. Backported from #209 (contributed by @renaesop).
2026-11-15 23:08:24 +01:00
7 changed files with 134 additions and 2 deletions
+13
View File
@@ -48,6 +48,19 @@ jobs:
# as rotating failures in the agent package). Serial package execution
# keeps the database state deterministic.
run: go test -p 1 ./...
- name: online index scripts
# Apply and roll back scripts/sql on a database built here from
# db/schema.sql and db/migrations. The job-level artextest database
# never gets the schema (packages use suffixed databases), and cached
# test results mean no test database is guaranteed to exist.
env:
SQL_DSN: postgres://artex:testpass@localhost:5432/artextest_sqlscripts?sslmode=disable
run: |
psql "$ARTEX_PG_DSN" -X -v ON_ERROR_STOP=1 -c 'CREATE DATABASE artextest_sqlscripts'
psql "$SQL_DSN" -X -q -v ON_ERROR_STOP=1 -f db/schema.sql
for f in db/migrations/*.sql; do psql "$SQL_DSN" -X -q -v ON_ERROR_STOP=1 -f "$f"; done
psql "$SQL_DSN" -X -v ON_ERROR_STOP=1 -f scripts/sql/20261115-task-query-indexes.sql
psql "$SQL_DSN" -X -v ON_ERROR_STOP=1 -f scripts/sql/20261115-task-query-indexes.rollback.sql
- name: go test -race
# Race detector on the packages that pass under -race today. ./server
# joined once its suite was checked with -race against postgres and its
+38
View File
@@ -0,0 +1,38 @@
package db
import (
"database/sql"
"errors"
"strings"
"testing"
)
// Migration 0004 leaves a valid, ready btree index on llm_usage(ts) for the
// time-window queries (UsageDaily, JudgeUsageStats).
func TestLLMUsageTsIndex(t *testing.T) {
d, err := Open(testDSN(t))
if err != nil {
t.Skipf("postgres unavailable (%v) — skipping", err)
}
t.Cleanup(func() { d.Close() })
var valid, ready bool
var def string
err = d.QueryRow(`
SELECT i.indisvalid, i.indisready, pg_get_indexdef(i.indexrelid)
FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname='public' AND c.relname='idx_llm_usage_ts'`).Scan(&valid, &ready, &def)
if errors.Is(err, sql.ErrNoRows) {
t.Fatal("index public.idx_llm_usage_ts does not exist after Open")
}
if err != nil {
t.Fatal(err)
}
if !valid || !ready {
t.Fatalf("idx_llm_usage_ts indisvalid=%v indisready=%v, want both true (%s)", valid, ready, def)
}
if !strings.Contains(def, "USING btree (ts)") {
t.Fatalf("idx_llm_usage_ts definition %q does not index ts with a btree", def)
}
}
+2 -2
View File
@@ -136,9 +136,9 @@ func assertEmbeddedApplied(t *testing.T, d *DB) []appliedMigration {
want := []struct {
version int
name string
}{{1, llmRecordsMigration}, {2, llmUsageMigration}, {3, "0003_task_scope_icp_index.sql"}}
}{{1, llmRecordsMigration}, {2, llmUsageMigration}, {3, "0003_task_scope_icp_index.sql"}, {4, "0004_llm_usage_ts_index.sql"}}
if len(applied) != len(want) {
t.Fatalf("schema_migrations = %+v, want versions 1 to 3", applied)
t.Fatalf("schema_migrations = %+v, want versions 1 to 4", applied)
}
for i, w := range want {
content, err := fs.ReadFile(migrationsFS(), w.name)
+15
View File
@@ -0,0 +1,15 @@
-- UsageDaily and JudgeUsageStats (db/llm_usage.go) filter llm_usage on
-- ts >= now() - interval; the dashboard polls UsageDaily every 15 seconds.
-- llm_usage is an append-only ledger that gains a row per LLM call and is never
-- purged, so without an index on ts those queries scan the whole table.
--
-- This is a plain (non-concurrent) CREATE INDEX: migrations run in a
-- transaction, where CONCURRENTLY is not allowed. It is safe here because
-- db.Open applies migrations at startup, before the HTTP server listens, so the
-- SHARE lock it takes blocks no agent call or request in flight.
--
-- For an exceptionally large ledger, build the same index online before the
-- upgrade, outside a transaction:
-- CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_llm_usage_ts ON llm_usage(ts);
-- This migration then finds it and does nothing.
CREATE INDEX IF NOT EXISTS idx_llm_usage_ts ON llm_usage(ts);
+6
View File
@@ -0,0 +1,6 @@
\set ON_ERROR_STOP on
-- Removes only the two indexes introduced by this change; retains table data.
SET lock_timeout = '5s';
SET statement_timeout = '10min';
DROP INDEX CONCURRENTLY IF EXISTS public.idx_activity_tool_result_lookup;
DROP INDEX CONCURRENTLY IF EXISTS public.idx_llm_usage_task_model_tokens;
+22
View File
@@ -0,0 +1,22 @@
\set ON_ERROR_STOP on
-- Run with psql outside a transaction. Adds indexes without blocking normal DML.
SET lock_timeout = '5s';
SET statement_timeout = '10min';
SET maintenance_work_mem = '128MB';
SET max_parallel_maintenance_workers = 1;
-- db/commands.go joins by tool_use_id without an exploration_id constraint.
-- The existing (exploration_id, tool_use_id, id) index has the wrong leading key.
CREATE INDEX CONCURRENTLY idx_activity_tool_result_lookup
ON public.activity (tool_use_id) INCLUDE (is_error)
WHERE kind = 'tool_result';
-- Match TokenByModel's actual COALESCE(task_id, '') predicate and cover its sums.
CREATE INDEX CONCURRENTLY idx_llm_usage_task_model_tokens
ON public.llm_usage ((COALESCE(task_id, '')), model)
INCLUDE (task_id, input_tokens, output_tokens, cache_read, cache_write);
-- Mark committed append-only pages visible so the covering index can avoid heap reads.
SET vacuum_cost_delay = '2ms';
SET vacuum_cost_limit = 1000;
VACUUM (ANALYZE) public.llm_usage;
+38
View File
@@ -0,0 +1,38 @@
# Online task-query indexes
`20261115-task-query-indexes.sql` adds two PostgreSQL indexes for existing queries:
- `activity(tool_use_id) INCLUDE (is_error) WHERE kind = 'tool_result'` supports the tool-result join in `db/commands.go` (`LEFT JOIN activity r ON r.tool_use_id = u.tool_use_id AND r.kind = 'tool_result'`). That join does not constrain the result row's exploration ID, so the existing `idx_act_tool_call (exploration_id, tool_use_id, id)` has the wrong leading key for it.
- `llm_usage((COALESCE(task_id, '')), model) INCLUDE (task_id, input_tokens, output_tokens, cache_read, cache_write)` covers the per-task model aggregation in `db/llm_usage.go` (`TokenByModel`). That query filters on `COALESCE(task_id,'') = $1` and groups by `model`. The expression matches that predicate; the existing `idx_llm_usage_model (task_id, model)` cannot serve it.
## Apply
```sh
psql -X -v ON_ERROR_STOP=1 -d <database> -f scripts/sql/20261115-task-query-indexes.sql
```
Run it outside a transaction: `CREATE INDEX CONCURRENTLY` cannot run inside one, so do not wrap the file in `BEGIN`/`COMMIT` or use `psql -1`. Use the deployment's normal PostgreSQL authentication.
Both indexes are created concurrently, so normal reads and writes continue. The script sets a 5-second lock timeout, a 10-minute statement timeout and bounded maintenance resources. Its final `VACUUM (ANALYZE)` is an ordinary vacuum, not `VACUUM FULL`; it updates visibility and statistics so the covering index can serve index-only scans.
This is an explicit, one-time maintenance step, not a numbered migration. `db/migrate.go` runs each migration file inside a transaction, where `CONCURRENTLY` is not allowed. A plain `CREATE INDEX` at startup would lock `activity` against writes for the whole build. No application or database restart is required.
## After an interruption
The script intentionally fails if an index with the same name already exists. A partial run may have created the first index before the second failed. An interrupted concurrent build can also leave an invalid index behind. Before retrying, check the state of both indexes:
```sql
SELECT c.relname, i.indisvalid, i.indisready
FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
WHERE c.relname IN ('idx_activity_tool_result_lookup', 'idx_llm_usage_task_model_tokens');
```
Both must show `indisvalid = true` and `indisready = true`. Drop an index that is invalid or not ready with `DROP INDEX CONCURRENTLY`, or run the rollback below, then rerun the script.
## Rollback
```sh
psql -X -v ON_ERROR_STOP=1 -d <database> -f scripts/sql/20261115-task-query-indexes.rollback.sql
```
The rollback drops only these two indexes, concurrently and only if they exist. Table data is kept. Review the current index definitions before applying it. No application restart is required.