4 ms·

> Looking forward to when joins are implemented so I can try using it with something non-trivial. these types of databases typically don't have joins. you'll b

> Looking forward to when joins are implemented so I can try using it with something non-trivial. these types of databases typically don't have joins. you'll be waiting a while.

tyingq 10y ago
It's not a nosql database if that's what you're thinking. https://github.com/cockroachdb/cockroach/issues/2970 https://github.com/cockroachdb/cockroach/issues/2970
- andrewvc 10y ago
  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.
  - wichsen 10y ago
    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..
  - teraflop 10y ago
    One could say exactly the same thing about joins in non-distributed databases. "You have to make some kind of tradeoff" is a platitude.
  - biokoda 10y ago
    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.
    - tyingq 10y ago
      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?
      - biokoda 10y ago
        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.
        tyingq 10y ago
        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.
        biokoda 10y ago
        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.
mjibson 10y ago
Joins are coming, although they will take some time.
teraflop 10y ago
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.
- sriram_malhar 10y ago
  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.
petermattis 10y ago
(employee/founder here) Re joins: we have top men working on it right now. Joins are definitely an interesting problem for a distributed database.
- chris_wot 10y ago
  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 :(
  - knz42 10y ago
    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.
  - petermattis 10y ago
    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.
jandrewrogers 10y ago
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.
manigandham 10y ago
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.

> Looking forward to when joins are implemented so I can try using it with something non-trivial. these types of databases typically don't have joins. you'll be waiting a while.

tyingq 10y ago

It's not a nosql database if that's what you're thinking. https://github.com/cockroachdb/cockroach/issues/2970 https://github.com/cockroachdb/cockroach/issues/2970

andrewvc 10y ago
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.
- wichsen 10y ago
  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..
- teraflop 10y ago
  One could say exactly the same thing about joins in non-distributed databases. "You have to make some kind of tradeoff" is a platitude.
- biokoda 10y ago
  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.
  - tyingq 10y ago
    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?
    - biokoda 10y ago
      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.
      - tyingq 10y ago
        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.
        biokoda 10y ago
        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.

mjibson 10y ago

Joins are coming, although they will take some time.

teraflop 10y ago

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.

sriram_malhar 10y ago
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.

petermattis 10y ago

(employee/founder here) Re joins: we have top men working on it right now. Joins are definitely an interesting problem for a distributed database.

chris_wot 10y ago
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 :(
- knz42 10y ago
  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.
- petermattis 10y ago
  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.

jandrewrogers 10y ago

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.

manigandham 10y ago

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.