Can I just expect things like full text search to work? https://www.postgresql.org/docs/13/textsearch.html
What about additional but supplied modules like ltree? 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... recently and damn it looks close... has anyone migrated a prod system that uses the above? What did you encounter?
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)
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.
I’m about to start a new project, and would very much like to utilize an existing cluster.
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.
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.
That is literally explained in more than 40% of the article.