[MUSIC].
>> Hi everyone. My name is Avijit Gupta,
and I'm the Senior Product Manager on
Azure Cosmos DB for PostgreSQL security.
In the session today, we are going to talk about building,
efficiently the architecture for multi-tenant SaaS apps on Azure,
Cosmos DB for PostgreSQL.
We'll be starting with looking at
some key features implementations
and then try to understand them to demo.
Let's get started.
So this is an excerpt from Wikipedia that
multi-tenancy is defined as
a software architecture where a pool of
resources are used to serve multiple tenants.
Here, a tenant is a group of users
differentiated through the privileges over the resources.
At a high level, multi-tenancy involves
three broader type of implementations with two-key objectives.
Objective 1 is building for isolation.
This requires a physically isolated environment or tenant.
This can be achieved through separating tenant per
database or an input schema model.
The pattern is more commonly adopted
in healthcare or banking industry.
Objective 2 is building for scale.
This requires utilizing common table
to host data for all the tenants.
Benefit with this approach is,
it avoids a lot of
operational overhead of individually handling every tenant.
Core interesting reason to go with either of
these options involves
compliance requirement of different tenants,
number of tenants on the platform,
performance SLAs that we guarantee on
our platform and the
customization that we want to permit on our platform.
Let me introduce Azure Cosmos DB for PostgreSQL,
which is a natural fit for a multi-tenant uses.
It is a distributed database with some good feature
addressing major issues raised by our multi-tenancy and customers.
Issue number 1 is performance bottleneck.
As the data size grows spontaneously,
we observe the data or index doesn't fit into the memory,
which leads to decline in the performance of our queries.
As your Cosmos DB for PostgreSQL
provides the in-built option of shutting the data,
which allows preserving indexes and memory,
and edit the data,
co-locate the related data for individual tenants.
Issue number 2 is scalability.
As the customer onward more and more tenants to the platform,
their naturally worry is if
a single node relational database would
be able to solve for the required scheme.
As your Cosmos db for Postgres,
natively support for scale-out
functionality with absolutely no downtime.
It also enables in isolating the tenant if the need arises.
Issue number 3 is tenant management.
As the hip, the issue of performance and scaling,
the need arises for moving the tenant to a new node
and write an application logic for routing the queries.
Effectively, this is reinventing the wheel of sharding
as your Cosmos DB for Postgres provides support for auto sharding,
and it takes care of routing the queries.
Additionally, we also get to observe the query patterns
per tenant through the inbuilt monitoring views.
Let us observe the key features that we
talked about with a simplified example scheme.
Here is a simplified B2B ICA electronic e-commerce store.
We are registering different stores as tenants in
the tenants table and
their corresponding chain of inventory in the product stable.
When all the sales on the platform
will be captured in all other state.
The important thing here is that we are sharding
all these three tables on a common key.
Let us understand mode or non the impact on
data placement due to the choice of a common key.
On the current slide, upper-half represents the view to
the application while lower half
represents the internal resistance of the tenant status.
In the current example,
we have two worker nodes existing in
the cluster and each of these nodes contain two tenants.
Important thing to observe here
is that we are keeping the related tenant data
across the tables persisted together on the same node,
which helps in avoiding cross-node queries and brings
efficiency in terms of utilization of
resources as well as in terms of performance.
Let us understand this tour demo.
For the demo purpose, we have already
created these three tables, tenant,
product and orders table on the database
and we have populated some data taken from Kaggle.
Let us first review the cluster.
We can see there is one coordinator existing in
this cluster with two worker nodes and should have
shard flag tells us that
the actual tenants data will lie on these two worker nodes.
Let us verify if there is any table that is already distributed.
Currently, we do not see any table distributed on the Database.
Azure Cosmos DB for Postgres SQL provides
the inbuilt function of sharding the data-set.
The function takes four parameters.
Table name, sharding key,
shard count and co-locate with.
Shard count and co-locate with are
the optional parameters wherein
shard count has a default value of 32 and
co-locate with is required only when we are explicitly
willing to co-locate the data of
current table with an already existing table.
Let's try to distribute these three tables.
The distribution has completed.
Let's run this query again.
Now we can see that orders, products,
and tenant table all have been
distributed and they are distributed on
a common key and they have
equal number of shard count, data co-location ID.
Co-location ID effectively could be
different for another set of tables
from the structure which
are meant to serve a different purpose on the platform.
Since they have already distributed this data-set,
let us now review how does the data placement look like?
In this case, we are looking for data tenant three
and we can observe that tenant product and orders,
all these three tables are actually
co-located on a single worker node,
which is worker node one.
Let us see this again for another tenant.
>> For Tenant 2, we can see the actual data for
all these three tables are located on worker node zero.
Till now, we were able to shard the data using inbuilt function,
and the system is smart enough to distribute
this newly created shards evenly.
It also colocates this data for efficiency reasons.
Now let us review some common query patterns
for Multi-tenant SaaS apps and
understand how does the data placement
help us with achieving this efficiency.
The Scenario 1 is where we are typically doing an update,
or delete for data from an existing dataset.
In this example, we are doing an update of orders table for
a quantity attribute where
there is an order id value we have picked.
Let's try to look at the execution plan.
We can see that we have three tasks generated.
In the previous step,
we actually created three shards for the order still,
and now you see there are
three tasks generated by running this query.
In the execution plan,
we can see that we are hitting worker node one, worker node zero.
There are two shards located on worker node zero,
and there is one shard located on worker node one.
We can see that we are hitting all these three shards.
Effectively when we look at the rows
being hit under these shards,
we see zero records being touched on those nodes.
Well, there is one rule touched on worker node zero,
which is where we see that
the shard that actually holds the data is 10 orders 102052,
while the other one also doesn't have any data modified.
Now, let us try to add a filter
on tenant id and really view this execution plan.
After running the same query again with the tenant id filter,
we observe that there is just one task
created which hips the actual shard,
which contains the real data.
Effectively we are scoping the query to just access the shard,
which actually contains the data instead of
primarily accessing all the shards that exist for the table.
Now let us look at another example where
we are going to run through a join and a group by query together.
The example, we are trying to get sales of different products for
a given tenant in a given month, so in this example,
we see that we are looking at June months data,
for tenant one,
and trying to pick the product for which we
want to see the number of orders placed during the month,
average units sold per order.
Then we want to also look at
the total revenue generated with the sale.
On running this query, we can actually
see that there is just one task created,
and this task actually hits
just one worker node, worker node zero.
Effectively, we can see in the execution plan that there is orders
102050 and there is products table 102047,
both of these tables are co-located on
single node and effectively
doesn't require us to do a cross-node query,
so effectively we are able to get
the output from running a query on a single node.
We can see that by shutting the data,
we are querying a smaller dataset,
and further by scoping the queries for different query patterns
we could add efficiency with
the utilization of available resources.
Let us get back to our PPT.
This is our second feature,
which is tenant isolation.
The left being here shows the before state of our data placement,
and the right side, right half shows
that data placement post doing the tenant isolation.
In the current scenario,
we have two worker nodes,
two tenants each,
and we are trying to isolate tenant Id for which is
effectively the store for data to a new node.
This will be achieved in three steps.
Number 1 will be, I think a new worker node to the cluster,
and then Step 2 will be shard
three contains data for two different stores,
which is tenant id two and tenant id four.
Effectively, we need to isolate tenant id four to its own shard.
Step 2 will involve isolating the tenant four to its own shard,
and then Step 3,
will actually move the data for
the newly created shard
from worker node two to the newly created worker node three.
Let us review this through them.
Now as part of Demo-2,
you have automatically go to the scale out step,
and you can actually see a new node object to the cluster.
We can see divert W2 is a new node available undisclosed term.
Now let us look at the data for Tenant 4 and Tenant 1.
By running this query, I can see that tenant table for
tenant id one and tenant id four is
actually located together into the same shard,
tenant underscore 102044,
and it has located on private w0 node,
which is our worker nodes zero.
Step 1 will involve isolating the tenant to a news chart.
Let's run this query and move Tenant 4 into its own shard.
Post executing the isolation of tenant function,
we are able to see a new shard created for tenant id four.
Now let us review the tenant id and the shard id key value.
We need to key in the shard id value
here and provide the source server name
that currently it is located provide
the port number and then provide the destination server
wherever you want to post test shard too,
and then the destination port number.
Let us run them step-by-step.
One of them has moved to a new node.
Let's turn the other two as well.
Let us now review the current location for tenant id four.
We can now see that tenant id four has been
moved to a new node added to the cluster,
which is worker node two.
To summarize, as we discussed in the previous slide as well,
there were three steps involved with tenant isolation.
Number 1 was adding a new node to the cluster.
Number 2 was isolating the tenant into its own shard,
and Number 3 is having
the shard from the source node to destination node.
This completes our Demo-2.
Let us take a look at Demo- 3.
As part of Demo-3,
we want to look at the system available
views as Cosmos DB for PostgreSQL chlorides,
and how does it provide some information about the query patterns?
Here I have exhibited
cooker query against hydrostatic statements,
and we can see the different queries
being executed by four different tenants,
and how many numbers of execution has
happened for each of these tenants.
We are still working on
improvising and adding more features to this.
But this is the current state
of information that is currently available under platform.
Let us summarize what we discussed in our current session.
We learned about capabilities offered by Azure,
Cosmos DB for PostgreSQL in the garden session.
We looked at data sharding and
how it helps in co-locating the data,
which further helps in supervising the performance.
We also looked at some of the SaaS query patterns and
learned scoping of queries for multi-tenant use cases,
which allowed us to use
the cluster resources more smartly and efficiently.
We also experienced scaling out the cluster and then
perform tenant isolation to the newly added node.
Effectively both of the scale-out operation and tenant isolation,
authentically online operations and will
not effect our reads to be affected.
Finally, we looked at the internal view and then monitoring view,
which gives some insight about
the different queries being triggered by different tenants.
Scan this QR code,
refer to this link for learning more about
hosting mechanic applications on Azure,
Cosmos DB for PostgreSQL. Happy learning.
[MUSIC]