9 ms·

Why CockroachDB and PostgreSQL Are Compatible

160 points by gesaint 6y ago

buro9 6y ago
How compatible? Can I just expect things like full text search to work? https://www.postgresql.org/docs/13/textsearch.html https://www.postgresql.org/docs/13/textsearch.html What about additional but supplied modules like ltree? https://www.postgresql.org/docs/current/ltree.html https://www.postgresql.org/docs/current/ltree.html I ask as I saw a related article https://www.cockroachlabs.com/blog/full-text-indexing-search/ https://www.cockroachlabs.com/blog/full-text-indexing-search... recently and damn it looks close... has anyone migrated a prod system that uses the above? What did you encounter?
- andy_ppp 6y ago
  Be very careful - even if things are compatible, in my experience some things do not perform as well in CockroachDB which seems a bit counter intuitive but can be very true... We had problems making date range queries fast with tiny amounts of data, for example. I would seriously consider if you need CockroachDB - if you need that level of scaling you should also consider Cassandra (or Cassandra like) solutions as they will scale better but you will have to architect your app to think in this way (i.e. your app generates the views of the data it needs). If you don't need to scale yet use Postgres. Every piece of complexity has a cost and Cockroach while incredibly clever adds complexity that you might not understand or desire to manage over time. A nice interface won't help you when replication between clusters breaks in production.
  - nix23 6y ago
    True, and you can go really far with postgresql alone, like zalado did (biggest german e-commerce platform): https://github.com/zalando/patroni https://github.com/zalando/patroni
  - hordeallergy 6y ago
    It's not just about (big) scalability - redundancy. Cockroach 3 node cluster is a no brainer to setup, just runs. I see cockroach as far easier to work with than Cassandra, and more easily expanded than postgres - sits very well between the two.
    - deleted 6y ago
      [deleted]
  - deleted 6y ago
    [deleted]
- knz42 6y ago
  > How compatible? That is literally explained in more than 40% of the article.
- latch 6y ago
  We migrated a medium-to-large (in terms of model complexity, not rows), from PG to CR about a year ago. It went pretty smoothly, but you'll definetly run into issues. (Very happy overall, btw) Every release has improved the compatibility story at an impressive rate. The 20.2 released added partial indexes and enums, which helped close the gap in our app(though, enums don't support binary encoding yet, so might not work with your pg driver (1)) Things that are still an issue for us (all can be worked around): 1 - Can't defer foreign key checks 2 - No pg_trgm 3 - Can't tell if an upsert was an insert or an update 4 - No triggers (this is pretty huge) (1) https://github.com/cockroachdb/cockroach/issues/57348 https://github.com/cockroachdb/cockroach/issues/57348
  - jpgvm 6y ago
    PostgreSQL enums don't have a binary encoding AFAIK. This is partially why they aren't considered that useful in many contexts vs a fact table. From memory this is because there is no natural binary encoding, you could use the int number of the member in the enum but then you would need to communicate the enum members to the client in some way. PostgreSQL network protocol currently doesn't have such out of band information/metadata support.
    - latch 6y ago
      Whatever the issue, it's compatibility related, as Elixir's PostgreSQL driver (Postgrex) works with PostgreSQL enums but not CockroachDB's enums, and it has to do how the driver encodes the values.
      - kitotik 6y ago
        Are Postgrex/Ecto usable with cockroach now? I’m about to start a new project, and would very much like to utilize an existing cluster.
        latch 6y ago
        We just use Postgrex directly. Not sure about Ecto.
- Rapzid 6y ago
  They used to have a huge caveat on the comparability page that cockroachdb only ran serializable transaction isolation. That's a huge caveat that appears to be gone from the page, but I don't believe gone from the solution. The default postgres isolation level is famously READ COMMITTED.
nix23 6y ago
If you want to use the CockroachDB be aware of the license: https://www.cockroachlabs.com/docs/stable/licensing-faqs.html https://www.cockroachlabs.com/docs/stable/licensing-faqs.htm...
- samblr 6y ago
  (From the FAQs) How does the change to the BSL affect me as a CockroachDB user? It likely does not. As a CockroachDB user, you can freely use CockroachDB or embed it in your applications (irrespective of whether you ship those applications to customers or run them as a service). The only thing you cannot do is offer CockroachDB as a service without buying a license. - - - - - Cockroach is asking not to take open source and start offering it as a service. Which sounds like completely reasonable thing to do! Of late there has been plenty of fear mongering when it comes to licensing. I wonder if this is some paid blogs from cloud companies which helps form some opinions or the age old open source license-and-principles which hold little water with predatory cloud companies around.
  - nix23 6y ago
    >Cockroach is asking not to take open source and start offering it as a service. Which sounds like completely reasonable thing to do! But i just wrote "be aware of it", with that license i don't consider it "Free Software", just imagine Apache or Nginx would do that. On the other hand I'm totally fine if they want to protect themself from leeches like Amazon/Google/Oracle/IBM.
    - bostik 6y ago
      > imagine Apache or Nginx would do that. Nginx will likely do something similar, very soon. It's not that long ago when F5 bought them, with clear plans to offer paid-only features while the core itself remains open. Give it another 5 years and I expect nginx to propagate some of those special features to the open-source version, but with the restriction that you can't offer them as a service vendor without a specific license.
      - 6y ago
defnotanai 6y ago
I am truly looking forward for CockroachDB to become the next PostgreSQL for planet-spanning database workloads. In our ecosystem we get more and more requests for CRDB integration. Generally though, I would not say that full compatibility should even be desired. A k/v database simply works differently from a strictly relational database. There are things like shard IDs, avoiding hot spots, deciding on how to paginate data. Going too much into "PostgreSQL" replacement will eventually hurt CRDB because too much focus will go into making legacy enterprise SQL (along the lines of https://news.ycombinator.com/item?id=25454635 https://news.ycombinator.com/item?id=25454635) work on this system. It's the forklift approach of moving to the cloud. This becomes clear when skimming through the docs: - https://www.cockroachlabs.com/blog/how-to-choose-db-index-keys/ https://www.cockroachlabs.com/blog/how-to-choose-db-index-ke... - https://www.cockroachlabs.com/docs/stable/performance-best-practices-overview.html https://www.cockroachlabs.com/docs/stable/performance-best-p... - https://www.cockroachlabs.com/docs/stable/limit-offset.html https://www.cockroachlabs.com/docs/stable/limit-offset.html - https://www.cockroachlabs.com/docs/v20.2/selection-queries#pagination-example https://www.cockroachlabs.com/docs/v20.2/selection-queries#p... I think many of the "light SQL" patterns are really great - things like SELECT or WHERE, which e.g. DynamoDB can simply not solve without ElasticSearch. But all in all, I am very excited for CRDB to get more industry acceptance, and I think their cloud offering could also become very interesting - a competitor to Google BigTable / CloudSpanner, AWS DynamoDB or Cloud SQL.
- jolux 6y ago
  CockroachDB is not a key-value store though.
  - gred 6y ago
    Unless I misunderstood the docs, it is a KV store underneath the abstraction layers: > At the highest level, CockroachDB converts clients' SQL statements into key-value (KV) data, which is distributed among nodes and written to disk. https://www.cockroachlabs.com/docs/stable/architecture/overview.html https://www.cockroachlabs.com/docs/stable/architecture/overv...
mauvehaus 6y ago
TL;DR: Because the Postgres folks have outstanding taste, even better documentation, a good community, and a compatible license. Which are all the same reasons that it's my goto database. I don't build software anymore, but the last project I worked on for money was a query engine, and the project founders were pretty open about the fact that whenever they were unclear about the behavior required by the SQL spec, they looked at the PostgreSQL docs and behavior to get pointed in the right direction.
- MrBuddyCasino 6y ago
  OT: your woodworking is amazing, always enjoyable to see someone transition to a more fulfilling line of work.
  - mauvehaus 6y ago
    Thanks!
ithrow 6y ago
The title is deceitful since it is not really compatible.
- Supermancho 6y ago
  Not every postgresql installation can perform every postrgresql feature (via plugins). That doesn't make postgresql incompatible with itself. I use a postgresql connector to execute some postgresql specific sql statement on CockroachDB. That's a baseline quality for "compatible".
- rafiss 6y ago
  We do have compatibility gaps -- some big, some small. But I would still call it compatible because it's definitely close enough to use a PostgreSQL driver with it in production. I would be curious to hear your opinion on which incompatibilities are most important to address.
single-node 6y ago
I wonder if it's better in a non sharded use of something like Cockroach to push the replication and HA outside the database, let the control plane(e.g. kubernetes) handle failure and restarts on different nodes, and let something like Ceph with it's efficient write path and replication handle data durability.
- knz42 6y ago
  You need to integrate transaction coordination with replication to ensure that the replication respects transaction atomicity (so that cross-shard queries get all their reads and writes isolated from each other, and rolled back atomically when a txn is aborted). So separating the layers like you do is only possible if there is an XA protocol between the layers. Neither K8s nor Ceph support that.
austinpena 6y ago
This may be the wrong place to ask this but something about Databases and Pebble DB has been making me curious, and I’ve been loving reading what CDB puts out. For a single node, strictly K/V workload, does CDB offer any advantages over Pebble? Does pebble have similar concurrent write issues like an SQLite? I wouldn’t think so because it’s split into multiple files. Then one step further, again for a purely KV workload, if all I need to do is add/delete/update/find a key, would a solution like Couchbase be useful compared to Pebble? If you even linked an article I would love a starting point.
muxator 6y ago
> As the team learned the hard way in the ramp-up to CockroachDB 1.0, many developers in the ecosystems that CockroachDB wants to enter do not write their own SQL queries any more—as opposed to, e.g., ten or twenty years ago. I cannot help but being saddened by this. It is really hard to understand how not knowing how to interface directly to the part of the system that holds the ultimate reason we write a program (handling data), and probably the performance bottleneck of that system when it scales, is beneficial to a professional developer.
- ozim 6y ago
  You assume that all those people don't know and don't understand how to interface directly. Maybe they do know, but different approach turns out more productive for them. For such "no true Scotsman" argument that "real developers write their SQL" I really like "ad absurdum" that "real developers write in machine code".

160 points by gesaint 6y ago

How compatible? Can I just expect things like full text search to work? https://www.postgresql.org/docs/13/textsearch.html https://www.postgresql.org/docs/13/textsearch.html What about additional but supplied modules like ltree? https://www.postgresql.org/docs/current/ltree.html https://www.postgresql.org/docs/current/ltree.html I ask as I saw a related article https://www.cockroachlabs.com/blog/full-text-indexing-search/ https://www.cockroachlabs.com/blog/full-text-indexing-search... recently and damn it looks close... has anyone migrated a prod system that uses the above? What did you encounter?

andy_ppp 6y ago
Be very careful - even if things are compatible, in my experience some things do not perform as well in CockroachDB which seems a bit counter intuitive but can be very true... We had problems making date range queries fast with tiny amounts of data, for example. I would seriously consider if you need CockroachDB - if you need that level of scaling you should also consider Cassandra (or Cassandra like) solutions as they will scale better but you will have to architect your app to think in this way (i.e. your app generates the views of the data it needs). If you don't need to scale yet use Postgres. Every piece of complexity has a cost and Cockroach while incredibly clever adds complexity that you might not understand or desire to manage over time. A nice interface won't help you when replication between clusters breaks in production.
- nix23 6y ago
  True, and you can go really far with postgresql alone, like zalado did (biggest german e-commerce platform): https://github.com/zalando/patroni https://github.com/zalando/patroni
- hordeallergy 6y ago
  It's not just about (big) scalability - redundancy. Cockroach 3 node cluster is a no brainer to setup, just runs. I see cockroach as far easier to work with than Cassandra, and more easily expanded than postgres - sits very well between the two.
  - deleted 6y ago
    [deleted]
- deleted 6y ago
  [deleted]
knz42 6y ago
> How compatible? That is literally explained in more than 40% of the article.
latch 6y ago
We migrated a medium-to-large (in terms of model complexity, not rows), from PG to CR about a year ago. It went pretty smoothly, but you'll definetly run into issues. (Very happy overall, btw) Every release has improved the compatibility story at an impressive rate. The 20.2 released added partial indexes and enums, which helped close the gap in our app(though, enums don't support binary encoding yet, so might not work with your pg driver (1)) Things that are still an issue for us (all can be worked around): 1 - Can't defer foreign key checks 2 - No pg_trgm 3 - Can't tell if an upsert was an insert or an update 4 - No triggers (this is pretty huge) (1) https://github.com/cockroachdb/cockroach/issues/57348 https://github.com/cockroachdb/cockroach/issues/57348
- jpgvm 6y ago
  PostgreSQL enums don't have a binary encoding AFAIK. This is partially why they aren't considered that useful in many contexts vs a fact table. From memory this is because there is no natural binary encoding, you could use the int number of the member in the enum but then you would need to communicate the enum members to the client in some way. PostgreSQL network protocol currently doesn't have such out of band information/metadata support.
  - latch 6y ago
    Whatever the issue, it's compatibility related, as Elixir's PostgreSQL driver (Postgrex) works with PostgreSQL enums but not CockroachDB's enums, and it has to do how the driver encodes the values.
    - kitotik 6y ago
      Are Postgrex/Ecto usable with cockroach now? I’m about to start a new project, and would very much like to utilize an existing cluster.
      - latch 6y ago
        We just use Postgrex directly. Not sure about Ecto.
Rapzid 6y ago
They used to have a huge caveat on the comparability page that cockroachdb only ran serializable transaction isolation. That's a huge caveat that appears to be gone from the page, but I don't believe gone from the solution. The default postgres isolation level is famously READ COMMITTED.

nix23 6y ago

If you want to use the CockroachDB be aware of the license: https://www.cockroachlabs.com/docs/stable/licensing-faqs.html https://www.cockroachlabs.com/docs/stable/licensing-faqs.htm...

samblr 6y ago
(From the FAQs) How does the change to the BSL affect me as a CockroachDB user? It likely does not. As a CockroachDB user, you can freely use CockroachDB or embed it in your applications (irrespective of whether you ship those applications to customers or run them as a service). The only thing you cannot do is offer CockroachDB as a service without buying a license. - - - - - Cockroach is asking not to take open source and start offering it as a service. Which sounds like completely reasonable thing to do! Of late there has been plenty of fear mongering when it comes to licensing. I wonder if this is some paid blogs from cloud companies which helps form some opinions or the age old open source license-and-principles which hold little water with predatory cloud companies around.
- nix23 6y ago
  >Cockroach is asking not to take open source and start offering it as a service. Which sounds like completely reasonable thing to do! But i just wrote "be aware of it", with that license i don't consider it "Free Software", just imagine Apache or Nginx would do that. On the other hand I'm totally fine if they want to protect themself from leeches like Amazon/Google/Oracle/IBM.
  - bostik 6y ago
    > imagine Apache or Nginx would do that. Nginx will likely do something similar, very soon. It's not that long ago when F5 bought them, with clear plans to offer paid-only features while the core itself remains open. Give it another 5 years and I expect nginx to propagate some of those special features to the open-source version, but with the restriction that you can't offer them as a service vendor without a specific license.
    - 6y ago

defnotanai 6y ago

I am truly looking forward for CockroachDB to become the next PostgreSQL for planet-spanning database workloads. In our ecosystem we get more and more requests for CRDB integration. Generally though, I would not say that full compatibility should even be desired. A k/v database simply works differently from a strictly relational database. There are things like shard IDs, avoiding hot spots, deciding on how to paginate data. Going too much into "PostgreSQL" replacement will eventually hurt CRDB because too much focus will go into making legacy enterprise SQL (along the lines of https://news.ycombinator.com/item?id=25454635 https://news.ycombinator.com/item?id=25454635) work on this system. It's the forklift approach of moving to the cloud. This becomes clear when skimming through the docs: - https://www.cockroachlabs.com/blog/how-to-choose-db-index-keys/ https://www.cockroachlabs.com/blog/how-to-choose-db-index-ke... - https://www.cockroachlabs.com/docs/stable/performance-best-practices-overview.html https://www.cockroachlabs.com/docs/stable/performance-best-p... - https://www.cockroachlabs.com/docs/stable/limit-offset.html https://www.cockroachlabs.com/docs/stable/limit-offset.html - https://www.cockroachlabs.com/docs/v20.2/selection-queries#pagination-example https://www.cockroachlabs.com/docs/v20.2/selection-queries#p... I think many of the "light SQL" patterns are really great - things like SELECT or WHERE, which e.g. DynamoDB can simply not solve without ElasticSearch. But all in all, I am very excited for CRDB to get more industry acceptance, and I think their cloud offering could also become very interesting - a competitor to Google BigTable / CloudSpanner, AWS DynamoDB or Cloud SQL.

jolux 6y ago
CockroachDB is not a key-value store though.
- gred 6y ago
  Unless I misunderstood the docs, it is a KV store underneath the abstraction layers: > At the highest level, CockroachDB converts clients' SQL statements into key-value (KV) data, which is distributed among nodes and written to disk. https://www.cockroachlabs.com/docs/stable/architecture/overview.html https://www.cockroachlabs.com/docs/stable/architecture/overv...

mauvehaus 6y ago

TL;DR: Because the Postgres folks have outstanding taste, even better documentation, a good community, and a compatible license. Which are all the same reasons that it's my goto database. I don't build software anymore, but the last project I worked on for money was a query engine, and the project founders were pretty open about the fact that whenever they were unclear about the behavior required by the SQL spec, they looked at the PostgreSQL docs and behavior to get pointed in the right direction.

MrBuddyCasino 6y ago
OT: your woodworking is amazing, always enjoyable to see someone transition to a more fulfilling line of work.
- mauvehaus 6y ago
  Thanks!

ithrow 6y ago

The title is deceitful since it is not really compatible.

Supermancho 6y ago
Not every postgresql installation can perform every postrgresql feature (via plugins). That doesn't make postgresql incompatible with itself. I use a postgresql connector to execute some postgresql specific sql statement on CockroachDB. That's a baseline quality for "compatible".
rafiss 6y ago
We do have compatibility gaps -- some big, some small. But I would still call it compatible because it's definitely close enough to use a PostgreSQL driver with it in production. I would be curious to hear your opinion on which incompatibilities are most important to address.

single-node 6y ago

I wonder if it's better in a non sharded use of something like Cockroach to push the replication and HA outside the database, let the control plane(e.g. kubernetes) handle failure and restarts on different nodes, and let something like Ceph with it's efficient write path and replication handle data durability.

knz42 6y ago
You need to integrate transaction coordination with replication to ensure that the replication respects transaction atomicity (so that cross-shard queries get all their reads and writes isolated from each other, and rolled back atomically when a txn is aborted). So separating the layers like you do is only possible if there is an XA protocol between the layers. Neither K8s nor Ceph support that.

austinpena 6y ago

This may be the wrong place to ask this but something about Databases and Pebble DB has been making me curious, and I’ve been loving reading what CDB puts out. For a single node, strictly K/V workload, does CDB offer any advantages over Pebble? Does pebble have similar concurrent write issues like an SQLite? I wouldn’t think so because it’s split into multiple files. Then one step further, again for a purely KV workload, if all I need to do is add/delete/update/find a key, would a solution like Couchbase be useful compared to Pebble? If you even linked an article I would love a starting point.

muxator 6y ago

> As the team learned the hard way in the ramp-up to CockroachDB 1.0, many developers in the ecosystems that CockroachDB wants to enter do not write their own SQL queries any more—as opposed to, e.g., ten or twenty years ago. I cannot help but being saddened by this. It is really hard to understand how not knowing how to interface directly to the part of the system that holds the ultimate reason we write a program (handling data), and probably the performance bottleneck of that system when it scales, is beneficial to a professional developer.

ozim 6y ago
You assume that all those people don't know and don't understand how to interface directly. Maybe they do know, but different approach turns out more productive for them. For such "no true Scotsman" argument that "real developers write their SQL" I really like "ad absurdum" that "real developers write in machine code".