News for hackers. Stuff that matters.
Re:Why CockroachDB and PostgreSQL Are Compatible by latch on Thursday December 17, 2020 @07:50AM (#25455093)
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

Re:Why CockroachDB and PostgreSQL Are Compatible by jpgvm on Thursday December 17, 2020 @07:59AM (#25455154)
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.

Re:Why CockroachDB and PostgreSQL Are Compatible by latch on Thursday December 17, 2020 @08:37AM (#25455415)
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.
Re:Why CockroachDB and PostgreSQL Are Compatible by kitotik on Thursday December 17, 2020 @01:44PM (#25458764)
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.

Re:Why CockroachDB and PostgreSQL Are Compatible by latch on Thursday December 17, 2020 @07:45PM (#25462447)
We just use Postgrex directly. Not sure about Ecto.
Re:Why CockroachDB and PostgreSQL Are Compatible by mikeyjk on Thursday December 17, 2020 @10:06AM (#25456174)
I'd love to know why you decided to make the move if you have the energy to summarise it.
Re:Why CockroachDB and PostgreSQL Are Compatible by latch on Thursday December 17, 2020 @07:58PM (#25462538)
Sure. I'm a developer and also do our devops. We're baremetal. Setting up an ELK stack, log ingestion, openresty, gitlab, CI, rabbitmq, etc: no problem. Making our applications hot-deploy and HA: no problem.

When we ran PostgreSQL, I was using barman for PITR and that was fine. But getting PostgreSQL in HA? My confidence in relation to how critical that piece is? Nope. Even just trying to handle upgrades with no downtime scared me. If I had to do it, I'd look at repmgr.

The story is completely different with CockroachDB. We have 3 instances and taking down an instance (or the server it's on) for maintenance is no problem. The setup also probably couldn't be easier. Most importantly, if an instance goes down in the middle of the night: our app keeps working.

Re:Why CockroachDB and PostgreSQL Are Compatible by radub on Thursday December 17, 2020 @10:31AM (#25456426)
Hi, regarding number 3, I have filed https://github.com/cockroachdb/cockroach/issues/58032. Would love to get your feedback on the proposal; does it address your need? do you have a different idea for how it would work?