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.
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.
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.