Scaling AppSheet with SQL: Questions on Record Limits, Offline Sync, and Data Loading

AppSheet AppSheet Q&A
,
1

Hi everyone!

I’m currently evaluating a backend migration to a SQL database on Google Cloud Platform (GCP) to handle larger datasets. I’m looking for community insights or official documentation on managing high-volume data (500k+ rows) within AppSheet.

I’d love to hear your experiences regarding:

  • Practical Record Limits: Are there real-world limits when handling millions of rows via SQL?

  • Data Loading Efficiency: Does AppSheet use pagination or partial loading for massive tables, or does it attempt to load everything to the device?

  • Offline Mode & Sync: How does the app manage synchronization for very large tables in offline mode? Are there strategies to avoid storage/memory issues on mobile?

  • Architecture Best Practices: What are your top recommendations for designing apps that need to scale long-term without sacrificing performance?

Any advice on Security Filters, indexing, or specific GCP configurations would be incredibly helpful. Thanks in advance for the support!

2 Likes
2

There are no practical limits but you need to control the data amount and column types so the data size won’t go too high to download. For this you need to filter the data either directly in the database (using SQL views) or using security filters that are converted to SQL query. Then the data is filtered directly in the database before it’s fetched to AppSheet server.

If the expression in the security filter is not possible to convert to SQL query, it’s possible that first the data is filtered in the database and then later in AppSheet server. But the goal is to fetch as little data as possible to AppSheet server. This filtering mechanism takes care that the AppServer pushes as little data as possible to client - which makes better sync and app performance.

AppSheet is built to work as offline.. because after the sync is completed all the data (after security filter) exist in the client side.

When you are building the app, you can find out from the Performance analyzer how the security filter is converted or is it converted at all to SQL query. The property name is “Data filter:”

Another good way to analyze the row count and data size after the sync, is to use this script in Chrome > More tools > Developer tool > Console:

let tableData = Object.values(AppModel.Tables)
.filter(t => !t.IsSlice)
.map(t => {
let sizeMB = JSON.stringify(t.Rows).length / 1024 / 1024;
return {
“Table Name”: t.Name,
“Rows”: t.Rows.length,
“Est. Size (MB)”: parseFloat(sizeMB.toFixed(2))
};
})
.sort((a, b) => b[“Est. Size (MB)”] - a[“Est. Size (MB)”]);

console.table(tableData);

let totalMB = tableData.reduce((sum, t) => sum + t[“Est. Size (MB)”], 0);

console.log(
%cTotal Data Footprint: ${totalMB.toFixed(2)} MB,
“font-weight: bold; color: #1a73e8; font-size: 12px;”
);

It gives row count and data size per table.

6 Likes
3

A few extra tidbits…

No pagination or partial loading but AppSheet does provide a mechanism for scaling large apps. You might want to check out Approaches to data scalability

The most important thing for efficient databases is data structure. You want the data to be as condensed as possible which usually means a good structure with table relationships. For example, if you have a large table where half of the rows have half of the columns blank, downloading still needs to download those blank columns. It is a very good sign you can split that table to condense it.

  1. Only download the data THAT user needs. Limiting the downloaded data makes the BIGGEST impact on Sync times and app performance!!

For instance, if you have a field worker who mainly submits data, they typically do not need to see history of entries. Create a “field app” that tailors the Security Filter to them - maybe only downloads the last week of entries.

An office person may be reviewing data and preparing it for payroll or monthly reporting. Create an “office app” that filter the data to, say, 6 weeks.

  1. Use the Performance Profile - especially when building - to keep performance good. Review Virtual Columns which are the biggest culprit (see 3 below).

  2. Avoid Virtual Columns that use Table Select expressions BUT do not change often. Instead use a table column with an App Formula and MANUALLY control, with actions, when that column updates. This sometimes means you need “update” actions placed in several places around the app and does complicate implementation a little but keeps performance good.

Not all expressions in a Security Filter will automatically translate to an SQL SELECT statement to download data. When they do not, AppSheet will split the Security Filter into 2 parts…one that filters as much on the database side as possible and then a second filter pass is made in the AppSheet servers. You can sometimes adjust your app so the Security Filter is more performant. The more that can be done on the database side the faster the download will be.

Get familiar with how to find and read the SELECT statements used by AppSheet to query the database. You can analyze that against the app Security Filter and make app adjustments to push more of the Security Filter criteria into the database SELECT query.

In BigQuery, as an example, an IN() function is not represented in the SELECT statement at all. An IN() function is just a series of OR conditions. If the source list in the IN() function is short, converting it into a set of OR’s might be worth the time saved in the database query.

An example of this, I had a work item that several people might be involved with and wanted that work item to show ONLY for those who are working in it. I collected all the people from all the child tables into a single list named “Worked By” and then used an IN() function in the Security Filter. This did NOT make it to the database query but filtered A LOT of rows.

I changed the “Worked By” list into a series of columns = “Worked By 1”, “Worked By 2” and “Worked By 3”. Then changed the Security Filter to a series of OR’s. This change was part of the database side query and made a huge difference in the download Sync time.

I hope this helps!

5 Likes
4

Thank you so much for the detailed explanation and the technical insights!

The clarification on how Security Filters and SQL Views act as the primary gatekeepers for data fetching is incredibly helpful. Knowing that the goal is to offload the filtering to the database before it even reaches the AppSheet server gives me a clear path for my architecture.

I especially appreciate the Chrome Console script you shared—that is a game-changer for auditing the data footprint on the client side. I will definitely use that along with the Performance Analyzer to monitor the “Data filter” property and ensure my expressions are being converted to native SQL.

One quick follow-up for the group: When using SQL Views to pre-filter data, have you found any specific limitations regarding Read/Write capabilities back to the source table, or do you generally recommend keeping the “heavy” tables as Read-Only and using smaller “Form” tables for data entry?

3 Likes
5

Thank you for these incredibly valuable “extra tidbits”!

Quick question on your point about Virtual Columns:
When you suggest replacing them with physical columns and “manually controlling” updates via Actions, do you typically trigger these via Form Saved actions or do you use Automation (Bots) to keep those values synced across the table?

2 Likes
6

There isn’t. In generally speaking.. don’t bring tables more than once. Otherwise you have the same data twice to be downloaded. Though it doesn’t matter if tables are small.

FYI.. most of the AppSheet expressions are not converted to query, but AppSheet is able to generate a parameter list but there is a parameter limit 4000 which is coming from Microsoft. AppSheet is limiting this list again to 2000. When row amount is small, it doesn’t matter but in your case where you have lot of data, it can give wrong results.

Another thing.. if you have files, images etc. columns, don’t use base64. It will increase the sync performance and a lot.

4 Likes
7

It depends. I prefer to use actions on Form Save when the data needs to be immediately seen by the user. This is the majority of the time for the apps I am currently building. Otherwise, it’s a toss up. I may choose actions just to maintain consistency. I’ll choose Bots if it seems more appropriate for the circumstance.

2 Likes
8

Qué tal, @sricse!

​Mira, yo pasé por algo parecido hace poco y con 500k filas el truco no es solo la base de datos, sino cómo haces que AppSheet la lea. Aquí te paso lo que me funcionó para que la app no se te cuelgue:

  1. ​Filtros de Seguridad (Security Filters): Esto es lo más importante. Tienes que configurarlos de modo que la app solo jale lo que el usuario necesita en ese momento. Si el filtro es muy complejo, AppSheet intenta bajar toda la tabla para procesarla y ahí es donde se traba. Trata de que el filtro sea simple para que el trabajo pesado lo haga Google Cloud SQL y no el celular.

  2. ​Usa ‘Vistas’ en SQL: No conectes AppSheet a la tabla grandota directamente. Mejor crea una ‘Vista’ en tu base de datos con solo las columnas y filas que vas a usar. Es mucho más rápido.

  3. ​Cuidado con la memoria: Con tantas filas, el problema suele ser la RAM del teléfono. Si puedes, usa ‘Slices’ para que la app solo cargue lo más reciente.

  4. ​Tip extra: En la consola de GCP, activa el ‘Cloud SQL Insights’. Te dice exactamente qué consultas están lentas para que les pongas un índice y vuelen.

​¡Suerte con la migración, cualquier cosa me avisas!"

1 Like
9

Slices have no effect on what data the app loads, only security filters do.

2 Likes