>> Hi, my name's Joe Nelson.
I write the documentation for Azure Cosmos DB for PostgreSQL,
and for the open-source
Citus Postgres extension the power is distributed query execution.
We're going to be talking about doing queries more easily.
Multi-tenant SaaS apps using Object Relational Mappers.
I guess the first and most natural question is what is an ORM?
Object Relational Mapper allows developers to query
the database using their native programming language
rather than going directly to SQL.
These ORMs available in
pretty much every application framework
but Django and Rails are there on the side.
To give you an idea of what that means,
writing it one way versus writing and another,
first we have a select count star from users.
That's the plain SQL way to count a table of users.
Then we have the way that the Rails Active Record
or I would express it,
you just say user.count.
That can be attractive because it's shorter for people.
In some cases, the queries that are shorter.
Then the second part is,
now we talked about ORMs,
what is a multi-tenant application in query
Azure Cosmos DB for PostgreSQL excels in several use cases,
and one of them is the multi-tenant use case.
What that looks like is generally SaaS applications.
They'll hold data for multiple businesses that we call tenants,
and then it isolates the information from one tenant to another.
We talked about one part, the ORM part.
Let's talk about the other part.
What are multi-tenant queries
and what's a multi-tenant application?
Azure, Cosmos DB for PostgreSQL excels at many use cases,
and one of them is the multi-tenant use case.
What does that look like?
A SaaS app could often hold data for
multiple businesses and we'll call them tenants.
Then the isolate each tenant's information from each other.
You could think of an Etsy or Shopify.
You're hosting store fronts for multiple merchants.
When you're on one person's story,
you don't want to seeing products from
someone else's store like everything needs to be separate.
Then individual tenants will
query for their own data only obviously.
We want to ensure not the date of the others.
You say the queries are filtered to a specific tenant ID,
like a store ID.
When we're writing for this use case,
multi-tenant SaaS use case with
ORM really looking for two big goals.
One is to be safe,
that is easy to automatically scope those queries to tenants.
There's no leaking from one to another,
and that it's fast,
that the generated SQL
when you write those ORM statements it creates SQL code.
The code that it creates should work efficiently
with Azure Cosmos DB for PostgreSQL.
You might think that it's easy,
it's just center SQL,
but there are some things to be aware of that
are specific to being in a distributed SQL database.
In this case, it requires
some special modeling and
query techniques to maximize the query performance.
This form of a database actually uses multiple machines,
nodes, and those nodes work together to process your SQL.
They all will do their part and combine the results to go faster.
But it's sensitive to small changes in the SQL
itself and combine that with and you behind an ORM.
It's generating that SQL for you.
It's difficult to make those customizations sometimes
you're hiding behind that higher level interface.
We need something that bridge the gap.
We've made our two libraries,
so far just two,
one is for Django, Django multi-tenant,
one is for Rails activerecord-multi-tenant.
They allow you to write code in a way that you normally would,
but translate your queries.
The SQL is generated to fit
really well with that distributed data.
But let's check it out or jump right into it.
We're going to look at a simplified schema
for managing stores, products and orders.
I say simplified because in this little dream world,
shoppers can purchase only one product per order.
We do that just that the relationships are simplified because
it's irrelevant to understanding the concept of code.
We're going to jump into the gray nondescript world of code here.
You've got the three tables stores,
products, and orders.
We don't have to look too closely at each of these columns.
This is just a dataset that I
had at hand and it seemed convenient.
But we should take some notes about it.
One is beneath each of the Create Table,
we're running a function called create distributed table.
That's what takes the table,
breaks it into what we call shards,
smaller sets of rows,
and those shards get put onto the individual node.
Each shard is stored in a single node and
the other thing you'll notice is that the uniqueness,
some of these primary keys are
composite because as a property there's too much overhead in
a distributed database to maintain or
enforce uniqueness across machines.
In this case, the uniqueness is enforced at
best per store or per tenant.
Just a detail to be aware of.
If we look at the way you would code this thing,
we saw what's inside the database.
This is just the basics to tell
Active Record what it needs to know about
the data so that we can use
active records form of bringing its own language to do it.
Here we say, look,
we have a store, product or just like before,
they're all active record models and we
declare the way they rely and depend on each other.
It's stores have products and orders or
that orders belong to products.
There is a product where orders,
and then we declare the primary keys,
which you'll notice are slightly different than I said.
But we'll correct that later.
Given we've set,
we could put the data in the database.
We've told Active Record.
Generally these are the models we have to work with.
Let's use Active Record to make some queries.
I thought a nice query would be,
let's find the product that's responsible for
the most expensive order in a particular store.
There's a lot going on in this slide. I'll just break it down.
The first line we have is a command and Ruby for saying,
take the first store that you find and call it S.
It doesn't really matter for this example, which one?
We see the result of that is found store ID1.
Its name is store A, set,
a date that was associated with
it at the time the data was created.
Then we see the SQL that,
that Active Record generated because of that,
the query store dot first.
It's essentially saying give me all the stores,
order them by their ID,
give me the smallest ID first and take one of those.
We've got S and then we then we ask Active Record of this S,
give me the orders,
and then find the one with the highest sales value.
>> It's an unfortunate play in words here.
Saying orders.order, that just means order them,
like sort them by the value and get the most expensive one,
and then find the associated product for that order.
You can see the result of this is a product has product id,
as a name, it's a MacBook Pro laptop.
I guess for in this case there was
an order which had a big batch of laptops,
so it was an expensive order.
As a result of doing this one command an Active Record,
the Active Record is actually
creating two separate SQL statements.
Now with some trickery,
we could probably make this more efficient
and make it generate only one.
But this is still a really good example of how it's generating
queries and the considerations
of how it's doing and what we should do.
First query it's doing is it's getting
all the orders for the current store,
and it's sorting by the most expensive one,
let's grabbing that one.
With that in hand, it has two product id inside of that result,
and it uses that product id to
construct a separate query, a second query,
looking through products saying
which product is the one for that particular id.
Give me all the details about that.
That's how it constructs that final result, the products.
Let's zoom in on this second,
or I should say that last query.
That's where the trouble lies here,
and there's two ways that's problematic.
First, it's not as safe as it could be.
If you look at the query,
I've copied it again here.
Doesn't filter on store id at all.
Now, in this case,
it's okay, it's safe.
It happens to work out,
the order is already chosen from the right store.
This is its product,
presumably also in the right store.
But really, if we code all queries,
always have that protective filter
on the tenant on the store in this case.
Just whatever queries Active Record is going to be sending,
they have a protective filter.
That will just be a nice thing to note.
Then, no matter how fancy my queries are and tricky and big,
they're always going to generate queries that are saved.
But safety is an issue,
and then second problem it's not as fast as it could be.
This is a distributed database thing because it didn't seem bad.
The SQL is just pulling some stuff as it should.
However, what's happening is when we make this query,
we're saying, okay, distribute the database, give us products.
I want products 7.
We did not tell is the specific tenant the store
because the tenant determines
the shard that it's going to be pulling data from,
and the shard is known in the metadata to live in a certain node.
Though, the query planner, given just this,
has no idea where this thing is,
all I can say as well,
we have products and I know there are
32 shards, that's the default.
I'm just going to ask each and every one,
I'm going to spin up a whole new query called a task,
a little fragment query that's
specific to a shard and send out 32,
send them out to all the workers and say,
do you have Product 7?
Thirty one of them are going to come back and say no,
I have no idea, I don't have it.
One of them will be good, and it will combine
that information to give you one.
But it just didn't know where to send it.
We need to provide the query planner more information.
We've seen that using the standard Active Record with
the distributed system like this and
a multi-tenant system is not particularly as safe as it could be,
and it's not even as fast as it could be.
Rather than having you try and mess with the Active Record,
Django we created some libraries for,
now we're focusing on the Active Record Rails 1 right now,
you called it activerecord-multi-tenant.
Here's how it will pull it in to take
our original example and make it work better.
It doesn't really change the length of the code much at all.
We just say bring in activerecord-multi-tenant,
and we also bring in composite primary keys which we didn't write.
It's another gem out there that'll give
this capability because we wanted to match
more closely the way uniqueness works,
and now we can because of this is scoped properly.
But really the keeping is in each of these models,
we added multi-tenant store,
saying this model participates in
a multi-tenant system and its notion of tenancy is the store id.
With that in place,
now we can execute queries inside of a context that's going to
scope anything and everything and Active Record generates any SQL.
What we do is we say multi-tenant.
We started off with a particular store.
As we said, we didn't care which one it was.
We just picked the first store.
We said with this in mind,
now everything is for that store.
Do these statements and
any queries inside of this section are going to be safe.
Not only will they be safe and fast as we saw,
but they actually look a little simpler.
Now I'd just say, go through all the orders,
find the one who's most expensive,
and then find the product for it.
By saying all the orders,
it's understood that it's all the orders just for this store.
I didn't even have to write as much code,
I know works better, kind of win-win.
If we're looking to verify the queries it's making,
just to see how is the safer, what is it doing?
The first query is the same,
describing the stores, pulling that first one out.
Then once again, it's finding the orders for the store,
the order that's most expensive.
But here in the magic,
the last one, it's pulling the product.
Yes, it's saying Product id is seven.
Here, I'm not showing the Number 7,
I'm showing this substitution thing,
but it's searching for a specific product id.
But it also put in a seemingly redundant filter
on store id equals one,
and that's where the protection and the speed
comes from. You don't have to touch it.
That's just the way it makes queries out,
though it's great and it's fast,
and we can see how it's fast by looking,
once again, asking the query planner,
what are you going to do with this query?
I say explain this thing. This is the query.
I know that Active Record generated,
and now the query plan is showing the Task Count is one.
It knows it only has to ask one question and
it knows exactly which record
ascended to. In this case, this worker.
Though it just sends it to one place,
the single machine sends the results back and it's really first.
Now that we've seen that the queries we generated are safe
and fast and easy to use, go get them.
We've created helper libraries for two application frameworks.
Popular ones, Django and Rails Active Record,
and with others possibly down the line.
They are all in the Citus Data github organization,
well, for you to try out.
Thanks for watching and
enjoy the rest of the Azure Cosmos DB Liftoff.
[MUSIC]