Scaling using Data Partitions

An AppSheet app defines a table as having one specific table source (usually a spreadsheet file with an optional worksheet name, but it could also be a database table or other table source).

When using the Data Partition feature,

  1. You partition (i.e. divide) the rows of the table into many table sources
  2. You register these different table sources in the app's table definition via the app editor
  3. You provide a partition expression that lets the system determine which table source to use for which user

For example, if you have a table of timesheet entries for employees and there are 10,000 employees, you can partition this data into 10 separate sheets or partitions, each holding 1000 employees. The partition expression will identify a specific partition for each user based on the USEREMAIL() or USERSETTINGS() values.

Data Partitions are a powerful mechanism to scale your app while keeping your data in the format of familiar spreadsheet systems like Google Sheets, Excel, or Smartsheet. However, because you have divided your data set into several physical sheets, you may create extra management overhead for data maintenance and reporting.

It is important that all the partitions have identical column structure! The system cannot guarantee this behavior so this becomes an additional responsibility for the app creator.

Data partitioning: multiple worksheets

If you are using Google Sheets and have enabled the Bulk Read option (via the Behavior -> Offline Settings pane), the system can efficiently read a single worksheet from the overall workbook/spreadsheet. Therefore, when considering data partitioning for scale, the easiest initial step is to partition the data into several worksheets in the same workbook/spreadsheet. This is an option in the Table definition in the app editor.

WorksheetPartitioning.PNG

Data partitioning: multiple spreadsheets

Whatever the data source, you can partition your table into several spreadsheets. This is an option in the Table definition in the app editor.

SheetPartitioning.PNG

Data partitioning: maximal scale

The different data partitioning mechanisms can be combined to achieve quadratic scale if necessary. The data can be partitioned across several spreadsheet workbooks and each can be partitioned across several worksheets. It is important that each of the workbooks has identically named worksheets. A security filter may further reduce the data sent to the mobile device. This combination will maximize the scale of the app.

As long as each user of the app needs only a reasonably small subset of the overall table, the underlying data can scale almost infinitely in this fashion. All the while. the app is still implemented on spreadsheets. Users on the Smartsheet platform can easily scale past the 5000 row limit imposed by Smartsheet. Users on the Google Sheets platform can easily scale past the 2 million cell limit imposed by Google.

Data partitioning: other scenarios

While data partitioning is primarily a feature to improve performance at scale, it can also be a convenient mechanism to handle deployment scale in a relatively common scenario. It is sometimes the case that data sets needs to be maintained in separate physical containers (database tables or spreadsheet files) for reasons other than performance. For example, if an app is used by five different clients, it might be important to keep each client's data in a separate sheet. However, if you want to build a single app that is used by all five clients, data partitions provide a convenient solution. You can build, maintain and distribute a single app, yet each client sees just their own data and the data sets for ech client are all kept physically distinct.

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.