@@ -15,8 +15,10 @@ import * as Duration from "../../Duration.ts"
1515import * as Effect from "../../Effect.ts"
1616import * as Exit from "../../Exit.ts"
1717import { identity } from "../../Function.ts"
18+ import { sqlCleanupBatchSize } from "../../internal/persistence.ts"
1819import * as Layer from "../../Layer.ts"
1920import * as PrimaryKey from "../../PrimaryKey.ts"
21+ import * as Schedule from "../../Schedule.ts"
2022import * as Schema from "../../Schema.ts"
2123import type * as Scope from "../../Scope.ts"
2224import * 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 <
0 commit comments