Skip to content

Commit 4950263

Browse files
bompust3dotgg
authored andcommitted
perf(server): thread search runs on the SQLite reader worker
Thread content search scans every message with a LIKE the planner cannot index, and ran on the main connection, holding the event loop for 100-300 ms per search on an 86k-message database. Run it in a read-only transaction so it uses the SQLite reader worker.
1 parent dd5c7bd commit 4950263

2 files changed

Lines changed: 76 additions & 12 deletions

File tree

‎apps/server/src/orchestration-v2/ThreadSearch.test.ts‎

Lines changed: 56 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -7,9 +7,14 @@ import {
77
ThreadId,
88
type OrchestrationV2DomainEvent,
99
} from "@t3tools/contracts";
10+
import * as NodeServices from "@effect/platform-node/NodeServices";
1011
import * as DateTime from "effect/DateTime";
12+
import * as Deferred from "effect/Deferred";
1113
import * as Effect from "effect/Effect";
14+
import * as FileSystem from "effect/FileSystem";
15+
import * as Fiber from "effect/Fiber";
1216
import * as Layer from "effect/Layer";
17+
import * as Path from "effect/Path";
1318
import * as SqlClient from "effect/sql/SqlClient";
1419

1520
import * as SqlitePersistence from "../persistence/Sqlite.ts";
@@ -199,3 +204,54 @@ it.layer(layerTest)("ThreadSearch", (it) => {
199204
}),
200205
);
201206
});
207+
208+
it.effect("searches on the reader worker while the writer is busy", () =>
209+
Effect.gen(function* () {
210+
const fileSystem = yield* FileSystem.FileSystem;
211+
const path = yield* Path.Path;
212+
const directory = yield* fileSystem.makeTempDirectoryScoped({ prefix: "t3-thread-search-" });
213+
const layerFile = Layer.mergeAll(
214+
ThreadSearch.layer,
215+
ProjectionStore.layer,
216+
ProjectStore.layer,
217+
).pipe(
218+
Layer.provideMerge(SqlitePersistence.layerFromPath(path.join(directory, "state.sqlite"))),
219+
Layer.provide(NodeServices.layer),
220+
);
221+
222+
yield* Effect.gen(function* () {
223+
const projections = yield* ProjectionStore.ProjectionStoreV2;
224+
const search = yield* ThreadSearch.ThreadSearch;
225+
const sql = yield* SqlClient.SqlClient;
226+
const project = ProjectId.make("project:search-reader");
227+
const threadId = ThreadId.make("thread:reader");
228+
yield* createProject(project);
229+
yield* Effect.forEach(
230+
[thread(threadId, project), message(threadId, "reader", "user", "needle on the reader")],
231+
projections.apply,
232+
{ discard: true },
233+
);
234+
235+
// A write transaction holds the writer connection. A search on the writer would queue
236+
// behind it and never finish; one on the reader worker does not wait.
237+
const writerHeld = yield* Deferred.make<void>();
238+
const releaseWriter = yield* Deferred.make<void>();
239+
const writer = yield* Effect.forkChild(
240+
sql.withTransaction(
241+
Deferred.succeed(writerHeld, undefined).pipe(
242+
Effect.andThen(Deferred.await(releaseWriter)),
243+
),
244+
),
245+
);
246+
yield* Deferred.await(writerHeld);
247+
const result = yield* search.search({ query: "needle" });
248+
yield* Deferred.succeed(releaseWriter, undefined);
249+
yield* Fiber.join(writer);
250+
251+
assert.deepEqual(
252+
result.matches.map((match) => match.threadId),
253+
[threadId],
254+
);
255+
}).pipe(Effect.provide(layerFile));
256+
}).pipe(Effect.scoped, Effect.provide(NodeServices.layer)),
257+
);

‎apps/server/src/orchestration-v2/ThreadSearch.ts‎

Lines changed: 20 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -12,6 +12,7 @@ import * as Layer from "effect/Layer";
1212
import * as Schema from "effect/Schema";
1313
import * as SqlClient from "effect/sql/SqlClient";
1414
import * as SqlSchema from "effect/sql/SqlSchema";
15+
import * as NodeSqliteClient from "@t3tools/shared/nodeSqliteClient";
1516

1617
/** Carries no query text: search input is user content. */
1718
export class ThreadSearchError extends Schema.TaggedError<ThreadSearchError>()(
@@ -138,18 +139,25 @@ export const make = Effect.gen(function* () {
138139

139140
const search: ThreadSearch["Service"]["search"] = Effect.fn("ThreadSearch.search")(
140141
function* (input) {
141-
const rows = yield* searchRows({
142-
pattern: `%${escapeLikePattern(input.query)}%`,
143-
limit: input.limit ?? 50,
144-
}).pipe(
145-
Effect.mapError(
146-
(cause) =>
147-
new ThreadSearchError({
148-
operation: Schema.isSchemaError(cause) ? "decode" : "query",
149-
cause,
150-
}),
151-
),
152-
);
142+
// The scan reads every message, so run it on the reader worker rather
143+
// than the event loop.
144+
const rows = yield* sql
145+
.withTransaction(
146+
searchRows({
147+
pattern: `%${escapeLikePattern(input.query)}%`,
148+
limit: input.limit ?? 50,
149+
}),
150+
)
151+
.pipe(
152+
NodeSqliteClient.readOnly,
153+
Effect.mapError(
154+
(cause) =>
155+
new ThreadSearchError({
156+
operation: Schema.isSchemaError(cause) ? "decode" : "query",
157+
cause,
158+
}),
159+
),
160+
);
153161
return {
154162
matches: rows.map((row) => ({
155163
threadId: row.threadId,

0 commit comments

Comments
 (0)