Every release has brought the function parity closer to PostgreSQL. When we first migrated, it didn't have enums or partial indexes, or (a rather long list of thing).
Now, I'd say there are 3 big missing features:
- Extensions, if you rely on a specific extension, you need to check if CockroachDB has built-in support for it.
- Full text search (we had very simple search needs (e.g. names, emails) and just stored trigrams into the database, and it worked ok)
- Triggers (and more generally, procedures/functions).
Queries will generally execute slower than PostgreSQL - whether we're talking about fetching a row by id, or doing an aggregate. Maybe the performance table flips at a huge scale where PostgreSQL can't even keep up, I don't know.
It's incredibly easy to manage and setup in a properly working HA environment. I was managing 2 PostgreSQL servers each with their own read replicas and a 3-node CockroachDB cluster. Doing upgrades to the PostgreSQL servers always involved downtime. Doing upgrades to CRDB was done without downtime and was much easy to automate.
When we started using CockroachDB, the free version barely had working backups. This has improved, but for a database built around resilience, the free offering still has serious shortcomings. Namely, you can't have read replicas or delayed read replicas and you can't do incremental backups or use other approaches that can facilitate PITR. So ya, you're protected against server failure, can easily upgrade and expand the cluster, but there's a class of errors/failures where (the free version at least), has ZERO solution.
Overall, I'd strongly recommend it, but I'd first check if any of the "fancy" PG/extension that I need for a specific project are available. I'd also do some performance testing on the specific schema/types of queries.
Thanks for the great feedback latch! I'm a Director of Engineering supporting the SQL implementation at Cockroach Labs and couldn't resist responding to your comment as we're aware of some of these limitations and are working on lifting them.
- CockroachDB 22.2 will support `LANGUAGE SQL` user-defined functions when it is released in the next few weeks. Have a look at the documentation: https://www.cockroachlabs.com/docs/dev/user-defined-function...
- Triggers and PL/pgSQL support are both on the roadmap.
- Postgres-compatible full-text search (tsvector/tsquery) is in development and we hope to land a preview in CockroachDB 23.1 (expected in the Spring of 2023).
Extensions-wise, we typically add support for them as users ask. Which Extensions do you typically need for a specific project that CockroachDB doesn't support?
There's none that I need, but I think it's worth pointing out to anyone who asks about CRDB. There are many available extensions for PostgreSQL, so if you happen to rely on one of them, you might need to think about how you'll do that with CockroachDB. Obviously the new-ish support for spatial data was one of the most obvious, but even in this case, if you rely heavily on Postgis, you'll need to make sure CockroachDB supports everything you need.
We are interested in a db system that can run adjacent to a CephFS external BLOB store, and are analyzing CRDB for comparable features (specifically data snapshots to offline mirrors, and time-shifting state for forensic auditing).
Transparent tuple salted-hash (like ephemeral seed) signing would also be nice for detecting bit-rot on backups. ;)
Now, I'd say there are 3 big missing features:
- Extensions, if you rely on a specific extension, you need to check if CockroachDB has built-in support for it.
- Full text search (we had very simple search needs (e.g. names, emails) and just stored trigrams into the database, and it worked ok)
- Triggers (and more generally, procedures/functions).
Queries will generally execute slower than PostgreSQL - whether we're talking about fetching a row by id, or doing an aggregate. Maybe the performance table flips at a huge scale where PostgreSQL can't even keep up, I don't know.
It's incredibly easy to manage and setup in a properly working HA environment. I was managing 2 PostgreSQL servers each with their own read replicas and a 3-node CockroachDB cluster. Doing upgrades to the PostgreSQL servers always involved downtime. Doing upgrades to CRDB was done without downtime and was much easy to automate.
When we started using CockroachDB, the free version barely had working backups. This has improved, but for a database built around resilience, the free offering still has serious shortcomings. Namely, you can't have read replicas or delayed read replicas and you can't do incremental backups or use other approaches that can facilitate PITR. So ya, you're protected against server failure, can easily upgrade and expand the cluster, but there's a class of errors/failures where (the free version at least), has ZERO solution.
Overall, I'd strongly recommend it, but I'd first check if any of the "fancy" PG/extension that I need for a specific project are available. I'd also do some performance testing on the specific schema/types of queries.