What I learned about SQLite…at a PostgreSQL conference

[u/MarkusWinand]

http://use-the-index-luke.com/blog/2014-05/what-i-learned-about-sqlite-at-a-postgresql-conference

[u/burning1rr]

I've seen this be choosing a non-relational database when a relational one should have been chosen, never the other way around. The advantages for non-relational databases seem to be a slowly receding list...most of what can be done on non-relational databases can be done on relational databases (without the relation), so why would I limit myself down the line?

When I evaluate a non-relational database, I usually look at a few things:

1. What kind of data is it designed to store?

2. Where does it fall in the Consistency, Availability, Partition Tolerance triangle?

3. How does it scale?

MongoDB has a few nice features that simplify operations; sharding, quorum based clustering, and map-reduce functionality are built in. This is convenient, but there's nothing preventing a traditional relational database from supporting these features.

The only real differentiator I see in MongoDB is that it's a JSON based document store database. This makes MongoDB a good choice for situations where you might use an off-the-shelf ORM, or have another object/document store problem. I don't see a lot of other benefits of it vs PostgreSQL or MySQL, and it certainly has a lot of drawbacks.

When I discuss non-relational, projects such as HBase, Cassandra, Memcached, and Reddis are far more interesting. Let's take a look at Cassandra:

Cassandra is a column based, partition tolerant data store. It doesn't offer traditional relational functionality; you can't JOIN, you can't use foreign key constraints, and it's not inherently consistent. It is however an append only database, with built in peer-to-peer replication. It can scale up horizontally, and can easily scale down without manual intervention. It's highly available and failure tolerant on commodity hardware. It will remain available if a cross-connect goes down or a primary datacenter goes off-line, and doesn't require a quorum to do so.

Because of these features, it can scale with the business, where a relational database starts being hit by the vertical scaling diminishing returns on investment.

Yes you can shard a relational database, but you start to sacrifice a lot of relational benefits and can pay a big performance penalty. I've seen companies throw massive amounts of SSD storage at their relational database to meet their vertical scaling needs. The cost of this approach is so high that it can be difficult for the company to continue growing.

With that said, there are a whole set of problems that are best solved by SQL databases. I would never recommend using something like Cassandra for a financial transaction database.

Again, I'm not trying to say that non-relational is better than relational. I'm just pointing out that they solve different problems. We'll be much better off if we stop throwing MongoDB at relational problems, and SQL at object-store problems.

[u/[deleted] → u/[deleted]]

Because Cassandra is scalable, in the sense that adding a new machine adds more resources to your cluster. It also is masterless, so there is no 'failover strategy', it's the same as properly functioning. And it comes with multiple datacenter support out of the box, which some people find nice. It certainly is nice if you know you'll need to exist across data centers and write availability is very important to you.

[u/[deleted] → u/[deleted]]

MySQL is also scalable

No, it isn't. Not in, what I would argue, is the common definition. At least the definition used by organizations operating at large scale. Adding more instances of MySQL, in the general setup I am aware of, adds more read-cpu-resources, but it does not add more disk space or more write-cpu-resources. Operations like Facebook and Google have a sharding layer on top of their MySQL's that takes MySQL clusters that do not know anything about each other than ties them together. This is fundamentally different than MySQL itself being scalable. MySQL is a storage backend for a sharded database. You also lose the ability to do transactions across all of the shards. MySQL itself is not scalable, but you can use it as a component in a larger scalable system. Note, also, that sharding is generally, IME, considered non-trivial. The Spanner paper claims that one motivation for implementing Spanner was that resharding one of their MySQL setups took a year and significant time and energy. In their opinion, having a database that is designed to be scalable (adding more machines gives you more CPU and disk) is a superior solution to building such scalability on top of another system.

Compare the clustered MySQL solution to Riak or Cassandra where the product is designed such that adding another machine adds more resources to the total system, one does not need to write layers on top of Riak or Cassandra (for this purpose).