Designing your Multi-tenant Database for Scale with Postgres
This is a modal window.
Das Video konnte nicht geladen werden, da entweder ein Server- oder Netzwerkfehler auftrat oder das Format nicht unterstützt wird.
Formale Metadaten
| Titel |
| |
| Serientitel | ||
| Anzahl der Teile | 19 | |
| Autor | ||
| Lizenz | CC-Namensnennung 3.0 Unported: Sie dürfen das Werk bzw. den Inhalt zu jedem legalen Zweck nutzen, verändern und in unveränderter oder veränderter Form vervielfältigen, verbreiten und öffentlich zugänglich machen, sofern Sie den Namen des Autors/Rechteinhabers in der von ihm festgelegten Weise nennen. | |
| Identifikatoren | 10.5446/48977 (DOI) | |
| Herausgeber | ||
| Erscheinungsjahr | ||
| Sprache |
Inhaltliche Metadaten
| Fachgebiet | ||
| Genre | ||
| Abstract |
|
PGCon 20172 / 19
1
7
8
10
11
12
15
00:00
Computeranimation
00:32
Computeranimation
01:11
Computeranimation
01:58
Computeranimation
02:59
Diagramm
03:48
Computeranimation
04:09
Computeranimation
04:51
Computeranimation
05:33
Computeranimation
06:05
Computeranimation
06:51
Computeranimation
07:48
Computeranimation
08:44
Computeranimation
09:20
Computeranimation
09:53
Computeranimation
12:14
Computeranimation
12:48
Computeranimation
13:32
Computeranimation
14:40
Computeranimation
15:07
Diagramm
15:48
Computeranimation
16:25
Flussdiagramm
16:58
Computeranimation
18:00
ComputeranimationFlussdiagramm
18:47
Computeranimation
19:25
Computeranimation
20:00
Computeranimation
21:08
FlussdiagrammComputeranimation
21:51
Computeranimation
22:15
Computeranimation
23:06
ComputeranimationDiagramm
24:50
Computeranimation
Transkript: Englisch(automatisch erzeugt)
00:05
This is a distributed database that uses the PostgreSQL extension APIs. Citus horizontally scales PostgreSQL across multiple machines using sharding and replication behind the covers. Citus' query engine parallelizes SQL queries across the machines in the cluster.
00:22
Most importantly, Citus 6.1 is open source. If you'd like to try it out, or if you have any feedback for us, please hit us up over GitHub, Slack, or Google forums. What are some good use cases for Citus? Citus serves many use cases, and three common ones include the following.
00:40
First, if you have a multi-tenant database that needs to linearly scale, Citus enables that with minimal changes to your application. Second, when you have a large data set, and when you want to get answers from that data in human real time, typically in less than a second, you want to use Citus. Third, if you need to write large volumes of data into your database,
01:02
and if you'd like to combine the power of structured and semi-structured data Citus is a good fit. With that, let's dive right into our talk. Here is a quick talk outline. We're going to start this talk by first defining what it means to scale. We're also going to talk about different ways to scale,
01:21
and when you need to scale. Then we're going to define what a multi-tenant database is. After talking about scale and multi-tenancy, we'll talk about data modeling for multi-tenant databases. We're also going to provide an example database schema here. Next, I'll talk about three different approaches to scaling a multi-tenant database.
01:46
Lucas will then take over the slides and talk about integrating your multi-tenant database to your application. He'll then do a short demo, and finally we'll conclude with our Q&A. With that, let's first get started by defining what it means to scale.
02:03
At a high level, scaling is the process of providing more resources to your application or your database with the intent to improve its performance. Also, as a general rule of thumb, scaling competition is always easier than scaling data. For example, let's say that you have a website that serves customer traffic, Amazon.com.
02:23
If you needed to scale your front-end, you can just provision hundreds of servers on the fly, put them behind a load balancer, and scale your website. Doing the same for your database, however, tends to be much harder. In this tutorial, we're going to focus on the database scaling aspect.
02:41
When you're scaling your database, you need to think about both hardware and software resources. Usually, though, if you can't linearly scale your hardware, you're in a good place to scale your database. We're therefore going to focus on the hardware scaling dimension in this talk, so scaling databases and then hardware scaling databases.
03:00
What are the ways to scale your hardware? The first way is known as vertical scaling. In this approach, we simply go and buy a bigger machine. In this example, we have a PostgreSQL machine that has four CPUs, 30 gigs of RAM, 80 gigs of local storage. We went and bought a machine that had four times the resources or provisioned one on AWS,
03:20
and now we simply migrated our PostgreSQL database to that bigger machine. This approach is the simplest form of scaling your hardware resources, and we recommend that you leverage it. That said, what is the possible drawback associated with this approach? Cost. Anything. Cost could be one.
03:42
Yes. Eventually, if your application keeps growing, you may hit a wall. And in that case, you will need to scale up your database into many different machines. When you follow this approach, you can continue to scale your CPU, memory, and storage resources by adding new machines into your cluster.
04:01
This approach is also known as horizontal scaling, and now we're going to focus on this type of scaling in our talk. Now, an important, a very important question to answer is, when is the right time to think about horizontal scaling? The answer to that question primarily depends on where in your application
04:21
and database's lifecycle you are. If you're at a stage where you can throw more hardware at the problem, we recommend that you always do that first, because that's easy. We also recommend spending time in tuning your database's config settings and optimizing your queries. In other words, if you don't need to scale out, don't scale out. With that said, if your SaaS application is growing,
04:42
there will be a point where you're going to start running into performance issues. So, when is the right time? When is that time to start thinking about scaling out? We've seen hundreds of users and customers who look to scale out their Postgres databases at Citus. In terms of when to scale, I compiled three heuristics that I wanted to share.
05:03
These aren't hard written rules. It's best to think of them as general guidelines. The first one is if your business is growing and you run the second largest instance type available on your cloud provider, you probably want to start thinking about scaling out. Why the second largest instance type and not the largest one?
05:20
Because your business will continue to grow on, and being on the second largest instance type will give you breathing room when you start thinking about scaling and sharding. A second heuristic that usually applies for OLTP type workloads is AutoVacue. PostgreSQL uses AutoVacue demons to clean up load caused by MVCC.
05:43
The default vacuum settings are too conservative. These are in this column. And if you haven't tuned them, we recommend that you look into tuning them. We have certain suggestions in here on the right hand side. If you tune them for your workload and you're still experiencing performance issues related to vacuum, then it may be a good time to start thinking about scaling out.
06:02
A third heuristic relates to how much of your working set your database can serve from memory. Most databases will track how often you hit the cache and when you need to issue disk IO. For OLTP applications, most of your working set should be served from the cache.
06:21
Ideally, you'd want to serve 95 to 99% of your lookup queries from the cache itself. In PostgreSQL, a good way to calculate your cache hit ratios is by running these queries. The query at the top measures the cache hit ratio for your tables and the query below measures it for indexes. And if your cache hit ratio for your database starts dropping below 95-99%,
06:44
then it may be a good time to start thinking about horizontally scaling. To recap, you have several heuristics that you can keep an eye out for. And by using these heuristics, you can have a sense of the right time to scale. Then when that time comes, what do you do?
07:02
What are different approaches that enable you to scale out? We find that the answer to that question depends a lot on your requirements. And the following two questions on the slides help better identify those requirements. First, are you looking to scale out a database that serves a B2B, B2B2C, or a B2C type workload?
07:23
This has multiple impact on your data model. Second, do you have a transactional or analytical workload? This question is orthogonal to the first one. You can have a B2C application that can be a transactional or an analytical workload that serves one.
07:42
And in the talk, we're focusing on the first dimension, the B2B type workloads in the rest of this talk. When you say multi-tenant, does that mean that you keep all of your clients' data in a single database, or does that mean that you have multiple databases? It doesn't necessarily mean that you can actually have multiple,
08:02
like one client in a separate database. And I'll talk about the different approaches that you can take in maybe five slides. On the B2B side, and I'm primarily thinking of it as B2B workloads, they lend themselves to what's known as a multi-tenant database. That's the primary relation.
08:20
And if you're building a B2B application, most information relates to your tenants or your customers or your accounts, and your database tables capture this natural relationship. As an example, you could be building a marketing automation tool for other businesses. In this case, each business that you serve, along with their data, becomes a tenant in your database. And the notion of a multi-tenant database isn't new.
08:42
It's been around for at least two decades. What's new and primarily in the context of scaling multi-tenant databases is the web and the cloud. We now have cost-effective SaaS applications. These applications no longer only serve Fortune 1000 companies, but they also empower thousands of other businesses. And these SaaS applications more and more rely on open source,
09:03
and they need ways to scale to tens of thousands or hundreds of thousands of tenants. Further, SaaS applications can store even more information to help their customers, thanks to sharp drops in hardware prices. These reasons combined create a distinct motivation to scale multi-tenant databases.
09:23
Google's F1 paper is a good example that demonstrates a multi-tenant database that scales this way. The paper talks about technical challenges associated with scaling out the Google AdWords platform to over a million tenants. The paper also describes common relational database properties and file leverages for powering the underlying AdWords platform.
09:42
Those features are transactions, joins across tables to avoid data duplication, and database constraints to make sure that each tenant's data remains consistent. Can everyone see this slide? So this is a slide from the F1 paper. The paper highlights the best way,
10:03
like the best way to model your data to support many tenants' customers in a distributed database. The database on the left-hand side is the relational data model. Can everyone see the slides more or less? And it is an example. At the top is that there is a customer, say Citus Data uses Google AdWords, so Citus Data is a Google AdWords customer.
10:22
That data in the relational model has a primary key on the customer ID dimension. We run campaigns, so each campaign goes into a table that has a campaign ID, and then each campaign has ad groups within it, so there are those three tables. If you model this database on the relational model,
10:40
the customer table has the customer ID as the primary key, the campaign ID and ad group as the ad group ID. And when you overlay that into a distributed cluster, so the customer data lives in some place, the campaigns live in like some other machines, and the ad groups live in a third different machine or like multiple different machines. The thing is, most operations that you perform on this model are on a per-customer basis.
11:03
So Citus Data runs campaigns and ad groups, so all of those operations actually are related to a particular customer. So when you start a transaction in a distributed cluster, you're actually performing a lot of operations to make sure that that transaction executes over the network.
11:20
On the right-hand side, there is an alternate data model, in fact the predominant data model prior to the relational data model, which is the hierarchical data model, and in that model you still have those three tables, but each table has the customer ID as part of its primary key at the very beginning. What that enables you to do is the data that relates to a particular customer,
11:42
say Citus Data, and the campaigns and ad groups are co-located together. That way, when you run a transaction that relates to Citus Data, it goes to the same machine. When you have joins, most of the joins are executed locally, and when you have foreign key constraints, you can actually enforce those foreign key constraints without paying the penalty of the network.
12:04
Does this make sense? The two models? Yes? No? Questions? And going into the key benefit to the hierarchical database model is that it enforces data co-location. In the simplest form, you add a customer ID, tenant ID column to your tables,
12:23
and then shard those tables on customer ID. This ensures that data from the same customer gets co-located together, and co-location dramatically reduces the costs associated with distributed transactions, joins, and constraints. Co-location also simplifies the costs associated with handling network and machine failures.
12:42
In summary, the hierarchical database model brings performance as a key benefit. I'll look at the concept a bit deeper because the rest of the talk actually depends on this concept. In the diagram, we have three tables, stores, products, and purchases. Think of a storefront, say AWS, Amazon storefronts or Shopify.
13:03
And then all three tables in this model are actually distributed on the store ID. So you'll see that the store ID 1 has the products in here, and has the purchases with the store ID 1 in here. So all of the data that belongs to a particular store is co-located together.
13:22
And in this way, when you're doing these transactions or joins, you can always push them down to the machine and not pay the penalty of the network. Now, this concept of co-location or co-locating tables to the same machine sounds great, but what happens if you have a table that doesn't fit into this model?
13:43
For example, this could happen if you have a web application that serves different organizations. Each organization normally has its own users. You may want to simplify the log-in process for users who are logging into multiple accounts. So you could handle this user's log-in table in one of two ways.
14:00
You could either keep it as a regular PostgreSQL table, or you could shove your user's log-in table on the user ID column and make sure that you don't join it with other tables. A second common table type is small tables that touch across all tenant-related tables. For these tables, one option is to denormalize them into larger tables. Another option is to create reference tables that are replicated across two machines.
14:26
So these tables could be, for example, your nation's table, your time zone table, your region's table. All good? Now that we talked about scaling multi-tenant databases, let's look at different ways to implement this approach. This goes into the earlier question.
14:42
If you'd like to scale your multi-tenant database, how do you go about it? At a higher level, you have three options. You can create a separate database for each tenant customer. You can create a separate schema for each tenant customer. Or you can have all tenants share the same tables within a database.
15:00
Each one of these design options come with different benefits, and I'll look to describe them in the next few slides. In the first approach, you create a separate database for each tenant. From a hardware standpoint, these databases could all be living on the same machine, or they could be living on separate physical machines. In each case, you allocate its own fair share of resources to each database.
15:25
For example, in this diagram, we have three databases for three tenants, and usually these databases share the same table schemas, indexes, PLPG, SQL functions. On the left-hand side, we have a tenant five, and then this database serves tenant five's data. So when you have a query that comes for tenant five,
15:42
you direct it to this particular database. And then this approach, creating a separate database for each tenant, optimizes for isolation of tenants. You may need to use this model in the industry, such as healthcare or finance, where you have regulatory requirements.
16:00
This model also has the benefit that you could give each tenant free-form SQL access to the underlying database, and have them run their own queries. The trade-off is DBAs now need to manage different databases, and make sure that each database gets allocated its fair share of hardware resources. In practice, customers who have isolation of tenants as their primary decision criteria, usually lean towards this approach.
16:27
In the second design pattern, you create a separate namespace slash schema for each tenant within the same database. From a hardware standpoint, these schemas could again all live on the same machine, or you could add logic to place them on different machines.
16:41
In this diagram, for example, tenant five lives in a particular namespace within the database. And when you send a query to your database, you first say search path, accept that search path to five, and then you route all queries to that particular schema namespace.
17:00
When you think of isolation, resource sharing as a trade-off, this second design pattern sits between options one and three. In this model, you can continue to isolate a tenant's data and queries into a particular database schema. Still, the model may or may not address the regulatory requirements depending on your industry. Similarly, compared to the one database per tenant model,
17:23
this approach does a more efficient job at sharing resources. You now have one database that manages all resources allocated to it. At the same time, you may start observing other resource challenges. For example, if each one of your tenants has a hundred tables, and you have a thousand tenants,
17:41
in total, your database needs to manage a hundred thousand tables and associated indexes. ORM tools cache metadata related to all tables in the database when they start up, so each ORM process then caches data related to a hundred thousand tenants' tables.
18:00
The third design pattern is the one where all tenants share the same tables in a particular database. In this approach, you simply add a tenant ID column to all your tables and then shard them across your cluster. In this diagram, for example, you have three tables, accounts, tenants, campaigns, leads, and in this approach, you add a tenant ID column to your leads table
18:21
and then you add a tenant ID 5 for the leads that belong to tenant ID 5 and another tenant ID for a different lead. So each tenant and each tenant's data becomes rows in your database within the same database table. And then you ensure that these tables are consistent through database constraints.
18:41
Any questions up to this point? And in the third approach, you don't have the strict isolation guarantees as the one database per tenant approach. For example, your application needs to add a tenant ID filter to queries going to the database. On the scaling side, this design pattern easily scales to tens of thousands of tenants
19:03
since all tenant data lives as rows in regular database tables. This approach also simplifies the operational burden. For example, you can add columns to your table schema and the database takes care of all the work across all of your tenants. So the question is, how do you pick between these three design patterns?
19:25
The truth is, each one of the three design options, with enough effort, can address questions around scale, data that varies across tenants, and isolation. The decision depends on the primary dimension you're building optimizing for.
19:40
A simple rule of thumb is, if you're building for scale, have all tenants share the same tables. If you're building for isolation, create a separate database for each tenant. Now, I'm not sure if our question is, why having all tenants share the same tables provides better scaling characteristics?
20:01
The answer to our question comes from our definition of scaling. Scaling is allocating more hardware and software resources to your database. The more efficiently those resources are shared, the better your scaling characteristics get. As an example, if you create a separate database for each tenant, then you need to separately allocate disk, memory, and CPU to each database.
20:22
Further databases usually make assumptions about resources available to them. For example, PostgreSQL has shared buffers, makes good use of the operating system cache, and comes with connection count settings. If you're running 50 of these databases as separate databases, then you may be okay. If you're running 100 or 200 on a few physical machines,
20:42
then resource pooling becomes tricky even with today's virtualization tech. And if you have a distribute... Sorry, I just want to say that I think there's something in between those two, because I think you're going to suggest the other way, is the tenant ID column. The other things are schemas with search paths, and then some people create a database named after each customer,
21:04
not a separate cluster, because you're talking about a separate cluster here. In this slide I am, and I think what you're proposing is the one that's in between, which is this approach where you basically are setting the search path and not sending the query to here. From a resource sharing and pooling standpoint, I think it sits between option 1 and 3.
21:21
Like that's I think more or less the... And yes, I'm basically comparing right now the 1 and 3 approaches, taking the isolation and scaling them together. In the schema model, I think you don't now need to manage the hardware, because your database manages the hardware for you. The flip side is now you're putting,
21:42
say again if you have a thousand schemas with each a thousand tables, now your database manages way more tables and indexes than it typically would. And going back into this, looking at the other one where you're sharing the entire table schema, if you have a distributed database that manages all of the tenants,
22:02
then you're basically delegating that, you're using your database for what it's designed to do. You could shard on tenant ID and easily support hundreds of thousands of tenants. Whenever tenants share the same tables, a second related benefit involves operational simplicity.
22:20
As your application grows, you will iterate on your database model and make improvements. For example, you may need to change the schema for one of your tables or add an index to improve query performance. If you're following an architecture where each tenant is in a separate database, then you need to implement an infrastructure that ensures that each schema change either succeeds across all tenants
22:40
or it eventually gets rolled back. So the responsibility is on your side. For example, what happens when you change the schema for 5,000 of your 10,000 tenants and one of the machines in your cluster fails? How do you handle that? When you shard your tables for multi-tenancy, then you're having your database do the work for you. The database will either ensure that an altered table goes through across all the shards
23:02
or it will roll it back. So I'm about to wrap up and before I wrap up, I also wanted to touch upon a question that we hear very frequently across all three design patterns. That question is, how does my largest tenant affect my scaling properties?
23:21
We find that the tenant data in multi-tenant databases usually follows a ZIF distribution that is you have a few popular tenants and then a long tail. You might have also heard of this distribution as the power law, the power law, or the 80-20 rule. How many of you have heard any one of those names, ZIF distribution, power laws, 80-20 rules?
23:41
Okay, about like a little more than half. And they actually all refer to the same concept, but then they use different ways to express that concept, visualize this concept. In this graph, I'm using the ZIF representation. What this basically is is to construct this graph. Let's say you start with a representative sample of your database.
24:00
Let's say you take 0.1% of your data. You take a dump. You then take all tenants in that sample and count the number of rules where they occurred. You then sort those tenants by their number of rules and plot that ranking on the x-axis. So this is the x-axis. In this sample, the most popular tenant had 100,000 rows.
24:21
The second most popular one had like 70, 80,000 rows. And this is the least popular tenant that appeared in the sample, which is like the 150,000 tenants, which appeared only once. And one thing to keep in mind is this is a long-axis. So if your sample was bigger, or if your data was bigger, the curve would move up,
24:42
but the slope would remain the same. So it's the same behavior. Your behavior would be the same. Now, how does the theory behind ZIF or power law distributions help us? It helps us in the following way. When you're looking to migrate your single machine database, your own PostgreSQL,
25:01
an important question to answer is, what percentage of the total data size does the largest tenant hold? That is, if your existing database serves 100 tenants and the largest tenant holds only 10 percent of the data, then this approach will help you scale by 10x. If you assume a standard ZIF distribution and more or less these databases fit into that distribution,
25:22
then we can come up with the following heuristics. When you have 10 tenants, your largest tenant roughly holds about 60 percent of the data. When you have 10,000 tenants, this means that your most popular tenant will hold about 2 to 3 percent of the data, and you'll be able to scale to 50x or where you are today.
25:42
Of course, these are general guidelines, and the best way to tell this is by looking at your own data. And with that, I'll hand it over to Lucas to talk about the application side of things and do a quick demo. If there's any questions, go ahead.
26:13
What is the biggest machine that you have? Not the biggest machine. Typically, people who use Citus
26:21
or people that we talk to have 10,000 to 100,000 tenants, like lots of tenants, and in that case, and if they're in PostgreSQL, the largest tenant is about 1 percent of the data, so they have room to grow. They still ask about, for my largest tenant, how can I isolate it? So in Citus, there is a function that isolates the largest tenant to a particular hardware
26:42
or the largest tenants. So far, for a large number of tenant counts, we haven't had customers who overgrew, whose largest tenant overgrew a single machine. We have worked in that area. We can chat after the talk. That wasn't me. Oh, yeah.