>> Hi, guys. I'm here to do a demo on building
HTAP applications with Python and Postgres in Azure.
Before I get started,
a quick intro about myself.
I'm Sai Srirampur and you can call me Sai.
I'm an engineer in the Postgres team at Microsoft,
and I joined Microsoft via the Citus Data acquisition.
At Citus I was an early engineer and I built
the Django-multitenant library that
helps you build scalable multi-tenant applications
using Django and Postgres,
and that is why I have this love towards Python and Postgres,
and that is why I am here to talk about both of them.
So this is what I'm going to do as a part of the demo.
First I'm going to demo how to deploy
a Hyperscale (Citus) cluster on
Azure Database for PostgreSQL using Azure portal.
Next, I will demo a scalable,
performant HTAP application that is running using
the Django framework and is being powered by Hyperscale (Citus).
Last but not the least,
I'll run some EXPLAIN plans using Azure Data Studio and show
you how this magic of performance and
scalability is happening under the covers.
Before I get started,
a quick overview of some concepts.
As a part of Azure Database for PostgreSQL,
there are two main deployment options.
First is the single server option that lets you deploy community,
traditional Postgres across a single machine in Azure.
Next is the Hyperscale (Citus) option that lets
you run Postgres across multiple servers.
So it transforms Postgres into
a distributed database to address
your performance and scalability requirements.
Today, we would be focusing more on
Hyperscale (Citus) and let's learn how to build Python applications,
those are scalable ones on Hyperscale (Citus).
Here is a quick overview of the Hyperscale (Citus) architecture.
In the Hyperscale (Citus) world,
you have something called the coordinator node
and multiple worker nodes.
The one in the center is the coordinator node,
and the ones to the right are the worker nodes.
The coordinator node and worker nodes are
nothing but simple plain PostgreSQL servers.
The coordinator node orchestrates
your queries to the right worker nodes.
The worker nodes are the nodes where the actual data exists,
that's where the actual computation happens.
All of this architecture from an end-user standpoint,
the client application standpoint looks
and feels like a single node Postgres database.
So you get a URL with a username, password,
host and the way you deal with Hyperscale (Citus) is
very similar to the way you deal with single server database.
This architecture gives you the ability to distribute,
shard your data across multiple workers, and also helps
you parallelize your queries across multiple servers,
across a lot of horse power,
and giving you great performance benefits.
Along with this, you also have the ability to keep adding
more workers and horizontally scale
out as your workload demands grow.
So today we will learn how
HTAP applications are great fits for Hyperscale (Citus).
So what is HTAP?
Applications that have
both transactional and analytical requirements
are HTAP applications.
So there are many applications out there that
have both these requirements.
An example of this is, say, you
have an app that manages
marketing and ad campaigns for businesses,
so this app has the transactional part because you create,
update, delete campaigns which is
the CRUD operations and these are more transactional.
Next finally, you need to analyze these campaigns.
So you want to track
how many clicks have happened from this country,
what are the number of impressions,
etc., that is more of the analytics part.
Typically, these applications have
a lot of demand for performance and scalability.
So typically, what customers do is they have
separate stores for each of the workloads.
So they have a separate store for
transactions and they have a separate store for analytics,
the transaction stores, are like,
relational databases like Oracle,
Postgres, or MySQL. Whereas the analytic store are
more like MPP databases that have parallelism.
The main challenge with this approach is customers
need to manage these two databases by
which they have more maintenance costs,
because they need to write
replication engines to replicate
data from transactional stores to analytical stores.
They need to have separate app code to manage these workloads.
Second is hardware costs as well,
because you need to pay for the hardware for each of these stores.
So to solve this problem,
you have Hyperscale (Citus) at rescue where you can have
both transactions and analytics in the same store.
The reason you're able to run transactions is
because under the covers Hyperscale (Citus) is all
built on the PostgreSQL core and PostgreSQL has
a rich story for relational transactional workload.
For the analytics part,
you have parallelism at rescue where you have the ability to
parallelize your queries across multiple machines and scale,
compute, and get linear performance benefits.
This would really help you reduce cost
both from a maintenance and a hardware perspective.
So let's get into the demo.
Let's see how to deploy a Hyperscale (Citus) cluster on Azure.
So the way you do it is you click on "Create a resource".
Then you go to "Databases",
and this is where you would see Azure Database for PostgreSQL.
If you remember, I showed you the two options,
single server and Hyperscale (Citus).
For now you click on Hyperscale (Citus),
and then you fill out this form.
Then you configure the size of the cluster.
So this is the interesting part.
So if you see here you have a horizontal slider
that defines the number of workers I want to go with,
and you have a horizontal slider to define
the vCores and the memory for each of these workers.
So you have the ability to start from
a smaller setup with just eight vCores to
even a larger setup with hundreds of vCores and
also terabytes of memory in a single Postgres database.
So to save some time,
I already spun up
a Hyperscale (Citus) cluster that we would use for the demo.
So this Hyperscale (Citus) cluster, if you are seeing,
has four worker nodes,
and a coordinator node.
Each of the worker node has 16 vCores and 128 gigabytes of memory.
So overall there is horsepower of 64 vCores and
half a terabyte of memory
across the single Hyperscale (Citus) cluster.
So next, what we'll do is,
we'll go and look at the application that I built
using the Django framework and this database.
So I have the ad analytics application that shows you;
number one, all the campaigns, if you see,
so these are all the campaigns for this particular business.
Then I can also
scroll through these campaigns and look at the ads if you see,
and then one thing to note to the right-hand side,
this captures what is the time that is being taken for loading
this dashboard and this also includes my network latency.
Then if you look at it, like say,
this dashboard took 155 milliseconds to load seven queries.
Then there is a significant amount of data under the covers.
There are 20 million impressions,
20 million clicks, so it is pretty sizable.
Then let's click "Manage campaign",
and you'll see nine queries
running in 286 milliseconds, so it is pretty quick.
You'll also see some analytics parts of
the number of impressions are 1513, etc.
So this is the application that I built.
Now, what we'll do is we'll move to Visual Studio,
where I have the source code.
I'll quickly walk through what I did.
So this is a traditional Django and
a Postgres application, not much different.
So this is the database connection string that I have,
the host name, the username, the password, etc.
Then I also have some models around 6,
7 models, ads, campaigns, clicks, etc.
If you see here to the center of the screen.
Then I also have some views that are
displaying these lookup, like analytics queries.
If you see, this is the ad, etc.
So if you ask me the effort that is needed
to make a Django application
seamlessly work with Hyperscale (Citus), it is twofold.
First is you need to decide the sharding strategy.
Typically, for multi-tenant applications,
this are multi-tenant application
because my customers are businesses.
For those kind of use cases,
the sharding strategy is the customer ID or the tenant ID,
because it's the natural sharding key.
The second step in the migration process is
to make sure that your queries are in
a way that they are optimal for Citus, for Hyperscale (Citus).
So in multi-tenant applications,
you basically add the tenant ID filter as a part of your queries.
To make all of this seamless,
we built the Django multi-tenant library and this also works with
plain Postgres and makes your multi-tenant app scalable.
So you can use this to drastically reduce
the migration effort and seamlessly migrate to Hyperscale (Citus).
So now, what we'll do is we connect to Azure Data Studio and
then quickly see how all of this is happening under the covers.
So let me go to Azure Data Studio.
So here I have Azure Data Studio where I would be
running some queries against the Hyperscale (Citus) cluster,
and show you how this performance for
transactions and analytics is happening under the covers.
So at the top of the screen, if you see here,
I have some queries and then
the bottom part of the screen, I'll get results.
So let's quickly run a query count (*)
across the impressions table and see the number of records it has.
If you see the query finished and it was
pretty quick to count 20 million records.
If you see, it took close to 489 milliseconds,
which is less than half a second.
So now, what we'll do is we'll
see what is happening under the covers.
So let me quickly run EXPLAIN plan for this query.
If you see what happens
in this EXPLAIN plan that you see at the bottom of the screen,
is Citus parallelizes this query across multiple workers.
If you see, there are 32 tasks,
there are 32 shards,
partitions under the covers,
and they are running asynchronously in parallel.
Under the covers,
the plan is a plain PostgreSQL plan on the worker nodes.
So the main reason you're getting this performance in
analytics workloads where you have aggregates, group bys,
is the virtue of parallelism that you
have across multiple workers,
across a lot of compute and memory.
Next, let's look at how OLTP workloads are performing,
which are transactional workloads.
So here I have a query,
where I'm just looking up on ID and the company ID column.
So I'm getting an impression from the impressions table.
So let's run this query and see how long it takes.
You see this query finished,
it was pretty quick and if you see,
it took around 34 milliseconds.
So this is like tens of digits of milliseconds
and remember that there is also network latency here.
So let's see how long this actually took.
So I'll put an EXPLAIN ANALYZE plan which
does not have the network latency and if
you see this particular query in the EXPLAIN ANALYZE,
if you see, it took 1.49 milliseconds.
So that was a single digit millisecond.
So the reason you are seeing
this great performance is because under the covers,
Citus routes this query to
the right shard where the company exists
because I sharded on the company ID and
executes the query on a smaller shard,
which has a smaller index,
and if you see the index scan here.
That is the reason you are getting a great performance
even for these transactional lookup queries,
similar is the story with inserts, deletes, updates, etc.
So this is the demo I wanted to show.
So the main take home message from my side
is that if you
have a Python or a Django app that has this HTAP requirement where you,
have both transaction and analytics in the same application,
please use Hyperscale (Citus), and trust me,
your maintenance costs and
hardware costs would drastically reduce.
Then you don't need to maintain
separate stores and getting to
that hassle of replicating data,
writing separate application code, etc.
So that's it from my side and thank you very much.
Here I have a couple of things,
first is my Twitter handle, and then I
have Azure Database for PostgreSQL Twitter handle as well.
Please feel free to go and tweet about the demo.
I have some resources,
first is the Hyperscale (Citus) docs, the Quickstart docs,
which would walk through how to deploy
Hyperscale (Citus) cluster in a very seamless way.
Then there are some tutorials
which are Hyperscale (Citus) and HTAP related,
and then there is also the Citus newsletter.
Please feel free to sign up for this.
There are around 12,000 subscribers
and it's one of the best mediums to
keep updated on what is going
on in the Postgres and the Citus community.
Thank you. Have a great day.