Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

What has key/value stores got to do with making database sharding easier?

Because if I do not use a key/value store, I can easily shard my database too right?

(Like say, I can easily say users with names that start with this character will have their data stored in this server, and those tables themselves aren't key/value stores. so what exactly had key/value stores got to do with database sharding?)

share|improve this question
add comment

6 Answers

Disclosure - I'm working for ScaleBase - who builds a transparent Database Sharding solution.

I don't really think those two terms have allot to do with one another. To scale relational databases (a.k.a SQL databases) you usually use sharding. But, if you don't need relational database, you can use NoSQL solutions, some of which are key-value based (like the popular Cassandra, but see here for more).

So the first is a solution for database scaling, the second is a relational database alternative.

share|improve this answer
    
is there any reason why is key/value store easier to shard? Or rather why couldn't my non-key-value-store database be easily sharded? –  jaytufch Jul 12 '11 at 16:31
    
What has key/value stores got to do with making database sharding easier? Because if I do not use a key/value store, I can easily shard my database too right? –  jaytufch Jul 12 '11 at 17:22
add comment

There is no direct correlation, but both of them often come up in the context of very large scale services - aka "cloud services".

In that type of system the data must be spread across multiple servers (because there is too much for a single server to handle). That is what "database sharding" is all about.

In very large scale systems the utility of SQL solutions decreases (because the ACID model doesn't work well when distributed across multiple machines) and it loses many of the advantages as compared to simpler systems such as key/value stores. Which makes the key/value stores appear more attractive since they are typically simpler and cheaper to run.

See these for more details: nosql and eventual consistency tutorials on nosql

share|improve this answer
    
is there any reason why is key/value store easier to shard? Or rather why couldn't my non-key-value-store database be easily sharded? –  jaytufch Jul 12 '11 at 16:28
    
What has key/value stores got to do with making database sharding easier? Because if I do not use a key/value store, I can easily shard my database too right? –  jaytufch Jul 12 '11 at 17:21
add comment

It isn't so much that key-value is easier to shard as it is that the best qualities of relational databases are harder to maintain when you shard. For instance if you have a unique field, it's uniqueness must be validated across shards. Foreign key validation also needs to cross shards.

Atomic transactions that need to check or touch multiple servers are also problematic.

Either you lose performance and possibly introduce complications or you forgo those features and you lose most of the benefits of using a relational database in the first place.

Key-value databases generally have less features, than relational databases and that simplicity makes scaling properly easier too. You can simulate a relational database too, but that's not how they're optimized.

share|improve this answer
    
Depends on your database. DB2 and Oracle have no problems maintaining a what looks like a single ACID relational database spread out over several servers. Do not make the mistake of thinking MySql's limitations apply to all SQL databases. –  James Anderson Apr 25 at 5:53
add comment

If you just devise your keys in a predictable manner, it makes it easy to shard the data.

It might be as simple as; All keys beginning with an 'A' lives in server1, all keys beginning with 'B' lives in server2 ... So you just gotta peek at the key first to know which server you should query.

Commonly you'll use some form of consistent hashing on the key to know what server to query.

share|improve this answer
    
so basically are you saying that the only purpose of using key value stores is such that we easily know which server to query? Is there any other reason to use key/value stores vs storing data in columns and rows with regards to sharding the data? –  jaytufch Jul 12 '11 at 16:30
    
What has key/value stores got to do with making database sharding easier? Because if I do not use a key/value store, I can easily shard my database too right? –  jaytufch Jul 12 '11 at 17:22
add comment

Sharding is just another name for partitioning.

It can be applied to various data stores, not only key/value - but for them is probably easy to implement as there is no reference integrity as in realational databases.

share|improve this answer
    
but it doesn't make sense, because the data can be relationally related, yet does not have relational constraints/checks right? –  jaytufch Jul 12 '11 at 16:54
    
What has key/value stores got to do with making database sharding easier? Because if I do not use a key/value store, I can easily shard my database too right? –  jaytufch Jul 12 '11 at 17:22
    
@jaytufch, if the data is relationally related and doesn't have such checks, there is approximately a 100% chance that you have bad data or will soon have bad data. If you don't actually need the checks, perhaps you don't really have relational data. –  HLGEM Jul 12 '11 at 18:02
    
jaytuch yes, everything is possible, maybe I should rephrase it - there is not reference integrity on dtoarage engine level. –  binary_runner Jul 12 '11 at 19:15
    
HLGEM, you do not need checks at data store level to keep some consistency, you can do them at application level and you can also allow some inconsistency and handle it differently. –  binary_runner Jul 12 '11 at 19:20
add comment

Directly answering your question of it is difficult to shard a relational database, physically it can be done relatively easily if you have the proper hardware and an Enterpise level database (you need Enterprise edition of SQL Server for instance not the standard version). But to do it correctly requires someone who understands high performance database systems and performance tuning and someone who thoroughly understands your data stucture (you do want related items to end up on the same partition). Your current design may or may not lend itself to partioning without changes. A database designed without the proper fields to do the partitioning on would need them added. Planning what is the best way to partition, designing the partions and testing them can be time consuming even if the actual SQL code to set up the partitions is relatively straighforward.

Lots of large terabyte-size relational databases exist and generally they are partitioned for performance. However, to do this properly requires someone with advanced database skills not only to partition the data but to make sure your queries are properly tuned for performance and that your design will perform well under the required load. A poorly designed data structure on top of hardware that is inadequate on top of badly designed queries (say those with correlated subqueries) will never perform well no matter how you slice the data. Most application developers don't have the skill set to properly design this stuff and then they complain that relational databases aren't fast enough. This is just a sign of incompetence in database design not the actual ability of the database to perform with a large number of users and a huge amount of data. I've seen badly designed and performing websites, does that mean Java or C# can't be used to produced a well-designed site?

If you already have the data in a relational database, it is probably better to hire a database expert to set up and manage your databases for growth that to try to convert to a noSQL solution. Whether you need to partition is irrelevant to the choice to use a key value store. You only want to use those for some specific special cases. For data that needs to be reliable and internally consistent, a key value store is a disaster waiting to happen. Don't forget there is a huge cost associated with converting relational data to a new type of data storage method and new bugs will be introduced, some of them possibly fatal bugs. IF your current databse physically can't handle the projected load (And Access just springs to mind here, you noted mySQL and SQL Server both of which can handle huge databases if properly designed), then it is less risky to convert to a more enterprise type of relational database than to convert ot a Key-value store.

For data that can lose consistency without a huge problem such as social networking sites (it's annoying if they lose your last post but not critical to the business) or search engines (Google isn't going out of business becasue it lost the references to your web site temporarily), then noSQL and key value stores are fine, but you won't find many companies trusting their critical financial data transactions to this type of data store and there's a reason for that. And incidentally using a key-value structure within a relational database will often cause performance issues as they are not optimized for that type of data.

There is a place for both types of database, but they store two very different types of data. It isn't so much about speed (which can be optimized to be excellent for either type of database by someone who actually knows what he or she is doing) as it is about how the data is used and queried and what kind of internal checks for data quality and consistency are necessary. And there is no reason at all why you can't use both in the same project - one for the data that doesn't need to be ATOMIC and one for the transactional data that you need to enforce key relationships for.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.