Compare commits

...
Author SHA1 Message Date
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
4 changed files with 79 additions and 0 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
+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.