But if you got 5 TB of data, that needs to be in a SSD drive, then please tell me how I can get that into 1 single physical database.
Now you don't have to shard. More info on how we accomplish distributed transactions. https://fauna.com/blog/distributed-consistency-at-scale-span...
if all you need is a lot of data in a single database, there's basically nothing except for money between you and your goal. JBODs full of SSDs coming into a single machine via SAS will get you into petabytes, just with commodity hardware you can order from amazon.
i'm expect IBM could sell you a mainframe that'll do it for whatever capacity you care to name.
http://www.dell.com/en-us/work/shop/povw/poweredge-r930
Some of us do need to shard for sure though (I have multi petabyte data sets).
Also, operations on a such huge data set can be really painful. Think how to backup a DB like that safely, or how to update the engine.
Some slides (little old, 2014) about a huge postgres instance serving as a backend for leboncoin.fr (main classified advertising website in France).
https://fr.slideshare.net/jlb666/pgday-fr-2014-presentation-...
Basically, they bought the best hardware money could buy at the time to scale vertically, they, in the end, run in some issues and started thinking about sharding this huge DB.
Perhaps the title is click bait, but at the time I was meeting with a lot of users looking for someone else's problems.
5TB could still easily be single server territory. It depends more on the queries.
My point is just that some workloads are better solved with (some) vertical scaling first.
or did you mean just use a large capacity RAID setup? that will probably work fine for a lot of situations but it can expensive and introduce more latency for certain types of operations (but that might not matter, depends on context).
https://petapixel.com/2015/08/15/samsung-16tb-ssd-is-the-wor...
Drive capacity in a server is not limited to the size of a single drive. You can build a raid array any size you like by simply adding more drives.
If you have any questions that aren't covered in the post, happy to answer them here!
For me a major question I have as I consider sharding is what my application code will look like. Let's say I have a query like:
'select products.name from vendor inner join products on vendor.id = products.vendor where vendor.location = "USA"'
If I shard such that there are many products table (1 per vendor), what would my query look like?
If that's too much work, then an easy preliminary step is to add the abstraction layer in your application code. That gets you most of the benefits of a proxy for the purpose of having clean application logic, and makes it easy to switch over later, but is less powerful and feature complete.
If you use Citus, you don't have to make any changes in your application. You just need to remodel your data and define your tables' sharding column(s). Citus will take care of the rest. [1]
In other words, your app thinks it's talking to Postgres. Behind the covers, Citus shards the tables, routes and parallelizes queries. Citus also provides transactions, joins, and foreign keys in a distributed environment.
[1] Almost. Over the past two years, we've been adding features to make app integration seamless. With our upcoming release, we'll get there: https://github.com/citusdata/citus/issues/595
If I understood your example query, your application serves vendors and each vendor has different products. Is that correct?
You can approach this sharding question in one of two ways.
1. Merge different product tables into one large product table and add a vendor column
2. Model product tables as "reference tables". This will replicate the product tables to all nodes in the cluster
Without knowing more about your application / table schemas, I'd recommend the first approach. I'd also be happy to chat more if you drop us a line.
To me it read like just a basic introductory post to a longer series.
How is it a benefit that you are no longer able to join data in your separate tables? Is this sentence a mistake?
How long do these sharding projects usually take? Do you know of any posts that break down the steps in more detail?
A good way to tell is by looking at your database schema. If you have a dozen tables, you'll likely migrate with one week's of effort. If your database has 250+ tables, then you'll take about eight weeks.
When you're looking to shard your B2B database, you usually need to take the following steps:
1. Find tables that don't have a customer / tenant column, and add that column. Change primary and foreign key definitions to include this column. (You'll have a few tables that can't have a customer column, and these will be reference tables)
2. Backfill data to tables that don't didn't have customer_id / tenant_id
3. Change your application to talk to this new model. For Rails/Django, we have libraries available that make the app changes simpler (100-150 lines). For example: https://github.com/citusdata/activerecord-multi-tenant
4. Migrate your data over to a distributed database. Fortunately, online data migrations are starting to become possible with logical decoding in Postgres.
If you have a B2C app, these estimates and steps will be different. In particular, you'll need to figure out how many dimensions (columns) are central to your application. From there on, you'll need to separate out the data and shard each data group separately.
Definitely depends on the workload, but often the "micro service" approach (whether or not it's a true micro service in its own runtime) of sharding just one type of data/small set of related tables that you can shard by a primary key or user id or something seems like the only reasonable option for sharding. If your data is becoming unwieldy there's often a bottleneck data set that's bigger than everything else so you don't necessarily have to share everything all at once.
