A Comprehensive Guide to Moving from SQL to RethinkDB | Hacker News Reader

A Comprehensive Guide to Moving from SQL to RethinkDB

airpair.com

After spending a decade working with Lotus Domino, I think all of these people who are willingly going back to document style databases are insane.

Being able to fiddle with your schema on the fly isn't necessarily a good thing. Brainstorm, plan, execute. Or just slap another field on the end of the document. Whichever sounds better for the long term health of your data!

There is a place for document store, and in my opinion it's for prototyping in a domain one's not necessarily experienced with yet. In that situation, trying to come up with the perfect (or just reasonably good enough) data model is overwhelming. So it's easier and faster to just go ahead with a document store. I like Solr because I know it best and Apache licensed and I'll likely need a search engine later. Postgres JSON type, Elastic search, Mongo or others are perfectly valid choices too.

However, once the app has shaped up, I make it a priority to harden up the data model and move the core data set to a relational database (Postgres is the likely choice now). One nice thing with this migration is I can do a lot of clean up and normalization with the originally unstructured data set.

I think this is the most reasonable take I've read and encompasses the whole "right tool for the job" mindset that I like.

I've found that when starting a new project I get paralyzed with coming up with the perfect data model that will also scale in the future, so I'd rather punt, take the tradeoffs that come with schemaless DBs, and keep a plan to migrate to something like Postgres in the backlog. Holding on to our tools too tightly is IMO how projects get crippled.

Exactly that, but I would also add that migrating things that are documents off to a document database (or leaving them there after prototyping) is perfectly fine.

I use RethinkDB as the backing database for a API server written in Haskell. So I have pretty strong guarantees that the data which enters the database is sane, and when I read it out Haskell automatically validates it against a very strict schema.

SQL schemas are not expressive enough to describe the data types which I want to store in the database. The argument SQL vs NoSQL becomes moot when any database is degraded to essentially pure blob storage. What matters to me is ease of use, administration/maintenance burden, and of course speed. How strictly the database verifies the schema isn't even on the picture.

> SQL schemas are not expressive enough to describe the data types which I want to store in the database.

I'm incredibly skeptical that SQL doesn't support your data types but JSON and RethinkDB do. A quick glance at the docs for PostgreSQL and RethinkDB, and the RethinkDB types are a small subset of the PostgreSQL types. It sounds to me like you just don't want to break out nested objects into separate tables.

http://www.postgresql.org/docs/current/static/datatype.html

http://www.rethinkdb.com/docs/data-types/

Neither database natively supports the types I need. The decision which database to use was not at all based on that criteria. I suspect either one would be suitable (from a purely technical perspective).

It is not realistic to create a separate table for each type. You'd end up with hundreds of tables (and would have to replicate the types in two places). You can only break down the types so much, at some point you'll want to have columns with your own special types which you want to treat as primitives.

He's saying that Sql or NoSql dont matter in this case, since he is using Haskell's type system to verify integrity. A document database is often a better fit for data structures than a relational database, if you are not using the integrity features of a relational database anyway.

If you are just doing blob stores, I can get behind a document style database. I know of some people doing a lot of work with blobs that use relational databases.

I know of a "Diablo clone" online RPG that uses SQL Server to store the character blobs for the people playing the game. They have a logically partitioned table, GUIDs for IDs (used for the partitioning), and just a big ass blob (somewhere between 32k and 128k) assigned to that id. I'm sure there are other items associated with the row, but that's the gist of it.

They handle all of the funky stuff in the application. I don't see why that wouldn't work in a document style database, and I'm not entirely sure what the design decision was to use SQL Server.

I'd like to see if they handle items in a relational manner or all of that is in the application. I wish more companies were a bit more open about their schemas and design decisions. I have done a lot of things a lot of different ways, and I'm always curious about how others have tackled similar problems.

> SQL schemas are not expressive enough to describe the data types which I want to store in the database

For my own edification, can you expand on this?

I suspect with (non-standard?) SQL extensions and pl/sql functions you could add enough validation that is otherwise inexpressible (in standard SQL). But then you have to replicate some of that logic in your application code and the database. Anyways, let's start with something simple: sumtypes. While simple sumtypes could be described using enums, what if the alternatives have more data attached to them?

    data User = Anonymous | Registered UserData
    data UserData = UserData
      { userName    :: UserName
      , registredAt :: UTCTime
      }

Foreign keys is something that can't be easily verified in Haskell code. So that's something where a proper database still is useful.

You will have to educate yourself on Haskell's type system and type category theory in general. The bottom line is that you can be very, very expressive using higher level types that simply is not possible in any database at all.

Personally I like foreign keys, a lot, and couldnt live without them. I dont think Haskell's type system would be a replacement to that, and would rather use a hybrid of those two.

You don't need to learn anything about category theory to use Haskell well.

Good to see a complaint about NoSQL that is at least targeted in a reasonable direction. Personally I see a lot of success coming from flexible document schemas but obviously it's not perfect for every app.

What did you think about Domino replication?

I have used it extensively. It's brittle. It's brilliant.

The way Domino works at a high level is that you have a "data" and "design" template applied to a database. A database would be a single file on disk, and it's something like a table (or a set of related tables) and various views attached to that data.

We had an internal Domino server with a set of internally focused 'design' templates applied to them. We used the notes clients, and our analysts had their workflow applications there. The design of these databases was focused on serving content to the notes clients - not to a web browser.

We replicated the data from those databases to our staging and development machines. Those staging and development machines had their own designs - which was the web-centric, customer focused design. There was a minimum amount of information available to you in the notes clients - data at this point was meant to be seen in a browser.

Those staging machines pushed the data on to our final production cluster. Every 15 minutes something was pushed. We'd go from internal to dev/stage 15 after the hour. 30 minutes after the hour the data from stage would go to production.

We used normal build/release tools control the flow of design template replicas making their way out of their playgrounds, but sometimes it happened. I say it's brittle because if you accidentally push design to the wrong place you could grenade a lot of stuff. More than once we accidentally flipped the switch to push design changes to development.

Here's a link to a presentation of the last thing I was a part of in the Lotus community. I'm pretty proud of the site we put together. It had a pretty nice feature set for the time period. This was put together by a coworker. https://www.youtube.com/watch?list=PL6D93ED85F970BAE6&v=v9IU...

Looks like a lot of requirements added over time.

Hmm. Interesting anecdote. I've spent a couple of decades in and around relational databases. They tend to be half-denormalized crufty beasts too.

Data integrity is less of a technology problem than people make out.

One of the reasons I like Postgres having JSON. In that experimental phase, make up a schema. Then solidify it.

I love me some document based databases. But I find it ridiculous how much FUD there is out there about them. After being severely burned by some "It's not fully ACID" types in my organization I am seriously reluctant to even use them on projects where they are ideally suited. Sorry for the rant. I think they are wonderful tools, but I also think people should know about the political implications of using them in organizations where 'old fashioned' or conservative thinking might come in and ruin their day.

The ACID vs non ACID all comes down to: can you afford to have invalid/missing data in your program?

The answer to this question really depends on the project you are working on.

If you think you don't need ACID even though your data is very important to you then I recommend you to read some great Jepsen articles series by Kyle Kingsbury[1][2] showing how popular distributed systems lose data on during network partition. If you don't believe it you can download the tests and run them yourself. Overall it's a very good read especially since he also tries to explain why they fail.

[1] https://aphyr.com/posts/281-call-me-maybe-carly-rae-jepsen-a...

[2] https://aphyr.com/tags/jepsen

I remember a comment on HN sometime in the last month [1] talking about how even an ACID-compliant RDBMS can be rendered non-ACID, through configuration or real-world usage (replication?). Do you agree?

[1] Unfortunately, searching hasn't turned up the comment... yet.

Yep. In Postgres for example:

* A - you have the ability to not use transactions (technically it still implicitly uses transactions per statement, but that's kind of expected behavior of the NoSQL databases).

* C - simply don't use rules such as constraints, triggers etc.

* I - reduce isolation level of the transaction. Postgres due to its design doesn't allow you to have dirty reads (ability to read from concurrent uncommitted transaction)

* D - there are several settings (e.g. disable sync, wal etc, create an unlogged table) which increase performance at the cost of potentially losing your data. You will also reduce it when you use asynchronous replication.

The ACID is a feature that is generally desired when your data is important to you and you also want to be certain that the data is always consistent at any point in time. It's quite hard to implement it right, so it should not be treated like a bug.

Generally you should think of RDBMS as general purpose databases that have strong consistency guarantees, and the NoSQL as a specialized databases which no longer provide ACID guarantees in order to tackle specific domain.

There are also NoSQL databases which try to be general purpose, such as MongoDB. Those are joke. MongoDB for example currently not only is much slower than Postgres[1] it also doesn't horizontally scale[2] on top of that you don't even have ACID guarantees so you're getting the worst out of both worlds.

[1] http://www.enterprisedb.com/postgres-plus-edb-blog/marc-lins...

[2] https://www.datastax.com/wp-content/uploads/2013/02/WP-Bench...

If people in your organization are concerned about document-based databases because "they're not fully ACID", they're right. If ACID guarantees are important for your use case, then that automatically nullifies all the other hypothetical advantages of NoSQL databases.

I'm not sure the level of FUD is unjustified.

I've seen far, far more cases of document databases being used in situations that are entirely inappropriate, than in situations where they are ideally suited.

I'd argue that the situations in which it's a good choice to use a document database are pretty rare, and that you're almost always better off using a bog-standard relational database.

I think many problems could be solved equally well using either technology IF you understand the strengths and weaknesses of each technology and implement them appropriately. It just so happens that knowledge of how to use a RDBMS appropriately is MUCH more common than document databases.

With the rise of realtime applications I'd argue that the situations for using NoSQL for the common project are growing.

I think the author of the post has made a good point, that many seem to forget - it is always a trade-off whichever tool you use.

With the rise of realtime applications

What does "realtime" have to do with the datastore?

The usage and access of data in your data store when building a realtime application. If you were building a chat-app these days you are given a number of very easy to adopt (minimal complexity/edge cases) NoSQL solutions - Firebase, Parse, RethinkDB.

Whereas with SQL you have the option of using cache layers (which you could say is NoSQL anyway), message queues, naive timely polling and log monitoring.

With NoSQL the solutions are just simpler. For example Firebase has a tutorial for creating a real-time chat [1] - it takes 5 minutes.

[1] https://www.firebase.com/tutorial/#example/chat

Some of these examples are not equivalent. For example,

    r
      .db('dragonball')
      .table('characters')
      .filter(function(row){
        return row('maxStrength').gt(700000).and(row('species').contains('Saiyan'));
      })
      .orderBy(r.desc('maxStrength'));

presumably returns one row per character, whereas

    SELECT c.* 
    FROM characters c
    INNER JOIN character_species cs ON c.id = cs.character_id
    INNER JOIN species s ON cs.species_id = s.id
    WHERE max_strength > 700000
    AND s.name = 'Saiyan'
    ORDER BY max_strength DESC;

returns at least one row per character. Rather, the equivalent SQL (depending on what syntax is available) might be:

    SELECT c.* 
    FROM characters c
    WHERE max_strength > 700000
    AND EXISTS (SELECT NULL
                FROM character_species cs
                INNER JOIN species s ON cs.species_id = s.id
                WHERE c.id = cs.character_id
                AND s.name = 'Saiyan')
    ORDER BY max_strength DESC;

The above returns one row per character, no matter how many matching entries there are in `character_species` or `species`.

Pursuing this a little further then, suppose you wanted to expand your search to include humans and androids. In SQL, only a slight modification is needed:

    SELECT c.* 
    FROM characters c
    WHERE max_strength > 700000
    AND EXISTS (SELECT NULL
                FROM character_species cs
                INNER JOIN species s ON cs.species_id = s.id
                WHERE c.id = cs.character_id
                AND s.name IN ('Saiyan', 'Human', 'Android'))
    ORDER BY max_strength DESC;

I shudder to think what that would look like in functional form. Something like this?

    r
      .db('dragonball')
      .table('characters')
      .filter(function(row){
        return row('maxStrength').gt(700000).and(row('species').contains('Saiyan')).or(row('species').contains('Human')).or(row('species').contains('Android'));
      })
      .orderBy(r.desc('maxStrength'));

But how would it know how to group the disjunctions?

In my very limited experience, examples like these always look so tantalizing until you start taking them a little further and then you quickly realize there's no such thing as a free lunch. In this case, you pay for the flexibility of RethinkDB with the expressiveness SQL.

The RethinkDB query language can be very expressive.

What you shudder to think of could be expressed as

    row('species').contains(function(s){ r.expr(['Saiyan', 'Human', 'Android']).contains(s) })

Or also as:

    row('species').setIntersection(['Saiyan', 'Human', 'Android']).isEmpty().not()

The succintness of ReQL depends on that of the host language. JavaScript's bulky syntax for functions and lack of operator overloading make queries a lot more cluttered.

Untested, but also something like this should work:

    r.expr(['Saiyan', 'Human', 'Android']).contains(row('specias'))

I still struggle for why someone would go RethinkDB over Hardoop. If you have the "Big Data" issue for using a Non-SQL DB (Another struggle of mine to find a clear case for its use).

Why go RethinkDB when you limit what you can do with your data at the start. You can't do so things but the big one for me is you really can't do statistical analysis (Author pointed it out in the article). Why have a Non-SQL that really hinders the biggest selling point for the non-SQL DB? Wouldn't it just be better to use a SQL and expand the scheme horizontally???

Serious question that I don't understand.

RethinkDB and Hadoop solve fundamentally different use cases.

RethinkDB is designed for building scalable, realtime apps -- the query language, realtime push features (e.g. subscribing to queries), and clustering are purpose-built to help developers build and scale realtime apps.

Hadoop is designed for distributed processing of large data sets, and is generally used for analytics workloads and data processing.

NoSQL really just means "not using SQL". A lot of projects fit under that umbrella, including graph databases, key-value stores, document databases, and analytics databases, and they all address very different use cases.

First, this is about scalability of your application and not your data analysis. If your application itself has a lot of writes, it may require to shard your SQL database. That may be easier using NoSQL.

Also, NoSQL has advantages other than scalability. RethinkDB has a more pleasant query language (arguably), shamelessness (meaning it is easy to add a column), build-in ui, ...

...and why the hell would you want to do this.

Reply on news.ycombinator.com