11 ms·

Migrating to CockroachDB

163 points by latch 7y ago

seibelj 7y ago
I’m sorry but the name of the database needs to change. It’s like if I named my company “Disgusting Bug Company” then expected everyone to ignore the fact I named my company after a gross creature. Call it “RockSolidDB” and you get the same point across without invoking gross bugs.
- Skunkleton 7y ago
  I get your point, but I personally really like the name. It really sticks with me.
- ComputerGuru 7y ago
  I don’t know anyone except the non-engineer non-programmer management types that gives a damn what anything is called. I respect the CockroachDB team for sticking to their guns on this. Anyway, just call it CrDB, it’s an accepted alias. And everyone needs to stop complaining about the name each and every single time.
- seisvelas 7y ago
  I used to agree, but the name actually seems to have an ironic marketing benefit: Everyone ascribes Cockroach's success to its technical merit. I've never heard Cockroach criticized as another dumb web dev trend caused by marketing and fads.
  - m0zg 7y ago
    I propose that they further strengthen this effect by dropping the word "roach" from their name, and rebrand as just "Cock DB". /s
- maximilianroos 7y ago
  OTOH they monopolize the market of people who do like cockroaches
- Keverw 7y ago
  It's because cockroaches can be hard to get rid of sometimes if infested. So supposed to be named that since the goal is the database to be reliably and available with replicas that can survive failures.
- barjavel88 7y ago
  Actually I think the name is about the myth that a cockroach would survive a nuclear blast, as a metaphor for the resiliency of your cockroach database.
  - kranner 7y ago
    It is not a myth though that they have been around for 320 million years; that is resilience.
    - nishantvyas 7y ago
      What makes it funny is they can survive everything but can’t manage to turn them upside down if they get flipped... kind or ironic....
      - kranner 7y ago
        But they're resilient as a group! Unless you could flip them all at once.
- gjs278 7y ago
  I hope you never use it and your company fails. you are a disgusting normie.
- evil-olive 7y ago
  This comes up in every single thread about CockroachDB. It's beyond a dead horse at this point.
- briandilley 7y ago
  does the name... BUG you? It's a nod at resiliency, clever IMO.
- nestorD 7y ago
  I believe their choice of name is actually very clever as it sticks into your mind. If you need to list the names of the databases you know, especially if you are not an expert in the domain, and you have heard of it, it will probably be on your list just because it has a memorable name (regardless of its merits).
- taywrobel 7y ago
  Obligatory link to BikeShedDB - https://github.com/tbg/bikesheddb https://github.com/tbg/bikesheddb Problem solved!
daxfohl 7y ago
Seems like overkill, and just a shiny new toy to play with for this use case. Stackoverflow.com uses SQL. You aren't exceeding stackoverflow. In a couple of years they'll migrate back, or to something else. The whole point of cdb is to enable things like Google scale. But all I ever see on it are random devs spinning up three nodes and being so proud. Which is too bad because I'd love to see someone pushing its limits, and betting mission critical services on it that nothing else could handle.
- Keverw 7y ago
  I could see the benefits though. If you get popular, you can just throw more hardware at it instead of feeling rushed to switch databases or start sharding in the application level. One downsides I didn't like though is no incremental backups unless enterprise. There's PG dump but not sure if it'd work on a bunch of threads and stuff. If using MySQL there's the MyDumper project that looked interesting. Then there's no full text search is a downside, but I know it seems people recommend running a separate search solution anyways so that can be a little slow to get started. Easier if you could just run it in one system in the beginning, and who knows maybe search isn't a bottleneck anyways depending on the implementation of the database or you want to be able to still full text search while debugging maybe. Then no geospatial can be a downside for some types of businesses or use cases, say a place lookup, factoring it into a game matching algo, etc. I've been really interested in MongoDB lately though, but was playing with a few newer SQL based databases for what to use on my own project. Then I know some companies actually run multiple databases too, some stuff might be noSQL while other workloads are SQL databases too, but I know some rather just one a single database so only one source of truth. Then I know PostgreSQL has the idea of foreign data wrappers [0], so I guess then you could treat a MongoDB collection as a table. So maybe you had a users collection, you could join on it I think using this but never played with it. However this seems mostly Postgre itself, other Postgre compatible databases don't implement this as far as I can tell. However I believe even MongoDB with it sharding can't handle GEO unless you separate that data in a unsharded collection, which is then placed on any replica set in the cluster. So maybe you could split geo results up by city level in different collections but then if you wanted to find near by things that could go into other cities based on the radius, you'd have to write your own logic. Unsure how larger sites like say Yelp handles GEO lookups. Only big downside I see with MongoDB though is no joins, so say you had a social networking site and wanted to hide posts by users suspended from the results, you'd have to write your own logic to do so. I think it'd be so cool if you could like symlink a value from one document to another. I think OrientDB has something like that though. I was asking a friend who uses Mongo for advice, apparently in that case you'd just update every single post when you suspend that user - not sure if that's really efficient or not but I doubt a single user would have millions of posts... So probably less than 10K posts at the most maybe. However I guess with social feeds themselves, seems it recommend to fan out on write instead of fanning out on read, so in a way you can sometimes precompute things to help the database. All about making decisions and tradeoffs I guess. [0] https://wiki.postgresql.org/wiki/Foreign_data_wrappers https://wiki.postgresql.org/wiki/Foreign_data_wrappers
jturpin 7y ago
I'm glad for the honest writeup of Cockroach. I like CockroachDB, and hope they continue it in the future, but the performance and backup capabilities are something to consider. Still, I'd rather deploy it to Kubernetes whenever I can over Postgres to ensure that I can do rolling upgrades and whatever else. At least the Helm chart they provide lets you use Cert Manager with it, so the TLS isn't as much of a pain.
Nican 7y ago
High-availability is always an issue that I am concerned with self-hosting MySQL or PostgreSQL. I am always worry about the fail-over failing, in case any step in the setup was not properly configured. Let me know if someone has some magical formula for easy replication. I still find CRDB's replication interesting, since all data is replicated 3-way on every commit, and data also auto-rebalances as needed. With some exceptions, as long as you are connected to any node, you should have access to all data.
- btmiller 7y ago
  Regarding reliability of your failover process: test it regularly and have it monitored so you get alerted at the first sign of trouble.
- SteveNuts 7y ago
  We used Percona's XtraDB Cluster for HA and have had very few issues. Just fronting it with haproxy to read/write to a single node at a time (setting others to "backup") has worked well for us, since we're not doing a huge number of queries.
  - Nican 7y ago
    Thank you! That is useful information. I will take a closer look at Percona in the future.
  - muffelsong 7y ago
    I concur, but consider ProxySQL instead of HAProxy. Percona with XtraDB/Galera failover fronted by ProxySQL is very resilient and performant.
- candiddevmike 7y ago
  Is your app that critical to warrant that kind of architecture? I would wager the majority of apps people interact with use a single node database that routinely goes down for maintenance or something else.
- rbjorklin 7y ago
Rapzid 7y ago
There is no free lunch with databases. I often argue the position that master-master is overrated, because you have to design your application around the constraints to see the benefits and avoid the pitfalls. Same thing with CockroachDB Postgresql "wire protocol support". The fact that it only supports "serializable" isolation level when Postgresql utilizes Read Committed OTB should be a screaming red flag to just about anyone that you are in for some challenges on the performance front. Some well-worn modeling techniques are simply infeasible under typical workloads at that isolation level, for one. Starting with bog-standard RDBMS you have to be deliberate to get great performance. Adding in extra, fancy scaling properties means even more considerations to take into account to maintain consistency while achieving gains.
sergiotapia 7y ago
We use Elixir and Postgres at work, I didn't realize cockroachdb didn't have geospatial features, unfortunately that makes it a non-starter for us. We need it to track pathing from mobile phones (ios and android). Thank you for writing this up and putting your experiences out there, clear as day. It's very valuable.
deleted 7y ago
[deleted]
brianpgordon 7y ago
> if you want password-authentication, you must configure your cluster to communicate using certificates. You can start each node in "insecure" mode, but then can't use password authentication. There's a issue about this. Like the people posting there, our network is already secured, so this requirement is just an unnecessary nuisance. Eh, I'm not sure how I feel about this. Obviously you should secure your network as best you can, but how confident are you really that a bad actor will never find a foothold anywhere in your network? I think I would advocate for your services to all communicate securely (TLS et al), even internally, and if your database supports mutual client/server auth like it sounds like Cockroach does then you should use that as well. Particularly if you're depending on at-rest encryption handled transparently by the DBMS to protect your users' data - that won't do you much good if someone can just sniff/MITM network traffic and wait until a bunch of your data has been queried.
- kccqzy 7y ago
  I actually have a similar concern. In a previous job I tried to push for internal services communicating over TLS with a custom certificate authority. It was ultimately not adopted as too much complexity, including operationally when something goes wrong the developer should always be able to issue unencrypted requests from an internal network. It seemed to boil down to removing as many hoops as possible when something terrible happens and automated tools fail and manual recovery is needed.
- jupp0r 7y ago
  I came here to mention this, too. It's been shown from numerous leaks that defense in depth is a much more effective approach than simply relying on network security. You mention that query response data can be sniffed by an attacker in case networks are compromised. The much worse scenario is that the passwords themselves can be intercepted, giving attackers full access.
  - fulafel 7y ago
    Relying on network security is usually a bad idea even if you don't have defense in depth. (Ie it's less bad to rely on some other one thing)
pjmlp 7y ago
What new class of RDMS? Cluster scalability has always been a thing with Oracle, SQL Server, Sybase, Informix, DB2.
- klodolph 7y ago
  Scalability is relative. To some people a 10TB database is large, but some teams I work with regularly see databases in the 10PB range. Going to that kind of scale you tend to see different design tradeoffs.
  - pjmlp 7y ago
    Those kind of dataloads were already typical 15 years ago in the telecommunication domain, for example Nokia NetAct report processing for the whole operator network, based on HP-UX with Oracle OLAP support. Or the CERN infrastructure to deal with non-stop handling from accelerator data and storage for posterior data analysis. Sometimes this industry feels like a continuous reboot.
    - ed_balls 7y ago
      I'm not sure how OLAP relates to CockroachDB. The use case for CockroachDB is quite simple. You have millions of applications that runs Postgres. A large chuck of them have to process more and more data which a single master cannot handle and there is no easy way of scaling. Any option you pick does require a lot of changes to the codebase. This is were CockroachDB should come in handy. The dream is to have a drop-in replacement for Postgres that would scale, so all SELECT .. JOIN still works.
      - pjmlp 7y ago
        Because that specific example made use of Oracle distributed cluster scalability, already 15 years ago. Postgres still has a couple of tricks to learn from big boys databases.
    - klodolph 7y ago
      I was thinking of OLTP databases, with transactions, joins, random writes, and everything operating at scale. Running reports is much easier. I doubt CERN is dumping all of their experiment data into a single SQL database.
sandstrom 7y ago
The lack of useable backups in the open-source version is confounding to me. They are effectively limiting adoption of both their free and paid version. If they would have proper backups in free, more people would start using it, and then discover that they’ll need some of the premium features (data locality, Role-Based Access Control or follower reads). Also, why is there no public pricing page? How will I know that pricing won’t change every year? Or be arbitrary based on how much they can squeeze us? (less likely with public, listed prices) Why would a company with low marginal cost (software) limit their growth like this?
- irfansharif 7y ago
  CockroachCloud has public pricing. https://www.cockroachlabs.com/docs/cockroachcloud/stable/cockroachcloud-create-your-cluster.html#step-2-select-the-cloud-provider https://www.cockroachlabs.com/docs/cockroachcloud/stable/coc...
  - PudgePacket 7y ago
    Still seems quite hidden compared to the usual https://sas.io/pricing https://sas.io/pricing
- StavrosK 7y ago
  This is a problem with Postgres as well (for different reasons). I currently have a server sitting without proper DB backups (only full backups and dumps) because figuring out how Wall-E (or whatever the latest tool is) works is more effort than "here's my bucket and encryption key, please start backing everything up".
  - latch 7y ago
    FWIW, I've had a lot of success with barman (1). If you set it up using the newer streaming protocol, which doesn't require a separate ssh channel, then it's relatively straightforward to setup. https://www.pgbarman.org/ https://www.pgbarman.org/
  - candiddevmike 7y ago
arjunbajaj 7y ago
Does CockroachDB work with Ecto in Elixir? Can it be used as a drop-in replacement for PostgreSQL?
- latch 7y ago
  The Postgrex driver works with cockroach as-is, and they're open to making tweaks as necessary (I made a small patch to Postgrex to support the way cockroachdb encoded empty arrays and it was quickly accepted). I don't think Ecto works as-is, you'd have to check. For simple apps, it might be a drop-in replacement, or require very few tweaks. In addition to the issues mentioned in the OP, the cockroachdb documentation has a detailed list of differences (1). (1) https://www.cockroachlabs.com/docs/stable/detailed-sql-support.html https://www.cockroachlabs.com/docs/stable/detailed-sql-suppo...
  - arjunbajaj 7y ago
    Thanks for the quick response.
  - andreimatei1 7y ago
    Would you mind providing a link to the empty arrays patch? Is this something CRDB should fix?
    - latch 7y ago
      It was fixed in CRDB also: https://github.com/cockroachdb/cockroach/issues/42942 https://github.com/cockroachdb/cockroach/issues/42942
- rafiss 7y ago
  Our goal is to make switching over from PostgreSQL easy. For Ecto in particular, we have a tracking issue that shows the items in our backlog that would improve compatibility: https://github.com/cockroachdb/cockroach/issues/33441 https://github.com/cockroachdb/cockroach/issues/33441
johnnyAghands 7y ago
What a world of difference good tests and coverage can make. Great write up.
adevx 7y ago
I've tried CockroachDB as well as YugabyteDB. A distributed ACID transaction Database sounds like the holy grail, but it comes with a price. For CockroachDB the performance penalty was high, even though I'm sure I could have squeezed out more performance if I was to design my DDL specifically for CRDB. There was an inconsistency issue when deleting a ton of (self referencing foreign key) rows in rapid succession. The results of a "select count(*) from table" returned different results than a "select count(id) from table". After some delay the results came back in sync again. As I was trying to shoehorn an existing PostgreSQL database into CRDB, I had to find solutions for SELECT .. FOR UPDATE, finding alternatives for triggers, issues with the query builder I use (knex.js). In the end it was too much, not even considering lack of backups and the risk of a VC funded company going belly up. I do hope they gain a solid foothold and these NewSQL databases gain more traction.
- jazoom 7y ago
  After your opening sentence I kept waiting for the comparison to Yugabyte but you never mentioned it again. How did it go?
  - adevx 7y ago
    My testing of YugabyteDB was not as extensive as CockroachDB. I had issues importing my PostgreSQL schema + data (I did use ysql_dump) I got timeouts and an occasional 100% cpu crash. I was able to import after trimming the amount of data and dumbing down the schema (dropping foreign key references) CPU usage was much higher than PostgreSQL and I found the overall tooling and setup less polished compared to CockroachDB.
- irfansharif 7y ago
  We take our claims if correctness very seriously. The behavior you're describing, depending on exactly what you're doing, is likely an allowed serializable history, which is the isolation level we claim to support. See [0] for a deeper dive on the subject. Either way, you should show us what you found (if you haven't already). As for your performance analysis, do share your results and methodology. We published an in-depth, reproducible comparison with YugaByte here [1] in addition to publishing our TPCC-100k numbers[2]. If you're seeing performance that doesn't line up with the above, let us know. [0]: https://www.cockroachlabs.com/blog/consistency-model/ https://www.cockroachlabs.com/blog/consistency-model/ [1]: https://www.cockroachlabs.com/blog/unpacking-competitive-benchmarks/ https://www.cockroachlabs.com/blog/unpacking-competitive-ben... [2]: https://www.cockroachlabs.com/blog/tpcc-100k/ https://www.cockroachlabs.com/blog/tpcc-100k/
ed_balls 7y ago
Has anyone tried to run CockroachDB with GCP TrueTime?
- irfansharif 7y ago
  TrueTime is not a publicly available GCP service.
isoos 7y ago
> But going from our single PostgreSQL instance to a 3 node cluster, performance is worse. This is true even for simple queries involving 1 node (say, getting a record by id or running locally with a single node). Still a few extra milliseconds of latency is a fair price for better availability. But the performance characteristics aren't the same as a relational database. I think this should be true to any distributed database: you can't really beat an optimized single-machine software with a networked app, no matter how hard you try. My experience with CockroachDB showed that increasing the cluster size from 3 to 6 and more provided more benefit than I've expected. 1 -> 3 seems to be a meaningless metric for such setup with a replication factor of 3.
jsilence 7y ago
Anyone have experience with rqlite and how it compares to cockroachDB?

163 points by latch 7y ago

I’m sorry but the name of the database needs to change. It’s like if I named my company “Disgusting Bug Company” then expected everyone to ignore the fact I named my company after a gross creature. Call it “RockSolidDB” and you get the same point across without invoking gross bugs.

Skunkleton 7y ago
I get your point, but I personally really like the name. It really sticks with me.
ComputerGuru 7y ago
I don’t know anyone except the non-engineer non-programmer management types that gives a damn what anything is called. I respect the CockroachDB team for sticking to their guns on this. Anyway, just call it CrDB, it’s an accepted alias. And everyone needs to stop complaining about the name each and every single time.
seisvelas 7y ago
I used to agree, but the name actually seems to have an ironic marketing benefit: Everyone ascribes Cockroach's success to its technical merit. I've never heard Cockroach criticized as another dumb web dev trend caused by marketing and fads.
- m0zg 7y ago
  I propose that they further strengthen this effect by dropping the word "roach" from their name, and rebrand as just "Cock DB". /s
maximilianroos 7y ago
OTOH they monopolize the market of people who do like cockroaches
Keverw 7y ago
It's because cockroaches can be hard to get rid of sometimes if infested. So supposed to be named that since the goal is the database to be reliably and available with replicas that can survive failures.
barjavel88 7y ago
Actually I think the name is about the myth that a cockroach would survive a nuclear blast, as a metaphor for the resiliency of your cockroach database.
- kranner 7y ago
  It is not a myth though that they have been around for 320 million years; that is resilience.
  - nishantvyas 7y ago
    What makes it funny is they can survive everything but can’t manage to turn them upside down if they get flipped... kind or ironic....
    - kranner 7y ago
      But they're resilient as a group! Unless you could flip them all at once.
gjs278 7y ago
I hope you never use it and your company fails. you are a disgusting normie.
evil-olive 7y ago
This comes up in every single thread about CockroachDB. It's beyond a dead horse at this point.
briandilley 7y ago
does the name... BUG you? It's a nod at resiliency, clever IMO.
nestorD 7y ago
I believe their choice of name is actually very clever as it sticks into your mind. If you need to list the names of the databases you know, especially if you are not an expert in the domain, and you have heard of it, it will probably be on your list just because it has a memorable name (regardless of its merits).
taywrobel 7y ago
Obligatory link to BikeShedDB - https://github.com/tbg/bikesheddb https://github.com/tbg/bikesheddb Problem solved!

daxfohl 7y ago

Seems like overkill, and just a shiny new toy to play with for this use case. Stackoverflow.com uses SQL. You aren't exceeding stackoverflow. In a couple of years they'll migrate back, or to something else. The whole point of cdb is to enable things like Google scale. But all I ever see on it are random devs spinning up three nodes and being so proud. Which is too bad because I'd love to see someone pushing its limits, and betting mission critical services on it that nothing else could handle.

Keverw 7y ago
I could see the benefits though. If you get popular, you can just throw more hardware at it instead of feeling rushed to switch databases or start sharding in the application level. One downsides I didn't like though is no incremental backups unless enterprise. There's PG dump but not sure if it'd work on a bunch of threads and stuff. If using MySQL there's the MyDumper project that looked interesting. Then there's no full text search is a downside, but I know it seems people recommend running a separate search solution anyways so that can be a little slow to get started. Easier if you could just run it in one system in the beginning, and who knows maybe search isn't a bottleneck anyways depending on the implementation of the database or you want to be able to still full text search while debugging maybe. Then no geospatial can be a downside for some types of businesses or use cases, say a place lookup, factoring it into a game matching algo, etc. I've been really interested in MongoDB lately though, but was playing with a few newer SQL based databases for what to use on my own project. Then I know some companies actually run multiple databases too, some stuff might be noSQL while other workloads are SQL databases too, but I know some rather just one a single database so only one source of truth. Then I know PostgreSQL has the idea of foreign data wrappers [0], so I guess then you could treat a MongoDB collection as a table. So maybe you had a users collection, you could join on it I think using this but never played with it. However this seems mostly Postgre itself, other Postgre compatible databases don't implement this as far as I can tell. However I believe even MongoDB with it sharding can't handle GEO unless you separate that data in a unsharded collection, which is then placed on any replica set in the cluster. So maybe you could split geo results up by city level in different collections but then if you wanted to find near by things that could go into other cities based on the radius, you'd have to write your own logic. Unsure how larger sites like say Yelp handles GEO lookups. Only big downside I see with MongoDB though is no joins, so say you had a social networking site and wanted to hide posts by users suspended from the results, you'd have to write your own logic to do so. I think it'd be so cool if you could like symlink a value from one document to another. I think OrientDB has something like that though. I was asking a friend who uses Mongo for advice, apparently in that case you'd just update every single post when you suspend that user - not sure if that's really efficient or not but I doubt a single user would have millions of posts... So probably less than 10K posts at the most maybe. However I guess with social feeds themselves, seems it recommend to fan out on write instead of fanning out on read, so in a way you can sometimes precompute things to help the database. All about making decisions and tradeoffs I guess. [0] https://wiki.postgresql.org/wiki/Foreign_data_wrappers https://wiki.postgresql.org/wiki/Foreign_data_wrappers

jturpin 7y ago

I'm glad for the honest writeup of Cockroach. I like CockroachDB, and hope they continue it in the future, but the performance and backup capabilities are something to consider. Still, I'd rather deploy it to Kubernetes whenever I can over Postgres to ensure that I can do rolling upgrades and whatever else. At least the Helm chart they provide lets you use Cert Manager with it, so the TLS isn't as much of a pain.

Nican 7y ago

High-availability is always an issue that I am concerned with self-hosting MySQL or PostgreSQL. I am always worry about the fail-over failing, in case any step in the setup was not properly configured. Let me know if someone has some magical formula for easy replication. I still find CRDB's replication interesting, since all data is replicated 3-way on every commit, and data also auto-rebalances as needed. With some exceptions, as long as you are connected to any node, you should have access to all data.

btmiller 7y ago
Regarding reliability of your failover process: test it regularly and have it monitored so you get alerted at the first sign of trouble.
SteveNuts 7y ago
We used Percona's XtraDB Cluster for HA and have had very few issues. Just fronting it with haproxy to read/write to a single node at a time (setting others to "backup") has worked well for us, since we're not doing a huge number of queries.
- Nican 7y ago
  Thank you! That is useful information. I will take a closer look at Percona in the future.
- muffelsong 7y ago
  I concur, but consider ProxySQL instead of HAProxy. Percona with XtraDB/Galera failover fronted by ProxySQL is very resilient and performant.
candiddevmike 7y ago
Is your app that critical to warrant that kind of architecture? I would wager the majority of apps people interact with use a single node database that routinely goes down for maintenance or something else.
rbjorklin 7y ago

Rapzid 7y ago

There is no free lunch with databases. I often argue the position that master-master is overrated, because you have to design your application around the constraints to see the benefits and avoid the pitfalls. Same thing with CockroachDB Postgresql "wire protocol support". The fact that it only supports "serializable" isolation level when Postgresql utilizes Read Committed OTB should be a screaming red flag to just about anyone that you are in for some challenges on the performance front. Some well-worn modeling techniques are simply infeasible under typical workloads at that isolation level, for one. Starting with bog-standard RDBMS you have to be deliberate to get great performance. Adding in extra, fancy scaling properties means even more considerations to take into account to maintain consistency while achieving gains.

sergiotapia 7y ago

We use Elixir and Postgres at work, I didn't realize cockroachdb didn't have geospatial features, unfortunately that makes it a non-starter for us. We need it to track pathing from mobile phones (ios and android). Thank you for writing this up and putting your experiences out there, clear as day. It's very valuable.

deleted 7y ago

[deleted]

brianpgordon 7y ago

> if you want password-authentication, you must configure your cluster to communicate using certificates. You can start each node in "insecure" mode, but then can't use password authentication. There's a issue about this. Like the people posting there, our network is already secured, so this requirement is just an unnecessary nuisance. Eh, I'm not sure how I feel about this. Obviously you should secure your network as best you can, but how confident are you really that a bad actor will never find a foothold anywhere in your network? I think I would advocate for your services to all communicate securely (TLS et al), even internally, and if your database supports mutual client/server auth like it sounds like Cockroach does then you should use that as well. Particularly if you're depending on at-rest encryption handled transparently by the DBMS to protect your users' data - that won't do you much good if someone can just sniff/MITM network traffic and wait until a bunch of your data has been queried.

kccqzy 7y ago
I actually have a similar concern. In a previous job I tried to push for internal services communicating over TLS with a custom certificate authority. It was ultimately not adopted as too much complexity, including operationally when something goes wrong the developer should always be able to issue unencrypted requests from an internal network. It seemed to boil down to removing as many hoops as possible when something terrible happens and automated tools fail and manual recovery is needed.
jupp0r 7y ago
I came here to mention this, too. It's been shown from numerous leaks that defense in depth is a much more effective approach than simply relying on network security. You mention that query response data can be sniffed by an attacker in case networks are compromised. The much worse scenario is that the passwords themselves can be intercepted, giving attackers full access.
- fulafel 7y ago
  Relying on network security is usually a bad idea even if you don't have defense in depth. (Ie it's less bad to rely on some other one thing)

pjmlp 7y ago

What new class of RDMS? Cluster scalability has always been a thing with Oracle, SQL Server, Sybase, Informix, DB2.

klodolph 7y ago
Scalability is relative. To some people a 10TB database is large, but some teams I work with regularly see databases in the 10PB range. Going to that kind of scale you tend to see different design tradeoffs.
- pjmlp 7y ago
  Those kind of dataloads were already typical 15 years ago in the telecommunication domain, for example Nokia NetAct report processing for the whole operator network, based on HP-UX with Oracle OLAP support. Or the CERN infrastructure to deal with non-stop handling from accelerator data and storage for posterior data analysis. Sometimes this industry feels like a continuous reboot.
  - ed_balls 7y ago
    I'm not sure how OLAP relates to CockroachDB. The use case for CockroachDB is quite simple. You have millions of applications that runs Postgres. A large chuck of them have to process more and more data which a single master cannot handle and there is no easy way of scaling. Any option you pick does require a lot of changes to the codebase. This is were CockroachDB should come in handy. The dream is to have a drop-in replacement for Postgres that would scale, so all SELECT .. JOIN still works.
    - pjmlp 7y ago
      Because that specific example made use of Oracle distributed cluster scalability, already 15 years ago. Postgres still has a couple of tricks to learn from big boys databases.
  - klodolph 7y ago
    I was thinking of OLTP databases, with transactions, joins, random writes, and everything operating at scale. Running reports is much easier. I doubt CERN is dumping all of their experiment data into a single SQL database.

sandstrom 7y ago

The lack of useable backups in the open-source version is confounding to me. They are effectively limiting adoption of both their free and paid version. If they would have proper backups in free, more people would start using it, and then discover that they’ll need some of the premium features (data locality, Role-Based Access Control or follower reads). Also, why is there no public pricing page? How will I know that pricing won’t change every year? Or be arbitrary based on how much they can squeeze us? (less likely with public, listed prices) Why would a company with low marginal cost (software) limit their growth like this?

irfansharif 7y ago
CockroachCloud has public pricing. https://www.cockroachlabs.com/docs/cockroachcloud/stable/cockroachcloud-create-your-cluster.html#step-2-select-the-cloud-provider https://www.cockroachlabs.com/docs/cockroachcloud/stable/coc...
- PudgePacket 7y ago
  Still seems quite hidden compared to the usual https://sas.io/pricing https://sas.io/pricing
StavrosK 7y ago
This is a problem with Postgres as well (for different reasons). I currently have a server sitting without proper DB backups (only full backups and dumps) because figuring out how Wall-E (or whatever the latest tool is) works is more effort than "here's my bucket and encryption key, please start backing everything up".
- latch 7y ago
  FWIW, I've had a lot of success with barman (1). If you set it up using the newer streaming protocol, which doesn't require a separate ssh channel, then it's relatively straightforward to setup. https://www.pgbarman.org/ https://www.pgbarman.org/
- candiddevmike 7y ago

arjunbajaj 7y ago

Does CockroachDB work with Ecto in Elixir? Can it be used as a drop-in replacement for PostgreSQL?

latch 7y ago
The Postgrex driver works with cockroach as-is, and they're open to making tweaks as necessary (I made a small patch to Postgrex to support the way cockroachdb encoded empty arrays and it was quickly accepted). I don't think Ecto works as-is, you'd have to check. For simple apps, it might be a drop-in replacement, or require very few tweaks. In addition to the issues mentioned in the OP, the cockroachdb documentation has a detailed list of differences (1). (1) https://www.cockroachlabs.com/docs/stable/detailed-sql-support.html https://www.cockroachlabs.com/docs/stable/detailed-sql-suppo...
- arjunbajaj 7y ago
  Thanks for the quick response.
- andreimatei1 7y ago
  Would you mind providing a link to the empty arrays patch? Is this something CRDB should fix?
  - latch 7y ago
    It was fixed in CRDB also: https://github.com/cockroachdb/cockroach/issues/42942 https://github.com/cockroachdb/cockroach/issues/42942
rafiss 7y ago
Our goal is to make switching over from PostgreSQL easy. For Ecto in particular, we have a tracking issue that shows the items in our backlog that would improve compatibility: https://github.com/cockroachdb/cockroach/issues/33441 https://github.com/cockroachdb/cockroach/issues/33441

johnnyAghands 7y ago

What a world of difference good tests and coverage can make. Great write up.

adevx 7y ago

I've tried CockroachDB as well as YugabyteDB. A distributed ACID transaction Database sounds like the holy grail, but it comes with a price. For CockroachDB the performance penalty was high, even though I'm sure I could have squeezed out more performance if I was to design my DDL specifically for CRDB. There was an inconsistency issue when deleting a ton of (self referencing foreign key) rows in rapid succession. The results of a "select count(*) from table" returned different results than a "select count(id) from table". After some delay the results came back in sync again. As I was trying to shoehorn an existing PostgreSQL database into CRDB, I had to find solutions for SELECT .. FOR UPDATE, finding alternatives for triggers, issues with the query builder I use (knex.js). In the end it was too much, not even considering lack of backups and the risk of a VC funded company going belly up. I do hope they gain a solid foothold and these NewSQL databases gain more traction.

jazoom 7y ago
After your opening sentence I kept waiting for the comparison to Yugabyte but you never mentioned it again. How did it go?
- adevx 7y ago
  My testing of YugabyteDB was not as extensive as CockroachDB. I had issues importing my PostgreSQL schema + data (I did use ysql_dump) I got timeouts and an occasional 100% cpu crash. I was able to import after trimming the amount of data and dumbing down the schema (dropping foreign key references) CPU usage was much higher than PostgreSQL and I found the overall tooling and setup less polished compared to CockroachDB.
irfansharif 7y ago
We take our claims if correctness very seriously. The behavior you're describing, depending on exactly what you're doing, is likely an allowed serializable history, which is the isolation level we claim to support. See [0] for a deeper dive on the subject. Either way, you should show us what you found (if you haven't already). As for your performance analysis, do share your results and methodology. We published an in-depth, reproducible comparison with YugaByte here [1] in addition to publishing our TPCC-100k numbers[2]. If you're seeing performance that doesn't line up with the above, let us know. [0]: https://www.cockroachlabs.com/blog/consistency-model/ https://www.cockroachlabs.com/blog/consistency-model/ [1]: https://www.cockroachlabs.com/blog/unpacking-competitive-benchmarks/ https://www.cockroachlabs.com/blog/unpacking-competitive-ben... [2]: https://www.cockroachlabs.com/blog/tpcc-100k/ https://www.cockroachlabs.com/blog/tpcc-100k/

ed_balls 7y ago

Has anyone tried to run CockroachDB with GCP TrueTime?

irfansharif 7y ago
TrueTime is not a publicly available GCP service.

isoos 7y ago

> But going from our single PostgreSQL instance to a 3 node cluster, performance is worse. This is true even for simple queries involving 1 node (say, getting a record by id or running locally with a single node). Still a few extra milliseconds of latency is a fair price for better availability. But the performance characteristics aren't the same as a relational database. I think this should be true to any distributed database: you can't really beat an optimized single-machine software with a networked app, no matter how hard you try. My experience with CockroachDB showed that increasing the cluster size from 3 to 6 and more provided more benefit than I've expected. 1 -> 3 seems to be a meaningless metric for such setup with a replication factor of 3.

jsilence 7y ago

Anyone have experience with rqlite and how it compares to cockroachDB?