5 ms·

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/t

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

by buro9 6y ago

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.