4 ms·

One word of warning about choosing a partitioning scheme: if you select something like customer (as recommended in the article's example), you might get to a po

by brandur 10y ago

One word of warning about choosing a partitioning scheme: if you select something like customer (as recommended in the article's example), you might get to a point down the road where you realize that some customers are much, _much_ bigger than other customers. This can put you in an awkward place as a single tenant is approaching the bounds of their shard because you may not have a lot of choice but to figure out how to further subpartition their data. That said, per-customer/user partitioning might still be your best bet given the advantages that it conveys around isolation. If I understand correctly, Citus can guarantee atomicity, consistency, and isolation (ACI*) within a transaction localized to any single partition which is a _huge_ benefit for building apps that are more tolerant to failure and problematic edge cases with very little effort on your part (compare this to Mongo, which in its latest versions is just starting to give you the "consistency" part and nothing else). Anyway, nice work from the Citus team!

craigkerstiens 10y ago
We actually have something on the roadmap to help address the case of a very large customer. In future versions of Citus you'll be able to isolate a single very large tenant to their own shard so their resource consumption won't compete as directly with other smaller users, and then if needed you could scale them out to their own physical node with no changes to your app.
- koolba 10y ago
  Is that being done via custom partitioning functions? i.e. rather than a blind hash of the partition key to the shard space, adding if/else logic to say that key ABC goes to shard XYZ?
- ddorian43 10y ago
  You'll need multishard table for big clients. And with ability to split shards to grow more.
smileysteve 10y ago
Indeed, partitioning by customer_id is probably the wrong idea. Your number of customers will typically not be an primary key that is difficult to index in memory. For something like page views, where a single customer will have millions of page views and your typical query will be ordered by date, you'd be much better off partitioning by date.
- brianwawok 10y ago
  Depends if it's time series data or not. Some data doesent shake down along date boundaries well.
ozgune 10y ago
(Ozgun from Citus) Yup, two good points. When you use this model, Citus guarantees ACI* and T for transactions that are scoped to shards on one machine. It also handles auto-failover. On one big customer being much bigger than the other, two notes. I completely agree that this could happen; we in fact find that this is more the norm than the exception. On its practical implications, we find that it matters less when you have 100+ customers/tenants. Most multi-tenant apps' data follow a Zipf distribution. In other words, if you're running on single Postgres and have 10K tenants, the largest tenant has around 2-3% of the data. When you follow this sharding scheme, it gives you the opportunity to scale to 50x of where you're today. I believe Salesforce and Google F1 are using a similar approach to sharding their multi-tenant database + the tenant isolation feature Craig mentioned below. Then again, I haven't personally worked on these databases and I'm happy to update my comment accordingly. https://www.citusdata.com/blog/2016/10/03/designing-your-saas-database-for-high-scalability/ https://www.citusdata.com/blog/2016/10/03/designing-your-saa...

by brandur 10y ago

craigkerstiens 10y ago

We actually have something on the roadmap to help address the case of a very large customer. In future versions of Citus you'll be able to isolate a single very large tenant to their own shard so their resource consumption won't compete as directly with other smaller users, and then if needed you could scale them out to their own physical node with no changes to your app.

koolba 10y ago
Is that being done via custom partitioning functions? i.e. rather than a blind hash of the partition key to the shard space, adding if/else logic to say that key ABC goes to shard XYZ?
ddorian43 10y ago
You'll need multishard table for big clients. And with ability to split shards to grow more.

smileysteve 10y ago

Indeed, partitioning by customer_id is probably the wrong idea. Your number of customers will typically not be an primary key that is difficult to index in memory. For something like page views, where a single customer will have millions of page views and your typical query will be ordered by date, you'd be much better off partitioning by date.

brianwawok 10y ago
Depends if it's time series data or not. Some data doesent shake down along date boundaries well.

ozgune 10y ago

(Ozgun from Citus) Yup, two good points. When you use this model, Citus guarantees ACI* and T for transactions that are scoped to shards on one machine. It also handles auto-failover. On one big customer being much bigger than the other, two notes. I completely agree that this could happen; we in fact find that this is more the norm than the exception. On its practical implications, we find that it matters less when you have 100+ customers/tenants. Most multi-tenant apps' data follow a Zipf distribution. In other words, if you're running on single Postgres and have 10K tenants, the largest tenant has around 2-3% of the data. When you follow this sharding scheme, it gives you the opportunity to scale to 50x of where you're today. I believe Salesforce and Google F1 are using a similar approach to sharding their multi-tenant database + the tenant isolation feature Craig mentioned below. Then again, I haven't personally worked on these databases and I'm happy to update my comment accordingly. https://www.citusdata.com/blog/2016/10/03/designing-your-saas-database-for-high-scalability/ https://www.citusdata.com/blog/2016/10/03/designing-your-saa...