What Database backend are you using ?

[chevdor]

I have been using Posteres so far without being amazed with the overall performance.
I added redis caching which improved a bit but still the perf is not matching my expectation for a local network and a decent infra. It is hard to compare oranges to apples but testing Actual for instance was much more enjoyable, not b/c of the (lack of...) features but b/c of the speed.

I have been running FF on a NAS in Docker so far and always blamed the limited amount of resources there (although having a decent NAS, using SSD, using caching, having enough RAM, and using SSD caching...).

I recently did a test install using K8S on a 3 nodes cluster running on Intel NUC with Core i5. I usually get very good perf. for OSS Apps with database backends. Still not amazed with the perf of FF+postgres+redis, no matter how much CPU/Mem/replicas I throw at it.

So I decied to give sqlite a try. I am hosting the files via NFS (hold you horses...) on a NFS 4.1 server running in the NAS (Synology).
This constellation NFS4.1 + Sqlite has shown working decently with all apps I run.

So far my DB is empty to I cannot really make a valid comparison in term of perf but FF in general is already much snappier with sqlite only (no redis atm).

That leads to this poll. What are YOU using as DB backend and what is the reason ?

I personally went for the argument that I already had some postgres. It ended up a bad option since FF does not support case insensitive queries on postgres. I will not argue the reasons, the fact is: no case insensitive search when using FF and postgres (and that sucks).

I guess many use MySQL as it seems to tbe the projects "default".

At the same time, I am pretty sure a very large portion of the users are single users with a single account on a single server... where sqlite would actually work very well if not better than the other options.

I know sqlite sounds less sexy but the speed benefit is already very attractive.

[chevdor]

I'd change my vote to sqlite now after seeing the benchmarks.

Here is how I tested after importing my data into sqlite.
So the 2 servers have 100% the same data.

BASE_URL1=http://<some_ip>:<some_port>
BASE_URL2=https://firefly.k8s.<some_domain>
ENDPOINT=accounts/expense

• URL1: is my Synology NAS (1819+) running Firefly / Postgres / Redis
• URL2: k8s cluster running Firefly / sqlite

The tests are ran as:

ab -c 1 -n 10 $BASE_URL1/$ENDPOINT
ab -c 1 -n 10 $BASE_URL2/$ENDPOINT

NAS postgres/redis:

Percentage of the requests served within a certain time (ms)
  50%     50
  66%     50
  75%     50
  80%     52
  90%     65
  95%     65
  98%     65
  99%     65
 100%     65 (longest request)

sqlite k8s/sqlite (the data being in the NAS on a NFS share anyway):

Percentage of the requests served within a certain time (ms)
  50%     29
  66%     29
  75%     29
  80%     38
  90%     54
  95%     54
  98%     54
  99%     54
 100%     54 (longest request)

I will eventually test adding redis to the mix but I am not convinced it will help since I am using R/W caching on the NAS...

[BackSeat]

Too many variables to be a meaningful comparison - different hardware, different software infrastructure. Maybe SQLite is faster - it's a fast database and benefits from being single user - but you haven't proved that yet. -- "You can lead a horse to water but you can't make it learn grammar" - Reddit

[chevdor]

Still I did the same test using postgres+redis in my k8s cluster and it was still very slow Barely a bit faster than the NAS version.
In that scenario, all hardware is the same, the config is the same, the k8s version should even have penalties for adding extra step with the reverse proxy and ssl support.

Sure my test is not super scientific but that's close to a 50% improvement and enough for me to be convinced. Not to mention that it needs way less resources and it is a breeze to "backup" a sqlite in k8s with a cronjob.

What DB did you settle with @BackSeat ?

[BackSeat]

I'm using MariaDB (essentially MySQL) only because I'm more familiar with that than PostgreSQL. -- "You can lead a horse to water but you can't make it learn grammar" - Reddit