Server Side Database Partitioning

Grant_Stead · 07-16-2019 08:55 AM

@praveen How can I leverage server side partitioning and row level security within an Appsheet app environment? I’m assuming some sort of information about the logged in user will be used by Appsheet to choose what DB partitions to interact with?

I’ve been working towards using Appsheet database partitioning, one partition for each of my clients. This will keep their information separate from other clients. However, I am planning on using Oracle DB in Oracle Cloud, and was talking with @Stefan_Quartemont, and he said that we might be able to keep the partitioning server side which would make the Appsheet apps more simplistic.

Another piece of information to consider. I’ll have admin app users signing-up new users into AWS Cognito through a user table via appsheet webhook and AWS Lambda. When a user is created in the table, the key is CONCATENATE([DB_Partition_ID], “-”, UNIQUEID()), the email is also captured. These values are pushed to AWS Cognito as the USERNAME() and USEREMAIL(). So, I’ll have the users email to work with along with their user key and db partition id.

https://docs.oracle.com/cd/B19306_01/server.102/b14220/partconc.htm

Harry2

@Grant_Stead Hi Grant, have you had the chance to check out the article on partitioning (link below)?

For databases, each partition will be a table or view in your SQL database. In AppSheet, this means that you need to check the option “Partitioned across many files/sources”. You will need to assign each partition a name or tag. Once this is done, you will need to come up with a partitioning expression. The output of the expression should be one of the partition names that you have assigned. For example, if the partition expression evaluates to “partition2”, then the table associated with the label “partition2” will be used as the data source for your table in the app.

How you want to create the partition expression is entirely up to you. You can assign certain users to particular partitions based on their email addresses, or you can assign users based on their role.

Grant_Stead

Yes, I understand data partitioning well. So, what you’re saying is that appsheet doesn’t utilize any of the petitioning logic built into the database.

Harry2

No the partitioning mechanism in AppSheet does not leverage any built-in partitioning mechanism in SQL databases. However, you can always implement partitioning in the database yourself independently from partitioning in AppSheet.

Grant_Stead

Thanks Harry!

pravse

@Grant_Stead database partitioning is a physical storage artifact used to make queries run fast (in parallel) for large scalable databases. Conceptually, the database has a single table but internally it may be stored across many partitions. I’d be very surprised if you need it — as a frame of reference, none of AppSheet’s internal databases (with more than a million registered users) needs database partitioning.

The partitioning we do in AppSheet is for a different purpose — it is meant to minimize the data fetched to a device. You can also achieve this via security filters, but for stores that do not support efficient queries (like a spreadsheet), our partitioning scheme provides a good alternative.

You would rarely need to use both. At the data scales I see in our customer apps, the only reason to use data partitioning in a relational database would be to separate data management for each partition. For that, I wouldn’t use the relational database partitioning — instead I’d suggest a separate table or view per logical partition.

Grant_Stead

@praveen I was planning on having multiple tables, one table for each client… Like Citgo, Exxon, Valero, etc… That way when they’re using the applications they have peace of mind that their data is physical separate. Then when they log into the applications I’ll be showing them their tables based on their partition. The workflow data change and API things are proving difficult with this… I think I have to create a workflow rule for each partition?

Bellave_Jayaram

@Harry, is there a sample app that implements data partitioning that users can look under the hood?

Harry2

@Bellave_Jayaram Here’s one sample app that demonstrates partitioning.

https://www.appsheet.com/samples/Example-of-partitioning-across-worksheets?appGuidString=40c9e4b7-42...

pravse

Grant, workflow rules should work seamlessly with partitions. You do not need to create a separate workflow rule per partition. It should pick up the appropriate partition for the user who made the update triggering the workflow rule. If you aren’t seeing this behavior, it is a bug and we should fix it.

You can definitely keep separate tables per client for their management. FWIW, there is little extra peace of mind in having separate tables in the same database – vs rows in the same table and then views for each client. If you had separate databases for each client, that would provide some physical isolation, but you’d be duplicating the tables that are shared (also not good). That’s why our partitioning model doesn’t try to support this case.

Grant_Stead

I sent an API workflow to another app that was set-up with the same partitioning schema, and it always dumped the record into the “root” table. So, API add records doesn’t work with partitioning.

The “views for each client” you mean SQL Views which would be referenced in Appsheet as distinct tables? Is it faster, with an Oracle DB, to have all one table and use an Appsheet security filter, or keep them as separate tables/views and utilize the Appsheet partitioning mechanism?

pravse

Partitioning is more static — a row lives in one partition or another.

If you put all the rows in the same table, then you can have some users see a subset of the data, but other users see all the data. So in general, I’d recommend security filters with all the data in the same underlying table.

Wrt API workflow with partitioning, could you please send the details to support@appsheet.com — seems like a bug to me.