Skip to content

Commit a8e4188

Browse files
only21milclaudemaria-rcks
authored
fix(server): SQLite temp files no longer block the event loop (pingdotgg#24194)
Co-authored-by: Claude Opus 5.5 <[email protected]> Co-authored-by: maria-rcks <[email protected]>
1 parent e692729 commit a8e4188

2 files changed

Lines changed: 48 additions & 0 deletions

File tree

‎apps/server/src/persistence/Sqlite.test.ts‎

Lines changed: 45 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5,12 +5,57 @@ import * as NodePath from "node:path";
55

66
import * as NodeServices from "@effect/platform-node/NodeServices";
77
import { assert, it } from "@effect/vitest";
8+
import * as HostProcess from "@t3tools/shared/HostProcess";
89
import * as Effect from "effect/Effect";
910
import * as Layer from "effect/Layer";
1011
import * as SqlClient from "effect/sql/SqlClient";
1112

1213
import * as SqlitePersistence from "./Sqlite.ts";
1314

15+
// Linux names an open, already-deleted file "<path> (deleted)" in /proc/self/fd.
16+
// SQLite names its temp files "etilqs_*" and, without TMPDIR, puts them in
17+
// /var/tmp while os.tmpdir() is /tmp, so match the name, not the directory.
18+
const openDeletedTempFiles = () =>
19+
NodeFS.readdirSync("/proc/self/fd").flatMap((fd) => {
20+
try {
21+
const target = NodeFS.readlinkSync(`/proc/self/fd/${fd}`);
22+
return target.includes("/etilqs_") && target.endsWith(" (deleted)") ? [target] : [];
23+
} catch {
24+
return [];
25+
}
26+
});
27+
28+
it.effect.skipIf(HostProcess.Platform.defaultValue() !== "linux")(
29+
"keeps a write transaction's statement journal off the temp directory",
30+
() => {
31+
const tempDir = NodeFS.mkdtempSync(NodePath.join(NodeOS.tmpdir(), "t3-sqlite-temp-"));
32+
const persistence = SqlitePersistence.layerFromPath(
33+
NodePath.join(tempDir, "state.sqlite"),
34+
).pipe(Layer.provide(NodeServices.layer));
35+
36+
return Effect.gen(function* () {
37+
const sql = yield* SqlClient.SqlClient;
38+
yield* sql`CREATE TABLE journal_probe(id INTEGER PRIMARY KEY, payload TEXT NOT NULL)`;
39+
for (let id = 0; id < 64; id++) {
40+
yield* sql`INSERT INTO journal_probe VALUES (${id}, ${"x".repeat(4096)})`;
41+
}
42+
const before = new Set(openDeletedTempFiles());
43+
const opened = yield* sql.withTransaction(
44+
Effect.gen(function* () {
45+
// A multi-row update can fail halfway, so SQLite journals every page
46+
// it changes, here well past the 64 KiB it holds in memory by default.
47+
yield* sql`UPDATE journal_probe SET payload = payload || 'y'`;
48+
return openDeletedTempFiles().filter((target) => !before.has(target));
49+
}),
50+
);
51+
assert.deepEqual(opened, []);
52+
}).pipe(
53+
Effect.provide(persistence),
54+
Effect.ensuring(Effect.sync(() => NodeFS.rmSync(tempDir, { recursive: true, force: true }))),
55+
);
56+
},
57+
);
58+
1459
it.effect("drops planner statistics a database already has", () => {
1560
const tempDir = NodeFS.mkdtempSync(NodePath.join(NodeOS.tmpdir(), "t3-sqlite-stats-"));
1661
const dbPath = NodePath.join(tempDir, "state.sqlite");

‎apps/server/src/persistence/Sqlite.ts‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -28,6 +28,9 @@ const layerSetup = Layer.effectDiscard(
2828
yield* sql`PRAGMA synchronous = NORMAL;`;
2929
// macOS fsync misses drive caches; F_FULLFSYNC flushes them at checkpoints and is ignored elsewhere.
3030
yield* sql`PRAGMA checkpoint_fullfsync = ON;`;
31+
// Keep statement journals and sort spills off the main thread's disk path.
32+
// Large statements and open savepoints can increase peak memory use.
33+
yield* sql`PRAGMA temp_store = MEMORY;`;
3134
// PASSIVE checkpoints retain peak WAL size until the last connection closes.
3235
yield* sql.unsafe(`PRAGMA journal_size_limit = ${WAL_SIZE_LIMIT_BYTES};`);
3336
yield* runMigrations();

0 commit comments

Comments
 (0)