Adding support for columnar databases

[diamant-x]

Hi,

After several years of using it, and importing my previously Excel-based domestic economy data since 2014, I can consider myself now fully FireflyIII-based.
One area were I see the tool would be ready to make the leap for next version is on speed for multi-year reporting. While there are multiple approaches to achieve this, not sure if adopting 'Columnar databases' has been explored yet.
For this kind of applications like FFIII, where data-writes are less frequent than data-reads, columnar databases (and even better, in-memory columnar databases) provide a 'quick' leap just by using them vs tradition SQL row-based databases.

Google's product based on this page provides a quick summary of its benefits https://cloud.google.com/blog/products/databases/alloydb-for-postgresql-columnar-engine and even have started working on a self-hsoted version of it https://cloud.google.com/alloydb/docs/omni but the web seems to agree on https://duckdb.org/ for the best single-instance 'small' scale (compared to enterprises).

I've myself seen the leap on enterprise scenarios with milions of rows aggregations going from minutes/hours (where pre-aggregations were needed) to seconds without needing pre-aggregations anymore. Of course this is a combination of technology and datamodel changes, but just the 'raw' engine change, presents an incredible leap.

The 'quickest' path would be to adopt an SQL-compatible database, so that all existing queries can just be "plugged-and-play' to an columnar database.

• Have you/anyone tested FFIII with this kind of databases?
• Any feelings about it?

Thanks!

[JC5]

I haven't tested it yet, I see there is some PHP/Laravel support. It seems like a nice to have. I'll check it out :)

[JC5]

I checked out some libraries but they're still very immature and the speed jump for Firefly III is not that big probably. So this is not going to happen for the time being.

[diamant-x]

What a pitty. Which ones did you checked? I might do some research to see if I find any that might be better candidates. Requirements would be support for... PHP native? Or something else?

For example, I see Laravel has native PostrgeSQL support https://laravel.com/docs/10.x/database, and PostgreSQL has this extension available to create columnar tables https://github.com/citusdata/citus . Don't know though if Laravel can support PostgreSQL extensions easily. Seems like people has attempted that https://github.com/metinagaoglu/Citus-Sharding-Example-Laravel

[JC5]

It basically has to run out of the box. If users want to tinker with Firefly III and columnar tables they're welcome to it, but I want to keep the app stable. So if I can't run the database migrations because the underlying engine doesn't support ALTER TABLES it's already hard to implement in Firefly III.

Let me know what you can find!

[diamant-x]

I found one that is opensource, is referenced by enterprise solutions (i.e. is not a hobby project that might be unstable and abandoned) and supports most of SQL commands, including ALTER TABLES:

https://www.monetdb.org/documentation-Dec2023/user-guide/introduction-to-monetdb/key-concepts/

Let's see how it looks! :)
I'm so looking forward to running reports across multiple years, specially to see the net worth chart (which right now crashes/times out). Hopefully this might be a solution to that as well.