We currently have a highly transactional client application built with AppSheet. The app contains many tables and a very large amount of records generated monthly. Over time, we have started facing performance issues due to the volume of data and operations.
We have already applied multiple optimization strategies, including:
Removing unnecessary virtual columns
Simplifying formulas and expressions
Reducing dependencies between tables
Deleting physical columns that are no longer required
Optimizing slices and security filters as much as possible
Even after these optimizations, the application is still becoming increasingly heavy because of the amount of transactional data and files being stored.
We are now considering migrating from Google Sheets to a relational database solution in Google Cloud Platform (such as Cloud SQL or another relational database). However, one of our main concerns is file storage management.
This client uploads a very large amount of files into the app, including:
Photos
Videos
Documents
Currently, the storage usage is extremely high (more than 100 GB previously), and we would like to understand the best architecture for handling this scenario when using a relational database backend.
Our questions are:
When using AppSheet with a relational database, where are uploaded files (images/videos/documents) actually stored?
Is it possible or recommended to connect AppSheet to an external storage service such as Google Cloud Storage instead of storing files under the app owner’s Google Drive account?
What would be the recommended architecture for a highly transactional AppSheet app with heavy file uploads?
How do companies usually manage scalability, storage costs, and ownership of uploaded files in enterprise environments?
Are there best practices for separating database storage from file storage while still keeping AppSheet functionality working correctly?
We would also like to better estimate infrastructure and storage costs before proceeding with the migration.
Any guidance, recommendations, or real-world experiences would be greatly appreciated.
“Optimizing” suggests efficiency of operation, but doesn’t necessarily imply what might be more useful: filtering. Commonly, most users don’t need access to all assets. Use security filters to prevent likely-unneeded assets from being sent to the user’s app. For instance, you might only deliver assets from the past month, or only those referenced by the user’s tickets/accounts/reports/etc. You could also devise a system that allows the user to configure their filters.
You’re on the right track, migrating to Cloud SQL is definitely the right way to go. Once properly setup with indexes and foreign keys that align with your security filters, you’ll notice a significant decrease in sync time.
Answers to your questions
The files are treated and saved the exact same way, the Cloud SQL is only for the tables; the files will be saved into Google Drive like they are right now, AppSheet can mix the two together no problem.
Lot’s of references, tables, with things built out efficiently and appropriately. (Kinda hard to answer this one, as it’s typically bespoke for each build.)
The path of least resistance is to create a dedicated Google account for the app, the data, and all files - everything lives on that app account.
Exception: if you have shared drives, use THOSE for the data (but still use a dedicated account for the app to live on)
Managing Costs: If you use a google drive, you pay for the bucket of space; if you switch to a cloud storage bucket, you pay for what you’re storing. (Cheaper to make use of Google Drive in this regard.)
Already separated
Estimating costs can be difficult, as there are a lot of things that go into the final costs at the end of the day for GCP.
Ultimately, no matter how you look at it, this migration will bring with it a cost increase:
Google Drive / Google Sheets: All have no cost of using
Cloud SQL / Cloud Storage: you pay for the machine RAM and CPU, you pay for traffic, you pay for a lot of little charges that all can add up…
What’s worse: if you don’t setup the SQL instance correctly, and allow GCP to set defaults, it sets defaults that are WAY over provisioned for you and will cost you way more than you need.
I had a cloud project that was costing me $20/day, because I just moved forward with the default settings; I was able to get that down to $1.50/day - same level of responsiveness/functionality/stability/capabilities/etc.
But ultimately with this migration comes an increase in cost. It will be low at first, but as you increase your usage of the GCP, it will increase over time as well - something that needs to be actively monitored and adjusted over time.
All of that to say: this is definitely the way to go. Your app will become MUCH more responsive, the capabilities of what you can do will expand, and you’ll be able to scale to just about any size needed.
An app setup like this will run for years on end, even if you have hundreds of thousands of records in the tables.
We completely understand your point regarding filtering, and we actually already implemented security filters for each user profile so users only receive the data they truly need and not the entire dataset.
Additionally, due to the high transactional volume of the client, we also implemented periodic data partitioning every 3 months to reduce the load and improve performance. However, despite these optimizations, the app still eventually becomes overloaded because of the continuous growth of transactions and uploaded files.
We even considered reducing the partitioning period to every 2 months, but recently we have noticed performance issues appearing after only around 1.5 months of operation. Because of that, partitioning every 2 months likely would not be sufficient either.
At the same time, performing monthly partitioning would become operationally difficult, especially because we would constantly need to coordinate with the client to gain access to the account where the data is stored, reconnect new spreadsheets, and maintain the entire structure repeatedly.
For that reason, we are now evaluating the possibility of migrating to a relational database architecture as a more scalable long-term solution.
Thank you again for your contribution and recommendations, Steve. We really appreciate it.
Thank you very much for your detailed contribution and for answering many of our doubts.
We can clearly see that, when migrating to Cloud SQL / GCP, proper configuration of the infrastructure and datasource setup becomes extremely important in order to avoid unnecessary costs and keep them as optimized as possible.
In our case, the storage costs are assumed by the client, so the increase itself is not necessarily the main concern. However, having a well-configured environment is definitely important to avoid unexpectedly high costs that could alarm the client.
Currently, the client already has a dedicated Google Drive account used exclusively for the application storage. The AppSheet application itself is hosted under our master account, but the client gives us temporary access to connect the Google Sheets stored in their own Drive account, so they manage the storage ownership directly on their side.
Based on your recommendation to continue using Google Drive for file storage even after migrating the database to GCP/Cloud SQL, we wanted to ask:
If we migrate the tables to a relational database in GCP, is it possible to keep the client’s Google Drive connected so that all new uploaded files continue being stored there normally?
Additionally, regarding the migration itself:
How would the existing files already linked to their corresponding records behave after migrating the datasource to Cloud SQL? Would AppSheet continue displaying them normally as long as the file paths remain unchanged, or is there an additional migration/relinking process typically required for previously uploaded files?
Thank you again for sharing your experience. Your explanation was extremely helpful for us.
PS: I’ll also take a look at the apprenticeship program you mentioned. It definitely sounds interesting.
Once you make the switch from google sheets to anything else, the automatic system AppSheet uses to determine where to save files will no longer work as assumed.
With a google sheet, it’s working to put files where the sheet lives (the same folder)
Once you make this change, you’ll need to make sure the default app folder is correctly set to the folder you want to save files into. This folder needs to be either a shared drive folder (Work with a shared drive - AppSheet Help), or if it’s a folder in some other Google Drive account that’s shared with you… you’d need add a shortcut to that folder in your drive (so it’s “visible” to AppSheet).
As long as the default app folder is set correctly, everything will continue to work as is - images will display so you can see them, Files will open when clicked.
