Skip to content

Commit 04fd44a

Browse files
AVthekingtim-smart
andauthored
Run SQL persistence cleanup in bounded batches (#6564)
Co-authored-by: Tim Smart <[email protected]>
1 parent 8037a98 commit 04fd44a

7 files changed

Lines changed: 329 additions & 8 deletions

File tree

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,5 @@
1+
---
2+
"effect": patch
3+
---
4+
5+
Run shared-table SQL persistence expiration cleanup in indexed, bounded background batches.
Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,2 @@
1+
/** @internal */
2+
export const sqlCleanupBatchSize = 1000

‎packages/effect/src/unstable/persistence/Persistence.ts‎

Lines changed: 116 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -15,8 +15,10 @@ import * as Duration from "../../Duration.ts"
1515
import * as Effect from "../../Effect.ts"
1616
import * as Exit from "../../Exit.ts"
1717
import { identity } from "../../Function.ts"
18+
import { sqlCleanupBatchSize } from "../../internal/persistence.ts"
1819
import * as Layer from "../../Layer.ts"
1920
import * as PrimaryKey from "../../PrimaryKey.ts"
21+
import * as Schedule from "../../Schedule.ts"
2022
import * as Schema from "../../Schema.ts"
2123
import type * as Scope from "../../Scope.ts"
2224
import * as SqlClient from "../sql/SqlClient.ts"
@@ -560,6 +562,120 @@ export const layerBackingSql: Layer.Layer<
560562
`
561563
}).pipe(Effect.orDie)
562564

565+
yield* sql.onDialectOrElse({
566+
pg: () =>
567+
sql`CREATE INDEX IF NOT EXISTS effect_persistence_expires_idx ON ${table} (expires) WHERE expires IS NOT NULL`
568+
.pipe(Effect.orDie, Effect.asVoid),
569+
mysql: () =>
570+
Effect.gen(function*() {
571+
const indexExists = sql<{ readonly count: number }>`
572+
SELECT COUNT(*) AS count FROM information_schema.statistics
573+
WHERE table_schema = DATABASE()
574+
AND table_name = 'effect_persistence'
575+
AND index_name = 'effect_persistence_expires_idx'
576+
`.pipe(
577+
Effect.map((rows) => Number(rows[0].count) > 0)
578+
)
579+
580+
yield* sql`CREATE INDEX effect_persistence_expires_idx ON ${table} (expires)`.pipe(
581+
Effect.catch((error) => Effect.flatMap(indexExists, (exists) => exists ? Effect.void : Effect.fail(error)))
582+
)
583+
}).pipe(Effect.orDie),
584+
mssql: () =>
585+
Effect.gen(function*() {
586+
const indexExists = sql<{ readonly count: number }>`
587+
SELECT COUNT(*) AS count FROM sys.indexes
588+
WHERE name = N'effect_persistence_expires_idx'
589+
AND object_id = OBJECT_ID(N'effect_persistence')
590+
`.pipe(
591+
Effect.map((rows) => Number(rows[0].count) > 0)
592+
)
593+
594+
yield* sql`CREATE INDEX effect_persistence_expires_idx ON ${table} (expires) WHERE expires IS NOT NULL`.pipe(
595+
Effect.catch((error) => Effect.flatMap(indexExists, (exists) => exists ? Effect.void : Effect.fail(error)))
596+
)
597+
}).pipe(Effect.orDie),
598+
// sqlite
599+
orElse: () =>
600+
sql`CREATE INDEX IF NOT EXISTS effect_persistence_expires_idx ON ${table} (expires) WHERE expires IS NOT NULL`
601+
.pipe(Effect.orDie, Effect.asVoid)
602+
})
603+
604+
const cleanupBatchDelay = Duration.millis(10)
605+
const cleanupInterval = Duration.minutes(5)
606+
607+
const deleteExpiredBatch = sql.onDialectOrElse({
608+
pg: () => (expiresAtOrBefore: number) =>
609+
sql<{ readonly count: number }>`
610+
WITH deleted_entries AS (
611+
DELETE FROM ${table}
612+
WHERE ctid IN (
613+
SELECT ctid FROM ${table}
614+
WHERE expires IS NOT NULL AND expires <= ${expiresAtOrBefore}
615+
LIMIT ${sql.literal(String(sqlCleanupBatchSize))}
616+
)
617+
RETURNING 1
618+
)
619+
SELECT COUNT(*)::INT AS count FROM deleted_entries
620+
`.pipe(Effect.map((rows) => rows[0].count)),
621+
mysql: () =>
622+
Effect.fnUntraced(
623+
function*(expiresAtOrBefore: number) {
624+
const connection = yield* sql.reserve
625+
const [statement, parameters] = sql`
626+
DELETE FROM ${table}
627+
WHERE expires IS NOT NULL AND expires <= ${expiresAtOrBefore}
628+
LIMIT ${sql.literal(String(sqlCleanupBatchSize))}
629+
`.compile()
630+
yield* connection.execute(statement, parameters, undefined)
631+
const rows = yield* connection.executeValues("SELECT ROW_COUNT()", [])
632+
return Number(rows[0][0])
633+
},
634+
Effect.scoped
635+
),
636+
mssql: () => (expiresAtOrBefore: number) =>
637+
sql<{ readonly store_id: string }>`
638+
WITH expired_entries AS (
639+
SELECT TOP ${sql.literal(String(sqlCleanupBatchSize))} store_id, id FROM ${table}
640+
WITH (UPDLOCK, READPAST, READCOMMITTEDLOCK)
641+
WHERE expires IS NOT NULL AND expires <= ${expiresAtOrBefore}
642+
)
643+
DELETE persistence
644+
OUTPUT DELETED.store_id
645+
FROM ${table} AS persistence
646+
INNER JOIN expired_entries
647+
ON persistence.store_id = expired_entries.store_id
648+
AND persistence.id = expired_entries.id
649+
`.pipe(Effect.map((deletedEntries) => deletedEntries.length)),
650+
// Some sqlite clients do not support interactive transactions, so use one bounded statement.
651+
orElse: () => (expiresAtOrBefore: number) =>
652+
sql<{ readonly deleted: number }>`
653+
DELETE FROM ${table}
654+
WHERE rowid IN (
655+
SELECT rowid FROM ${table}
656+
WHERE expires IS NOT NULL AND expires <= ${expiresAtOrBefore}
657+
LIMIT ${sql.literal(String(sqlCleanupBatchSize))}
658+
)
659+
RETURNING 1 AS deleted
660+
`.pipe(Effect.map((deletedEntries) => deletedEntries.length))
661+
})
662+
663+
const deleteExpired = Effect.gen(function*() {
664+
const expiresAtOrBefore = yield* Clock.currentTimeMillis
665+
return yield* deleteExpiredBatch(expiresAtOrBefore).pipe(
666+
Effect.repeat({
667+
while: (deletedCount) => deletedCount === sqlCleanupBatchSize,
668+
schedule: Schedule.spaced(cleanupBatchDelay)
669+
})
670+
)
671+
})
672+
673+
yield* deleteExpired.pipe(
674+
Effect.catch((cause) => Effect.logWarning("Failed to clean up expired persistence entries", cause)),
675+
Effect.repeat(Schedule.spaced(cleanupInterval)),
676+
Effect.forkScoped
677+
)
678+
563679
type UpsertFn = (
564680
entries: Array<{ store_id: string; id: string; value: string; expires: number | null }>
565681
) => Effect.Effect<unknown, SqlError>
@@ -606,11 +722,6 @@ export const layerBackingSql: Layer.Layer<
606722
make: Effect.fnUntraced(function*(storeId) {
607723
const clock = yield* Clock.Clock
608724

609-
// Cleanup expired entries on startup
610-
yield* Effect.ignore(
611-
sql`DELETE FROM ${table} WHERE store_id = ${storeId} AND expires IS NOT NULL AND expires <= ${clock.currentTimeMillisUnsafe()}`
612-
)
613-
614725
return identity<BackingPersistenceStore>({
615726
get: (key) =>
616727
sql<
Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,22 @@
1+
import { Duration, Effect, Schedule } from "effect"
2+
import { sqlCleanupBatchSize } from "effect/internal/persistence"
3+
import { TestClock } from "effect/testing"
4+
5+
export const expiredEntryCount = sqlCleanupBatchSize + 1
6+
export const expiredAtEpoch = 0
7+
export const futureExpiresAt = Number.MAX_SAFE_INTEGER
8+
export const cleanupBatchDelay = Duration.millis(10)
9+
export const testTimeout = 30_000
10+
11+
export const waitForCount = <E, R>(
12+
effect: Effect.Effect<number, E, R>,
13+
predicate: (count: number) => boolean
14+
) =>
15+
effect.pipe(
16+
Effect.repeat({
17+
until: predicate,
18+
schedule: Schedule.spaced(cleanupBatchDelay)
19+
}),
20+
Effect.timeout(Duration.millis(testTimeout)),
21+
TestClock.withLive
22+
)

‎packages/sql/mysql2/test/Persistence.integration.test.ts‎

Lines changed: 64 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,11 @@
1-
import { Layer } from "effect"
1+
import { assert, it } from "@effect/vitest"
2+
import { Effect, Layer } from "effect"
23
import * as PersistedCacheTest from "effect-test/unstable/persistence/PersistedCacheTest"
34
import * as PersistedQueueTest from "effect-test/unstable/persistence/PersistedQueueTest"
5+
import * as SqlCleanupTest from "effect-test/unstable/persistence/SqlCleanupTest"
6+
import { TestClock } from "effect/testing"
47
import { PersistedQueue, Persistence } from "effect/unstable/persistence"
8+
import { SqlClient } from "effect/unstable/sql"
59
import { MysqlContainer } from "./utils.ts"
610

711
PersistedCacheTest.suite(
@@ -20,3 +24,62 @@ PersistedQueueTest.suite(
2024
Layer.provide(MysqlContainer.layerClient)
2125
)
2226
)
27+
28+
it.layer(MysqlContainer.layerClient, { timeout: "30 seconds" })("Persistence SQL cleanup", (it) => {
29+
it.effect("deletes expired entries in batches", () =>
30+
Effect.gen(function*() {
31+
const sql = (yield* SqlClient.SqlClient).withoutTransforms()
32+
const table = sql("effect_persistence")
33+
const expiredCount = sql<{ readonly count: number }>`
34+
SELECT COUNT(*) AS count FROM ${table} WHERE store_id = 'expired'
35+
`.pipe(Effect.map((rows) => Number(rows[0].count)))
36+
yield* sql`
37+
CREATE TABLE ${table} (
38+
store_id VARCHAR(191) NOT NULL,
39+
id VARCHAR(191) NOT NULL,
40+
value TEXT NOT NULL,
41+
expires BIGINT,
42+
PRIMARY KEY (store_id, id)
43+
)
44+
`
45+
46+
const entries = Array.from({ length: SqlCleanupTest.expiredEntryCount }, (_, i) => ({
47+
store_id: "expired",
48+
id: String(i),
49+
value: "{}",
50+
expires: SqlCleanupTest.expiredAtEpoch
51+
}))
52+
yield* sql`INSERT INTO ${table} ${sql.insert(entries)}`.unprepared
53+
yield* sql`
54+
INSERT INTO ${table} (store_id, id, value, expires)
55+
VALUES ('live', 'live', '{}', NULL), ('live', 'future', '{}', ${SqlCleanupTest.futureExpiresAt})
56+
`
57+
58+
yield* Layer.build(Persistence.layerBackingSql)
59+
60+
let expired = yield* SqlCleanupTest.waitForCount(
61+
expiredCount,
62+
(count) => count < SqlCleanupTest.expiredEntryCount
63+
)
64+
assert.strictEqual(expired, 1)
65+
66+
while (expired > 0) {
67+
const previous = expired
68+
yield* TestClock.adjust(SqlCleanupTest.cleanupBatchDelay)
69+
expired = yield* SqlCleanupTest.waitForCount(expiredCount, (count) => count < previous)
70+
}
71+
assert.strictEqual(expired, 0)
72+
const live = yield* sql<{ readonly count: number }>`
73+
SELECT COUNT(*) AS count FROM ${table} WHERE store_id = 'live'
74+
`
75+
assert.strictEqual(Number(live[0].count), 2)
76+
77+
const indexes = yield* sql<{ readonly count: number }>`
78+
SELECT COUNT(*) AS count FROM information_schema.statistics
79+
WHERE table_schema = DATABASE()
80+
AND table_name = 'effect_persistence'
81+
AND index_name = 'effect_persistence_expires_idx'
82+
`
83+
assert.strictEqual(Number(indexes[0].count), 1)
84+
}), { timeout: SqlCleanupTest.testTimeout })
85+
})

‎packages/sql/pg/test/Persistence.integration.test.ts‎

Lines changed: 59 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2,6 +2,7 @@ import { assert, it } from "@effect/vitest"
22
import { Effect, Exit, Fiber, Latch, Layer, Schema } from "effect"
33
import * as PersistedCacheTest from "effect-test/unstable/persistence/PersistedCacheTest"
44
import * as PersistedQueueTest from "effect-test/unstable/persistence/PersistedQueueTest"
5+
import * as SqlCleanupTest from "effect-test/unstable/persistence/SqlCleanupTest"
56
import { TestClock } from "effect/testing"
67
import { PersistedQueue, Persistence } from "effect/unstable/persistence"
78
import { SqlClient } from "effect/unstable/sql"
@@ -104,3 +105,61 @@ it.layer(PgContainer.layerClient, { timeout: "30 seconds" })("PersistedQueue SQL
104105
assert.strictEqual(value, "valid")
105106
}).pipe(TestClock.withLive))
106107
})
108+
109+
it.layer(PgContainer.layerClient, { timeout: "30 seconds" })("Persistence SQL cleanup", (it) => {
110+
it.effect("deletes expired entries in batches", () =>
111+
Effect.gen(function*() {
112+
const sql = (yield* SqlClient.SqlClient).withoutTransforms()
113+
const table = sql("effect_persistence")
114+
const expiredCount = sql<{ readonly count: number }>`
115+
SELECT COUNT(*)::INT AS count FROM ${table} WHERE store_id = 'expired'
116+
`.pipe(Effect.map((rows) => rows[0].count))
117+
yield* sql`
118+
CREATE TABLE ${table} (
119+
store_id TEXT NOT NULL,
120+
id TEXT NOT NULL,
121+
value TEXT NOT NULL,
122+
expires BIGINT,
123+
PRIMARY KEY (store_id, id)
124+
)
125+
`
126+
127+
const entries = Array.from({ length: SqlCleanupTest.expiredEntryCount }, (_, i) => ({
128+
store_id: "expired",
129+
id: String(i),
130+
value: "{}",
131+
expires: SqlCleanupTest.expiredAtEpoch
132+
}))
133+
yield* sql`INSERT INTO ${table} ${sql.insert(entries)}`.unprepared
134+
yield* sql`
135+
INSERT INTO ${table} (store_id, id, value, expires)
136+
VALUES ('live', 'live', '{}', NULL), ('live', 'future', '{}', ${SqlCleanupTest.futureExpiresAt})
137+
`
138+
139+
yield* Layer.build(Persistence.layerBackingSql)
140+
141+
let expired = yield* SqlCleanupTest.waitForCount(
142+
expiredCount,
143+
(count) => count < SqlCleanupTest.expiredEntryCount
144+
)
145+
assert.strictEqual(expired, 1)
146+
147+
while (expired > 0) {
148+
const previous = expired
149+
yield* TestClock.adjust(SqlCleanupTest.cleanupBatchDelay)
150+
expired = yield* SqlCleanupTest.waitForCount(expiredCount, (count) => count < previous)
151+
}
152+
assert.strictEqual(expired, 0)
153+
const live = yield* sql<{ readonly count: number }>`
154+
SELECT COUNT(*)::INT AS count FROM ${table} WHERE store_id = 'live'
155+
`
156+
assert.strictEqual(live[0].count, 2)
157+
158+
const indexes = yield* sql<{ readonly count: number }>`
159+
SELECT COUNT(*)::INT AS count FROM pg_indexes
160+
WHERE tablename = 'effect_persistence'
161+
AND indexname = 'effect_persistence_expires_idx'
162+
`
163+
assert.strictEqual(indexes[0].count, 1)
164+
}), { timeout: SqlCleanupTest.testTimeout })
165+
})

‎packages/sql/sqlite-node/test/Persistence.test.ts‎

Lines changed: 61 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,11 +1,12 @@
11
import { NodeFileSystem } from "@effect/platform-node"
22
import { SqliteClient } from "@effect/sql-sqlite-node"
3-
import { expect, it } from "@effect/vitest"
3+
import { assert, expect, it } from "@effect/vitest"
44
import { Duration, Effect, FileSystem, Layer } from "effect"
5+
import * as SqlCleanupTest from "effect-test/unstable/persistence/SqlCleanupTest"
56
import { TestClock } from "effect/testing"
67
import { Persistence } from "effect/unstable/persistence"
78
import { Reactivity } from "effect/unstable/reactivity"
8-
import type * as SqlClient from "effect/unstable/sql/SqlClient"
9+
import * as SqlClient from "effect/unstable/sql/SqlClient"
910

1011
const ClientLayer = Effect.gen(function*() {
1112
const fs = yield* FileSystem.FileSystem
@@ -107,3 +108,61 @@ const suite = (name: string, layer: Layer.Layer<Persistence.BackingPersistence,
107108

108109
suite("table-per-store", Persistence.layerBackingSqlMultiTable)
109110
suite("single-table", Persistence.layerBackingSql)
111+
112+
it.layer(ClientLayer)("Persistence SQL cleanup", (it) => {
113+
it.effect("deletes expired entries in batches", () =>
114+
Effect.gen(function*() {
115+
const sql = (yield* SqlClient.SqlClient).withoutTransforms()
116+
const table = sql("effect_persistence")
117+
const expiredCount = sql<{ readonly count: number }>`
118+
SELECT COUNT(*) AS count FROM ${table} WHERE store_id = 'expired'
119+
`.pipe(Effect.map((rows) => rows[0].count))
120+
yield* sql`
121+
CREATE TABLE ${table} (
122+
store_id TEXT NOT NULL,
123+
id TEXT NOT NULL,
124+
value TEXT NOT NULL,
125+
expires INTEGER,
126+
PRIMARY KEY (store_id, id)
127+
)
128+
`
129+
130+
const entries = Array.from({ length: SqlCleanupTest.expiredEntryCount }, (_, i) => ({
131+
store_id: "expired",
132+
id: String(i),
133+
value: "{}",
134+
expires: SqlCleanupTest.expiredAtEpoch
135+
}))
136+
yield* sql`INSERT INTO ${table} ${sql.insert(entries)}`.unprepared
137+
yield* sql`
138+
INSERT INTO ${table} (store_id, id, value, expires)
139+
VALUES ('live', 'live', '{}', NULL), ('live', 'future', '{}', ${SqlCleanupTest.futureExpiresAt})
140+
`
141+
142+
yield* Layer.build(Persistence.layerBackingSql)
143+
144+
let expired = yield* SqlCleanupTest.waitForCount(
145+
expiredCount,
146+
(count) => count < SqlCleanupTest.expiredEntryCount
147+
)
148+
assert.strictEqual(expired, 1)
149+
150+
while (expired > 0) {
151+
const previous = expired
152+
yield* TestClock.adjust(SqlCleanupTest.cleanupBatchDelay)
153+
expired = yield* SqlCleanupTest.waitForCount(expiredCount, (count) => count < previous)
154+
}
155+
assert.strictEqual(expired, 0)
156+
const live = yield* sql<{ readonly count: number }>`
157+
SELECT COUNT(*) AS count FROM ${table} WHERE store_id = 'live'
158+
`
159+
assert.strictEqual(live[0].count, 2)
160+
161+
const indexes = yield* sql<{ readonly count: number }>`
162+
SELECT COUNT(*) AS count FROM sqlite_master
163+
WHERE type = 'index'
164+
AND name = 'effect_persistence_expires_idx'
165+
`
166+
assert.strictEqual(indexes[0].count, 1)
167+
}), { timeout: SqlCleanupTest.testTimeout })
168+
})

0 commit comments

Comments
 (0)