refactor(daemon): optimize _signals prune SQL #178

2026-04-27T07:57:52Z

xingyue commented

2026-04-27 07:57:52 +00:00

What

Optimize the _signals table prune query for better performance at large retention values.

Why

As noted in PR #177 review by @xiaomo, the NOT IN subquery scales poorly with large retention values. The OFFSET-based approach finds the cutoff id directly and deletes everything below it.

Changes

packages/daemon/src/sense-runtime.ts — Replace DELETE ... WHERE id NOT IN (SELECT id ... LIMIT ?) with DELETE ... WHERE id <= (SELECT id ... LIMIT 1 OFFSET ?)

Ref

Follow-up from PR #177 review feedback

## What Optimize the _signals table prune query for better performance at large retention values. ## Why As noted in PR #177 review by @xiaomo, the NOT IN subquery scales poorly with large retention values. The OFFSET-based approach finds the cutoff id directly and deletes everything below it. ## Changes - **packages/daemon/src/sense-runtime.ts** — Replace DELETE ... WHERE id NOT IN (SELECT id ... LIMIT ?) with DELETE ... WHERE id <= (SELECT id ... LIMIT 1 OFFSET ?) ## Ref Follow-up from PR #177 review feedback

xingyue added 1 commit 2026-04-27 07:57:53 +00:00

refactor(daemon): optimize _signals prune query 40accf3b7c

Replace NOT IN subquery with OFFSET-based cutoff for better
performance at large retention values.

Ref: PR #177 review feedback from @xiaomo

xiaomo approved these changes 2026-04-27 07:59:00 +00:00

xiaomo left a comment

LGTM ✅ 一行精准优化，LIMIT 1 OFFSET ? 比 NOT IN 子查询少扫一遍表。

LGTM ✅ 一行精准优化，`LIMIT 1 OFFSET ?` 比 `NOT IN` 子查询少扫一遍表。

xiaomo merged commit e467271cbc into main

2026-04-27 07:59:02 +00:00

xiaomo referenced this issue from a commit

2026-04-27 07:59:03 +00:00

Merge pull request 'refactor(daemon): optimize _signals prune SQL' (#178) from refactor/prune-sql-optimization into main

This repo is archived. You cannot comment on pull requests.

2 Participants

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: uncaged/nerve#178