CockroachDB doesn't currently support joins, but it's been designed so that it can potentially support them, unlike most NoSQL databases. In particular, it supports cross-machine transactions, which are a critical building block for both correctness and performance.

For instance, suppose you want to join on a column that's not a primary key. No big deal in a typical RDBMS; just do an index lookup on the join column. But if your data is sharded across machines, that doesn't work, because you can't update the index without running the risk that readers will observe it in an inconsistent state. Unless you can do transactions, that is.

So I'm optimistic that Cockroach will get join support before too long. The necessary primitives are available, making it "merely" a matter of figuring out the engineering details.

Yes, distributed joins are well understood. But I'm not at all convinced that the relational model is a good fit for a scale-out, widely distributed, fault-tolerant database. The engineering decisions for each of these aspects add enough latency to the process as it is.

It's not a nosql database if that's what you're thinking.

https://github.com/cockroachdb/cockroach/issues/2970

Joins in distributed databases all must make some sort of unsavory tradeoff be it speed or space or limiting what you can join.

So, while any distributed database can do a join, it may not be fast or flexible enough to be worth it.

One could say exactly the same thing about joins in non-distributed databases. "You have to make some kind of tradeoff" is a platitude.

Joins in ActorDB work great. Of course that is because we use an entirely different way of making an SQL database distributed and our joins aren't actually distributed even though the database is.

Best way to solve a problem is to avoid it.

I looked at ActorDB, with the thought of using it as a sort of "Sql Enabled" version of etcd. Meaning, storing config data, mostly reads, with no big performance requirements.

But, the use case is to ensure that the config data is available on all nodes....high availability. So, for example, sharding isn't wanted or needed.

It was difficult, however, to get my arms around the whole "actor model", and understand how to use ActorDB in this relatively simple deployment.

Long story short, is there some reference, or dead simple example of a "single actor" deployment with a small number of tables?

It's no different from any other deployment really. You have a single actor type in your init.sql then when you send in queries you always specify the same actor. It's easier to explain if you tell me what you don't understand.

It's basically trying to figure out what the purpose of an "actor" is, and what it means in terms of schema design. I can't tell if I'm supposed to use multiple actors because it adds some kind of resilience, or performance, or something else?

On the surface, it seems analogous to "CREATE DATABASE" / "USE DATABASE", but then the examples show applications using multiple actors...which wouldn't be typical (one app / multiple databases). So, it seems clear the idea is multiple actors within a single app, but what drives the choice of what the actors are?

If the documentation started with some deeper explanation of actors, it might be easier to follow...as is, it jumps into actor syntax, creation, etc, without the reader really knowing what one is first.

I do get that this might be unique to me...I'm just not grocking the concept.

If you're an email host, an actor would be an email account.

If you're dropbox/evernote/wunderlist, an actor would be a user account.

If you're a payment processor, every payment would be an actor.

If you're a messaging app, an actor would again be a user. If you have user groups, then every group would also be an actor.

For your use case. An actor would be configuration for an app. If you have multiple apps (or services), they would have their own actors.

You are right. And without data and performance numbers there is no way to extrapolate beyond "potential" scenarios and outcomes.

Please forgive me, but what are you really trying to get at? Otherwise it just seems like naysaying and rabble rousing to me..

(employee/founder here)

Re joins: we have top men working on it right now. Joins are definitely an interesting problem for a distributed database.

Hi Peter, sounds pretty awesome! Quick question though - on your front page you say that CockroachDB does SQL - but if it can't do a join, then how can you say it uses SQL? Or is distributed SQL a different thing entirely? It does sounds like a very limited SQL subset though... I'm sure I must be missing something as I'm not familiar with your product.

Also, what levels of isolation do you actually offer? Serialized snapshot isolation appears to be MVCC, but I see you also have just snapshot isolation - what is the difference?

Edit: oh brother, the proof you link to, I just realized I bought that book some time ago and never got around to reading it... Transactional Information Systems by Weikum & Vossen, right? time for me to hit the books I guess. Still trying to get my head around that second graph you have drawn, can't work out how you have gotten it :(

SQL is not a single language but rather a family of languages. Each RDBMS that advertises support for SQL ends up implementing a different flavor of SQL, most of the times they are not even compatible :)

For now CockroachDB's supports a subset of the SQL implemented by other databases, with some extensions of its own. This subset will grow over time.

The two levels of isolation offered are snapshot (SI) and serializable (SSI). Snapshot means that concurrent transactions are atomic with regards to each other and "see" the same initial state from the DB. Serializable adds into this that they can't introduce write skews, ie if there are concurrent transactions they will "see" each others effects in some order.

Hi Chris, we describe CockroachDB as a SQL database because that is what we're aspiring to. The missing functionality (i.e. joins) is on our near-term roadmap.

Sweet :-)

Joins over a distributed database aren't easy. Love to see what you have going! The main issue I see with distributed joins are that they need to be done in a single transaction - if even one table gets an insert, delete or update then it invalidated the join. But this distributed serialised snapshot isolation, that sounds like it might be the best way around it.

You may find that you will have to choose at best between two of these three: joins, speed and convenience. In other words, you won't be able to join quickly in a convenient way for the users, of if you want the user to join like he can in SQL database, it will be much slower.

It is possible to massively distribute joins but it requires a much more sophisticated database design than you see in most distributed database implementations because the whole system has to be designed for that use case e.g. support for ad hoc inter-node orchestration and data flows. At that point you basically have a parallel database instead of just a distributed one.

And if you add an additional requirement to correctly execute ad hoc consistent joins under very high write workloads and continuous failures, then it is definitely non-trivial engineering. But it is possible, I've designed implementations like this before, it just requires a very high degree of effort and skill that is rarely applied to the design of typical NoSQL databases.

Joins are coming, although they will take some time.

MemSQL does distributed joins really well, using a combination of in-memory rowstore + disk columnstore and the ability to have certain tables replicated to every node for faster joining.