Doing so would require ElasticSearch to reach consensus on every read/write, which would remove most of the point of a distributed cluster. Despite this, ZomboDB's documentation says "complex aggregate queries can be answered in parallel across your ElasticSearch cluster".
They also claim that transactions will abort if ElasticSearch runs into network trouble, but the ElasticSearch documentation notes that writes during network partitions don't wait for confirmation of success[1], so I'm not sure how they would be able to detect that.
In short: I'll wait for the Jepsen analysis.
[1] https://www.elastic.co/blog/tracking-in-sync-shard-copies#:~...
ZomboDB only requires that ES have a view of its index that's consistent with the active Postgres transaction snapshot. ZDB handles this by ensuring that the ES index is fully refreshed after writes.
This doesn't necessarily make ZDB great for high-update loads, but that's not ZDB's target usage.
> They also claim that transactions will abort if ElasticSearch runs into network trouble...
I had to search my own repo to see where I make this claim. I don't. I do note that network failures between PG & ES will cause the active Postgres xact to abort. On top of that, any error that ES is capable of reporting back to the client will cause the PG xact to abort -- ensuring consistency between the two.
Because the ES index is properly refreshed as it relates to the active Postgres transaction, all of ES' aggregate search functions are capable of providing proper MVCC-correct results, using the parallelism provided by the ES cluster.
I don't have the time to detail everything that ZDB does to project Postgres xact snapshots on top of ES, but the above two points are the easy ones to solve.
While I'm here might I ask, are you finding the hosted PostgreSQL services (AWS, Azure, etc.) growing or shrinking your market opportunities? Also, does it play nice with Citus?
ZDB is still alive and well but I have a real job now too.
> As such, any sort of failure either with ZomboDB itself, between Postgres and Elasticsearch (network layer), or within Elasticsearch will cause the operating Postgres transaction to ABORT. [1]
In my defense, there is a fairly important distinction between "any error that ES is capable of reporting back" and "any sort of failure within Elasticsearch".
That said, I and trust that you're more familiar with consistency levels than me, so I'll bow out here.
"The only limit, is yourself…"
It’s a difference between having separate OLAP and OLTP databases, which is what the parent post suggested.
For example, if a place has developed fairly good knowledge of PG already, they can "just" (!) continue developing their PG knowledge.
Adding ES into the mix though, introduces a whole new thing that needs to be learned, optimised, etc.
You CAN score results using setweight, although it's likely not as sophisticated as Elasticsearch's
https://www.postgresql.org/docs/9.1/textsearch-controls.html
Disclaimer: I use Postgres fulltext search in production, very happy with it although maintaining the various triggers and stored procs it requires to work becomes cumbersome whenever you have to write a migration that alters any of them (or that in fact touches any related field, as you may be required to drop and recreate all of the parts in order not to violate referential integrity)
It is certainly nice having not to worry about 1 additional dependency when deploying, though
There are in my mind two reasons to not use PG's search.
1. Elasticsearch allows you to build sophisticated linguistic and feature scoring pipelines to optimize your search quality. This is not a typical use case in PG.
2. Your primary database is usually your scaling bottleneck even without adding a relatively expensive search workload into the mix. A full-text search tends to be around as expensive as a 5% table scan of the related table. Most DBAs don't like large scan workloads.