Grocy users, update to SQLite 3.50

Numerous bug reports exist of Grocy being slow (all closed reflexively by the author, Bernd Bestel, blaming the users of the app with pretexts like "slow hardware" or "bullshit data entry") .

Bernd is wrong (he's also rude but that's for another topic). I found the actual problem and I will share a fix.

The bug

I chose to spend a few hours to track down the issue.  While I initially (and mistakenly) chalked the issue to inefficient SQL querying,  ultimately the issue is caused by a substantial regression present in older versions of SQLite — specifically appearing when SQLite transitioned from version 3.46 to 3.47, which is the version of SQLite present in Fedora 42 and many other last-generation Linux distributions.

Quoted from the issue report in their forum / mailing list (linked in the previous paragraph):

complex CTE query goes from 1.5s in 3.46.1 to 17s in 3.47.2
(1) By gotson on 2025-01-14 04:35:51 [source]
Hi,

i recently had some reports of poor performance in my application, after some investigation i narrowed it down to the version of SQLite used.

I have a database.sqlite file provided by a client that is ~7GB.

The impacted query is using 2 CTE expressions.

In 3.46.1, the query takes ~1.5s.

In 3.47.2, the same query takes ~17s.

I have ran explain query plan on both versions, and i get a different plan, but i don't know what i can do from there.

I can always remain on 3.46.1 for now, but that won't work in the long term.

What steps could I take in order to try to resolve this ?

I could provide the database file (i would need to ask my user if that is OK to share), as well as the query, if that is something that could help to understand why the 3.47 version doesn't perform as well as 3.46.

This regression impacts complex CTE queries, which Grocy uses in at least one of its database views (recipes_pos_resolved, in turn used by recipes_resolved).  It made the recipes and the meal plan Web views of my Grocy setup take in excess of six seconds to load.  Here is the query with Fedora 42's SQLite 3.47:

bash-5.2# sqlite3 new.db
SQLite version 3.47.2 2024-12-07 20:39:59
Enter ".help" for usage hints.
sqlite> .timer on
sqlite> select count(*) from recipes_resolved;
101
Run Time: real 5.989 user 5.737005 sys 0.009360
sqlite> 

For context, this is on a tiny database with 12 recipes, 200 products and 21 quantity units — so, the opposite of a big data warehouse.

The fix

Here is the same query on the same hardware with a Toolbox container of Fedora 43 sporting SQLite 3.50:

bash-5.2# toolbox enter fedora-toolbox-43
⬢ [root@toolbx tmp]# sqlite3 new.db
SQLite version 3.50.2 2025-06-28 14:00:48
Enter ".help" for usage hints.
sqlite> .timer on
sqlite> select count(*) from recipes_resolved;
101
Run Time: real 0.115 user 0.100834 sys 0.007156
sqlite> 

If you have performance issues, then you are strongly recommended to upgrade to SQLite 3.50 or later.

Linuxserver container users of Grocy — fix may be incoming 

Most users are relying on the linuxserver Docker image container for Grocy.  At the time of this writing, the version the container ships of SQLite3 is 3.48, and it is affected by the bug:

bash-5.2# podman exec -it upbeat_bell /bin/sh
root@e2da071c4f01:/# apk list | grep sqlit
WARNING: opening from cache http://dl-cdn.alpinelinux.org/alpine/v3.21/main: No such file or directory
WARNING: opening from cache http://dl-cdn.alpinelinux.org/alpine/v3.21/community: No such file or directory
php83-pdo_sqlite-8.3.19-r0 x86_64 {php83} (PHP-3.01 BSD-3-Clause LGPL-2.0-or-later MIT Zend-2.0) [installed]
sqlite-libs-3.48.0-r4 x86_64 {sqlite} (blessing) [installed]
root@e2da071c4f01:/# exit
bash-5.2# podman exec -it upbeat_bell /bin/sh
root@e2da071c4f01:/# apk add sqlite
s(1/1) Installing sqlite (3.48.0-r4)
Executing busybox-1.37.0-r14.trigger
OK: 88 MiB in 118 packages
liroot@969acf43fec6:/# sqlite3 new.db
SQLite version 3.48.0 2025-01-14 11:05:00
Enter ".help" for usage hints.
sqlite> .timer on
sqlite> select count(*) from recipes_resolved;
101
Run Time: real 4.496 user 4.322394 sys 0.049214
sqlite> 

The fix will probably have to involve either building a custom SQLite 3.50+ library for the container, or updating the base Alpine image of the container to a later major release that contains a fixed SQLite.

Fortunately, I have alerted the Linuxserver folks in an issue open since September of last year regarding the slowdown noticed by users of the container (as well), and it looks like now they might take a look at it.

The background

Yesterday, I filed a very detailed bug report on the Grocy bug  tracker (the bug report is deleted now).

I received a very rude reply from the maintainer, blaming me and refusing to look at the issue, insta-closing it, despite having everything he needed to reproduce the issue.  Not exactly the level of gratitude one expects for the work one puts into helping others fix problems in their projects.

I then posted an angry reply, saying I all but swore off the project — and indeed criticized his coding skills (somewhat of a reasonable hypothesis, because the slow query is in fact quite the complex query, which a poor database query planner would have no problem making exponentially slow).

This almost certainly got under the maintainer's skin, who finally proceeded to take the data I gave him and test it locally (thank God for my angry rant, otherwise he likely would just have closed the ticket as he did with many others).

Whaddya know, on his system, the same stupid query went fast!  He could not reproduce the issue at all.  I was incredulous for a moment, given the ocean-drying levels of hardware power I'm throwing at my self-hosted setup, but then I reasoned... OK, if the query itself goes fast on another system, maybe it's something in my system that is not the hardware?

Sure enough, a quick test on another machine I own with a different, more up-to-date OS, showed the query to be near-instantaneous — thus proving that the maintainer's thesis that the query wasn't too terrible was correct (while his final reply showed that his curiosity for what ails his project's many users is nil... but again, that's for another post).

So if hardware isn't the issue, and the query isn't the issue, all that's left must be the database itself.

From then on I began searching online about performance problems with SQLite, which then in mere minutes led me to simply testing a newer version of SQLite (thank god for Fedora Toolbox!)... and that was the key.

Angery debugging is best debugging?

Update on the drama

Bernd is also the moderator of the Reddit Grocy community as well, where I posted how to fix the Grocy performance problem. He deleted the post on Grocy, and banned me from the community.

See for yourself:

No worries — we have an archive of the thread at the bottom of this post.  I guess he did not like the comments (in the now-deleted post) from other community members acknowledging his temper: