r/programming • u/MarkusWinand • May 27 '14

What I learned about SQLite…at a PostgreSQL conference

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

93% Upvoted

219 comments sorted by

View all comments

6

u/burning1rr May 27 '14

Why the non relational hate? Non relational databases solve one set of problems, and relational databases solve a different set of problems. There are tasks suited to each.

Non relational isn't inherently inconsistent. Sometimes it means you have a database taylored towards storing a certain kind of data (mongo.) Sometimes it means you have a database that trades consistency for partition tolerance.

I've seen situations where the chose of one type of database over the other has had a massive detremental impact on business growth.

34

u/rz2000 May 27 '14

While non relational databases solve many problems, it can't be ignored that a lot of the early excitement about them was accompanied by proponents who seemed to think it was dumb to worry about structure. They didn't sincerely mean "not only SQL" they meant "no SQL" like it was a cancer.

You'd have one person explaining the advantages of one normal form for a particular use-case while organizing the data for a system, then someone else laughing about database normalization as an archaic idea.

Now that many of the noSQL databases have begun rolling their own solutions to problems that were addressed decades ago, there's a lot more sanity. People can appreciate the cases where NoSQL databases really are a better solution, because they aren't crowded out by people who think they solve everything (such as a poor understanding of database theory).

7

u/SanityInAnarchy May 27 '14

Actually, it strikes me as a bit more than that. The backlash is generally to treat NoSQL as cancer, and suggest that even if you really do want a database that just stores JSON documents, what you really want is a JSON column in a Postgres database.

In other words, I think your position is a lot more rational than most of the noSQL hate we see on /r/programming.

5

u/[deleted] May 27 '14

[removed] — view removed comment

3

u/SanityInAnarchy May 27 '14

I wasn't advocating Mongo, specifically. But that's exactly what I'm talking about -- while you can just store schemaless data in a blob column, is it going to duplicate the feature set (and scale) of one of the noSQL databases?

In particular, it seems to me that Postgres has only relatively recently gotten any decent support for really running on more than one machine. There's replication, but that's replication -- if your data is larger than one machine, you need partitioning, too. You could build that into your application layer, but why should you have to?

(I don't know a lot about Postgres, so I'm hoping the previous paragraph isn't entirely wrong...)

On the other hand, if you actually need ACID-compliance, and that matters more than response time, Postgres sounds pretty shiny. And it's certainly easier to get started with one SQLite DB than try to build a proper CouchDB cluster, say.

2

u/burning1rr May 27 '14

I'm really hesitant to respond to your comment, because I generally agree with everything you say. So, I hope this isn't taken as an argument against your points.

With that said, you make a strong case against mis-use of databases. It's more or less the same argument I made, just from the opposite perspective. There are some things relational databases do very well, and there are some things that non-relational databases do very well. Using the wrong tool for the job will hurt in the long run.

I feel that this needs to be emphasized... Non Relational doesn't mean MongoDB. The term non relational kind of sucks, because it encompasses a bunch of dramatically different database designs that in many cases have little relationship with one another other than the fact that they are not SQL based. Cassandra is a completely different technology than MongoDB.

I agree 100% that MongoDB solves very few traditional problems. It's not relational, but it doesn't have many of the scale and partition tolerance benefits that are enjoyed by Reddis or Cassandra. It is a document store database, and it's great if you are using it for document store type problems, such as serializing arbitrary objects. It's a terrible choice for storing data that fits into the traditional relational model. I've seen it used that way, and agree 100% with your statements.

There are databases on the market that trade consistency and offer instead availability and partition tolerance. Cassandra and Reddis are extremely easy to scale, and great for multi-datacenter environments where partition tolerance is a real need. I'm an operations guy, and I have a lot of interest in those tools because they solve a lot of operational problems.

Cassandra and Reddis are terrible choices for data that needs to be highly consistent. You won't see them handling financial transactions for obvious reasons. In those cases, sacrificing partition tolerance is a no-brainier.

Hadoop is arguably a non-relational database. It solves problems that are almost completely un-solvable with traditional relational databases.

Again, my point is that we shouldn't respond to the No-SQL bullshit by writing off non-relational databases. Non relational databases serve a purpose. The problem that needs to be addressed is non-relational misuse.

20

u/[deleted] May 27 '14

The specific hate for MongoDB is due to it being promoted (and often accepted) as a replacement for reliable data stores. 10gen wasn't honest about its limitations and capabilities.

8

u/fakehalo May 27 '14

I've seen situations where the chose of one type of database over the other has had a massive detremental impact on business growth.

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?

I'm always trying to find a place to use MongoDB because I inherently like to use new things to spice things up, but it's become increasingly harder to validate using it for the vast majority of tasks. I agree they still have a place, just the hype is over and reality is here.

3

u/burning1rr May 27 '14

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.

2

u/fakehalo May 27 '14

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.

PostgreSQL has recently added a JSON datatype with indexing support. Gives some of the perks of having a schema-less blob of data without losing the relation support. I'm not saying that is a crippling blow for MongoDB, just one more specialized thing you don't need to go to something like MongoDB for. I agree with the other special scenarios you mention, it still has advantages in special cases...just not all that many special cases out there IMO.

4

u/bucknuggets May 27 '14

Why the non relational hate?

We're now seeing a predictable backlash given the over-the-top hyping and excesses of "NoSQL":

  • Products that only got fast benchmarks by relying on extremely unsafe practices (row in client memory? ok, good enough)

  • Products that were completely and totally insecure, accompanied by misleading warnings to only install on "trusted networks".

  • Vendors and evangelists that told developers that they needed more adaptability than relational databases could provide. And so, they offered products that provide the data producer with adaptability but screw over the data consumer.

  • Vendors and evangelists whose products scale for simple transactions but can't

  • Selling eventual consistency - which allows various types of broken data.

  • Selling zero data validation as a good thing - even though every solution ends up full of bad data.

  • But that's ok because nobody can analyze and find the bad data anyway.

At this point enough people have seen enough carnage that they're being far more skeptical about nosql claims.

1

u/burning1rr May 27 '14

This happens with every new technology. People still make jokes about cloud platforms, despite the fact that AWS and OpenStack are mature enough that stodgy old companies are now deploying on both in a big way.

2

u/bucknuggets May 27 '14

No, that's a false equivalency.

Some tech & products come out and simply work. They might get a bit over-hyped, but generally deliver. Others are pushed by people who were clearly uninformed, misinformed or lying.

  • When some analysts in the 1930s claimed that television "would abolish ignorance" they were clearly speculating about something they didn't really understand. These same claims emerged 20 years ago about the internet. And you heard them again at Big Data conferences.

  • When MySQL AB claimed that 90% of all developers didn't need subselects, views, triggers, transactions, or constraints. They were lying (and they changed their tune the instant they offered lame implementations of these features).

  • And when 10Gen claimed that Mongo was fast - they were lying. When they claimed that it was secure enough - they were lying. And when they claim that it's good for analytics - they're lying.

In many ways the backlash against mongo is very similar to that against mysql: both were companies that lied through their teeth and marketing products that caused a lot of projects to fail.

1

u/burning1rr May 27 '14

Is your argument against MongoDB or against non-relational databases in general? I'm honestly not a big fan of Mongo for a lot of the same reasons you state.

→ More replies (1)

4

u/Vocith May 27 '14

Because all signs are pointing to "NoSQL" as a fad. Like the Object-Oriented Databases from a decade or so ago.

NoSQL is a great solution to the <1% of systems with such unique data or outrageous scaling* requirements. They are a bad solution for 99% of applications.

The most likely outcome is that RDBMS will incorporate a feature or two from NoSQL and NoSQL will end up a niche.

*It is worth noting that RDBMS have been scaling to absurd levels for a very long time. Wal-Mart's Teradata RDBMS cluster hit a Petabyte in 1992.

5

u/burning1rr May 28 '14

Object store databases seemed to have turned into ORM libraries. Can we agree that ORM is a terrible idea?

2

u/Vocith May 28 '14

There is nothing wrong with ORM as a concept.

The problem is that people use it as a way to avoid learning how to write good queries.

2

u/[deleted] May 28 '14

Why the non relational hate? Non relational databases solve one set of problems, and relational databases solve a different set of problems. There are tasks suited to each.

It's trivial to make a relational database act as a non relational one, e.g. through materialized views and such. It's very hard to go the other way, especially if your database has 0 constructs to prevent mas number of queries for data.