[PR #425] [MERGED] Improve performance for sqlite queries #592

New issue

Closed

opened 2026-02-25 23:34:50 +03:00 by kerem · 0 comments

kerem commented

2026-02-25 23:34:50 +03:00

Owner

📋 Pull Request Information

Original PR: https://github.com/go-shiori/shiori/pull/425
Author: @Orhideous
Created: 5/10/2022
Status: ✅ Merged
Merged: 8/8/2022
Merged by: @fmartingr

Base: master ← Head: fix/performance

📝 Commits (10+)

7a6d59e [sqlite] Added has_content column and relevant migration
8a03f02 Fix typo in docstring
81927f4 [sqlite] Fetch content for bookmarks separately
4619813 [sqlite] Store has_content alongside with bookmark
bf18635 [sqlite] Rename variable to distinguish it from main query
7e3be82 [sqlite] Use by-reference instead of no-op copy
bdd2712 [sqlite] Reduce queries count from ≈30 to 1 to fetch tags
c928a5d Lint fixes
b6d0541 Make minor logging improvements
204f316 Wrap entire migration in transaction block

📊 Changes

5 files changed (+105 additions, -39 deletions)

View changed files

📝 internal/database/database.go (+1 -1)
➕ internal/database/migrations/sqlite/0002_denormalize_content.down.sql (+3 -0)
➕ internal/database/migrations/sqlite/0002_denormalize_content.up.sql (+8 -0)
📝 internal/database/sqlite.go (+92 -37)
📝 internal/webserver/server.go (+1 -1)

📄 Description

This PR is intended to address a range of performance regressions for the SQLite database.

Namely:

Removed the slow JOIN for bookmark lists
Reduced the number of queries for tags

Tested manually on 3k bookmarks with content. Last version (7394b10) returned main page in a second or two. Now it takes few, maybe tens of milliseconds.

Note that migration is required: shiori migrate.

Built an image for the test, now I use it myself: docker.io/orhideous/go-shiori:1.5.2-perf-fix

P.S. There is definitely a room for improvement for another databases.

Fixes #408

_{🔄 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/425 **Author:** [@Orhideous](https://github.com/Orhideous) **Created:** 5/10/2022 **Status:** ✅ Merged **Merged:** 8/8/2022 **Merged by:** [@fmartingr](https://github.com/fmartingr) **Base:** `master` ← **Head:** `fix/performance` --- ### 📝 Commits (10+) - [`7a6d59e`](https://github.com/go-shiori/shiori/commit/7a6d59e5405eab2eaeac9f6ed80028ea734183b0) [sqlite] Added has_content column and relevant migration - [`8a03f02`](https://github.com/go-shiori/shiori/commit/8a03f029ed324b2759942b13a3236d8d2ad42be0) Fix typo in docstring - [`81927f4`](https://github.com/go-shiori/shiori/commit/81927f4c069ecc3b2772e511ffc316f71edd300a) [sqlite] Fetch content for bookmarks separately - [`4619813`](https://github.com/go-shiori/shiori/commit/4619813d7346714429faa2277bc136838a0ee0aa) [sqlite] Store has_content alongside with bookmark - [`bf18635`](https://github.com/go-shiori/shiori/commit/bf18635c6ec7b8e3f514f3ba17b5bed65f895a04) [sqlite] Rename variable to distinguish it from main query - [`7e3be82`](https://github.com/go-shiori/shiori/commit/7e3be824e05e719d74a963803163505f3085e454) [sqlite] Use by-reference instead of no-op copy - [`bdd2712`](https://github.com/go-shiori/shiori/commit/bdd27127d55aad16aa9c3bbb27859bd92e0b5a7d) [sqlite] Reduce queries count from ≈30 to 1 to fetch tags - [`c928a5d`](https://github.com/go-shiori/shiori/commit/c928a5d61b18534de6e9d518b1b05c1225d20a0e) Lint fixes - [`b6d0541`](https://github.com/go-shiori/shiori/commit/b6d0541cfeaa0ef509007340e235b4111ebb0142) Make minor logging improvements - [`204f316`](https://github.com/go-shiori/shiori/commit/204f316089a129c2ab2b1b89721631de65d41607) Wrap entire migration in transaction block ### 📊 Changes **5 files changed** (+105 additions, -39 deletions) <details> <summary>View changed files</summary> 📝 `internal/database/database.go` (+1 -1) ➕ `internal/database/migrations/sqlite/0002_denormalize_content.down.sql` (+3 -0) ➕ `internal/database/migrations/sqlite/0002_denormalize_content.up.sql` (+8 -0) 📝 `internal/database/sqlite.go` (+92 -37) 📝 `internal/webserver/server.go` (+1 -1) </details> ### 📄 Description This PR is intended to address a range of performance regressions for the SQLite database. Namely: - Removed the slow JOIN for bookmark lists - Reduced the number of queries for tags Tested manually on 3k bookmarks with content. Last version ([7394b10](https://github.com/go-shiori/shiori/commit/7394b10)) returned main page in a second or two. Now it takes few, maybe tens of *milliseconds*. Note that migration is required: `shiori migrate`. Built an image for the test, now I use it myself: `docker.io/orhideous/go-shiori:1.5.2-perf-fix` _P.S. There is definitely a room for improvement for another databases._ Fixes #408 --- <sub>🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.</sub>