[PR #1024] [MERGED] feat: improve SQLite performance #934

New issue

Closed

opened 2026-02-25 23:35:56 +03:00 by kerem · 0 comments

kerem commented

2026-02-25 23:35:56 +03:00

Owner

📋 Pull Request Information

Original PR: https://github.com/go-shiori/shiori/pull/1024
Author: @fmartingr
Created: 12/10/2024
Status: ✅ Merged
Merged: 1/2/2025
Merged by: @fmartingr

Base: master ← Head: feat/sqlite-performace

📝 Commits (10+)

5882935 refactor: Improve SQLite performance with connection pooling and retry logic
9a02441 feat: Add withTx and withTxRetry methods to SQLiteDatabase for handling database locks
b52dd10 refactor: add Init command to all databases
e4e059b refactor: Improve transaction handling with retry and error management
99ab3d0 refactor: Remove panic/recover pattern in transaction handling
739f860 refactor: Replace errors.WithStack with fmt.Errorf in transaction methods
ac4c329 docs: Add docstrings to withTx and withTxRetry methods in SQLite database implementation
673778f feat: use new withTxRetry in SaveBookmarks
f89af36 feat: sqlite inmmediate transactions by default
bc0eb96 refactor: Split SQLiteDatabase into separate writer and reader dbbase instances

📊 Changes

8 files changed (+234 additions, -97 deletions)

View changed files

📝 internal/config/config.go (+1 -1)
📝 internal/database/database.go (+3 -0)
📝 internal/database/mysql.go (+5 -0)
📝 internal/database/pg.go (+5 -0)
📝 internal/database/sqlite.go (+183 -75)
📝 internal/database/sqlite_noncgo.go (+17 -4)
📝 internal/database/sqlite_openbsd.go (+17 -4)
📝 internal/domains/bookmarks_test.go (+3 -13)

📄 Description

Improve SQLite performance tuning the parameters in connections
Using different connections for reading and writing
Added a new withTxRetry method to try and avoid database is busy errors.
Code formatting

_{🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.}

## 📋 Pull Request Information **Original PR:** https://github.com/go-shiori/shiori/pull/1024 **Author:** [@fmartingr](https://github.com/fmartingr) **Created:** 12/10/2024 **Status:** ✅ Merged **Merged:** 1/2/2025 **Merged by:** [@fmartingr](https://github.com/fmartingr) **Base:** `master` ← **Head:** `feat/sqlite-performace` --- ### 📝 Commits (10+) - [`5882935`](https://github.com/go-shiori/shiori/commit/5882935cf8844b306034d82f302f58f161906581) refactor: Improve SQLite performance with connection pooling and retry logic - [`9a02441`](https://github.com/go-shiori/shiori/commit/9a024411cb081ecc3417b097b4c2bcf289e53c08) feat: Add withTx and withTxRetry methods to SQLiteDatabase for handling database locks - [`b52dd10`](https://github.com/go-shiori/shiori/commit/b52dd108066e09bc3b9edec47bff29e9aef9bdc5) refactor: add Init command to all databases - [`e4e059b`](https://github.com/go-shiori/shiori/commit/e4e059bd60fc857db211e4b43723455492ef9d11) refactor: Improve transaction handling with retry and error management - [`99ab3d0`](https://github.com/go-shiori/shiori/commit/99ab3d0ed20783499c1b991e1c4f63647b8973d9) refactor: Remove panic/recover pattern in transaction handling - [`739f860`](https://github.com/go-shiori/shiori/commit/739f860523caf3958e04fd48cfa5018485c2fe99) refactor: Replace `errors.WithStack` with `fmt.Errorf` in transaction methods - [`ac4c329`](https://github.com/go-shiori/shiori/commit/ac4c32935f3136eff19977a1d773b932c1bd3840) docs: Add docstrings to `withTx` and `withTxRetry` methods in SQLite database implementation - [`673778f`](https://github.com/go-shiori/shiori/commit/673778fb129b934b958a9c4f71a1ac0684f5272d) feat: use new withTxRetry in SaveBookmarks - [`f89af36`](https://github.com/go-shiori/shiori/commit/f89af361d66d2cf0d98df4a8b713de8e40222e4c) feat: sqlite inmmediate transactions by default - [`bc0eb96`](https://github.com/go-shiori/shiori/commit/bc0eb96a35677426c3bc2fb86ad48b63787a07a9) refactor: Split SQLiteDatabase into separate writer and reader dbbase instances ### 📊 Changes **8 files changed** (+234 additions, -97 deletions) <details> <summary>View changed files</summary> 📝 `internal/config/config.go` (+1 -1) 📝 `internal/database/database.go` (+3 -0) 📝 `internal/database/mysql.go` (+5 -0) 📝 `internal/database/pg.go` (+5 -0) 📝 `internal/database/sqlite.go` (+183 -75) 📝 `internal/database/sqlite_noncgo.go` (+17 -4) 📝 `internal/database/sqlite_openbsd.go` (+17 -4) 📝 `internal/domains/bookmarks_test.go` (+3 -13) </details> ### 📄 Description - Improve SQLite performance tuning the parameters in connections - Using different connections for reading and writing - Added a new `withTxRetry` method to try and avoid `database is busy` errors. - Code formatting --- <sub>🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.</sub>