We recently migrated a few small systems to CockroachDB (as a stepping stone). Overall, the experience was positive. The hassle free HA is a huge peace of mind.

I know people say this is easy to do in PG. I have recently setup 2ndQuadrant's pglogical for another system. That was also easy (though the documentation was pretty bad). The end result is quite different though and CockroachDB is just simpler to reason about and manage and, I think, more generally applicable.

But, on top of the well-documented postgresql incompatibilities, we have run into other issues.

First, and probably the reason you should stay away from CockroachDB's community edition, is the poor story around backups. You only get full backups. They are very slow to take and very slow to restore. There are even cases where it can fail to backup (https://github.com/cockroachdb/cockroach/issues/28948).

There are a lot of little bugs, which isn't a huge deal, but it's hard to find and you can spend a lot of time looking into it. Just yesterday we noticed that queries that use now() can't use indexes. Searching github's thousands of issues for "now() index performance" I get ~500 hits. Thankfully they are fast to respond to questions on slack / forums. But they _really_ need to clean up the hundreds (thousands?) of little bugs - some of them have been around for years. Most of them are near-non-issues, except for the time you waste looking into it.

Finally, performance seems worse than it could be. Running in single-node-mode with an _in memory_ database (for our integration tests) is still slower than postgresql. Restoring a 150mb dump file (just a bunch of inserts) takes over 2 minutes. But it's more than fine for the CRUD kind of thing we're doing.

> Running in single-node-mode with an _in memory_ database (for our integration tests) is still slower than postgresql.

Can you share your numbers to have some kind of order of magnitude for this difference?

This has been pretty much my experience as well.

The killer features are definitely the HA and the region-based sharding, but the backup situation and bugs are a real bummer. Not a total dealbreaker, but teetering right on the edge.

We are looking closely at moving to FoundationDB at some point.

> We are looking closely at moving to FoundationDB at some point.

I'm curious to better understand the use case where this change makes sense.

CockroachDB does a lot more than FoundationDB. FoundationDB is pretty much just a KV store. There are layers out there but they IIUC they have immature drivers. Have you had success developing applications on top of any of them?

Do features like secondary indexes, joins, or constraints matter for your use case?

A big thing cockroach does that foundation doesn't help with at all is contention. If transactions contend in foundation, they will fail with retriable errors at commit time. The process of coordinating concurrent read-write and write-write interactions fall onto the layer implementer. This is a huge burden. Cockroach has an increasingly advanced story around concurrency control. It's a problem which should not be understated.

Other important properties are:

  * Rolling upgrades
  * Long-running transactions (they are starting to have a story about read-only transaction with the new storage engine)
  * Online schema changes coordinated by the system.
  * FoundationDB has a nascent story around multi-region which seems doomed to poor performance and even then only exists to provide higher availability at the cost of synchronous replication (i.e. there is no geo-partitioning).

I don't want to sound overly critical about FoundationDB. It's a very cool piece of software for a single-region, scale-out, consistent KV that, for right use-case, which is low-contention or externally coordinated, can probably deliver a high degree of predictability.

edit: I work on CockroachDB.

Perhaps the blog post from the company is a better link

https://www.cockroachlabs.com/blog/cockroach-labs-raises-87-...

At my last company, we had a great experience using postgresql flavored Aurora, especially because we could use recursive CTEs to do deep traversal of graphs.

Since CockroachDB is SQL, what would be the benefit over using Aurora?

Afaik, Postgres flavored Aurora has a single master for writes and doesn't allow horizontally scaling writes[1]. I believe reads can scale by bringing up multiple read replicas. Cockroach DB is "distributed SQL" so writes are also scaled across multiple nodes.

You can see some details about how this affects write perf in this blog by another distributed SQL database which compares the three databases (YugabyteDB, Aurora and CockroachDB) - https://blog.yugabyte.com/comparing-distributed-sql-performa...

[1] There is also multi-master Aurora (but based on https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide... that only supports 2 read-write masters and has other limitations).

Being able to run it yourself is a big thing. You can also run one cluster across multiple cloud platforms (AWS, GCP, Azure), so there is no vendor lock in apart from obviously the database choice, and you aren’t tied to the availability of any one platform.

Note that the marketing might make you think it's a drop in replacement for Postgres, and while it is very impressive, it lacks many common features and some are just different enough to be confusing.

The first difference I ran into was ALTER TABLE commands could not modify the datatype of a column. (It should be added soon.)

Your app probably won't "just work" -- yet.

But IMO this is the coolest open source database with a bright future.

We haven't tried cockroach yet because we are still on RethinkDB and are scarred by the experience of them going out of business.

I hope someday I'll be comfortable enough to experiment and use something other than postgres.

Such a disappointment RethinkDB ended up being. Converted over my side projects quickly after that went South.

Non paywall link - https://techcrunch.com/2020/05/05/cockroach-labs-scores-86-6...

Aside from growth-oriented activities, are they in the black yet? Like could they be sustainable if needed?