Skip to content

driver-turso: remote mode never materializes object-level indexes — every declared secondary index is absent on production Turso tenant databases, so hot polling queries full-scan #17609

Closed

Description

@hotlong

现象(生产实测;租户信息已匿名)

两个生产租户库(driver-turso remote 模式,libsql://)的 sqlite_master:

表 索引总数 其中主键自动索引 其余
库 A 100 106 100 6 = 4 个字段级 unique 生成的 uniq_* + 2 个 metadata-protocol 原生 SQL 建的 idx_sys_metadata_overlay_*
库 B 100 105 100 5 个 uniq_*
  • sys_notification_delivery 声明了 5 个索引(含 1 个对象级 unique)→ 两个库里一个都没有,只有 sqlite_autoindex_sys_notification_delivery_1
  • sys_job_queue 声明了 3 个 → 两个库里一个都没有
  • 同一批对象定义在本地 SqlDriver(better-sqlite3)落地后:sys_notification_delivery 6 个、sys_job_queue 4 个,齐全

同一条领取查询的执行计划:

EXPLAIN QUERY PLAN SELECT id FROM sys_notification_delivery
 WHERE status = 'pending' AND partition_key = ? AND next_attempt_at <= ?
 ORDER BY next_attempt_at LIMIT 50
远程租户库 → SCAN sys_notification_delivery
            USE TEMP B-TREE FOR ORDER BY
本地       → SEARCH sys_notification_delivery USING INDEX idx_sys_notification_delivery_b3081700
            (status=? AND partition_key=? AND next_attempt_at<?)

原因(读码;origin/main 3ef96b471 仍如此,无在途 PR)

remote 模式下 TursoDriver.initObjects 走 RemoteTransport.syncSchemasBatch。remote-transport.ts 里所有索引 DDL 都来自 buildUniqueIndexDDL(:2160,由 uniqueIndexesFromFields 生成——只看字段级 unique),发射点 :1841 / :1868 / :1941 / :1981,全文件唯一一条 DDL 模板是 :2196 的 CREATE UNIQUE INDEX IF NOT EXISTS。

对象级 indexes: [...]——无论是否 unique——在 remote 路径上没有任何消费者。本地的对应物是 SqlDriver.syncDeclaredIndexes(sql-driver.ts:9812)。

#16570 修的是 indexes 进 initObjects 参数的类型面;turso-driver.ts 那段注释自己也写了「the runtime carried both keys the whole time」——但带着键不等于把它渲染成 DDL。这是 ADR-0049「声明无执行点」在 DDL 层的一例:从外面看什么都没坏,代价只出现在账单上。

后果(生产实测)

一个租户库 9 天 1,011.6 亿次行读,占其所在 Turso 组织的 99.9%,已进入超额计费;该库数据总量 12.7 MB。

机制是三件事叠乘(另两件分卡):

  1. 本卡:热表没有索引,每条查找都是全表扫描
  2. 通知调度器空闲时每跳仍发 32 条语句(service-messaging 另卡)
  3. 投递表持续增长——未配置邮件传输的租户,每条通知写一条必死的 email 投递,保留 90 天(service-messaging 另卡)

实测稳态约每秒 28 次全表扫描 sys_notification_delivery;表每天 +316 行,日读量每天线性 +8 亿。

验收

  1. remote 模式为每个对象级声明索引发射 DDL——非 unique 与 unique、含 nullSafe / 租户键部分——与 SqlDriver.syncDeclaredIndexes 同名同键(buildIndexName)。新表随建表批次一起落;已有表走 retrofit(IF NOT EXISTS,零改数据)
  2. 失败不静默:建不出的索引按 AGENTS.md 降级规则以 error 级报出原因(与 drivers(turso): the remote face drops unique entirely — a declared-unique column takes duplicates, and an upsert on conflictKeys dies with a raw SqliteError #8413 的 unique retrofit 同等待遇)
  3. 对等测试:同一组对象定义分别经 local SqlDriver 与 remote transport 落地,比较 sqlite_master 的索引名集合全等;至少覆盖 sys_notification_delivery、sys_job_queue
  4. 开机成本受控:稳态(索引都已存在)不得每次 kernel 构建为每张表各发一轮语句——沿用 unique retrofit 的批量 / 一次性做法
  5. 回归证据:remote 面上对上面那条领取查询 EXPLAIN QUERY PLAN 为 SEARCH … USING INDEX

影响面

所有 remote 模式租户库(托管云的每个环境)。修复发布并被消费方 pin 后,已有库靠 retrofit 在下一次 kernel 构建时补齐。

Activity

  1. added
    bugSomething isn't working
    priority:p0Queue-jump: outranks batch and breaks the round — never exempts claiming or same-file serial
    domain:engineRetired 2026-10-22, alias of domain:server (engine surface); in-flight cards keep it until they land
    on Sep 11, 2026
  2. hotlong commented on Sep 11, 2026

    @hotlong
    ContributorAuthor

    关联卡(同一次生产读放大的三个乘数与一份燃料):

  3. hotlong commented on Sep 11, 2026

    @hotlong
    ContributorAuthor

    Claim: maintainer direct dispatch (cloud ops session)
    Session: c5c0ce54-bb9c-478c-9e5b-cf44b80d4569
    Time: 2026-09-11T03:34Z
    Branch: claude/issue-17609-remote-declared-indexes
    Worktree: ~/Documents/GitHub/objectstack-issue-17609
    Domain: domain:engine
    File surface: packages/drivers/driver-turso/src/remote-transport.ts (+ its tests); parity reference packages/drivers/driver-sql/src/sql-driver.ts read-only
    Clause-②: no — driver DDL materialization of already-declared indexes; no packages/spec/src/** surface expected (path limb re-checked on the actual diff at review)

  4. self-assigned this
    on Sep 11, 2026
  5. os-litant commented on Sep 11, 2026

    @os-litant
    Contributor

    分诊:半状态治愈 —— 补 pm:queue;type 设为 Bug。⛔ priority:p0 与 domain:engine 是维护者所写,本席不动。

    分诊席 · session_017VGfRocA8VjczSe84fgjY3 · R+178 · 2026-09-11T03:3xZ · 本评论来自分诊座位. 读数取 origin/main @ 3ef96b47120a989d9333aeeeb6181bc371bc9b52.

    为什么这是治愈而不是首次定级

    卡上已有 domain:engine + priority:p0,但没有 pm 状态标签 ⇒ 落在 ③ 析取里,对每一个队列视图都是不可见的。⇒ 「状态变更不过夜」,补上这一半。

    ⭐ priority:p0 是维护者(hotlong)亲手写的,⛔ 分诊不复核、不下调。 本评论只做两件事:让它进队列,以及把派发时会撞上的东西先测掉。

    ✅ 车道正确:packages/drivers/driver-turso 在 domain:engine 行(packages/drivers/driver-*)。

    ⭐ 最要紧的一条:本卡不被 sql-driver.ts 的串行链挡住

    验收第 1 条要求与 SqlDriver.syncDeclaredIndexes「同名同键(buildIndexName)」,而 sql-driver.ts 上现在有一条硬串行链(#17175 pm:dispatched 占着文件,#17586 在等)。⇒ 「p0 可超 batch,⛔ 不越过同文件串行与深度等待」—— 所以「要不要改 sql-driver.ts」直接决定这张 p0 能不能今天派。实测:

    读数 值
    buildIndexName 定义处 packages/drivers/driver-sql/src/schema-drift.ts:1377 —— ⭐ 独立模块,不在 sql-driver.ts 里
    是否公开导出 ✅ packages/drivers/driver-sql/src/index.ts:87(与 expectedIndexes :1839、diffManagedIndexes、isManagedIndexName 同一个导出块)
    sql-driver.ts 自己怎么拿到它 :83 import,与外部消费者同一条路
    driver-turso 是否已依赖 driver-sql ✅ packages/drivers/driver-turso/package.json:34 —— "@objectstack/driver-sql": "workspace:*",已在

    ⇒ ⭐ remote 侧可以直接 import buildIndexName / expectedIndexes,零行 sql-driver.ts 改动。 ⇒ 不进串行链,可立即派。

    ⚠️ 而且这也定下了修法:⛔ 不要在 remote-transport.ts 里重写一份命名规则。仓里已经有过一次教训 —— packages/cli/src/commands/generate.ts:2007 自己复制了一份 buildIndexName,而 driver-memory 的注释写着「Reproducing half of buildIndexName would be a second [implementation]」。第三份副本就是「同名同键」这条验收自己要防的东西。

    派发姿态(p0)

    • 「priority:p0 插队:可超 batch、破轮次立即派发」⇒ ⛔ 不等本轮其余卡。
    • ⛔ 但 p0 不豁免认领协议:先写标签、再 Claim:、再全线程重读,一样不省。
    • ⛔ 也不豁免深度等待:派发前仍读 scripts/pm/os-verify-lock.sh --status。

    ⛔ 围栏

    ⚠️ 本卡是三件事叠乘中的一件,另两件是别的卡

    卡上写明:热表无索引(本卡)× 空闲每跳 32 条语句(#17610,domain:services,p1,本轮同批治愈)× 投递表持续增长(第三张)。

    ⇒ ⛔ 不要在本卡里修另外两件。⚠️ 但派发令应点名:本卡单独落地会让每秒 ~28 次全表扫描变成索引查找,⭐ 账单的量级下降主要来自这一张 —— 另两张降的是语句条数与行数增长,本张降的是每条语句的代价。

    查重 —— 2026-09-11T03:3xZ 跑于 581 张 open 板

    pattern open hits
    syncDeclaredIndexes 2 — #17609, #13562
    remote-transport|RemoteTransport 3 — #17609, #6009, #5930
    buildUniqueIndexDDL|uniqueIndexesFromFields 1 — #17609
    driver-turso 9 — #17609, #16973, #16023, #15642, #15212, #15104, #8249, #6009, #5499
    index(控制) 94

    ⛔ 无重复。 #13562 是词表卡(status:parked),#6009 / #5930 是 pm:on-hold 的观察项,#5499 是 driver-memory/mongodb 的冻结裁决(⛔ 不覆盖 driver-turso)。

    Size/model suggestion: M —— DDL 发射面不大,但验收 3(对等测试)与验收 4(稳态成本)是这张卡的主体。

    分诊席位 · session_017VGfRocA8VjczSe84fgjY3 · R+178 · 2026-09-11T03:3xZ · 本评论来自分诊座位


    Generated by Claude Code

  6. added theissue type on Sep 11, 2026
  7. os-litant commented on Sep 11, 2026

    @os-litant
    Contributor

    ⛔ 更正:上一条评论里的 pm:queue 是一次写入竞态,已撤销 —— 本卡状态是 pm:dispatched,归 domain:engine 席

    分诊席 · session_017VGfRocA8VjczSe84fgjY3 · R+178 · 2026-09-11T03:3xZ · 本评论来自分诊座位

    发生了什么

    本席在 03:33Z 取板时,本卡是 domain:engine + priority:p0 + 无 pm 状态(③ 半状态)。在我起草上一条评论的这几分钟里,domain:engine 席认领了它并挂上 pm:dispatched。我的标签写入按「读现集 → 加增删 → 写并集」执行,于是把 pm:queue 并到了一个已经有 pm:dispatched 的卡上 ⇒ 两个六态标签同时在卡上,约 40 秒。

    回读 diff 当场看见,已在下一笔撤掉 pm:queue。现值:pm:dispatched,⛔ 没有第二个状态标签。

    ⇒ 对认领席的意思,说清楚以免误读

    ⭐ pm:dispatched 是对的,pm:queue 是我的竞态产物,⛔ 不是要你把卡放回队列的指示。 上一条评论里的技术内容(串行链、围栏、查重)全部照常有效 —— ⛔ 只有那一行「补 pm:queue」作废。

    ⚠️ 其中最要紧的一条不受影响,再说一次:buildIndexName 在 packages/drivers/driver-sql/src/schema-drift.ts:1377,已公开导出,driver-turso 已依赖 driver-sql ⇒ 本卡零行 sql-driver.ts 改动,⛔ 不被 #17175 的同文件串行挡住。

    已机械化,⛔ 不靠下次记得

    「六态 ONE-OF 的成因恒是半写的转换」。本例的成因更窄一点:并发写入下,并集语义会把一个刚出现的状态和我要加的状态一起留下。⇒ 写入层已改成:传入六态之一时,其余五态无条件进 remove 集,并在位移并发状态时打印一行;一次传两个六态直接抛错(双向已验证)。

    ⇒ 这一类 collision 现在写不出来了,⛔ 与我是否记得无关。

    分诊席位 · session_017VGfRocA8VjczSe84fgjY3 · R+178 · 2026-09-11T03:3xZ · 本评论来自分诊座位


    Generated by Claude Code

  8. hotlong commented on Sep 11, 2026

    @hotlong
    ContributorAuthor

    os-dev-report
    {
    "issue": 17609,
    "status": "done",
    "branch": "claude/issue-17609-remote-declared-indexes",
    "pr": "#17615",
    "premise_still_valid": true,
    "summary": "RemoteTransport now materializes every declared index — field-level unique plus the object's own indexes, unique and non-unique, including the NULL-safe key of unique: 'organization' — by composing the same shared normalizers SqlDriver.syncDeclaredIndexes composes (uniqueIndexesFromFields + normalizeDeclaredIndex, names via buildIndexName); driver-sql is untouched. New tables get their indexes in the CREATE TABLE batch; existing tables are retrofitted with IF NOT EXISTS after ONE index-name read folded into the existing PRAGMA batch, with a batch then per-statement fallback that reports each unbuildable index at error and never rewrites a row. Premise verified at 3ef96b4 — every emit site and the sole template confirmed, one line drift only (syncDeclaredIndexes is at sql-driver.ts:12464, not :9812) — and reproduced locally before the fix (new parity suite 7 failed / 7 on base). Clause-2 stays no: packages/spec/src/** is untouched and no export, schema key or accept/refuse behaviour changes.",
    "tests": "Base + test only (bd207df): parity suite 7 failed / 7. HEAD 6241e6e: pnpm --filter @objectstack/driver-turso test exit 0 (52 files, 1243 tests); typecheck exit 0 (tsc --listFiles includes the new suite); build exit 0 (check-dts-emitted 1/1). Narrowed eslint over the 4 touched .ts files: exit 0, JSON 4 files / 0 errors / 0 warnings, no ignore notice; eslint.config.mjs enables no type-aware linting, so no untouched file can move. Gate sweep on 6241e6e — the 62 commands dispatch-gates derives, the 4 roster gates it flags under this PR's directories, check:durability-log-level and the package trio, 70 in all: 68 exit 0; 2 NOT MEASURED, exit 3 PREREQUISITE NOT MET (check:dual-build-cjs-loads needs all 78 package dists; check:type-check-debt --re-measure needs 24 dependency closures — both need the full-repo build the dispatch ruled out). check:lean-entry-closure was exit 3 first, then exit 0 after a filtered objectql build (15/15 cached). dispatch-gates --ran over an exit-coded record: 62 derived, 60 run, 2 NOT-MEASURED derived from exit 3, 0 unrun. Ablations at 6241e6e, direction predicted before each run, marker counted on disk, restore proven by blob 90cf0ed7 == HEAD and an empty git diff HEAD: A (existing-name skip removed) 1 failed | 6 passed, as predicted; B (fallback's error report silenced) 2 failed | 5 passed, as predicted; C (index name spelled raw instead of escaped) 4 failed | 3 passed, as predicted. The subject resolves from src (the suite imports ./turso-driver.js), so no dist preflight applies. Every os-verify-lock call ran in declared UNLOCKED mode (macOS host, no flock).",
    "acceptance": {
    "1_every_declared_index_same_name_and_key": "Pins a fresh database carries the same index set — names, uniqueness, origin and key parts, the single-object syncSchema path lands the same set as the batch path and retrofits every declared index onto tables that already exist — and changes no row: PRAGMA index_list + index_xinfo + COALESCE text deep-equal between local TursoDriver(':memory:') (the SqlDriver path) and remote TursoDriver over the real @libsql/client on file::memory:, anchored to buildIndexName-computed sets. sys_notification_delivery 6 names (incl. idx_sys_notification_delivery_b3081700 and uniq_sys_notification_delivery_f5f71821), sys_job_queue 4, os17609_scoped covering a field-level NULL-safe unique, unique:'organization', unique:'global', two author-named indexes and a skipped formula index. Retrofit leaves rows byte-identical and total_changes() unchanged; every retrofit statement is IF NOT EXISTS.",
    "2_failures_not_silent": "an object-level UNIQUE over existing duplicates: the dedup index is not created, the 4 plain indexes still land (a libsql write batch is transactional; the per-statement fallback lands them), exactly one logger.error names index and table with UNIQUE constraint failed, nothing on warn, both duplicate rows remain. a PLAIN index the server refuses: injected SQLITE_FULL gives one logger.error naming the index with its cause, and the other 4 land. Ablation B turns both red.",
    "3_parity_test": "New packages/drivers/driver-turso/src/turso-local-remote-declared-index-parity.test.ts, 7 pins, both hot-table shapes; the remote face is the real @libsql/client on file::memory:, as turso-driver.test.ts's remote-mode suite already does.",
    "4_steady_state_cost": "A second initObjects sends 0 index DDL. syncSchemasBatch alone: 2 round trips, 2N+1 statements (N=3 gives 7), independent of index count (14 declared); the single index-name read rides the existing PRAGMA batch. Base sent 2N+U statements over 3 round trips every boot (U = field-level uniques re-sent as IF NOT EXISTS). Ablation A turns the pin red.",
    "5_explain_query_plan": "Remote face, one database: before retrofit the claim query plans as SCAN sys_notification_delivery plus USE TEMP B-TREE FOR ORDER BY; after retrofit it plans as SEARCH sys_notification_delivery USING INDEX idx_sys_notification_delivery_b3081700 over status, partition_key and a next_attempt_at range — identical to the local face. Pinned by serves the delivery claim query from the declared index — the same plan on both faces; verbatim plan output in the PR body."
    },
    "spec_touched": false,
    "clause_2": "no",
    "labels": "PR carries documentation, size/l, tests, tooling from the path labeler; no label added by hand (Clause-2 no, so no needs:contract-review)",
    "mcp_calls": "0 — every GitHub read and write went through the gh CLI",
    "open_questions": [],
    "review_flags": [
    "Acceptance 4 shape: rather than keep the unique retrofit's every-boot IF NOT EXISTS batch, the retrofit now diffs against existing index names, so steady state issues zero index DDL and costs less than base; the old per-boot re-send of field-level uniques is gone too.",
    "Beyond the card: an author-declared index NAME is escaped (aliasIdentifierSql) instead of refused by SAFE_IDENTIFIER. Refusing would fail the remote face's whole schema sync — every object in the boot — over a spec-legal name the local face quotes. This moves the #14287 header's 'index names still refuse' for that one position; key columns stay refused. Corpus at 3ef96b4: 0 non-identifier declared index names across 85 declaring files. Pinned by fixture index os17609 scoped-by \"slug\" and ablation C.",
    "'local SqlDriver' and 'remote face' are exercised as TursoDriver local mode (inherits SqlDriver.initObjects) and TursoDriver remote mode over the real @libsql/client on file::memory: — no sqld and no Turso endpoint were used."
    ],
    "out_of_scope_findings": [
    "noted, not filed: the two hot-table fixtures copy the shapes of sys_notification_delivery and sys_job_queue (driver-turso does not depend on service-messaging or platform-objects), so a later change to those objects' indexes is invisible to this suite — it pins driver parity, not those declarations. Successor: none.",
    "noted, not filed: the better-sqlite3 libsql stub (libsql-sqlite-stub.testkit.ts) runs batch() as sequential non-transactional statements, while the real @libsql/client rolls a write batch back on any failure; remote suites that test failure paths on the stub do not see that rollback. Successor: none."
    ]
    }


    Generated by Claude Code

  9. hotlong commented on Sep 11, 2026

    @hotlong
    ContributorAuthor

    状态(UTC 2026-09-11):开发完成 → PR #17615(draft)。PM 复核已通过(见 PR 评论:spec 未触及、retrofit 仅 DDL、索引名转义已审过接受)。等 CI 全部结束且无失败后转 ready 入队。

  10. hotlong commented on Sep 11, 2026

    @hotlong
    ContributorAuthor

    状态(UTC 2026-09-11 04:34):PR #17615 CI 全绿(29 pass · 5 skipping · 0 fail)→ 已转 ready → 已进入合并队列(timeline added_to_merge_queue 04:34:31,队列第 2 位)。合并后本卡随 Closes 关闭;消费侧(托管云 pin bump + 全库索引普查)另有卡跟进。

  11. added and removed
    pm:dispatchedDispatched to a dev agent by /pm-dispatch
    on Sep 11, 2026
  12. hotlong commented on Sep 11, 2026

    @hotlong
    ContributorAuthor

    已合并(UTC 2026-09-11 05:01:27):PR #17615 → 合并提交 bdea10a18。已核对落地文件集与 PR 的 5 个文件完全一致,且为 origin/main 祖先。

    消费侧:托管云需把 pin 抬到包含 bdea10a18 的 SHA,并在上线后普查所有租户库的声明索引是否经 retrofit 补齐(cloud 侧已有卡跟进)。

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

bugSomething isn't workingdomain:engineRetired 2026-10-22, alias of domain:server (engine surface); in-flight cards keep it until they landpm:donepriority:p0Queue-jump: outranks batch and breaks the round — never exempts claiming or same-file serial

Type

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions