A columnstore index
in Azure SQL Database provides
a very high level of data compression, typically up to 10x.
This can both reduce the storage cost and improve the performance of your database.
And Davide Mauri is here to show us how it works.
Today on Azure Friday.
Welcome back, Davide!
It's great to have you back on Azure Friday talking about columnstore indexes.
So tell me, what are these indexes and why do they matter?
Yeah. So these are one of my favorite topic.
Well, I guess all of my favorite topics and I'm just kidding you
know, so let me talk about the columns because these are really amazing.
So first of all, I think personally that as you see,
it's the only cloud database that allows to have a both row
store and column store index on the same table at the same time.
Now, you may be wondering, well, what's what's the big deal here?
Because it's just indexes, right?
Yeah, that's true.
But let me show a little bit why so important?
How different is indexes?
Are I prepared a couple of slide because this could be a little bit more
technical topic than the usual.
So let me switch to the slide a second.
So here we go.
So let's say these are a table, right?
So we are storing a name, surname, email and whatever,
a usual process or index is an index that take basically
all the data you have in your role in this story together.
So if you let's say you
if you are looking for an ID equal, one, all the other values David Mora
in, you know, e-mail, whatever are close to each other, right.
Are physically close to each other.
And this is perfect.
The when you want to given the ID
or the email, you want to get all the other values.
So again, it's perfect for what we usually say.
It's a single tone
searcher or,
you know, accessing the entire role of the data.
But what happened instead, if instead of being interested
in, you know, all the other data in the same role,
you just want to say, oh, I want to get a data
and see how many David or Ada or Mike that are in my system
while reading also this sort of name and email is basically
just wasting a little bit of resources
because you want to count the name, not the surname.
So it would be better if we could store the data by column instead of by it all.
So as you can see, a cluster take all the value highlighted by the green box,
so all the names and store them together,
which means that every time you want to access all the names,
like counting them, well, they are physically close to each other
and you don't have to read the ID or the email or any other call.
So that's to reduce some of the I oh, it's going to reduce the memory.
L Great.
Every day if we can optimize
the read of the entire courtroom
in an amazing way, we are even using
some special instructions that are in the CPU that are used to do
vector computation. So, you know, for 3D games.
So basically the same computation here
and this make the performance go so fast, that is incredible.
And the beauty of sequel and SQL Server, but specifically, as you'll see
what happens in this case is that they can exist in the same table.
So many system allows you to have all the row store
and then you have to export the data into a column store database
and and of course that is also good too.
But then you have two database, two different system
that you have to maintain, which is your sequel.
Everything is just one place and you don't even have to, you know,
maintain the index in the sense
that you don't have to worry about the data being out of sync
because everything is on the same table and we do all the
work behind the scene for you.
So by adding those two different type of indexes to,
let's say now the put aside for a second
the technical aspect for our developer, what are the important things to remember?
Well, a store is great for singles.
Don't look up or when you want to scan data in an order
like a give me, you know all the data for all the people with a certain surname
and that's for photo, for example, when you know you have to retrieve
a shopping cart, for example, because you want to have all the data
for the shopping cart returned and said the column store is perfect for one
you wouldn't want you want to do like reporting or analytics
like what is the average size of my shopping cart, right?
So we can really scan the data on the column very, very quickly and very fastly.
If you're interested in a little bit of detail behind the scene.
We do quite a lot of interesting stuff here.
So we take all the table, all the rows that we need to order.
We split it in row groups to avoid it.
We have a huge amount of memory to be allocated, so we work in
groups of rows and each row group is split in column segmenter.
So basically all the column is, you know, managed separately
and each column is then compressed using various stage
of compression and very different techniques of compression.
So for example, you can imagine in a, let's say a billion row table
where you store cities of the world
that are not 1 billion cities in the world. Right.
So probably names would be repeating very frequently.
And we can compress the repeating names
amazingly like we used 10%.
There is probably a good indication, but we can even go more than that.
So depending on how compressible is the data
in an amazing thing we did in the last year is that usually
you can imagine that all this compression make read the very faster.
But writing data into columns
is pretty complex because it's not like you just saved over.
I get to go through all the steps in probably
now we slow down at some point your performance white
when when you have to read or sorry when you have to write or change a data.
So we also created something called a Delta store
that has nothing to do with what Apache did for data breach.
So I would say the principles are similar.
We did this in 2012, if I remember correctly, so long ago
and this just happened.
The the name is the same by the principle of the logical principle are similar.
So we allow you to update the column store in the sense that we allow you
to write into the column store. But we do a little trick
every time you write, we actually write the entire row.
And when the number of rows that you have written is big enough,
we convert it into our row
group and column, column, segment, and we merge it into the column store.
So basically we, we remove all the complexity for you
to whatever updatable column store and that's it.
This is kind of the magic that we do.
You still use something
that is very complex to manage, but it's very simple to user in
that can really make a huge difference in your performances.
And so the advantage for us as developers
by having both the column store and the Rows store index is
we don't have to think about how to optimize for performance
based on certain query patterns and often takes care of it for us.
Exactly.
So let me show you a quick, simple demo.
So for example, here I'm showing, for example, of how you can create
the different indexes also because there is something interesting here.
So the first one is a usual cluster.
The index,
let's say, is just a typical index that people are used to have on a table.
And you notice that, of course, we have to specify the columns
on which we want to create the index because the column and the the position,
the ordering of this column is important because basically an index
and this again, it's the columns is the row store index orders
data follow using the logic specified here.
So my data will be physically ordered by order key and line number.
Ordering this
basically would make our whole index fast because we can search the data quickly.
Now what happens if I instead create a call misery index?
You notice that there are no columns here, right?
Because we cluster at columns story index, we are creating
a composite index on the entire table.
So we don't need to ask you which columns you want to create
because we create the the index on all the columns.
And this doesn't affect performance at all because each column is saved separately
and we only access the column that you are specifying in the query.
So this is a completely different approach from row store indexes.
Now, for those who doesn't know what a cluster order index is,
no matter if it's columns or roster, well, basically, let's say
I simplify a lot that the cluster index is actually the table.
So that's why here I'm creating a cluster index on one table and the Arrow Store
index on another because physically here I'm deciding and just to show the example,
I'm showing you how you can create a cluster index,
both rows stored in column stock.
But let's let's take a look at these.
Actually, let me do another sample here.
I created another table
where I create a regular row store, cluster the index
because I want that to be very fast that to find the
an order by specifying the order key or the line item.
So I want to be super fast going to that specific order number, but also on
the same table I'm creating a column store index, the non cluster.
That means I am just selecting some column
I want to use it to create the column store index
because those column is tools I will be using, for example,
in a reporting scenario and I'm creating a column misery
index on top of our store index, which means that my table
would be super faster in finding data by row,
but also will be super fast every time I want to find
how many rows are in process or how many of those has been delivered yet.
This is amazing because as you said, that a developer
doesn't have to compromise anymore.
You just create the table.
You want performance to find a single row, you have it.
You want to have performance to improve your
aggregation in end of the month, report, report, you have it.
So this is really something that can improve performance
in developer velocity a lot and how much and how or what a lot means.
Yeah. So let me show you here.
However,
some small database, it's a hyper scale general generation five eight core.
So not exactly huge but still decent.
And this is my table so I have, you know order key
the party number quantity as a typical order table
and actually this comes from the typical standard database.
Now let's count how many rows I have here and here.
I'm using the row store table and as you see, it
takes a while, but is 60 million rows.
So what happened here is that the edge of secret to read the all
the 60 million rows physically starting from the first up to the last one.
Now, let's add it to, let's say a little bit more than a second.
Let's do the same on the column store.
Wow, that's sensibly fast.
Yeah. Because here we don't have to read the rows.
All the rows we can just pick one column, maybe the smallest one and read that.
Right.
And actually we I say that is comprised we can do three X with the
with the vertex processing.
So you can already sense the speed.
But now let's do that.
Let's do a group buy query.
I want to see all the rows for which the quantities are greater than 300.
Let's do it on the road store query under the row store table.
Well, we have
to scan all the browser and, you know, do the aggregation.
And while wait.
Oh, okay, that's taking a lot.
That's me stopping,
let's say what happened if I switched to a column store, right?
So 12 seconds faster and we didn't get the answer.
So let's see what happened with the column store.
Oh, wow. Okay.
I was the last stop to say, let's wait.
That is here.
It's it's a lot, right?
It's probably more than ten s it is this performance.
So this is the beauty of the column store
in of course here I'm just for a demo using a columns or a row store.
But remember you can use together and everything also works nicely
even if you are more complex, square would defy ever group buy.
And then on top of this group I do harder
computation, but still it's very fast because
basically we are
we can take advantage of, for example of even of better caching
in the CPU core because we can push all, let's say
cache in the data of the same column and do more operation later very fast.
And in fact, if I go to explain and go here
and take a look at the at the
execution plan, you can see something called the execution.
What is batch?
Batch means we can work on a set of roll instead of roll by roll.
And this means that, again, we can do some average
or computation like data in a more efficient way.
So typically, is this improving the performance?
But you know, as we called out earlier, it's
also improving our storage costs because you're selling a lot.
Less. Information. Yes. Yes.
So you're basically it's a kind of also compression in.
Yeah, it saves several resources storage on disk,
storage in memory, which means your memory usage is more efficient
in general, CPU
resources and that's that's beautiful.
Even because everything I just showed, if you remember the last one
of the first episode we discussed about windowing function.
Yeah, right now we put, we start to put things together.
So let me show you my last name on here.
I'm using if you remember, we discussed about the leg.
Leg is the ability to look at the rows before mind.
So let's say you have a list of here
and I want to calculate the growth rate you write.
Well, I can aggregate the data as I did before.
Then I can use the log operator to say, take,
I don't know, 2020 and look at 2019
and calculate the previous quantity
and then calculate the difference and then calculate the growth ratio.
This is dawn on the 60 million roasted.
Right.
So let me I will not even try to you run on the roaster,
but on the column store.
A collision like that can be done pretty quickly.
This is basically real time and these only are at the core.
Like it's literally my my phone is 80 record today.
So it's pretty impressive, I would say.
And this enables a huge amount of scenarios for each stop.
So a hybrid transaction, analytical processing
where you have data coming in, you need to access a single role,
but you also need to present a dashboard or a report.
This is something really exciting, honestly, for me.
Yeah.
So we're saving on storage costs for saving.
Saving on our compute costs.
We're getting increased performance and I don't have to think about it.
Yeah.
And more importantly, you'll have only one that the base
that you have to learn and love
because you don't have
to supply data across several different, more specialized storage,
which I guess, you know, some, some in, some cases are super useful.
But in many case, you ought to just be having one thing
that does a lot of stuff together and basically remove all the integration
problem that are probably the most complex problem to resolve.
So everything is here.
You don't have to move data around, don't care about,
you know, having a out of sync data, all data, stale data.
That's that's beautiful, too.
And there's a lot more to learn about column store indexes and,
you know, when, when and how to use them, how to design the work.
Where can I go to learn more?
Oh, yeah.
So, we just saw Niko a well-known MVP.
He just joined us (Microsoft).
He wrote, I think, I don't know, hundreds probably blog post on columnstore.
So we make sure to share the link in the description here.
You can learn everything literally about the column store.
Of course, that is our documentation.
But yeah, that's the place I would go.
Thank you very much.
It's been great hearing all about columns for indexes from Davide Moretti today.
And as for Friday.
Hey, thanks for watching this episode of Azure Friday.
Now I need you to like it, comment on it, tell your friends retweeted.
Watch more as of Friday.