Hey, Cloud Gurus.
Welcome back to the wonderful world of partitioning.
In the last lesson,
we took a look at some of the best practices
of partitioning in general,
as well as some specific advice for Azure Data Lake.
In this lesson,
we're going to take a look at partitioning in Azure Synapse.
As I said before,
many of the things we discussed in the last lesson
apply here as well.
So, instead of reiterating what's already been said,
we're going to focus in on what is key for Azure Synapse.
And so, we'll be listing some best practices
and then, wrapping up with a review.
Just like with Azure data lakes,
it's important upfront to understand how partitioning works
in Azure Synapse.
This is a massively parallel processing system.
And what that means is that clients are connecting
to a control node, and then that control node
optimizes the query and passes it on
to a number of compute nodes underneath.
And those execute the work in parallel.
Because of that,
there are some extra factors in determining the number
of partitions in an Azure Synapse system,
because it is distributed by default.
The data is automatically distributed
across 60 underlying databases or distributions.
And so, any partitioning done
is in addition to these underlying distributions.
So let's take an example.
If you decided to make 100 partitions,
you'd actually need to multiply that by 60 distributions,
giving you, in total, 6,000 partitions.
So keep that in mind so that you don't accidentally
spread your data too thin.
If your table only has 6,000 rows,
then that's only 1 row per partition.
And that's obviously not ideal.
With that in mind, let's move on to best practices.
Be focused.
Scope your queries to as few partitions as possible.
Your biggest enemy in these systems
is going to be data movement,
and that's shuffling around the data
to satisfy your queries.
If your query can look in just a couple of partitions,
it's going to be much faster.
And for the same reasons,
you need to avoid cross-partition joins or transactions.
If you're constantly having to
join together other partitions,
it's definitely going to slow down your performance.
Also, utilize partition operations.
It can be tempting to just use an insert and update
or delete, but that has a heavy transactional cost.
Instead, use partition switching, merging, and deletion,
really taking an advantage of carefully crafted partitions.
And that will help these operations go much faster,
especially the larger amount of data you have.
And don't over-partition.
Just like our 6,000-row table,
think of it like this empty auditorium.
You could have lots of partitions
and very little or no information in it.
Generally, you don't want to shoot for
thousands of partitions,
but you're aiming more in the ballpark of tens or hundreds.
Each scenario will be different, of course, but overall,
just because partitioning usually helps us performance-wise,
it can also hurt us if we do it wrong.
By way of review, when creating partitions,
remember that Azure Synapse
already has 60 distributions included.
And so, any number of partitions you create
is going to be multiplied by 60.
Take advantage of partition switching, merging,
and deletion for much better performance
over inserts, updates, and deletes.
And avoid performance pitfalls
that result from crossing partitions.
You really want to craft your partitions,
and your queries against those partitions,
in a way that's going to scope them to as few as possible,
really striking that balance between
having all the information you need,
but not having to split out to other partitions
to join against them,
or do write and delete operations across them.
That's it for this lesson.
And of course, keep in mind all the other lessons we learned
in part one about scalability, performance,
and availability.
But I hope this lesson helps you understand specifically
how to best approach partitioning in Azure Synapse.
That's it for now.
And I will see you in the next lesson, when you're ready.