In-process DBMS?

[jmgirard]

Currently, postgres is used as a client-server DBMS, which allows the separation of storage and compute. I imagine this is beneficial for keeping the load on the shiny server lower. However, it also introduces some delay in transfer between servers and requires additional accounts and authentication. This may be a dumb idea, but I'm wondering if a viable alternative would be to store the data using an in-process DBMS (sqlite or duckdb) to a database file local to the shiny server. I think that would eliminate the transfer delay and need for separate authentication. I'm just not sure how well the local database file would be shared across workers and how persistent it would be across updates to the app. Anyway, just thought I'd share the idea - even if I'm missing something and it's not viable.

[jhelvy]

We thought a good amount about this, but there are some pretty good reasons to use a remote storage service for persistent data storage (e.g. see here). Now that we've modified how often the data writing happens, I'm fairly confident that the DBMS side of the platform won't be a bottle neck for performance. I think any lag experienced will now be on the shiny side. We've tried to make it efficient, and I'm sure there are more things we could do to improve it, but there are a lot of calculations being made on the shiny side, and I'm afraid that the compute needs might get pretty high in a real-time survey setting where you've got tens (possibly hundreds) of concurrent users. We'll keep seeing if we can refactor things to simplify the compute requirements.

The good news is that we might just be able to throw $ at the problem by having more RAM (i.e. using a paid tier). Of course, the bad news is that you have to spend $. The goal of the project was to find a fully free & open source solution for surveys, but there may be limits to what we can achieve when relying on the goodness of companies like Posit to make services like shinyapps.io available for free. At some point you may just have to pay for some compute.

One thing I'd like to experiment with is seeing if there are other ways to relatively easily host a shiny app somewhere else. I haven't tried Heroku, but it may have better free options.

[jmgirard]

Totally fair and thanks for the link.

[jmgirard]

It seems like Heroku can also host postgres. I wonder if hosting both shiny and postgres on the same service would ease transfer and authentication.

[jhelvy]

Sweet! Yeah that would be pretty nice if we could have a one-stop-shop for hosting everything. We should be able to already try this out too with the current architecture. The sd_database() function should work with any postgres database, just pass the appropriate parameters in. I might give this a try. If it seems to work well / better, then maybe we could add a streamlined approach to hosting everything on Heroku.