Scale using security filters

A security filter is an expression that is evaluated on each row in a table and returns true or false. If true, the row is included in the app and if false, the row is not included. While security filters are primarily meant to be a security feature, they can also be used as a scalability feature. 

Security filters usually compare data from the row with some user-specific data (such as, the user's email or some user setting value). For example, the app user's email address might be used to retrieve only the records that have that email value in a specific column.

Security filters with spreadsheet data

When reading data from a spreadsheet source, the entire spreadsheet or worksheet is retrieved from the cloud backend before the security filter is applied. Therefore, this does not reduce the time needed for this initial step of the sync process. However, it can significantly reduce the time needed for the second step (sending the data to the app) and can significantly reduce the time and space needed for the third step (saving the data locally with the app).

Security filters with database data

When security filters are used with database sources, there is the potential for the system to convert the security filter into a database query. To explain, if the underlying database table has a million records but the security filter limits the table to those where the value of the Email column is, there are two ways this can be executed:

  • Fetch all the million rows from the database to the AppSheet backend, evaluate the filter and retain just the ten records that match, or
  • Send the database a query: SELECT * from MyTable where Email
    The database would return just the ten records that match. 

Obviously, the second approach is much more efficient. Database systems have techniques like indexing that can run such queries quickly. AppSheet can scale to handle millions of database records, provided the security filters can be mapped to efficient queries.

AppSheet attempts to "push" the security filter into the SQL query for the following database providers:

  • MySql
  • Microsoft SQL Server
  • MariaDB
  • Oracle
  • Postgres
  • Redshift

Efficient security filter definitions for database data

The best way to ensure that a security filter is efficient is if it reduces to a simple comparison of a column to a value, or if it involves an AND() with two or more such comparisons. More complex conditions are almost certain to lead to inefficiency as the data set scales.

Here is the list of simple conditions that AppSheet does recognize and push into the SQL query:

  • [Column] = <constant value>
  • [Column] < <constant value>
  • [Column] > <constant value>
  • IN([Column], {<constant value 1>, <constant value 2>, ... <constant value N>})
  • AND(<simple condition 1>, <simple condition 2>, ... <simple condition N>)

Almost all other conditions are not pushed into the SQL queries. The platform does make an effort to push partial conditions. For example:

  • AND([Column1] = 5, LEN(CONCATENATE([Column2], " file")) < 20) - The second condition is not one of the simple conditions, but the first is, so the first alone will be pushed into the SQL query.

It is important to understand that often, the expression itself may be more complex, but what matters is that it can be reduced to a simple form just before execution. For example:

  • OR([Column] = 5, USERROLE() = "Admin") - At runtime, we know the user role. If the user is an admin, this expression either reduces to TRUE (which means "fetch all the rows"). If the user is not an admin, this expression reduces to [Column] = 5, which can be pushed to the database.

  • IN([Column], SELECT(AnotherTable[SomeColumn], [EmailColumn] = USEREMAIL())) - The entire SELECT() function evaluates to a constant list at runtime before this security filter has to be run. Therefore, the whole expression reduces to IN([Column], <list of values>) which can be pushed into the SQL query.

In general, for efficiency, avoid complex OR() expressions in the security filter. If there are complex expressions, they should avoid using columns of the table that is being fetched.

Was this helpful?
How can we improve it?
Clear search
Close search
Google apps
Main menu
Search Help Center