9 ms·

CockroachDB is getting a lot of interest these days. It has broad PGSQL language (and also wire I think) compatibility yet has a clustered peer architecture we

CockroachDB is getting a lot of interest these days. It has broad PGSQL language (and also wire I think) compatibility yet has a clustered peer architecture well suited to running in a dynamic environment like cloud or k8s. Nodes can join dynamically and it can survive them leaving dynamically as long as there's a quorum. Data is distributed across the nodes without administrator needing to make any shard rebalance type interventions. PGSQL is designed for deployment as a single server with replica servers for HA. It's not really designed for horizontal scalability like Cockroach. You can do it - the foreign data wrappers feature and table partitioning can give you poor man's scale out. Or you can use Citus which won itself a FOSS license earlier this year. And there are other Foss and proprietary approaches too. MySQL is similar - you can do it, like with their recent router feature, but it has been retrofitted, and it's not as fluid as Cockroach. IIRC MySQL router is similar in configuration to Galera - that is, a static config file containing a list of cluster members. Listen I'm sure that the design approach of Cockroach could be retrofitted to PGSQL and MySQL, but I'm pretty sure that doing a good job of it would be a lot of work. So in answer to your question, I'm not sure that there's all that much RDBMS can't be made to do. Geospatial, Graph, Timeseries, GPU acceleration. Postgres has it all and often the new stuff comes to Postgres first. By the way I love MySQL and PostgreSQL, and the amazing extensions for PGSQL make it extra awesome. Many are super mature and make pgsql perfect for many many diverse use cases. For XXL use cases though, CockroachDB is taking a very interesting new path and I think it's worth watching.

traceroute66 5y ago
> It has broad PGSQL language compatibility Depends how you define broad. :) Many key features are missing, including but not limited to: - UDFs and sprocs. - Useful datatypes such as TSTZRANGE - More limited constraints I was recently looking at Cockroach as a PGSQL replacement because of its distributed nature. But the equivalence featureset is still lagging badly, unfortunatley.
- tuckerconnelly 5y ago
  Are you still using postgres? Thinking about a similar transition, would really appreciate any shared learnings :)
  - traceroute66 5y ago
    > Are you still using postgres? At the moment yes. The final decision has not been made yet, but I think the reality is we're getting tired of evaluating all these distributed databases that claim Postgres compatibility only to find its the usual clickbait marketing speak. So the most likely outcome is we're going to stick with Postgres and give the emerging distributed tech a couple more years to pull their socks up. We're not going to go round changing all our Postgres code and schemas just to shoehorn into the limitations of a random definition of "postgres support".
- rad_gruchalski 5y ago
  May I suggest looking at YugabyteDB, which is a distributed SQL database built on actual PostgreSQL 11.2 engine? It's way ahead of CockroachDB in terms of feature support. YugabyteDB has UDFs, stored procedures, distributed transactions, the range types are working from what I can tell, at least the example from here: https://wiki.postgresql.org/wiki/Extract_days_from_range_type https://wiki.postgresql.org/wiki/Extract_days_from_range_typ... works right out of the box, just copy paste. Postgres extensions are of course working. Orafce, postgis (with extra work needed for gin indexes, afair...), custom extensions. YugabyteBD == Postgres. The query planner, analyzer and executor are all Postgres. Mind you, some features are not readily available because handling them properly in a distributed manner takes effort. Those unsupported features are disabled on the grammar level, before being worked on. But - unsupported features will not corrupt your data. Missing features are enabled very fast. For example, I have recently contributed foreign data wrapper support: https://github.com/yugabyte/yugabyte-db/pull/9650 https://github.com/yugabyte/yugabyte-db/pull/9650 (enables postgres_fdw at a usable level) and working on table inheritance now. Yugabyte is an amazing bit of technology and more people should know about it. By the way - it's Apache 2 with no strings attached. For clarification: I'm not associated with Yugabyte in any way other than very happy user and contributor. Yugabyte organizes the Distributed SQL Summit later this month: https://distributedsql.org/ https://distributedsql.org/. Might be a good opportunity to learn more about it. Second clarification: I'll be speaking at the event.
why-el 5y ago
I don't know if anybody from CockroachDB is reading this but their article[1] on serializable transactions is somewhat questionable, as it compares CockroachDB's Serializable to Postgres's Read Committed, which seems to imply CockroachDB is better. Of course, Postgres has serializable as well. The only novelty in the article is that Cockroach DB runs Serializable by default, so I am not sure what that comparison was doing. [1] https://www.cockroachlabs.com/docs/stable/demo-serializable.html https://www.cockroachlabs.com/docs/stable/demo-serializable....
- ummonk 5y ago
  I don't get that implication from the article at all. What I do get is that the only transaction isolation level available under CockroachDB is serializable. And to show why serializable is valuable, they have to demonstrate using Postgres' default of read committed (because they would be unable to demonstrate this using CockroachDB which doesn't allow any other transaction isolation levels.
  - why-el 5y ago
    Ah, fair reading, thanks. As Postgres supports that level of isolation as well, I was thrown off a bit. but your phrasing makes sense.
- AdamProut 5y ago
  I don't think CockroachDB supports anything other then serializable isolation level, so of course they are going to be pushing this type of argument. Its an interesting philosophy really. It would be one thing if the performance penalty of running all transactions at serializable isolation level vs weaker isolation levels was reasonably low, but its not (see [1] - yes I know the entire difference in performance here is not due to CDB using serializable transactions for everything, but I'm guessing a very large part of it is). Its kind of like saying the only cars in the world should be ford pintos because we don't trust folks to safely drive faster cars. [1] https://www.scylladb.com/2021/01/21/cockroachdb-vs-scylla-benchmark/ https://www.scylladb.com/2021/01/21/cockroachdb-vs-scylla-be...
mirekrusin 5y ago
It always rubs me the wrong way - all those paxos/raft approaches (which are great, but...) simply elect the leader to pick writes. In that sense there is no distribution of computation at all. It's still single target that has to cruch through updates. Replication is just for reads. Are we going to have something better anytime soon? Like real distribution, when you add more servers writes distribute as well?
- riku_iki 5y ago
  data is also sharded in those databases
  - mirekrusin 5y ago
    true, thanks for pointing out, but this is a bit cheating isn't it? ie. atomic transactions cross shards flip over, right? it's basically ergonomic equivalent of just using multiple databases?
    - eis 5y ago
      Not 100% sure what you mean by flipping over (fail?) but at least in CRDB you can of course do cross shard transactions. They wont be as fast as a transaction that can be completely handled by a single leader but it works fine and is transparent to the client.
    - radicalbyte 5y ago
      You move over into the world of distributed transactions, which can be really expensive. Thankfully sharding works great for a large number of applications (or in other cases you can accept eventual consistency).
    - GeneralMayhem 5y ago
      Cross-shard transactions will use some sort of two-phase commit protocol internally. They still work just fine (the extra complexity is transparent to the client), but with somewhat worse performance. Most of the difficulty/expertise involved in using such systems optimally is in designing your schema and access patterns such that you minimize the number of cross-shard transactions.
alberth 5y ago
Sounds like Mnesia, which has existed for 20+ years. https://erlang.org/doc/man/mnesia.html https://erlang.org/doc/man/mnesia.html
- rad_gruchalski 5y ago
  That’s a huge oversimplification. Mnesia is distributed but that’s about it. There’s so much more to sql than selecting data. Transactions, user defined functions, stored procedures, custom types…
  - alberth 5y ago
    None of what you detail though is included in what the GP describes as a modern database. I’m not trying to nitpick but what GP describes aligns to what Mnesia provides.
    - rad_gruchalski 5y ago
      The GP mentioned CockroachDB though. There’s a lot of implicit functionality behind that name. Even just behind mentioning Postgres. The distributed part of modern sql is fairly recent.
- ramchip 5y ago
  Mnesia isn't SQL-compatible, can't scale horizontally (each node has a full copy of the DB + writes hit all nodes), needs external intervention to recover from netsplits, and expects a static cluster. It's pretty much the opposite of what the parent described IMHO. It's meant more like a configuration store than a proper DB. That's how RabbitMQ uses it for instance, it stores metadata in Mnesia and messages in a separate store, but they're working on replacing Mnesia with their own more modern store based on Raft.
  - toast0 5y ago
    > Mnesia isn't SQL-compatible, I think this is accurate. There are some references to a masters project to do SQL with mnesia, but afaik, it's not supported or used by anyone. > can't scale horizontally (each node has a full copy of the DB + writes hit all nodes), This isn't accurate, each table (or fragment, if you use mnesia_frag) can have a different node list; the schema table does need to be on all the nodes, but hopefully doesn't have a lot of updates (if it does need a lot of updates, probably figure something else out) > needs external intervention to recover from netsplits, This is true; but splits and joijs are hookable, so you can do whatever you think is right. I think the OTP/Ericsson team uses mnesia in a pretty limited setting of redundant nodes in the same chasis, so netsplit is pretty catastrophic and they don't handle it. Different teams and tables have different needs, but it would probably be nice to have good options to choose from. There's also no built-in concept of logging changes for a split node to replay later, which makes it harder to do the right thing, even if you only did quorum writes. > and expects a static cluster. It's certainly easier to use with a static cluster, especially since schema changes can be slow[1], but you can automate schema changes to match your cluster changes. Depending on how dynamic your cluster is, it might be appropriate to have a static config defined based on 'virtual' node names, so TableX is on db101, but db101 is a name that could be applied to different physical nodes depending on whatever (but hopefully only one node at a time or you'll have a lot of confusion). [1] The schema changes themselves are generally not actually slow, especially if it's just saying which node gets a copy, ignoring the part where the node actually gets the copy which could take a while for large enough tables or slow enough networks; it's that mnesia won't change the schema until all of the nodes are not currently doing a 'table dump' where up to date tables are written to disk so table change logs on disk can be cleared and those dumps can take a while to complete and any pending schema operations need to wait.
dvtkrlbs 5y ago
Not a database expert but here is my thoughts after using Cockroach DB for a mid sized: - Single cluster mode is really nice for local deployment - WebUI is a cool idea but I wish it had more info - The biggest problem is Postgres compatibility there are some quirks that was annoying examples being. The default integer types having different sizes, cockroachdb having a really nice if not exists clause for create type but having no way to maintain a Postgres compatible way of doing it (i think this one is on postgres only being able to do it with plsql scripts is cumbersome) - For me the neutral one. IT HAS ZERO DOWNTIME SCHEMA CHANGES. But if you are just coming from Postgres and just using a regular migration tool and transactions and schema changes having serious limitations and could end up your database in inconsistent state is scary. (Docs really document the behavior but still I would expect a runtime warning for it.
- Supermancho 5y ago
  CDB was great until we started doing table creation on the minute and hundreds of inserts on those tables. When we tried to drop tables on a schedule, CDB never could catch up and would generally just crash (3 nodes). I really don't like the magic you have to do with CDB for things that your commodity DBs can be expected do.
  - vvern 5y ago
    CockroachDB dev here. We've gotten a good bit better in the last few versions in terms of schema change stability. We're still not very good at large schemas with more than 10s of thousands of tables but we've got projects under way to fix that which I expect will be in the release in the spring of 22. I'd like to hear more about the magic.
    - Aeolun 5y ago
      To be fair, while I kind of agree the system should be able to handle it regardless, 10,000s of tables sounds outside the realm of 99.99% of all use cases.
      - 5y ago
someelephant 5y ago
Make sure you thoroughly test your multi-region deploys. Last time we tried the system was not stable when a region went down. Also beware of this anti pattern: https://www.cockroachlabs.com/docs/stable/topology-patterns.html#anti-patterns https://www.cockroachlabs.com/docs/stable/topology-patterns....
- bogomipz 5y ago
  Interesting. So you followed the advice of deploying to greater than two regions and your DB didn't survive when you lost a region or you only deployed to two and got bit by the anti-pattern?
  - someelephant 5y ago
    Even with >2 when one goes down CDB performed poorly.
sandstrom 5y ago
I agree, CockroachDB is overall very good! A few things I'm missing from it though: - Advisory Locks (only exist as a shim/no-op today) - LISTEN/NOTIFY - CTEs (Common Table Expressions) - Introspection and management features (for example pg_locks, pg_stat_activity, pg_backend_pid(), pg_terminate_backend()) They are making good progress though, and more recently they've spent effort on making adapters for well, e.g. their ActiveRecord adapter. I think this is clever, since the deciding factor for many companies will simply be "Can I easily replace this with PostgreSQL in my Rails/Django/etc. project?"

traceroute66 5y ago

> It has broad PGSQL language compatibility Depends how you define broad. :) Many key features are missing, including but not limited to: - UDFs and sprocs. - Useful datatypes such as TSTZRANGE - More limited constraints I was recently looking at Cockroach as a PGSQL replacement because of its distributed nature. But the equivalence featureset is still lagging badly, unfortunatley.

tuckerconnelly 5y ago
Are you still using postgres? Thinking about a similar transition, would really appreciate any shared learnings :)
- traceroute66 5y ago
  > Are you still using postgres? At the moment yes. The final decision has not been made yet, but I think the reality is we're getting tired of evaluating all these distributed databases that claim Postgres compatibility only to find its the usual clickbait marketing speak. So the most likely outcome is we're going to stick with Postgres and give the emerging distributed tech a couple more years to pull their socks up. We're not going to go round changing all our Postgres code and schemas just to shoehorn into the limitations of a random definition of "postgres support".
rad_gruchalski 5y ago
May I suggest looking at YugabyteDB, which is a distributed SQL database built on actual PostgreSQL 11.2 engine? It's way ahead of CockroachDB in terms of feature support. YugabyteDB has UDFs, stored procedures, distributed transactions, the range types are working from what I can tell, at least the example from here: https://wiki.postgresql.org/wiki/Extract_days_from_range_type https://wiki.postgresql.org/wiki/Extract_days_from_range_typ... works right out of the box, just copy paste. Postgres extensions are of course working. Orafce, postgis (with extra work needed for gin indexes, afair...), custom extensions. YugabyteBD == Postgres. The query planner, analyzer and executor are all Postgres. Mind you, some features are not readily available because handling them properly in a distributed manner takes effort. Those unsupported features are disabled on the grammar level, before being worked on. But - unsupported features will not corrupt your data. Missing features are enabled very fast. For example, I have recently contributed foreign data wrapper support: https://github.com/yugabyte/yugabyte-db/pull/9650 https://github.com/yugabyte/yugabyte-db/pull/9650 (enables postgres_fdw at a usable level) and working on table inheritance now. Yugabyte is an amazing bit of technology and more people should know about it. By the way - it's Apache 2 with no strings attached. For clarification: I'm not associated with Yugabyte in any way other than very happy user and contributor. Yugabyte organizes the Distributed SQL Summit later this month: https://distributedsql.org/ https://distributedsql.org/. Might be a good opportunity to learn more about it. Second clarification: I'll be speaking at the event.

why-el 5y ago

I don't know if anybody from CockroachDB is reading this but their article[1] on serializable transactions is somewhat questionable, as it compares CockroachDB's Serializable to Postgres's Read Committed, which seems to imply CockroachDB is better. Of course, Postgres has serializable as well. The only novelty in the article is that Cockroach DB runs Serializable by default, so I am not sure what that comparison was doing. [1] https://www.cockroachlabs.com/docs/stable/demo-serializable.html https://www.cockroachlabs.com/docs/stable/demo-serializable....

ummonk 5y ago
I don't get that implication from the article at all. What I do get is that the only transaction isolation level available under CockroachDB is serializable. And to show why serializable is valuable, they have to demonstrate using Postgres' default of read committed (because they would be unable to demonstrate this using CockroachDB which doesn't allow any other transaction isolation levels.
- why-el 5y ago
  Ah, fair reading, thanks. As Postgres supports that level of isolation as well, I was thrown off a bit. but your phrasing makes sense.
AdamProut 5y ago
I don't think CockroachDB supports anything other then serializable isolation level, so of course they are going to be pushing this type of argument. Its an interesting philosophy really. It would be one thing if the performance penalty of running all transactions at serializable isolation level vs weaker isolation levels was reasonably low, but its not (see [1] - yes I know the entire difference in performance here is not due to CDB using serializable transactions for everything, but I'm guessing a very large part of it is). Its kind of like saying the only cars in the world should be ford pintos because we don't trust folks to safely drive faster cars. [1] https://www.scylladb.com/2021/01/21/cockroachdb-vs-scylla-benchmark/ https://www.scylladb.com/2021/01/21/cockroachdb-vs-scylla-be...

mirekrusin 5y ago

It always rubs me the wrong way - all those paxos/raft approaches (which are great, but...) simply elect the leader to pick writes. In that sense there is no distribution of computation at all. It's still single target that has to cruch through updates. Replication is just for reads. Are we going to have something better anytime soon? Like real distribution, when you add more servers writes distribute as well?

riku_iki 5y ago
data is also sharded in those databases
- mirekrusin 5y ago
  true, thanks for pointing out, but this is a bit cheating isn't it? ie. atomic transactions cross shards flip over, right? it's basically ergonomic equivalent of just using multiple databases?
  - eis 5y ago
    Not 100% sure what you mean by flipping over (fail?) but at least in CRDB you can of course do cross shard transactions. They wont be as fast as a transaction that can be completely handled by a single leader but it works fine and is transparent to the client.
  - radicalbyte 5y ago
    You move over into the world of distributed transactions, which can be really expensive. Thankfully sharding works great for a large number of applications (or in other cases you can accept eventual consistency).
  - GeneralMayhem 5y ago
    Cross-shard transactions will use some sort of two-phase commit protocol internally. They still work just fine (the extra complexity is transparent to the client), but with somewhat worse performance. Most of the difficulty/expertise involved in using such systems optimally is in designing your schema and access patterns such that you minimize the number of cross-shard transactions.

alberth 5y ago

Sounds like Mnesia, which has existed for 20+ years. https://erlang.org/doc/man/mnesia.html https://erlang.org/doc/man/mnesia.html

rad_gruchalski 5y ago
That’s a huge oversimplification. Mnesia is distributed but that’s about it. There’s so much more to sql than selecting data. Transactions, user defined functions, stored procedures, custom types…
- alberth 5y ago
  None of what you detail though is included in what the GP describes as a modern database. I’m not trying to nitpick but what GP describes aligns to what Mnesia provides.
  - rad_gruchalski 5y ago
    The GP mentioned CockroachDB though. There’s a lot of implicit functionality behind that name. Even just behind mentioning Postgres. The distributed part of modern sql is fairly recent.
ramchip 5y ago
Mnesia isn't SQL-compatible, can't scale horizontally (each node has a full copy of the DB + writes hit all nodes), needs external intervention to recover from netsplits, and expects a static cluster. It's pretty much the opposite of what the parent described IMHO. It's meant more like a configuration store than a proper DB. That's how RabbitMQ uses it for instance, it stores metadata in Mnesia and messages in a separate store, but they're working on replacing Mnesia with their own more modern store based on Raft.
- toast0 5y ago
  > Mnesia isn't SQL-compatible, I think this is accurate. There are some references to a masters project to do SQL with mnesia, but afaik, it's not supported or used by anyone. > can't scale horizontally (each node has a full copy of the DB + writes hit all nodes), This isn't accurate, each table (or fragment, if you use mnesia_frag) can have a different node list; the schema table does need to be on all the nodes, but hopefully doesn't have a lot of updates (if it does need a lot of updates, probably figure something else out) > needs external intervention to recover from netsplits, This is true; but splits and joijs are hookable, so you can do whatever you think is right. I think the OTP/Ericsson team uses mnesia in a pretty limited setting of redundant nodes in the same chasis, so netsplit is pretty catastrophic and they don't handle it. Different teams and tables have different needs, but it would probably be nice to have good options to choose from. There's also no built-in concept of logging changes for a split node to replay later, which makes it harder to do the right thing, even if you only did quorum writes. > and expects a static cluster. It's certainly easier to use with a static cluster, especially since schema changes can be slow[1], but you can automate schema changes to match your cluster changes. Depending on how dynamic your cluster is, it might be appropriate to have a static config defined based on 'virtual' node names, so TableX is on db101, but db101 is a name that could be applied to different physical nodes depending on whatever (but hopefully only one node at a time or you'll have a lot of confusion). [1] The schema changes themselves are generally not actually slow, especially if it's just saying which node gets a copy, ignoring the part where the node actually gets the copy which could take a while for large enough tables or slow enough networks; it's that mnesia won't change the schema until all of the nodes are not currently doing a 'table dump' where up to date tables are written to disk so table change logs on disk can be cleared and those dumps can take a while to complete and any pending schema operations need to wait.

dvtkrlbs 5y ago

Not a database expert but here is my thoughts after using Cockroach DB for a mid sized: - Single cluster mode is really nice for local deployment - WebUI is a cool idea but I wish it had more info - The biggest problem is Postgres compatibility there are some quirks that was annoying examples being. The default integer types having different sizes, cockroachdb having a really nice if not exists clause for create type but having no way to maintain a Postgres compatible way of doing it (i think this one is on postgres only being able to do it with plsql scripts is cumbersome) - For me the neutral one. IT HAS ZERO DOWNTIME SCHEMA CHANGES. But if you are just coming from Postgres and just using a regular migration tool and transactions and schema changes having serious limitations and could end up your database in inconsistent state is scary. (Docs really document the behavior but still I would expect a runtime warning for it.

Supermancho 5y ago
CDB was great until we started doing table creation on the minute and hundreds of inserts on those tables. When we tried to drop tables on a schedule, CDB never could catch up and would generally just crash (3 nodes). I really don't like the magic you have to do with CDB for things that your commodity DBs can be expected do.
- vvern 5y ago
  CockroachDB dev here. We've gotten a good bit better in the last few versions in terms of schema change stability. We're still not very good at large schemas with more than 10s of thousands of tables but we've got projects under way to fix that which I expect will be in the release in the spring of 22. I'd like to hear more about the magic.
  - Aeolun 5y ago
    To be fair, while I kind of agree the system should be able to handle it regardless, 10,000s of tables sounds outside the realm of 99.99% of all use cases.
    - 5y ago

someelephant 5y ago

Make sure you thoroughly test your multi-region deploys. Last time we tried the system was not stable when a region went down. Also beware of this anti pattern: https://www.cockroachlabs.com/docs/stable/topology-patterns.html#anti-patterns https://www.cockroachlabs.com/docs/stable/topology-patterns....

bogomipz 5y ago
Interesting. So you followed the advice of deploying to greater than two regions and your DB didn't survive when you lost a region or you only deployed to two and got bit by the anti-pattern?
- someelephant 5y ago
  Even with >2 when one goes down CDB performed poorly.

sandstrom 5y ago

I agree, CockroachDB is overall very good! A few things I'm missing from it though: - Advisory Locks (only exist as a shim/no-op today) - LISTEN/NOTIFY - CTEs (Common Table Expressions) - Introspection and management features (for example pg_locks, pg_stat_activity, pg_backend_pid(), pg_terminate_backend()) They are making good progress though, and more recently they've spent effort on making adapters for well, e.g. their ActiveRecord adapter. I think this is clever, since the deciding factor for many companies will simply be "Can I easily replace this with PostgreSQL in my Rails/Django/etc. project?"