From 8633ab4bc13bf957d7598700338c8d0e251e0cfa Mon Sep 17 00:00:00 2001 From: JP Appel Date: Tue, 22 Jul 2025 23:43:00 -0400 Subject: Add periodic db optimizations --- pkg/data/db.go | 43 ++++++++++++++++++++++++++++++++++--------- 1 file changed, 34 insertions(+), 9 deletions(-) (limited to 'pkg') diff --git a/pkg/data/db.go b/pkg/data/db.go index 3c9f6d0..178c19d 100644 --- a/pkg/data/db.go +++ b/pkg/data/db.go @@ -4,6 +4,7 @@ import ( "context" "database/sql" "fmt" + "log/slog" "strings" "time" @@ -286,6 +287,7 @@ func createSchema(db *sql.DB) error { } func (q Query) Close() error { + q.db.Exec("PRAGMA OPTIMIZE") return q.db.Close() } @@ -335,28 +337,51 @@ func (q Query) GetDocument(path string) (*index.Document, error) { // Shrink database by removing unused authors, aliases, tags and VACUUM-ing func (q Query) Tidy() error { - _, err := q.db.Exec(` + if _, err := q.db.Exec(` DELETE FROM Authors WHERE id NOT IN ( SELECT authorId FROM DocumentAuthors - ) - `) - if err != nil { + )`); err != nil { return err } - _, err = q.db.Exec(` + if _, err := q.db.Exec(` DELETE FROM Tags WHERE id NOT IN ( SELECT tagId FROM DocumentTags ) - `) - if err != nil { + `); err != nil { + return err + } + + if _, err := q.db.Exec("VACUUM"); err != nil { return err } - _, err = q.db.Exec("VACUUM") - return err + return nil +} + +func (q Query) PeriodicOptimize(ctx context.Context, d time.Duration) { + _, err := q.db.ExecContext(ctx, "PRAGMA OPTIMIZE optimize=0x10002") + if err != nil { + return + } + + ticker := time.NewTicker(d) + + for { + select { + case <-ticker.C: + slog.Debug("Running periodic db optimization", + slog.Int64("next", time.Now().Unix()+int64(d)), + ) + if _, err := q.db.ExecContext(ctx, "PRAGMA OPTIMIZE"); err != nil { + return + } + case <-ctx.Done(): + return + } + } } func (q Query) Execute(artifact query.CompilationArtifact) (map[string]*index.Document, error) { -- cgit v1.2.3