Hi @RainTown,
Thanks for the constructive friction 
Our biggest technical problems are not related to ES, but the biggest technical problems related to ES are:
- People don't trust the data (they've been burned enough times that they open support tickets to perform exports of data because the UI isn't showing them right/complete data)
- Enormous amounts of resources spent / Yak shaving to 'stay on top' of data issues.
- Managing dev/staging/preprod environments is hard from a data perspective, i.e. copying data or streaming changes is not built-in at the DB level. They haven't invested in proper seeding scripts, nor ETL + anonymization, so it's kind of a mess. There's a variety of tools/extensions I've used for this in the past with PG in particular.
The reasons those haven't been solved already are:
- No technical employees on staff that is qualified to make executive-level technical decisions, so the architecture is made by external contractors that are each trying to improve their part of the puzzle, but nothing unified
- They've been talking about moving away from this platform for years (a lot of frustration is due to it being poorly hosted in a colo), so they haven't invested in core problems.
Every technical colleague I've discussed with is flabbergasted that ES is used as the primary datastore for this kind of project.
The migration to the hosted SaaS CRM failed because the systems integrator promised ERP features they couldn't deliver on... I'm not surprised given the architecture of the destination platform, but it's unrelated to the technical aspects of the current system.
As I acknowledged in my response to @john-wagster, the use of the word 'insane' is inappropriate/unhelpful, sorry for polluting this discussion with my (strong) frustrations. I am anticipating a board that wants 'quick results' when there's a foundational problem (as I perceive it) requiring a significant investment (migrating to PG), which is why I'm looking for validation (or differing opinions) on this forum.
Also, the quote about "Elasticsearch data doesn't persist restarts" was absolutely not something I'm giving credence to, I promise 
but it is indicative that Elasticsearch's reputation is not "bulletproof data store", but instead "great search tool".
I have no doubts about Elasticsearch's underlying technology, teams, nor product. I've always heard good things about it's ability to deliver on things like logging + search, search ranking problems, etc.
I assure you I understand the difference between the HTTP response code and the content... but I also assure you I'm working with an application built by people who do not. I checked in the codebase, the _shards metadata is not currently used to verify query completeness. My position is that when you're developing an application that requires heavy business domain knowledge, from a management standpoint, I don't want to make choices that add a cognitive burden to the developers, i.e. they shouldn't need to understand the subtleties of the underlying infra when performing basic document fetching. We're not needing to optimize for blistering performance, so I want developers to work in an environment where they can assume a successful response is what it seems to be.
If I may push back gently on something you said, relational databases are more than just 'really cool'... they're a foundational technology for almost every modern service that requires authoritative records. Travel, Finance, Logistics, Healthcare, Telco, Governments, Insurance, and more are all built on relational databases. I'm at the helm of this platform that wants to provide invoicing, event registration + attendance verification, event management, some algorithmic stuff (combinatorial problems), some basic search, and tons and tons of CRUD/CMS backend work.
Even the fact that updating the same document causes a reindex... and all we do is manage member contact info, event attendance/registration, descriptions of events, etc. This seems like such a massive architectural mis-match between our use-case and how ES works fundamentally!
I'm open to staying on ES, especially if someone with an abundance of experience in a related project (relational business + ERP workflows) can show us what practices are creating the current problems, and what non-hack/non-custom solutions exist to solve them.
Currently, it seems we're running up against ES's weaknesses, without benefitting from its strengths.