Solved: Best Database Option

Boray · 01-28-2025 04:31 AM

Hello everyone, I know it’s difficult to compare databases, but based on your experiences, what would you say is the best database for an app with around 40-50 tables? Approximately 10 of these tables have about 10,000 rows and 10 columns, while the others usually have around 1,000 rows and 30 columns. The app also includes around 150 virtual columns, 50 relatively simple automations, and has about 50 daily users, with an expectation to scale to 100+ daily users. Additionally, a total of around 1,000 rows are added across all tables daily.

WillowMobileSys

First, as an FYI, from a Sync button perspective...if your 150 virtual columns are expression heavy with SELECT()'s and/or FILTER()'s, it doesn't really matter which database you use. Sync performance will continue to suffer. It might be only a little faster due to database read time being faster. To gain significant Sync performance when there are lots of heavy lifting Virtual columns, if that is one of the things you are after, you will need to re-implement the Virtual Column functionality is another way that doesn't use Virtual Columns. It is very possible but can be tedious.

Using a database, you will see a marked improvement in performance throughout the rest of an app that is the size you describe. So, if users do not use Sync as part of there daily app usage, they won't perceive any slowness issues. For example, I have a data heavy app used by the office to review and correct data entries in the field. It has about 5 times the data you describe and about the same number of tables. It takes over 30 seconds to Sync BUT the office users rarely ever tap Sync - basically only when app updates have been rolled out. To them the app is snappy and they are happy!! (rhyming intended 🙂 )

Now, as for a database...we are using BigQuery. It is intended as a database warehouse but we are finding it is working extremely well with our AppSheet app. The difference between it and other databases is that there is no referential integrity - i.e. it does not manage Primary or Foreign Keys. Since AppSheet manages that, it is not a problem. So basically it's just a speedier spreadsheet. I have found their query inspector very useful in tailoring conditions on Security Filters for best performance.

View solution in original post

scott192

The only data sources with that sort of scalability would in my opinion be Cloud SQL.

Boray

Thank you so much for the response Scott!!

WillowMobileSys

First, as an FYI, from a Sync button perspective...if your 150 virtual columns are expression heavy with SELECT()'s and/or FILTER()'s, it doesn't really matter which database you use. Sync performance will continue to suffer. It might be only a little faster due to database read time being faster. To gain significant Sync performance when there are lots of heavy lifting Virtual columns, if that is one of the things you are after, you will need to re-implement the Virtual Column functionality is another way that doesn't use Virtual Columns. It is very possible but can be tedious.

Using a database, you will see a marked improvement in performance throughout the rest of an app that is the size you describe. So, if users do not use Sync as part of there daily app usage, they won't perceive any slowness issues. For example, I have a data heavy app used by the office to review and correct data entries in the field. It has about 5 times the data you describe and about the same number of tables. It takes over 30 seconds to Sync BUT the office users rarely ever tap Sync - basically only when app updates have been rolled out. To them the app is snappy and they are happy!! (rhyming intended 🙂 )

Now, as for a database...we are using BigQuery. It is intended as a database warehouse but we are finding it is working extremely well with our AppSheet app. The difference between it and other databases is that there is no referential integrity - i.e. it does not manage Primary or Foreign Keys. Since AppSheet manages that, it is not a problem. So basically it's just a speedier spreadsheet. I have found their query inspector very useful in tailoring conditions on Security Filters for best performance.

njordan

@WillowMobileSys can you share your experience with managing app / user licenses while using BQ (or Google Cloud SQL) as a data source? See @Boray 's other post on this topic - https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Using-Enterprise-Plan-App-with-Core-License/m-p...