Using sqlite has allowed an improvement for our shiny apps in terms of allowing a standard set of queries between both postges and local backends.
However, a recurring problem is the relatively large size of the sqlite data sets due to indexes that need to be built as table references.
Andromeda is converting from sqlite to arrow as a new backend, this has resulted in significant performance increases. In addition, the base storage can simply be as a CSV model that can be queried through an SQL interface.
Investigating Arrow should be based around the following principles:
How do we convert our existing data sets easily?
How do we query this data with minimal changes to the data model?
How much does it impact the performance of the application?
Does it actually reduce the data footprint significantly?
After evaluation, there are limitations with arrow when used as an SQL backend that won't let us easily update the schema easily.
Pursuing an alternative solution changing database ids to integers.
Using sqlite has allowed an improvement for our shiny apps in terms of allowing a standard set of queries between both postges and local backends. However, a recurring problem is the relatively large size of the sqlite data sets due to indexes that need to be built as table references.
Andromeda is converting from sqlite to arrow as a new backend, this has resulted in significant performance increases. In addition, the base storage can simply be as a CSV model that can be queried through an SQL interface.
Investigating Arrow should be based around the following principles: